How to transfer xml data to sql server using SqlBulkCopy in asp.net c#?

In this article we will discuss, to transfer xml data to sql server using SqlBulkCopy in asp.net c#.  SqlBulkCopy class used to write data only to SQL server tables. It is efficient and very easy to copy large amount of data.

We will be using Employee table.

Step 1: Create a table using the following script with data:

CREATE TABLE[dbo].[Employee](
      [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NULL,
      [Gender] [nvarchar](50) NULL,
      [City] [nvarchar](50) NULL,
 CONSTRAINT[PK_tbl_Employee] PRIMARY KEY CLUSTERED 
(
      [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON[PRIMARY]

Step 2: Copy and paste the following code.

Default.aspx:

<table style="border: 1px solid #e2e2e2; font-family: Arial">
        <tr>
            <td>
                <asp:Button ID="btnLoadData" runat="server" ForeColor="Blue" Text="click me to transfer xmldata to database"
                   OnClick="btnLoadData_Click" />
                <br />
                <br />
                <asp:Label ID="lblMessage" ForeColor="green" runat="server"></asp:Label>
            </td>
        </tr>
    </table>

Default.aspx.cs: 

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

public partial class _Default : Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
        }
    }
    protected void btnLoadData_Click(object sender, EventArgs e)
    {
       BindData();
    }
 
    private void BindData()
    {
        string cs = ConfigurationManager.ConnectionStrings["ShoppingZone"].ConnectionString;
        using (SqlConnection con = new SqlConnection(cs))
        {
            DataSet ds = new DataSet();
           ds.ReadXml(Server.MapPath("~/Employees.xml"));
            DataTable dtEmp = ds.Tables["Employee"];
           con.Open();
            using (SqlBulkCopy bc = new SqlBulkCopy(con))
            {
               bc.DestinationTableName = "Employee";
               bc.ColumnMappings.Add("EmployeeID", "EmployeeID");
               bc.ColumnMappings.Add("Name", "Name");
               bc.ColumnMappings.Add("Gender", "Gender");
               bc.ColumnMappings.Add("City", "City");
               bc.WriteToServer(dtEmp);
            }
           lblMessage.Text = "xmlData transfered to Database";
        }
    }
} 

Output: