Insert Rows Using User Defined Table Types

Article

Introduction

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

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

First, I am creating a UI design to add the employee's name and salary. It has a button that, when clicked, creates a row in the Data Table 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>

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

using System;
using System.Data;
using System.Data.SqlClient;

Step 3. Create a Data Table

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

static DataTable dt = new DataTable();

Step 4. Create Columns and Add on the Page Load

First, I create columns for the Data Table in the page load event. These columns are defined as per a user-defined table type; in other words, in a data table, we define several 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 the page load method because we want to create the table structure in the page load only once. See:

protected void Page_Load(object sender, EventArgs e)
       {
        /* only once of page load */
        if (!Page.IsPostBack)
        {
            AddColumns();
        }
     }

Step 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();
    }

Step 6. Create a User-Defined Table Type

In the database, I create a user-defined Table type with two columns to store the employee's name and salary.

Step 7. Create a store procedure

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

Step 8. Add a Button in the 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" />

Step 9. Insert All Rows

Here I insert all rows on button click. I pass the data table using a user-defined table type with all rows of the Data Table, and it is used as a SQL parameter.

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();
        }
    }

Output