Send datatable as parameter to stored procedure

how to pass datatable to stored procedure in c#.net
pass datatable to stored procedure in sql server 2008

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 to insert 5 employees at a time into the database table.

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.

WebForm1.aspx.cs code
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{}

private DataTable GetEmployeeData()
{
DataTable dt = new DataTable();
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;
}

protected void btnInsert_Click(object sender, EventArgs e)
{
string cs = ConfigurationManager.ConnectionStrings[“DBCS”].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand(“spInsertEmployees”, con);
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter paramTVP = new SqlParameter()
{
ParameterName = “@EmpTableType”,
Value = GetEmployeeData()
};
cmd.Parameters.Add(paramTVP);

con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}

protected void 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”;
}
}

Text version of the video
http://csharp-video-tutorials.blogspot.com/2015/09/send-datatable-as-parameter-to-stored.html

Slides
http://csharp-video-tutorials.blogspot.com/2015/09/send-datatable-as-parameter-to-stored_18.html

All SQL Server Text Articles
http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html

All SQL Server Slides
http://csharp-video-tutorials.blogspot.com/p/sql-server.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