In this article I will explain with an example, how to insert (save) to SQL Server Table in C# and VB.Net using the SQL Server User Defined Table Type.
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.
 
 
Database
I have created a simple table named Customers whose schema is shown below.
Insert (Save) DataTable to SQL Server Table using C# and VB.Net
Then I have created a User Defined Table Type 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 created the User Defined Type will be visible in the Object Explorer as shown below.
Insert (Save) DataTable to SQL Server Table using C# and VB.Net
Finally the following stored procedure is created which will accept the DataTable as parameter and then will insert all records into the table.
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

Note: The SQL for creating the database is provided in the attached sample code.
 
 
HTML Markup
The HTML Markup consists of an ASP.Net GridView with CheckBox to select rows and a Button for inserting bulk data from GridView to SQL Server Database table.
<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" />
 
 
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
In this article I am populating the GridView using the following XML file in the Page Load event.
<?xmlversion="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>
 
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
 
Insert (Save) DataTable to SQL Server Table using C# and VB.Net
 
 
Passing DataTable as parameter to Stored Procedure in C# and VB.Net
The following event handler is executed on the click of the Button. Here 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 of 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 Stored Procedure is executed with the DataTable passed as parameter to it.
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
 
Insert (Save) DataTable to SQL Server Table using C# and VB.Net
 
 
Downloads