Pass DataTable in Stored Procedure Parameter in .Net

Now here in this tutorial, I’ll explain how you can pass datatable in stored procedure parameter in asp.net using c# or vb.net with example code and step by step guide.

In my previous tutorials, I’d explained passing table valued parameters in stored procedure in sql server, get gridview selected row hiddenfield value using jquery, exporting gridview data to word excel text or pdf and other more cracking tutorials on Asp.net, SQL Server here.

To pass Table Valued Parameter or DataTable as a SP Parameter, we need to split the task into two steps.

1. In first step, we need to create a SQL Table, Type Table and Stored Procedure in your SQL database. So I recommend you to check my previous tutorial on how to pass table valued parameter as a stored procedure parameter in sql server.

2. In second step, you need to pass TVP or DataTable as a SP parameter from your .NET application.

I guess you completed the first step and created a stored procedure as per your requirements. Now we need to go ahead and go for the step 2.

ADO.NET – Pass DataTable In Stored Procedure Parameter – [C#/Vb]

The following code generates a DataTable that will be used as our Table Valued Parameter and used by the stored procedure that we had created in our first step.

If you are using C# as code-behind, use the following code:
First add the following namespace:

using System.Data;
using System.Data.SqlClient;

After that add the following code in .aspx.cs code-behind file to pass datatable in stored procedure parameter:

protected void Page_Load(object sender, EventArgs e) {
    DataTable dtResult = new DataTable("Student Result");
    //Creating Column
    dtResult.Columns.Add("SubjectID", typeof(int));
    dtResult.Columns.Add("SubjectName", typeof(string));
    dtResult.Columns.Add("Marks", typeof(int));
    dtResult.Columns.Add("Grade", typeof(string));

    //Adding Subjects in column
    dtResult.Rows.Add(1, "Asp.net", 97, "A+");
    dtResult.Rows.Add(2, "C#", 89, "A");
    dtResult.Rows.Add(3, "Vb.net", 86, "B+");

    //Executing DML Operation using ADO.NET & SQL Stored Procedure
    string con = @"Data Source=datasource;Integrated Security=true;Initial Catalog=dbname";
    using (SqlConnection sqlConn = new SqlConnection(con)) {
        using (SqlCommand sqlCmd = new SqlCommand("sp_TVPInsertResult", sqlConn)) {
            sqlConn.Open();
            sqlCmd.CommandType = CommandType.StoredProcedure;
            //Passing @TVP as Table Valued Parameter to SQL Stored Procedure
            SqlParameter sqlParameter = sqlCmd.Parameters.AddWithValue("@TVP", dtResult);
            //Following statement tells ADO.NET that we are passing Table Valued Parameter
            sqlParameter.SqlDbType = SqlDbType.Structured;
            int retVal = sqlCmd.ExecuteNonQuery();
            sqlConn.Close();
            if (retVal > 0) {
                //SUCCESS: Rows Successfully Inserted into Table
            } else {
                //Error: Error while Inserting records into Table
            }
        }
    }
}

If you are using Vb.net as code-behind, use following code:
First add the following namespace:

Imports System.Data
Imports System.Data.SqlClient

After that add the following code in .aspx.vb code-behind file to pass datatable in stored procedure parameter:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    Dim dtResult As New DataTable("Student Result")
    'Creating Column'
    dtResult.Columns.Add("SubjectID", GetType(Integer))
    dtResult.Columns.Add("SubjectName", GetType(String))
    dtResult.Columns.Add("Marks", GetType(Integer))
    dtResult.Columns.Add("Grade", GetType(String))

    'Adding Subjects in column'
    dtResult.Rows.Add(1, "Asp.net", 97, "A+")
    dtResult.Rows.Add(2, "C#", 89, "A")
    dtResult.Rows.Add(3, "Vb.net", 86, "B+")

    'Executing DML Operation using ADO.NET & SQL Stored Procedure'
    Dim con As String = "Data Source=datasource;Integrated Security=true;Initial Catalog=dbname"
    Using sqlConn As New SqlConnection(con)
        Using sqlCmd As New SqlCommand("sp_TVPInsertResult", sqlConn)
            sqlConn.Open()
            sqlCmd.CommandType = CommandType.StoredProcedure
            'Passing @TVP as Table Valued Parameter to SQL Stored Procedure'
            Dim sqlParameter As SqlParameter = sqlCmd.Parameters.AddWithValue("@TVP", dtResult)
            'Following statement tells ADO.NET that we are passing Table Valued Parameter'
            sqlParameter.SqlDbType = SqlDbType.Structured
            Dim retVal As Integer = sqlCmd.ExecuteNonQuery()
            sqlConn.Close()
            If retVal > 0 Then
                'SUCCESS: Rows Successfully Inserted into Table'
            Else
                'Error: Error while Inserting records into Table'
            End If
        End Using
    End Using
End Sub

As you can see from the above sample code, we passed sp_TVPInsertResult stored procedure that is created in our first step and pass the DataTable as a @TVP parameter to insert records. After executing the above code, the records under data table will be inserted in your database table. Here in our example, you can check dbo.[Result] table to check all the 3 sample records that are inserted during executing code at a same time as a bulk insert.