Part 18 Load xml data into sql server table using sqlbulkcopy

Показать описание

In this video we will discuss
1. What is the use of SqlBulkCopy class
2. Loading xml data into sql server table using sqlbulkcopy

Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help.

What is the use of SqlBulkCopy class
SqlBulkCopy class is used to bulk copy data from different data sources to SQL Server database. This class is present in System.Data.SqlClient namespace. This class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server, any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

From a performance standpoint, SqlBulkCopy makes it very easy and efficient to copy large amounts of data.

Loading xml data into sql server table using sqlbulkcopy

The following are the steps to achieve this using SqlBulkCopy class

Step 1 : Create the database tables using the following sql script
Create table Departments
(
ID int primary key,
Name nvarchar(50),
Location nvarchar(50)
)
GO

Create table Employees
(
ID int primary key,
Name nvarchar(50),
Gender nvarchar(50),
DepartmentId int foreign key references Departments(Id)
)
GO

[?xml version="1.0" encoding="utf-8" ?]
[Data]
[Department Id="1"]
[Name]IT[/Name]
[Location]New York[/Location]
[/Department]
[Department Id="2"]
[Name]HR[/Name]
[Location]London[/Location]
[/Department]
[Department Id="3"]
[Name]Payroll[/Name]
[Location]Mumbai[/Location]
[/Department]
[Employee Id="1"]
[Name]Mark[/Name]
[Gender]Male[/Gender]
[DepartmentId]1[/DepartmentId]
[/Employee]
[Employee Id="2"]
[Name]John[/Name]
[Gender]Male[/Gender]
[DepartmentId]1[/DepartmentId]
[/Employee]
[Employee Id="3"]
[Name]Mary[/Name]
[Gender]Female[/Gender]
[DepartmentId]2[/DepartmentId]
[/Employee]
[Employee Id="4"]
[Name]Steve[/Name]
[Gender]Male[/Gender]
[DepartmentId]2[/DepartmentId]
[/Employee]
[Employee Id="5"]
[Name]Ben[/Name]
[Gender]Male[/Gender]
[DepartmentId]3[/DepartmentId]
[/Employee]
[/Data]

[connectionStrings]
[add name="CS" connectionString="server=.;database=Sample;integrated security=true"/]
[/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.

string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;

using (SqlConnection con = new SqlConnection(cs))
{
DataSet ds = new DataSet();

DataTable dtDept = ds.Tables["Department"];
DataTable dtEmp = ds.Tables["Employee"];

con.Open();

using (SqlBulkCopy sb = new SqlBulkCopy(con))
{
sb.DestinationTableName = "Departments";
sb.ColumnMappings.Add("ID", "ID");
sb.ColumnMappings.Add("Name", "Name");
sb.ColumnMappings.Add("Location", "Location");
sb.WriteToServer(dtDept);
}

using (SqlBulkCopy sb = new SqlBulkCopy(con))
{
sb.DestinationTableName = "Employees";
sb.ColumnMappings.Add("ID", "ID");
sb.ColumnMappings.Add("Name", "Name");
sb.ColumnMappings.Add("Gender", "Gender");
sb.ColumnMappings.Add("DepartmentId", "DepartmentId");
sb.WriteToServer(dtEmp);
}
}

Note: Please make sure to include the following using declarations
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

Text version of the video

Slides

All ADO .NET Text Articles

All ADO .NET Slides

All Dot Net and SQL Server Tutorials in English

All Dot Net and SQL Server Tutorials in Arabic

Рекомендации по теме

Комментарии

Thanks For Helping Every Student, Teacher and Developer in Understanding various Concepts of C#.NET.

JaiShreeRam

Good job making it nice and simple. It doesn't get any easier to understand than that. This will come in very handy. I plan to obtain the table names and create them on the fly from the XML file prior to importing. Also, I plan on handling multiple tables at once from the dataset without creating the data table as my source. I'm sure it will require some looping and probably take me a week because I very new to programming. Also I plan to do it in VB. wIsh me luck. Your tutorial has been a HUGE help.

Thanks

sanfordhoffman

Thank you venkat for giving such a big and useful knowledge in a very simplified manner

AryaChuodhury

man i love you really.. you are lifesaver

dosovibustayes

Hi Venkat, nice video and thanks for sharing. Could you create a video to consume web service which returns multiple rows of XmlNode data with attributes? Not sure how to consume web service XmlNode data with multiple rows and multiple attributes. Also need to know how sqlbulkcopy can capture attributes besides names.

BrianEHo

Hi sir is there any vedios on loading xml data into sql server table using sql bulk
copy with danyamically

shareefshaik

Hi, sqlbulkcopy doesnt work with oledb (access) connections or in WPF forms. What is the best way to copy the dataTable to Access ? Also how to combine the data of two datatables in to one table and then map it to Access Tables.

MuhammadAdeelAhmed

Dear Venkat sir, am new, had a curiosity, Is the ds.readxml() method that intelligent to separately identify the employee and department from the xml? Is it like whatever the readxml found inside [department id= ..][/department], it will create a table of that name? Else how does it come to know which data will go into one table and the others into another? And how their intelligence works to name the tables automatically? The xml has no separation like pointing from here to here copy a data inside employee table, rest to Dept.

amlanbdey

Would it still be viable to use ReadXML when you have to read certain attributes within the XML, like for example <field xsi:type="Value1"> ? I think you cannot read attributes in this way.

drizzle

Hi Venkat thanks for sharing knowledge through these tutorials.
I have got one query as per my requirement:
I have to validate each column in the table of dataset before uploading it to database.
For eg:I need to validate one column for > 0 value, one column for 1 to 100 character and so on, if any of the value fails validation that value won't be inserted in the database table.do u have any idea how it can be done?

princessaadyascreations

You are soo good at teaching.
My suggestion to you is that you should make an Android app of your Blog and Tutorial.

I ll be the first one to download it. :)

achintify

how to import ms-access data to sqlserver/sqlite in

parthdattani

Will this work with multiple name spaces in the XML file?

nasarazam

Hi Venkat, what if we have 50 columns of data?please help me with this question

madhudonkana

What if the xml file is so huge that I cannot really use the DataSet approach and would like to go via reading it via XmlReader (SAX based), does it behave fast even with XmlReaders?

Mr