Hello all

Working with SQL Server we have to copy data from multiple sources and from this purpose we can use SqlBulkCopy Class which helps us to bulk copy data from different data sources to SQL Server database. This class is present in the System.Data.SqlClient namespace in ASP.NET C#.

This class can be used to write data only to SQL Server tables and it gives a very good performance when we copy large data to our SQL Server.

We use WriteToServer() method of SqlBulkCopy Class that copies the supplied data to the destination tables.

Following is the code packet:

In XML we have:

<?xml version="1.0" encoding="utf-8" ?>
<Data>
  <TGUser ID="1">
    <FirstName>Gaurav </FirstName>
    <LastName>Gautam</LastName>
    <Email>gaurav.gautam@evontech.com</Email>
  </TGUser>
  <TGUser ID="2">
    <FirstName>Ashish</FirstName>
    <LastName>Negi</LastName>
    <Email>ashish.negi@evontech.com</Email>
  </TGUser>
<TGUser ID="3">
    <FirstName>Amanpreet</FirstName>
    <LastName>singh</LastName>
    <Email>amanpreet.singh@evontech.com</Email>
  </TGUser>
</Data>

In C# we have:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void uploadXml_Click(object sender, EventArgs e)
    {
        string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
        using (SqlConnection con = new SqlConnection(cs))
        {
            DataSet ds = new DataSet();
            ds.ReadXml(Server.MapPath("~/xmlData.xml"));
            if (ds.Tables[0].Rows.Count == 0)
            {
                DataTable dataTable = ds.Tables["TGUser"];
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
                {
                    sqlBulkCopy.DestinationTableName = "TGUser";
                    sqlBulkCopy.ColumnMappings.Add("Id", "Id");
                    sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                    sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");
                    sqlBulkCopy.ColumnMappings.Add("Email", "Email");
                    sqlBulkCopy.WriteToServer(dataTable);
                }
            }
        }
    }
}