Send datatable as parameter to stored procedure Video Lecture | SQL Server Administration: Basic Tutorials - Database Management

this is part 100 of signal server
tutorial in this video we'll discuss how
to send a data table as a parameter to a
stored procedure this is continuation to
part 99 so please watch what 99 before
proceeding in the previous video session
we created this SP insert employees this
stored procedure accepts a table
variable as a parameter here is what we
want to do we want to design a web form
that looks like this this waveform can
capture up to 5 different employees
details for each employee we can capture
ID name and gender so when we click this
insert employees button we want to call
this stored procedure SP insert
employees now the data that we have on
the webform we want to retrieve that
into a data table and then we want to
pass that data table to this stored
procedure and the stored procedure is
then going to insert all those five
employees into this employees table at
once let's see how to achieve this the
first step here is to design this
waveform which I have already done so
here is the HTML so the text boxes here
are actually in a table so here we've
got a table and within the table we've
got five different rows for five
different employees for heat I mean
within each row we've got three TDS for
ID name and gender and look at the text
boxes look at the naming convention so I
have named them txt ID for I D similarly
takes the name for name and txt gender
for gender for the first employee I have
used number one for the second employee
I have used two so on and so forth
so all the text boxes are present within
this table above the table I've got this
filled dummy data so what is this button
this button is to populate all these
text boxes if I have to type five
different employee details manually it's
going to take a bit of time so to save
some time I have this button here when I
click this it's going to fill some dummy
data into these text box controls and
when we click this insert employees
button we want to store that employee's
data into the employees table so the
first thing that I'm going to do is
double click on these button controls to
generate their respective click event
handlers we are going to write some ATO
dotnet code so I have already typed the
required area or net namespaces here
system data systems or data or sequel
client and system dot configuration
within our web config file I also have a
connection string that's pointing to our
sample DB database where we have of our
employees table and our inserts to a
procedure so within our code-behind file
so within in our button filled a
mediator click that is when we click
this button we want to populate these
text boxes with some dummy data so I
have that code right here so I'm going
to copy this and paste that within that
button click event handler so what is
this doing within the text box ID
textbox is storing one in the second ID
text box it's going storing to etc
similarly it's storing some employee
names and their respective genders
within those text boxes so if you look
at this at the moment when we click on
the button all it's going to do is
populate these text boxes with that data
now when we click the insert button we
want to retrieve this data into a data
table and then we want to call that the
array stored procedure pass the data
table as a parameter to it
so within our button insert click the
first thing that I am going to do is
read the connection string into a
variable and for that we are going to
use the configuration manager class
configuration manager dot connection
strings off and the name of the
connection string here is TBC s so dot
connection string and then let's use the
sequel connection class so sequel
connection let's call it corn equals new
sequel connection and to this let's pass
the connection string variable and then
let's create our sequel command object
let's call it CMD equals new sequel
command and we will have to specify our
command here the command is actually
going to be of Stewart procedure SP
insert employees so I am
to pass that to the sequel command
object and then we will have to specify
the connection object as well so we are
going to pass this connection object to
the command object since this command
object here is executing a stored
procedure we have to tell that to the
command object so command or command
type equals command type dot store
procedure and if you look at this stored
procedure it has got an input parameter
table variable as an input parameter and
that's the name of the parameter so
let's create a sequel parameter object
and I'm going to call this pram equals
new sequel parameter that I meet a dot
parameter name equals does the name of
the parameter of hostel procedure that I
am e2 dot value so where is the value
going to come from now we want to
retrieve the data from the webform into
a data table so what I'm going to do now
is create a private function here and
this private function is going to return
a data table and let's call this get
employee data so all this function is
going to do is retrieve the data from
the form into a data table so I'm going
to create a data table object here so
data table let's call it D D equals new
data table and then I'm going to specify
columns for our data table so DT dot
columns dot add and I'm going to specify
the name for our column so I'm going to
use ID as the name so what columns do we
need within the table we want to capture
for each employee ID name and gender so
we want those three columns so d d dot
column start add ID similarly we want a
column for name and we want a column for
gender and then let's add the rows so d
d dot rows dot add so what do we need to
do now we need to provide values for ID
name and gender so I'm going to
for the first employee and for the first
employee the ID value is present in txt
ID 1 dot txt so that's the text box
which has the first employee ID the name
is present in txt name 1 text box and
finally we want the gender of the first
employee and that is present in txt
gender 1 so we need to retrieve you know
the data for the rest of the employees
in the interest of time I have already
typed the required code for the rest of
the employees so let's copy that from
the notepad and paste it right here
so this retrieves for the second
employee and then for the third fourth
and fifth employee so we have added all
the rows to our data table what else we
need to do we need to simply return that
data table from this private function ok
so now I'm going to call this private
function right here get employee data so
this is going to retrieve the data from
the form into a data table and this
function is returning the data table and
we are setting that as the value for
this parameter and we need to associate
this parameter with this command object
so command or parameters dot add and the
name of the parameter is pram and then
we need to open the sequel connection so
connection dot open and this is going to
be an insert operation so we can use
command dot execute non query and
finally close the connection so
straightforward ATO dotnet code here
nothing different all we are doing is
you know retrieving the data from the
form into a data table and we are
passing that data table as a value for
this table variable parameter ok all
right so let's go ahead and run this
form one more time when we click this
filled
data we have the data within the farm
now before I click insert employees
button let's go ahead and check what
data we have in the employees table
notice that at the moment the employees
table is empty and I'm going to click
this insert employees button now let's
select the data notice that we have all
the five employees data within this
employees table so here is the code that
we have just written thank you for
listening and have a great day