Posted on 12/27/2020 8:59:24 AM by Admin

Loading xml data into sql server table using sqlbulkcopy

We want to load the following XML data into SQL Server database.

XML data
XML data

Notice that we have Departments and Employees data in the XML file. We would like to load Employees data into Employees table and Departments data into Departments table. After the data is loaded, the database tables should look as shown below.

Department Table
Department Table
Employee Table
Employee Table

The following are the steps to achieve this using SqlBulkCopy class

Step 1 : Create the database tables using the following sql script


   Create Database Test
   Go
   Use Test
   Go
   CREATE TABLE [dbo].[Department] (
    [Id]   INT          IDENTITY (1, 1) NOT NULL,
    [Name] VARCHAR (50) NULL,
    CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   CREATE TABLE [dbo].[Employee] (
    [Id]           INT          IDENTITY (1, 1) NOT NULL,
    [Name]         VARCHAR (50) NULL,
    [Salary]       INT          NULL,
    [DepartmentId] INT          NULL,
    CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Employee_Department] FOREIGN KEY ([DepartmentId]) REFERENCES [dbo].[Department] ([Id])
   );

Step 3 : Create a new empty asp.net web application project. Name it DemoForSqlBulkCopy

 Create a web application
Create a web application
Create Empty webform project
Create Empty webform project
Step 4 :Add a webform to the project.
Add webform
Add webform

Step 3 : Add a new xml file to the project. Name it Data.xml. Copy and paste the following XML.

Add XML file 1
Add New Item
Add XML file
Add XML File

   <Data>
	<Department Id="1" Name="HR" />
	<Department Id="2" Name="Development" />
	<Department Id="3" Name="Database" />
	<Department Id="4" Name="Business" />
  <Employee Id="1" Name="sachin" Salary="100000" DepartmentId="1" />
  <Employee Id="2" Name="arjun" Salary="23000" DepartmentId="1" />
  <Employee Id="3" Name="vikash" Salary="30000" DepartmentId="2" />
  <Employee Id="4" Name="pocha" Salary="43000" DepartmentId="3" />
  <Employee Id="5" Name="Nivedita" Salary="24000" DepartmentId="4" />
  <Employee Id="6" Name="agla" Salary="1000" DepartmentId="2" />
  <Employee Id="7" Name="dadad" Salary="10005" DepartmentId="1" />
 </Data>

Step 4 : Include the database connection string in web.config file


  <connectionStrings>
  <add name="TestConnectionString" connectionString="Data Source=SACHIN-PC\SQLEXPRESS;Initial Catalog=Test;Integrated Security=True" 
   providerName="System.Data.SqlClient" />
  </connectionStrings>

Step 5 : Add a new WebForm to the project. Drag and drop a button control on the webform. Double click the button control to generate the click event handler. Copy and paste the following code in the the click event handler method.


   protected void Button1_Click(object sender, EventArgs e)
        {
            string cs = ConfigurationManager.ConnectionStrings["TestConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(cs))
            {
                DataSet ds = new DataSet();
                ds.ReadXml(Server.MapPath("~/Data.xml"));
                DataTable dtDept = ds.Tables["Department"];
                DataTable dtEmp = ds.Tables["Employee"];
                con.Open();
                using (SqlBulkCopy bc = new SqlBulkCopy(con))
                {
                    bc.DestinationTableName = "Department";
                    bc.ColumnMappings.Add("Id", "Id");
                    bc.ColumnMappings.Add("Name", "Name");             
                    bc.WriteToServer(dtDept);
                }

                using (SqlBulkCopy bc = new SqlBulkCopy(con))
                {
                    bc.DestinationTableName = "Employee";
                    bc.ColumnMappings.Add("Id", "Id");
                    bc.ColumnMappings.Add("Name", "Name");
                    bc.ColumnMappings.Add("Salary", "Salary");
                    bc.ColumnMappings.Add("DepartmentId", "DepartmentId");
                    bc.WriteToServer(dtEmp);
                }
            }
        }