Insert Multiple Rows from ASP.NET

This blog explains how to insert multiple rows from an ASP.NET webpage to SQL SERVER database with the help of TVP(table valued parameter).

Take a WebForm and design by taking the following one.



The “Add to datatable” button will add rows to a datatable. On clicking the “Add to database” button, all the rows from the datatable will be inserted in a database table with the help of a stored procedure which takes a Table Valued Parameter.
The table structure is as below:

CREATE TABLE [dbo].[BCP](
    [Name] [varchar](50) NULL,
    [Age] [int] NULL
)

The TVP is as below:

create type tbl1 as table
(nm varchar(50),ag int)

The stored procedure is as below:

CREATE PROCEDURE InsertBcp
  (@tvp1 dbo.tbl1 READONLY)
  as begin
  insert bcp
  select * from @tvp1
  end

Code for the Page Load event:

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("Age", typeof(int));
        Session["datatable"] = dt;
    }

}

Code for the “Add to datatable” button:

DataTable dt = (DataTable)Session["datatable"];
dt.Rows.Add(TextBox1.Text, Convert.ToInt32(TextBox2.Text));

Code for the “Add to database” button:

DataTable dt = (DataTable)Session["datatable"];
SqlConnection conn =
  new SqlConnection("server=.;database=cts1;integrated security=true");
SqlCommand cmd = new SqlCommand("InsertBcp", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@tvp1", dt);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Remember to use session state because of ASP.NET’s stateless nature.