Send datatable as parameter to stored procedure

By
admin
-
28
36



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/

Xem thêm Bài Viết:

RELATED ARTICLESMORE FROM AUTHOR

28 COMMENTS

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

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

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

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

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

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

  20. Which is better –

    Passing table parameters to SQL server ?

    OR

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

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

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

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

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

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

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

LEAVE A REPLY

Please enter your comment!
Please enter your name here