In this article I will explain with an example, how to use SqlBulkCopy to insert bulk data to database with Transaction i.e. Commit and Rollback feature in ASP.Net using C# and VB.Net.
SqlBulkCopy will be used along with the SqlTransaction class in order to perform Bulk Insert with Transaction i.e. Commit and Rollback feature in ASP.Net.
 
 

Database

I have made use of the following table Customers with the schema as follow.
SqlBulkCopy example with Transaction Commit and Rollback in ASP.Net using C# and VB.Net
 
Note: The SQL for creating the database is provided in the attached sample code.
 
 

HTML Markup

The following HTML Markup consists of:
GridView – For displaying data.

Columns

The GridView consists of one TemplateField and three BoundField columns.
TemplateField – The TemplateField column consists of ItemTemplate.
ItemTemplate – It consists of a CheckBoxheckBox.
Checkbox – For select rows.
Button – For inserting bulk data from GridView to SQL Server Database Table.
<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="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"));
        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("~/Customers.xml"))
        gvCustomers.DataSource = ds.Tables(0)
        gvCustomers.DataBind()
    End If
End Sub
 
SqlBulkCopy example with Transaction Commit and Rollback in ASP.Net using C# and VB.Net
 
 

Implement SqlBulkCopy with Transaction i.e. Commit and Rollback in 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 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 we had created earlier.
Now a connection is established with the database and a SqlTranaction is created for the Connection using the BeginTransaction function.
Then the SqlBulkCopy object is initialized and the SqlTranaction object is passed as parameter to the SqlBulkCopy constructor.
Now the name of the Table is specified using the DestinationTableName property and the columns are mapped.
Finally, the WriteToServer method of the SqlBulkCopy class is called inside a Try Catch block where the Commit function of SqlTranaction is called right after the execution of the WriteToServer method and the Rollback function of the SqlTranaction is called inside the Catch block.
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 consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
        using (SqlConnection con = new SqlConnection(consString))
        {
            con.Open();
            using (SqlTransaction sqlTransaction = con.BeginTransaction())
            {
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con, SqlBulkCopyOptions.Default, sqlTransaction))
                {
                    //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");
                    try
                    {
                        sqlBulkCopy.WriteToServer(dt);
                        sqlTransaction.Commit();
                    }
                    catch
                    {
                        sqlTransaction.Rollback();
                    }
                }
            }
            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 consString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
        Using con As New SqlConnection(consString)
            con.Open()
            Using sqlTransaction As SqlTransaction = con.BeginTransaction()
                Using sqlBulkCopy As New SqlBulkCopy(con,SqlBulkCopyOptions.Default, sqlTransaction)
                    '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")
                    Try
                        sqlBulkCopy.WriteToServer(dt)
                        sqlTransaction.Commit()
                    Catch
                        sqlTransaction.Rollback()
                    End Try
                End Using
            End Using
            con.Close()
        End Using
    End If
End Sub
 
 

Screenshots

Successful Transaction

SqlBulkCopy example with Transaction Commit and Rollback in ASP.Net using C# and VB.Net
 

Transaction with Rollback

SqlBulkCopy example with Transaction Commit and Rollback in ASP.Net using C# and VB.Net
 
 

Downloads

Download Code