SQLBulkCopy in C#

Sourabh Mishra
9y
34.5k
0
2

Article

In development sometimes we need to transfer a large amount of data from sources to a SQL Server database table. We can do this kind of stuff using sqlbulkcopy. Bulk means many things. So with the use of SqlBulkCopy you can send a large amount of data from any source to SQL Server database. For example you have a collection of data in XML format and you want to save this data into your database table. You can do this very easily. Today in this article we will learn how to use SqlBulkCopy in C#.

The SqlBulkCopy class is part of the System.Data.SqlClient namespace. In this class you have always 2 parts, the source and the destination. The source could be XML, Access, Excel or SQL (in other words any type of datasource you have). This data can be loaded into a datatable or datareader after the destination portion of the data is inserted very quickly into the table.

How it works

Have a look at the following example:

Open Visual Studio.
"File" -> "New" -> "Project..."then select ASP.Net Webform Application.
Add New web form.

In this example, we have a XML file data.xml. In that file we have some student records.

Data.xml

<?xml version="1.0" encoding="utf-8" ?>
<data>
<Student Id="1">
<Name>Sourabh Mishra</Name>
<Phone>9999999999</Phone>
<Address>Delhi</Address>
<Class>MCA</Class>
<IsActive>1</IsActive>
</Student>
<Student Id="2">
<Name>Surbhee Mishra</Name>
<Phone>9123456789</Phone>
<Address>Delhi</Address>
<Class>BTech</Class>
<IsActive>1</IsActive>
</Student>
<Student Id="3">
<Name>Sachin</Name>
<Phone>9123499999</Phone>
<Address>Mumbai</Address>
<Class>Bsc</Class>
<IsActive>1</IsActive>
</Student>
<Student Id="4">
<Name>James</Name>
<Phone>9123499007</Phone>
<Address>London</Address>
<Class>Ms</Class>
<IsActive>1</IsActive>
</Student>
</data>

In this example we have a StudentMaster table where we stored the preceding XML data.

Webform.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="SqlBulkCopyDemo.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Load Bulk Data" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>

WebForm1.aspx.cs

protected void Button1_Click(object sender, EventArgs e)
{
try
{
string cs = ConfigurationManager.ConnectionStrings["SchoolConnectionString"].ConnectionString;
using (SqlConnection sqlConn = new SqlConnection(cs))
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Data.xml"));
DataTable dtStudentMaster = ds.Tables["Student"];
sqlConn.Open();
using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn))
{
sqlbc.DestinationTableName = "StudentMaster";
sqlbc.ColumnMappings.Add("Name", "Name");
sqlbc.ColumnMappings.Add("Phone", "Phone");
sqlbc.ColumnMappings.Add("Address", "Address");
sqlbc.ColumnMappings.Add("Class", "Class");
sqlbc.WriteToServer(dtStudentMaster);
Response.Write("Bulk data stored successfully");
}
}
}
catch (Exception ex)
{
throw ex;
}
}

Understand the code

Look at the preceding code, in this code we have dataset where we read XML data and store the data into a DataTable.

DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("~/Data.xml"));
DataTable dtStudentMaster = ds.Tables["Student"];

Then we create an object of SqlBulkCopy class, in this class we have some properties.

using (SqlBulkCopy sqlbc = new SqlBulkCopy(sqlConn))
{
sqlbc.DestinationTableName = "StudentMaster";
sqlbc.ColumnMappings.Add("Name", "Name");
sqlbc.ColumnMappings.Add("Phone", "Phone");
sqlbc.ColumnMappings.Add("Address", "Address");
sqlbc.ColumnMappings.Add("Class", "Class");
sqlbc.WriteToServer(dtStudentMaster);
Response.Write("Bulk data stored successfully");
}

Look at the preceding code, we have a sqlbc object here we have a DestinationTable property, so here we need to define a table name where we store that data. In the next line we have ColumnMappings where we must define a Source column and a Destination column.

SQLBC

Then we have a WriteToServer method where we define the datatable. This datatable copies all the data into the destination table.

That's it, press F5 and run your code.

local host