Insert Rows Using User Defined Table Types

In this article I am going to explaining how to insert multiple rows using a user defined table type. Here I explain step-by-step:

1. Create UI Design to Add Row and Display Rows on .aspx page

First of all I am creating a UI design to add the employee name and salary. It has a button that when clicked a row in the Data Table is added. See:

<table>

        <tr>

            <td>Name</td>

            <td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td>

        </tr>

 

        <tr>

            <td>Salary</td>

            <td><asp:TextBox ID="txtSalary" runat="server"></asp:TextBox></td>

        </tr>

 

        <tr>

           <td colspan="2" style="text-align:center"><asp:Button ID="btnAdd" runat="server" Text="Add" OnClick = "AddEmployee" /></td>

        </tr>

 

    </table>

 

<!--To Display Rows these are adding on "btnAdd" button click -->

    <asp:GridView ID="empGrid" runat="server">

    </asp:GridView>

2. Add required namespaces on aspx.cs page; they are:

using System;

using System.Data;

using System.Data.SqlClient;

3. Create a Data Table

I create a Data Table that contains dynamically added data rows. This table should be declared globally so it can be used in all methods. This data table is used to pass data to the user defined table type.

static DataTable dt = new DataTable();

4. Create Columns and Add on Page Load

First I create columns for the Data Table in the page load event of the page. These columns are defined as per a user defined table type, in other words in a data table we define a number of columns as in a user defined table type.

  private void AddColumns()
      {
        DataColumn empName = new DataColumn("Name", typeof(String));
        DataColumn empSalary = new DataColumn("Salary", typeof(Int32));
        dt.Columns.Add(empName);
        dt.Columns.Add(empSalary);
      }

Here it is called in the page load method because we want to create the structure of the table in the page load only once. See:

protected void Page_Load(object sender, EventArgs e)

       {

        /* only once of page load */

        if (!Page.IsPostBack)

        {

            AddColumns();

        }

     }

5. Add Row and Display

Here I am adding a new row in the Data Table and that data row is shown in a grid view when the Add button is clicked.

    private void AddNewRow()

    {

        string empName = txtName.Text;

        int empSalary = Convert.ToInt32(txtSalary.Text);

 

        DataRow dr = dt.NewRow();

        dr["Name"] = empName;

        dr["Salary"] = empSalary;

 

        dt.Rows.Add(dr);

    }

 

    protected void AddEmployee(object sender, EventArgs e)

    {

        AddNewRow();

        empGrid.DataSource = dt;

        empGrid.DataBind();

    } 

6. Create User-Defined Table Type

In the database I create a user defined Table type that has two columns to store the employee's name and their salary.



7. Create store procedure

Create a stored procedure that has a user defined table type as parameter. This parameter contains all data passed by the client.



8. Add Button in UI to Insert all rows(.aspx page)

I create a button that has a click event. On that event we add all the rows in the database table.
 

<asp:Button ID="btnAddAllEmployee" runat="server" Text="Add Employees" OnClick="AddAllEmployee" />

9. Insert All Rows

Here I insert all rows on button click. I pass the data table using user defined table type that has all rows of Data Table and it is used as a sqlparameter.
 

protected void AddAllEmployee(object sender, EventArgs e)

    {

        string connString = "Data Source = sandeepss-PC; database = Development; user = sa; password = knowdev";

        using (SqlConnection con = new SqlConnection(connString))

        {

 

            SqlParameter pTableValue = new SqlParameter("@EmployeeData", dt);

            pTableValue.SqlDbType = SqlDbType.Structured;

            pTableValue.TypeName = "[dbo].[EmplyeeType]";

 

            SqlCommand cmd = new SqlCommand("_sp_AddEmployee", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(pTableValue);

 

            con.Open();

            cmd.ExecuteNonQuery();

        }

    }

10. OUTPUT