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.
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.
         Download SQL file
Creating User Defined Table Type
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
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
    INSERT INTO Customers(CustomerId, Name, Country)
    SELECT Id, Name, Country FROM @tblCustomers
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">
            <asp:CheckBox ID="CheckBox1" runat="server" />
    <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" />
<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"?>
    <Name>John Hammond</Name>
    <Country>United States</Country>
    <Name>Mudassar Khan</Name>
    <Name>Suzanne Mathews</Name>
    <Name>Robert Schidner</Name>
You will need to import the following namespaces.
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
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.
protected void Page_Load(object sender, EventArgs e)
    if (!this.IsPostBack)
        DataSet ds = new DataSet();
        GridView1.DataSource = ds.Tables[0];
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim ds As New DataSet()
        GridView1.DataSource = ds.Tables(0)
    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.
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);
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
    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)
            End Using
        End Using
    End If
End Sub
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