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.