Sql server, .net and c# video tutorial: Send datatable as parameter to stored procedure

Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

(追記) (追記ここまで)

Send datatable as parameter to stored procedure

Suggested Videos
Part 97 - Select into in sql server
Part 98 - Difference between where and having in sql server
Part 99 - Table valued parameters in SQL Server

(追記) (追記ここまで)

In this video we will discuss how to send datatable as parameter to stored procedure. This is continuation to Part 99. Please watch Part 99 from SQL Server tutorial before proceeding.

(追記) (追記ここまで)

In Part 99, we discussed creating a stored procedure that accepts a table as a parameter. In this video we will discuss how to pass a datatable from a web application to the SQL Server stored procedure.

Here is what we want to do.
1. Design a webform that looks as shown below. This form allows us to insert 5 employees at a time into the database table.
Send datatable as parameter to stored procedure

2. When "Insert Employees" button is clicked, retrieve the from data into a datatabe and then pass the datatable as a parameter to the stored procedure.

3. The stored procedure will then insert all the rows into the Employees table in the database.

Here are the steps to achieve this.
Step 1 : Create new asp.net web application project. Name it Demo.

Step 2 : Include a connection string in the web.config file to your database.

<addname="DBCS"

connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 3 : Copy and paste the following HTML in WebForm1.aspx

<asp:ButtonID="btnFillDummyData"runat="server"Text="Fill Dummy Data"

OnClick="btnFillDummyData_Click"/>

<table>

<tr>

<td>

ID : <asp:TextBoxID="txtId1"runat="server"></asp:TextBox>

</td>

<td>

Name : <asp:TextBoxID="txtName1"runat="server"></asp:TextBox>

</td>

<td>

Gender : <asp:TextBoxID="txtGender1"runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

ID : <asp:TextBoxID="txtId2"runat="server"></asp:TextBox>

</td>

<td>

Name : <asp:TextBoxID="txtName2"runat="server"></asp:TextBox>

</td>

<td>

Gender : <asp:TextBoxID="txtGender2"runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

ID : <asp:TextBoxID="txtId3"runat="server"></asp:TextBox>

</td>

<td>

Name : <asp:TextBoxID="txtName3"runat="server"></asp:TextBox>

</td>

<td>

Gender : <asp:TextBoxID="txtGender3"runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

ID : <asp:TextBoxID="txtId4"runat="server"></asp:TextBox>

</td>

<td>

Name : <asp:TextBoxID="txtName4"runat="server"></asp:TextBox>

</td>

<td>

Gender : <asp:TextBoxID="txtGender4"runat="server"></asp:TextBox>

</td>

</tr>

<tr>

<td>

ID : <asp:TextBoxID="txtId5"runat="server"></asp:TextBox>

</td>

<td>

Name : <asp:TextBoxID="txtName5"runat="server"></asp:TextBox>

</td>

<td>

Gender : <asp:TextBoxID="txtGender5"runat="server"></asp:TextBox>

</td>

</tr>

</table>

<br/>

<asp:ButtonID="btnInsert"runat="server"Text="Insert Employees"

OnClick="btnInsert_Click"/>

Step 4 : Copy and paste the following code in the code-behind file

using System;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace Demo

{

publicpartialclassWebForm1 : System.Web.UI.Page

{

protectedvoid Page_Load(object sender, EventArgs e)

{ }

privateDataTable GetEmployeeData()

{

DataTable dt = newDataTable();

dt.Columns.Add("Id");

dt.Columns.Add("Name");

dt.Columns.Add("Gender");

dt.Rows.Add(txtId1.Text, txtName1.Text, txtGender1.Text);

dt.Rows.Add(txtId2.Text, txtName2.Text, txtGender2.Text);

dt.Rows.Add(txtId3.Text, txtName3.Text, txtGender3.Text);

dt.Rows.Add(txtId4.Text, txtName4.Text, txtGender4.Text);

dt.Rows.Add(txtId5.Text, txtName5.Text, txtGender5.Text);

return dt;

}

protectedvoid btnInsert_Click(object sender, EventArgs e)

{

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

using (SqlConnection con = newSqlConnection(cs))

{

SqlCommand cmd = newSqlCommand("spInsertEmployees", con);

cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramTVP = newSqlParameter()

{

ParameterName = "@EmpTableType",

Value = GetEmployeeData()

};

cmd.Parameters.Add(paramTVP);

con.Open();

cmd.ExecuteNonQuery();

con.Close();

}

protectedvoid btnFillDummyData_Click(object sender, EventArgs e)

{

txtId1.Text = "1";

txtId2.Text = "2";

txtId3.Text = "3";

txtId4.Text = "4";

txtId5.Text = "5";

txtName1.Text = "John";

txtName2.Text = "Mike";

txtName3.Text = "Sara";

txtName4.Text = "Pam";

txtName5.Text = "Todd";

txtGender1.Text = "Male";

txtGender2.Text = "Male";

txtGender3.Text = "Female";

txtGender4.Text = "Female";

txtGender5.Text = "Male";

}

Email This BlogThis! Share to X Share to Facebook Share to Pinterest

3 comments:

Unknown September 18, 2015 at 10:03 PM
Congratulations for 100 video in SQL Server.
Really it's a great work for us who want to learn from Online and also from a great teacher like YOU.
Thanks a lot and go ahead.
Reply Delete
Replies
Unknown September 21, 2015 at 4:57 AM
Hi Venkat Sir ,thanks for this video this is very special video for me as was searching this type of things for a long time i have mailed you so many times for this topic finally you have done it.....one more request please discuss paging in sql server
Reply Delete
Replies
AnonymousMarch 15, 2019 at 9:08 AM
What if the values are coming form a checkbox list? In this case the number of rows is really variable and I would like to understand how can you assign a variable number of rows to the query.
Reply Delete
Replies