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.SqlClient;
After that add the following code in .aspx.cs code-behind file to pass datatable in stored procedure parameter:
{
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.SqlClient
After that add the following code in .aspx.vb code-behind file to pass datatable in stored procedure parameter:
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.
