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
Slides
All SQL Server Text Articles
All SQL Server Slides
All Dot Net and SQL Server Tutorials in English
All Dot Net and SQL Server Tutorials in Arabic
Nguồn: https://chaoticpharmacology.com/
Xem thêm bài viết khác: https://chaoticpharmacology.com/cong-nghe/
thank you.
Please I haven't gotten an answer to my question. Please I really need your answer.
When inserting the records, can we handle errors in n each record?
Please I have a question and I would so much love the get a response from you.
Please when passing the datatable parameter to sql procedure, is it possible to check for errors just like you did in lesson 56 and 57 videos?
For example, you checked if the quantity sold is not greater than quantity available. Is that also possible with datatable parameter?
Hi Venkat, In Stored Procedure how did you define EmpTableType for Param.
I really like this concept, I'm going to try to use it at work and on my personal projects. Updating data is such a bandwidth killer. I can I update hundreds of records at once thats huge
Excellent!
Is that like temporary table and actual table must have exactly same column's??
what we can do for N number of rows?
Why dont we use a trigger over here? any specific reason?
THANK YOU VERY MUCH!!! life saver for school!
i am unable to create a parameter in stored procedure using @table variable
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..
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
dt.Rows.Add(int.Parse(txtId1.Text), txtName1.Text, txtGender1.Text); IN CASE IF YOU GET EXCEPTION
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
Are there videos where C# that incorporates sql is explained in detail? thanks.
What if you want to send two or more parameters
Hi Venkat..
Can you please upload video for Locks and lock types in Sql server.
Thanks lot for your videos.
How can we do it using linq?
Which is better –
Passing table parameters to SQL server ?
OR
Passign scalar parameters within "INSERT" statements to SQL server ?
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.
Hello venkat how to create that web form in the sql server management studio. Where can i find that option ??????????
whats is the difference between this way to insert data and the function of BulkCopy? someone is faster than other? greetings 🙂
sir.. can u please upload videos on 3-Tier Architecture
Since you are using "using" with SqlConnection, you don't need to write … con.Close(); as it will automatically close it for you.
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
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.
Awesome video