In this article I will explain with an example, how to send (pass) DataTable as parameter to Stored Procedure using the SQL Server User Defined Table Type in ASP.Net with C# and VB.Net.
The User Defined Table Type has to be created in SQL Server and it must have the same schema as that of the DataTable that we need to pass to the Stored Procedure as parameter in ASP.Net with C# and VB.Net.
Database
I have made use of the following table Customers with the schema as follows.
![Send (Pass) DataTable as parameter to Stored Procedure in C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
Creating User Defined Table Type
A User Defined Table Type needs to be created in SQL Server using the following query.
CREATE TYPE [dbo].[CustomerType] AS TABLE(
[Id] [int] NULL,
[Name] [varchar](100) NULL,
[Country] [varchar](50) NULL
)
GO
Once the User Defined Table Type is created it will be visible in the Object Explorer as shown below.
![Send (Pass) DataTable as parameter to Stored Procedure in C# and VB.Net]()
Creating Stored Procedure to Pass DataTable as Parameter
The following Stored Procedure needs to be created which will accept the Table of CustomerType Type as parameter.
CREATE PROCEDURE [dbo].[Insert_Customers]
@tblCustomers CustomerType READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Customers(CustomerId, Name, Country)
SELECT Id, Name, Country FROM @tblCustomers
END
HTML Markup
The HTML Markup consists of an ASP.Net GridView with four columns with one TemplateField column and three BoundField columns.
The TemplateField column consists of an ASP.Net CheckBox control which will be used to select Rows to be inserted.
Below the GridView, there is a Button which will be used to insert selected records into Database Table by passing DataTable to the Stored Procedure.
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:CheckBox ID="CheckBox1" runat="server" />
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Id" HeaderText="Id" ItemStyle-Width="30" />
<asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
<asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
</Columns>
</asp:GridView>
<br />
<asp:Button ID="Button1" Text="Bulk Insert" OnClick="Bulk_Insert" runat="server" />
XML File
The following XML file will be used to populate the GridView.
<?xml version="1.0" standalone="yes"?>
<Customers>
<Customer>
<Id>1</Id>
<Name>John Hammond</Name>
<Country>United States</Country>
</Customer>
<Customer>
<Id>2</Id>
<Name>Mudassar Khan</Name>
<Country>India</Country>
</Customer>
<Customer>
<Id>3</Id>
<Name>Suzanne Mathews</Name>
<Country>France</Country>
</Customer>
<Customer>
<Id>4</Id>
<Name>Robert Schidner</Name>
<Country>Russia</Country>
</Customer>
</Customers>
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Populating the GridView
Inside the Page Load event, the GridView is populated using the XML file.
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Customers.xml"));
GridView1.DataSource = ds.Tables[0];
GridView1.DataBind();
}
}
VB.Net
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim ds As New DataSet()
ds.ReadXml(Server.MapPath("~/Customers.xml"))
GridView1.DataSource = ds.Tables(0)
GridView1.DataBind()
End If
End Sub
Passing DataTable as parameter to Stored Procedure in C# and VB.Net
When the Bulk Insert Button is clicked, first a DataTable is created with column schema same as that of the User Defined Table Type that was created and then a loop is executed over the GridView rows.
Inside the loop, the values of the cells of the rows in which the CheckBox is checked are fetched and are inserted into the DataTable.
Finally, the DataTable is passed as Parameter to the Stored Procedure and the Stored Procedure is executed.
C#
protected void Bulk_Insert(object sender, EventArgs e)
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
new DataColumn("Name", typeof(string)),
new DataColumn("Country",typeof(string)) });
foreach (GridViewRow row in GridView1.Rows)
{
if ((row.FindControl("CheckBox1") as CheckBox).Checked)
{
int id = int.Parse(row.Cells[1].Text);
string name = row.Cells[2].Text;
string country = row.Cells[3].Text;
dt.Rows.Add(id, name, country);
}
}
if (dt.Rows.Count > 0)
{
string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlCommand cmd = new SqlCommand("Insert_Customers"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = con;
cmd.Parameters.AddWithValue("@tblCustomers", dt);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
}
VB.Net
Protected Sub Bulk_Insert(sender As Object, e As EventArgs)
Dim dt As New DataTable()
dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Name", GetType(String)), New DataColumn("Country", GetType(String))})
For Each row As GridViewRow In GridView1.Rows
If TryCast(row.FindControl("CheckBox1"), CheckBox).Checked Then
Dim id As Integer = Integer.Parse(row.Cells(1).Text)
Dim name As String = row.Cells(2).Text
Dim country As String = row.Cells(3).Text
dt.Rows.Add(id, name, country)
End If
Next
If dt.Rows.Count > 0 Then
Dim consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(consString)
Using cmd As New SqlCommand("Insert_Customers")
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
cmd.Parameters.AddWithValue("@tblCustomers", dt)
con.Open()
cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
End If
End Sub
Screenshots
GridView with CheckBoxes
![Send (Pass) DataTable as parameter to Stored Procedure in C# and VB.Net]()
Inserted records in Database Table
![Send (Pass) DataTable as parameter to Stored Procedure in C# and VB.Net]()
Downloads
