August 24, 2020
  • 7:26 am Cách chèn nhạc vào ảnh và video trên Facebook
  • 6:59 am Tóm Tắt The Walking Dead (Xác Sống) | Season 4
  • 6:15 am [Mất gốc Hóa – số 16] – HƯỚNG DẪN CÁCH GỌI TÊN OXIT AXIT – OXIT BAZƠ NHANH THUỘC
  • 5:49 am Cách cài Win từ ổ cứng: cả Win 7/8/8.1 và Win10 (làm mẫu với Win 10)
  • 5:12 am Thủ thuật excel: Cách khóa dữ liệu không cho chỉnh sửa trong excel



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/

admin

RELATED ARTICLES

28 COMMENTS

  1. TallCoolDrink Posted on July 9, 2020 at 2:23 am

    thank you.

    Reply
  2. Jonathan Ismaila Posted on July 9, 2020 at 2:23 am

    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?

    Reply
  3. Jonathan Ismaila Posted on July 9, 2020 at 2:23 am

    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?

    Reply
  4. Sachin Bhosale Posted on July 9, 2020 at 2:23 am

    Hi Venkat, In Stored Procedure how did you define EmpTableType for Param.

    Reply
  5. Micah Holmes Posted on July 9, 2020 at 2:23 am

    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

    Reply
  6. Suhas Sorake Posted on July 9, 2020 at 2:23 am

    Is that like temporary table and actual table must have exactly same column's??

    Reply
  7. Shivani Shah Posted on July 9, 2020 at 2:23 am

    what we can do for N number of rows?

    Reply
  8. Ishan Kamat Posted on July 9, 2020 at 2:23 am

    Why dont we use a trigger over here? any specific reason?

    Reply
  9. Jon Espinoza Posted on July 9, 2020 at 2:23 am

    THANK YOU VERY MUCH!!! life saver for school!

    Reply
  10. Varma Anand Posted on July 9, 2020 at 2:23 am

    i am unable to create a parameter in stored procedure using @table variable

    Reply
  11. Arif Javed Posted on July 9, 2020 at 2:23 am

    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..

    Reply
  12. Niranjan Gowd Posted on July 9, 2020 at 2:23 am

    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

    Reply
  13. Tanuj Nayanam Posted on July 9, 2020 at 2:23 am

    dt.Rows.Add(int.Parse(txtId1.Text), txtName1.Text, txtGender1.Text); IN CASE IF YOU GET EXCEPTION

    Reply
  14. Munna Ansari Posted on July 9, 2020 at 2:23 am

    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

    Reply
  15. Saleem Khan Posted on July 9, 2020 at 2:23 am

    Are there videos where C# that incorporates sql is explained in detail? thanks.

    Reply
  16. Danish Posted on July 9, 2020 at 2:23 am

    What if you want to send two or more parameters

    Reply
  17. Gopi Goud Posted on July 9, 2020 at 2:23 am

    Hi Venkat..

    Can you please upload video for Locks and lock types in Sql server.

    Thanks lot for your videos.

    Reply
  18. Shahbaaz Posted on July 9, 2020 at 2:23 am

    How can we do it using linq?

    Reply
  19. malhar jajoo Posted on July 9, 2020 at 2:23 am

    Which is better –

    Passing table parameters to SQL server ?

    OR

    Passign scalar parameters within "INSERT" statements to SQL server ?

    Reply
  20. Gamal Abdall Posted on July 9, 2020 at 2:23 am

    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.

    Reply
  21. hyd 2016 Posted on July 9, 2020 at 2:23 am

    Hello venkat how to create that web form in the sql server management studio. Where can i find that option ??????????

    Reply
  22. David Garza Posted on July 9, 2020 at 2:23 am

    whats is the difference between this way to insert data and the function of BulkCopy? someone is faster than other? greetings 🙂

    Reply
  23. Mohamed Noordheen Posted on July 9, 2020 at 2:23 am

    sir.. can u please upload videos on 3-Tier Architecture

    Reply
  24. JI Vlogs Posted on July 9, 2020 at 2:23 am

    Since you are using "using" with SqlConnection, you don't need to write … con.Close(); as it will automatically close it for you.

    Reply
  25. Eniodunmo Akeem Posted on July 9, 2020 at 2:23 am

    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

    Reply
  26. RAQIBUL ALAM RASHED Posted on July 9, 2020 at 2:23 am

    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
  27. TAN YEW MENG Posted on July 9, 2020 at 2:23 am

    Awesome video

    Reply
LEAVE A COMMENT