HomeОбразованиеRelated VideosMore From: kudvenkat

Part 18 Load xml data into sql server table using sqlbulkcopy

182 ratings | 63504 views
In this video we will discuss 1. What is the use of SqlBulkCopy class 2. Loading xml data into sql server table using sqlbulkcopy 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 Step 2 : Create a new empty asp.net web application project. Name it Demo. Step 3 : Add a new xml file to the project. Name it Data.xml. Copy and paste the following XML. [?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] Step 4 : Include the database connection string in web.config file [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(); ds.ReadXml(Server.MapPath("~/Data.xml")); 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 http://csharp-video-tutorials.blogspot.com/2014/09/part-18-load-xml-data-into-sql-server.html Slides http://csharp-video-tutorials.blogspot.com/2014/09/part-18-load-xml-data-into-sql-server_23.html All ADO .NET Text Articles http://csharp-video-tutorials.blogspot.com/p/free-adonet-video-tutorial.html All ADO .NET Slides http://csharp-video-tutorials.blogspot.com/p/adonet-slides.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists
Html code for embedding videos on your blog
Text Comments (31)
madhu donkana (26 days ago)
Hi Venkat,what if we have 50 columns of data?please help me with this question
Luigi Zambetti (3 months ago)
Hi, what I need to change if I need to save XML data in a Oracle table?
neha dalakoti (1 year ago)
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?
abhishek aman (1 year ago)
sir can you provide a tutorial or code for the bulk data insertion & update from the excel file or please mail me at [email protected]
Muhammad Adeel Ahmed (1 year ago)
Hi All, Can someone please help how to load the excel workbook file with multiple sheets in it. All sheets have the data headers in row 2 and data from row 3. It is like a bulk upload but the input file should be from asp web form. Can some please help?
Mr767267 (1 year ago)
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?
Learner (1 year ago)
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.
Domi. (2 years ago)
how do i do this with a fileupload control to select the xml file?
Johnny Depp (2 years ago)
6:09 let's call it *bc* lol..
Sridevi Pindi (2 years ago)
hi venkat sir could u show us how load excel sheet data into sql server pls
Tresor Omari (2 years ago)
Hi, To do that you need to use ssis.  I personally use visual studio shell 2013 to do that. hope this helps. thanks
Jon Persaud (2 years ago)
I have data located in different tables in my data visualization, how do i export data from different tables to my SQL DataBase
Kostas Greek (3 years ago)
man i love you really.. you are lifesaver
Arya Chuodhury (3 years ago)
Thank you venkat for giving such a big and useful knowledge in a very simplified manner
Sanford Hoffman (3 years ago)
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
Nasar Azam (3 years ago)
Will this work with multiple name spaces in the XML file?
Vineeth S (3 years ago)
how to insert only 3 data to the table containing 4 columns?
Achint Rastogi (3 years ago)
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. :)
Shiva Ram (4 years ago)
Thank u so much...
ipsita pani (3 years ago)
+Shiva Ram sorry for my late reply,in bc.writetoserver() u have to pass the xml table name.otherwise it will show error like value cannot be null.
Shiva Ram (4 years ago)
It give some error that "Value cannot be null." in the line  ""bc.WriteToServer(dtDept1);"" Any body please tell me the solution
ipsita pani (3 years ago)
sorry for my late reply,in bc.writetoserver() u have to pass the xml table name.otherwise it will show error like value cannot be null.
Sharad Singh (4 years ago)
thk u sir ur uploads are GREAT!!
Mageshwaran K (4 years ago)
Thank Venkat,....................Keep Continue
vinod kashyap (4 years ago)
Very Nice sir.. please sir make Jquery tutorials
Jitendra Mahapatro (4 years ago)
nice .. Plz upload Jquery
Good Understanding. 
Manish Dixit (4 years ago)
thanks Venkat for loading this tutorial. How do i upload a xml file if i have 100 columns. i mean it will be very tedious to map 100's of columns. please advise.
Sanford Hoffman (2 years ago)
+Sanford Hoffman Oops, Length -1. Not Count -1.
Sanford Hoffman (2 years ago)
I did .....for (int I = 0 ; I < dt.Columns.Count -1 ; I ++) { bc.ColumnMappings.Add(I,I)}; This maps the column index of the data table to the column index of the SQL table. There could be a million columns and it would do the same. There is an overloaded method for bc.ColumnMapping.Add(); that allows you to use index instead of name.
Sanford Hoffman (2 years ago)
I used the index of the tables and columns instead of the names. Looping through them was a piece of cake no matter how many tables or columns are in your xml file. Good luck.

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.