ADO.NET using XML Data

Back to: ADO.NET Tutorial For Beginners and Professionals

How to Load XML Data to a Data Table using ADO.NET

In this article, I am going to discuss How to Load XML Data to a Data Table using ADO.NET, and then we will see how to insert those data from the data table to a database table using C# with Examples. Please read our previous article where we discussed the Connected and Disconnected Architecture of ADO.NET with Examples.

Note: In this article, I am going to use SqlBulkCopy class to insert the data from the data table into a database table. From a performance point of view, SqlBulkCopy class makes it very easy and efficient to copy large amounts of data from a data table to a SQL Server Database Table.

What is the use of SqlBulkCopy Class in C#?

The SqlBulkCopy Class in C# is used to bulk copy data from different data sources to the SQL Server database. The SqlBulkCopy class belongs to System.Data.SqlClient (this is the .NET Data Provider for SQL Server Database) namespace. This class is used to insert the data into the SQL Server Database table only i.e. the destination database must be SQL Server only. However, there is no restriction on the source data source i.e. any source data source can be used as long as the data can be loaded into the Data table.

Loading XML data into SQL Server Database Table using ADO.NET SqlBulkCopy Class using C#:

Let us understand how to Load XML data into SQL Server Database Table using the ADO.NET SqlBulkCopy class. For this, we are going to use the following XML file. Please create a file with the name DepartmentEmployees.xml and then copy and paste the following into it.

<DepartmentEmployeesData>
  <Department Id="101">
    <Name>IT</Name>
    <Location>Mumbai</Location>
  </Department>
  <Department Id="102">
    <Name>HR</Name>
    <Location>Delhi</Location>
  </Department>
  <Department Id="103">
    <Name>Sales</Name>
    <Location>Pune</Location>
  </Department>
  <Employee Id="1001">
    <Name>Pranaya</Name>
    <Gender>Male</Gender>
    <DepartmentId>101</DepartmentId>
  </Employee>
  <Employee Id="1002">
    <Name>Pratik</Name>
    <Gender>Male</Gender>
    <DepartmentId>101</DepartmentId>
  </Employee>
  <Employee Id="1003">
    <Name>Priyanka</Name>
    <Gender>Female</Gender>
    <DepartmentId>102</DepartmentId>
  </Employee>
  <Employee Id="1004">
    <Name>Ramesh</Name>
    <Gender>Male</Gender>
    <DepartmentId>102</DepartmentId>
  </Employee>
  <Employee Id="1005">
    <Name>Preety</Name>
    <Gender>Female</Gender>
    <DepartmentId>103</DepartmentId>
  </Employee>
</DepartmentEmployeesData>

The above XML file is going to be our Source File. As you can see, the above XML file has the Department’s and Employee’s data. Our business requirement is, we want to load the Employee’s data into the Employees database table and the Departments data into the Departments database table. And both of these tables are created in SQL Server Database. Once you load the above Employees and Departments data, the database tables should look as shown in the below image.

Example to Understand How to Load XML data to SQL Server Database Table using ADO.NET DataTable:

Let us understand How we can Load XML data into SQL Server Database Table using SqlBulkCopy class. Let us first create the SQL Server Database and the required Employees and Departments Tables by executing the following SQL Scripts.

CREATE DATABASE CompanyDB
GO

USE CompanyDB
GO

CREATE TABLE Departments
(
     ID INT PRIMARY KEY,
     Name VARCHAR(50),
     Location VARCHAR(50)
)
GO

CREATE TABLE Employees
(
     ID INT PRIMARY KEY,
     Name VARCHAR(50),
     Gender VARCHAR(50),
     DepartmentId INT FOREIGN KEY REFERENCES Departments(Id)
)
GO

Once you created the Database and required tables, let us see how we can load the XML data into the above database tables. So, what are going to do we will load the above XML data into the DataSet. Within the DataSet, the first DataTable is going to store the Department data and the second Data Table is going to hold the Departments from the XML file. Once we have data in the Data Tables, then we are using the SqlBulkCopy class to copy the data from the Data Tables into the corresponding Database tables. For a better understanding, please have a look at the following example which exactly does the same thing. The following example code is self-explained, so please go through the comment lines.

using System;
using System.Data;
using System.Data.SqlClient;

namespace DataViewClassDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            //Store the connection string in a variable
            string ConnectionString = @"data source=LAPTOP-ICA2LCQL\SQLEXPRESS; database=CompanyDB; integrated security=SSPI";

            //Create the connection object
            using (SqlConnection connection = new SqlConnection(ConnectionString))
            {
                //Create a DataSet store XML Data which contains multiple DataTables 
                DataSet dataSet = new DataSet();

                //Complete Path of the XML File
                string XMLFilePath = @"D:\Training\XMLFiles\DepartmentEmployees.xml";

                //Fill The DataSet using the ReadXml Method by passing the Complete XML File Path
                //ReadXml: Reads XML schema and data into the DataSet from the specified XML file.
                dataSet.ReadXml(XMLFilePath);

                //Store the Departments Data in a separate Data table i.e. DepartmentsDataTable
                DataTable DepartmentsDataTable = dataSet.Tables["Department"];

                //Store the Employees Data in a separate Data table i.e. EmployeesDataTable
                DataTable EmployeesDataTable = dataSet.Tables["Employee"];

                //Display the Departments data
                Console.WriteLine("Departments List:");
                foreach (DataRow row in DepartmentsDataTable.Rows)
                {
                    Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}, Location: {row["Location"]}");
                }

                //Display the Employees data
                Console.WriteLine("\nEmployees List:");
                foreach (DataRow row in EmployeesDataTable.Rows)
                {
                    Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}, Gender: {row["Gender"]},  DepartmentId: {row["DepartmentId"]}");
                }

                //Opening the connection object as we are going to interact with the database
                connection.Open();

                //Copying the Departments DataTable Data into the Departments Database Table
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
                {
                    //Provide the Destination Database Table Name i.e. Departments Database Table
                    sqlBulkCopy.DestinationTableName = "Departments";

                    //Column Mapping is Optional as both Source and Destination same number, name and type of columns
                    sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                    sqlBulkCopy.ColumnMappings.Add("Location", "Location");

                    //Pass the DataTable whose data you want to copy into the Destination Departments Database Table
                    sqlBulkCopy.WriteToServer(DepartmentsDataTable);
                }

                //Copying the Employees DataTable Data into the Employees Database Table
                using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connection))
                {
                    //Provide the Destination Database Table Name i.e. Employees Database Table
                    sqlBulkCopy.DestinationTableName = "Employees";

                    //Column Mapping is Optional as both Source and Destination same number, name and type of columns
                    sqlBulkCopy.ColumnMappings.Add("ID", "ID");
                    sqlBulkCopy.ColumnMappings.Add("Name", "Name");
                    sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
                    sqlBulkCopy.ColumnMappings.Add("DepartmentId", "DepartmentId");

                    //Pass the DataTable whose data you want to copy into the Destination Employees Database Table
                    sqlBulkCopy.WriteToServer(EmployeesDataTable);
                }
            }
            Console.ReadKey();
        }
    }
}

Output:

As you can see in the above output screenshot, the Employees and Departments Data table filling with the Data from XML File. Now, you can also verify the Employees and Departments database tables and they also hold the same data as shown in the below image.

In the next article, I am going to discuss SQL Injection and Prevention in C# with Examples. Here, in this article, I try to explain How to Load XML Data to a Data Table using ADO.NET, and then we will see how to insert those data from the data table to a database table using C# with Examples. I hope you enjoy this How to Load XML Data to a Data Table using the ADO.NET article.