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

Send datatable as parameter to stored procedure

159 ratings | 52293 views
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
Html code for embedding videos on your blog
Text Comments (22)
Arif Javed (2 months ago)
Great video sir.. But how will the code differ in case of bulk insert.. For say there is huge amount of data that is to be inserted into the database.. How u r gonna deal with that.. We can not fetch the data like this in case of bulk insert..
Niranjan Gowd (4 months ago)
Hai sir, ur are sending whole data to single table. My doubt is based one the datatable row information we send to row information to different tables .its possible or not
Tanuj Nayanam (9 months ago)
dt.Rows.Add(int.Parse(txtId1.Text), txtName1.Text, txtGender1.Text); IN CASE IF YOU GET EXCEPTION
Munna Ansari (1 year ago)
please tell me How to insert data into sql server using visual studio express for web using c#. i am using microsoft sql server managment studio 2012 and web for vs exppress 2013. please reply me
Saleem Khan (1 year ago)
Are there videos where C# that incorporates sql is explained in detail? thanks.
Danish (1 year ago)
What if you want to send two or more parameters
Gopi Goud (1 year ago)
Hi Venkat.. Can you please upload video for Locks and lock types in Sql server. Thanks lot for your videos.
Shahbaaz (2 years ago)
How can we do it using linq?
malhar jajoo (2 years ago)
Which is better - Passing table parameters to SQL server ? OR Passign scalar parameters within "INSERT" statements to SQL server ?
Gamal Abdall (2 years ago)
Thanks again and again Venkat, I did not know that you can send a data table  as a parameter in SQLCommand object...  Thanks a million.
hyd 2016 (2 years ago)
Hello venkat how to create that web form in the sql server management studio. Where can i find that option ??????????
Dan Hagea (2 years ago)
+hyd 2016 The web form is created in Microsoft Visual Studio
David Garza (3 years ago)
whats is the difference between this way to insert data and the function of BulkCopy? someone is faster than other? greetings :)
Mohamed Noordheen (3 years ago)
sir.. can u please upload videos on 3-Tier Architecture
Mohamed Noordheen (3 years ago)
sure sir... thank u very much. expecting videos  on 3 tier soon
kudvenkat (3 years ago)
+Mohamed Noordheen Sure, at the moment, we don't have videos on 3-tier architecture. I will record and upload as soon as I can. Thank you very much for your patience. I have organised all the Dot Net & SQL Server videos in to playlists, which could be useful to you https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd Slides and Text Version of the videos can be found on my blog http://csharp-video-tutorials.blogspot.com Tips to effectively use my youtube channel. https://www.youtube.com/watch?v=y780MwhY70s If you want to receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. http://www.youtube.com/subscription_center?add_user=kudvenkat If you like these videos, please click on the THUMBS UP button below the video. May I ask you for a favor. I want these tutorials to be helpful for as many people as possible. Please share the link with your friends and family who you think would also benefit from them.
JI Vlogs (3 years ago)
Since you are using "using" with SqlConnection, you don't need to write ... con.Close(); as it will automatically close it for you.
archrodney (2 years ago)
True.
Eniodunmo Akeem (3 years ago)
Hello i have sent you request on your twitter page but no reply. Can you please do a video on how to reduce the size of Sql Server Database without using shrink
RAQIBUL ALAM RASHED (3 years ago)
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.
Tan YEW MENG (3 years ago)
Awesome video
Therapyx (3 years ago)
thank you for sharing a video and have a great day ^^

Would you like to comment?

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