In this article I will explain with an example, how to use SqlBulkCopy to insert bulk data from GridView to database in ASP.Net using C# and VB.Net.
SqlBulkCopy class as the name suggests does bulk insert from one source to another and hence multiple selected rows
from the GridView can be easily read and inserted in SQL Server database table using the SqlBulkCopy class.
 
 

Database

I have made use of the following table Customers with the schema as follows.
Using SqlBulkCopy to insert bulk data from GridView to database in ASP.Net
 
Note: You can download the database table SQL by clicking the download link below.
          Download SQL file
 
 

HTML Markup

The HTML Markup consists of following controls:
GridView – For displaying data.
The GridView consist of a TemplateField column and three BoundField columns.
The TemplateField column consist of an ItemTemplate which consists of a CheckBox.
Button – For inserting selected records.
The Button has been assigned with an OnClick event handlers.
<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Id" HeaderText="Customer Id" ItemStyle-Width="80" />
        <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
        <asp:BoundField DataField="Country" HeaderText="Country" ItemStyle-Width="150" />
    </Columns>
</asp:GridView>
 
 

Namespaces

You will need to import the following namespaces.
C#
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
 
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
 
 

Populating the GridView

The following XML file is used as source of data to populate the GridView.

XML file

<?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>
 
Inside the Page_Load event handler, first an object of DataSet is created and using ReadXml method of DataSet the content of the XML file is read and assigned to the DataSource property of the GridView.
C#
protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("~/Files/Customers.xml"));
        gvCustomers.DataSource = ds.Tables[0];
        gvCustomers.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("~/Files/Customers.xml"))
        gvCustomers.DataSource = ds.Tables(0)
        gvCustomers.DataBind()
    End If
End Sub
 
 

Inserting bulk data from GridView to SQL Server database table in ASP.Net

When the Bulk_Insert button is clicked, an object of DataTable is created and column are defined.
Then, a FOR EACH loop is executed over the GridView rows and a check is performed whether the row CheckBox is checked or not.
And wherever the CheckBox is checked that row data is added as Row to DataTable.
Then, the connection is read from Web.Config file.
Note: For more details on how to read connection string from Web.Config file, please refer my article Read or Write Connection Strings in Web.Config file using ASP.Net using C# and VB.Net.
 
Now a connection is established with the database and the SqlBulkCopy object is initialized and the name of the Table is specified using the DestinationTableName property.
Finally, the columns are mapped and all the rows from the DataTable are inserted into the SQL Server table.
Note: The mapping of columns of the DataTable and the SQL Server table is optional and you need to do only in case where your DataTable and/or the SQL Server Table do not have same number of columns or the names of columns are different.
 
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 gvCustomers.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 constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                //Set the database table name
                 sqlBulkCopy.DestinationTableName = "dbo.Customers";
 
                //[OPTIONAL]: Map the DataTable columns with that of the database table
                sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
                sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                sqlBulkCopy.ColumnMappings.Add("Country", "Country");
                con.Open();
                sqlBulkCopy.WriteToServer(dt);
                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 gvCustomers.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 constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
         Using con As New SqlConnection(constr)
             Using sqlBulkCopy As New SqlBulkCopy(con)
                 'Set the database table name
                  sqlBulkCopy.DestinationTableName = "dbo.Customers"
 
                 '[OPTIONAL]: Map the DataTable columns with that of the database table
                 sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId")
                 sqlBulkCopy.ColumnMappings.Add("Name", "Name")
                 sqlBulkCopy.ColumnMappings.Add("Country", "Country")
                 con.Open()
                 sqlBulkCopy.WriteToServer(dt)
                 con.Close()
             End Using
         End Using
     End If
 End Sub
 
 

Screenshot

GridView with CheckBoxes

Using SqlBulkCopy to insert bulk data from GridView to database in ASP.Net
 

Inserted records in Database Table

Using SqlBulkCopy to insert bulk data from GridView to database in ASP.Net
 
 

Downloads

Download Code