In this article I will explain with an example, how to bulk copy data from DataTable (DataSet) to SQL Server database Table using SqlBulkCopy in C#, VB.Net and ASP.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.
![SqlBulkCopy: Bulk Copy data from DataTable (DataSet) to SQL Server Table using C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
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.
<?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>
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
Bulk copy data from DataTable (DataSet) to SQL Server database Table using SqlBulkCopy in C#, VB.Net and ASP.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 destination SQL Server database table 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 we had created earlier.
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 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 (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 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 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
Screenshots
GridView with CheckBoxes
![SqlBulkCopy: Bulk Copy data from DataTable (DataSet) to SQL Server Table using C# and VB.Net]()
Inserted records in Database Table
![SqlBulkCopy: Bulk Copy data from DataTable (DataSet) to SQL Server Table using C# and VB.Net]()
Downloads
