HI WELCOME TO Sirees

Send datatable as parameter to stored procedure

Leave a Comment

In this we will discuss how to send datatable as parameter to stored procedure


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 that looks as shown below. This form allows us to insert 5 employees at a time into the database table.
Send datatable as parameter to stored procedure

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.

Here are the steps to achieve this.
Step 1 : Create new asp.net web application project. Name it Demo. 

Step 2 : Include a connection string in the web.config file to your database.
<add name="DBCS"
      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 3 : Copy and paste the following HTML in WebForm1.aspx
<asp:Button ID="btnFillDummyData" runat="server" Text="Fill Dummy Data"
    OnClick="btnFillDummyData_Click" />
<br /><br />
<table>
    <tr>
        <td>
            ID : <asp:TextBox ID="txtId1" runat="server"></asp:TextBox>
        </td>
        <td>
            Name : <asp:TextBox ID="txtName1" runat="server"></asp:TextBox>
        </td>
        <td>
            Gender : <asp:TextBox ID="txtGender1" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            ID : <asp:TextBox ID="txtId2" runat="server"></asp:TextBox>
        </td>
        <td>
            Name : <asp:TextBox ID="txtName2" runat="server"></asp:TextBox>
        </td>
        <td>
            Gender : <asp:TextBox ID="txtGender2" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            ID : <asp:TextBox ID="txtId3" runat="server"></asp:TextBox>
        </td>
        <td>
            Name : <asp:TextBox ID="txtName3" runat="server"></asp:TextBox>
        </td>
        <td>
            Gender : <asp:TextBox ID="txtGender3" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            ID : <asp:TextBox ID="txtId4" runat="server"></asp:TextBox>
        </td>
        <td>
            Name : <asp:TextBox ID="txtName4" runat="server"></asp:TextBox>
        </td>
        <td>
            Gender : <asp:TextBox ID="txtGender4" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td>
            ID : <asp:TextBox ID="txtId5" runat="server"></asp:TextBox>
        </td>
        <td>
            Name : <asp:TextBox ID="txtName5" runat="server"></asp:TextBox>
        </td>
        <td>
            Gender : <asp:TextBox ID="txtGender5" runat="server"></asp:TextBox>
        </td>
    </tr>
</table>
<br />
<asp:Button ID="btnInsert" runat="server" Text="Insert Employees"
    OnClick="btnInsert_Click" />

Step 4 : Copy and paste the following code in the code-behind file
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";
        }
    }
}

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.