Part 18 Load xml data into sql server table using sqlbulkcopy

Sharing buttons:

this is part 18 over a deodorant

tutorial in this video we'll discuss

loading xml data into sequence of a

table using sequel bulk copy class

what is the use of this equal bulk copy

class sequel bulk copy class is used to

bulk copy data from different data

sources to sequence our database this

class is present in system or data dot

sequel client namespace this class can

be used to write data only to sequel

server tables however the data source is

not limited to just sequel server any

data source can be used as long as the

data from the source can be loaded into

a data table instance or read with an

IEEE data reader instance in this demo

we'll look at an example of using data

table instance and in later video

session we'll discuss using an IEEE data

reader instance from a performance

standpoint sequel bulk copy makes it

very easy and efficient to copy large

amounts of data right to serve a method

of sequel bulk copy class copies the

supplied data to the destination table

now let's look at an example here we

have caught an XML document and this XML

document contain Department and

employees data notice that at the moment

we have got three departments and each

department has got ID name and location

so we want to store this departments XML

data into departments table in a sequel

server database so notice departments

table has got ID name and location so

this XML data is loaded into this

departments table we have three

departments in the XML and notice within

the destination table also we have three

rows and we also have employees data

here at the moment we've got five

employees notice you know all the five

rows are loaded into the employees table

and each employee has got ID name gender

and department ID and you know those are

the columns within the destination

database and this department ID is a

foreign key referencing ID property

within the departments table so let's

see how to load this XML data into these

two sequel server tables using sequel

bulk copy class let's flip to sequel

server management studio so the first

step is to actually create these two

tables we

I have already done and here is the

sequel script I have the script on my

blog in case you need it now at the

moment these tables are empty now let's

flip to visual studio so here I have a

new empty asp.net web application

project all I have done so far is

included a connection string to our

sample database so these two tables are

present within the sample database and

we have a connection string pointing to

that database now to this project let's

go ahead and add a new item and we want

to add an XML file and let's call this

XML file data dot XML and to speed

things up I have already typed the

required XML so this is the same XML

that we have seen on the slide so we

have got department and employees data

so there are three departments and five

employees all right so to this project

let's add a web form and on this Web

form let's drag and drop a button

control and let's flip this to the

design mode double click on the button

to generate the click event handler now

we are going to write some ad or net

code here so let's bring in the area of

namespaces we need system dot data

system dot data dot sequel client and

finally system dot configuration so the

first thing that we need to do here is

read the connection string from web

config file and for that we are going to

make use of configuration manager class

dot connection strings of the name of

the connection string within the web

config file is CS so read the connection

string and then we are going to create

an instance of sequel connection object

and we pass the connection string all

right now let's create an instance of a

data set so we are going to use this

data set to read the XML data from this

XML document into this data set so data

set dot read XML method

and you know to this method we need to

specify the path of this XML file and

for that we are going to make use of the

server dot map path function so till

four slash the name of the XML file is

data dot XML all right so let's draw in

a breakpoint here and let's run this

application in debug mode and let's

actually see how the data is loaded from

the XML file into the data set so now

let's click that button so it should hit

the breakpoint and if we look at the

data within the data set using the data

set visualizer look at that the

Department data from the XML file is

loaded into department table and this

department table within the data set has

got ID location and name columns and the

employee data is loaded into employee

data table and it has got ID name gender

Department ID columns all right

so the table names are Department and

employee within the data set okay so

let's stop debugging and now we are

going to use sequel bulk copy class to

you know transfer that data from the

data set to you know sequel server

destination tables which is departments

and employees okay so first we need to

create an instance of our sequel bulk

copy class so using sequel bulk copy

let's call it BC equals new sequel bulk

copy now we need to specify the

connection it has to use so what is the

job of the sequel bulk copy object it's

going to write data in order to a sequel

server table so it has to know the

connection information so we can pass

either the connection object or we can

pass a connection string you know since

we already have the connection object

let's go ahead and pass the connection


okay now the sequel bulk copy object

knows the server but server and database

but it doesn't know to which table it

has to write right so we need to specify

the destination table to which it has to

write the data and for that we use this

destination table name property and the

name of the table to which we want to

write data is departments so we are

going to pass that here and then if you

look at the department the table within

this data set and department table

within you know sequel server we need to

specify the column mappings okay

so from the source what is the column

that maps to ID from the source what is

the column that maps to name and what's

the column that maps to location so we

need to specify those column mappings

and to do that we are going to make use

of column mappings and to this property

we are going to add a column mapping

okay and we are going to use this

overloaded method where we specify the

source and destination columns using

string datatype so within our source the

column is ID and the destination column

is also going to be highly similarly we

have name and location columns so name

and location within destination and if

you look at the XML you know for a

department the you know element names

are name and location and these have

become the columns within the data table

within the data set right so name and

this one is also going to be named

location and this will also be location

and finally we will have to tell it to

write the data to this table and to do

that we are going to use this right to

server method and to this method we need

to pass the data table now again there

are several overloaded versions we could

either pass in a set of data rows or

data table or

an instance of high data reader okay so

we are going to use this overloaded

version where we pass data table and you

know within this data set we have those

two tables Department and employees

tables so let's do this let's actually

create a data table object let's call

this DT D EPT equals D s dot tables off

and we know that within the data set

there is a table with named Department

okay and similarly let's also retrieve

the employees table so within the data

set there is a table called employee and

we want to store that in this variable

DT EMP so we want the data that is

present within this object to be written

to sequel server into this department

table and these are the column mappings

and sequel bulk copy knows the server

and database information because you

know this connection object knows we

constructed this connection object using

the connection string defined in the web

config file all right now this is going

to write departments information to the

department stable in sequel server we

also want to write the employee's

information so I'm going to make a copy

of this code and then make the required

changes so we are creating another

instance of sequel bulk copy so this

time the destination table is going to

be employees and if you look at the

employees table we have got ID name

gender and department ID columns so we

need to specify column mappings here so

we have already got ID and name we need

another column mapping for gender and

this one is going to be department ID

all right and the final thing we want to

take the employees data and then write

to the employees table and the employees

data is present in DT EMP variable so

let's go ahead and pass it here all

right so let's run the application and

before we click the button let's execute

this piece of code notice that at the

moment the tables are empty so let's

click the button right to server

requires an open and available

connection so we have forgot to open the

connection so let's go and do that so

this right - silver method requires an

open connection so we have the

connection object but we failed to open

that so let's go ahead and open the

connection here all right let's rerun

this code and let's click the button

alright so now let's go back to sequel

server execute these queries and look at

this the data is loaded into these two

tables as expected thank you for

listening and have a great day