how to save multiple rows at a time in storepocedure using asp.net

First create a table in your database

Create table EMP

(

eid int,

name varchar(50)

)



Create a type in your database



Create type tb as table
(
id int,
name varchar(50)
)

Create a Procedure in your database

 

Create proceure EMP1
    (
    @x tb readonly
    )
  
AS
    begin
    insert into emp select * from @x
    end



C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection cn;
    SqlDataAdapter da;
    SqlCommand cm;
    DataTable dt = new DataTable();
    protected void Page_Load(object sender, EventArgs e)
    {
        cn = new SqlConnection(ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString());
        if (!IsPostBack)
        {
            cn.Open();
            dt.Columns.Add("EID", typeof(string));
            dt.Columns.Add("NAME", typeof(string));
            DataRow dr;
          
            for (int i = 11; i < 21; i++)
            {
                dr = dt.NewRow();
                dr[0] = i.ToString();
                dr[1] = "AB" + i.ToString();
                dt.Rows.Add(dr);
            }
            cm = new SqlCommand("emp1", cn);
            cm.CommandType = CommandType.StoredProcedure;
            SqlParameter sp = cm.Parameters.Add("@x", SqlDbType.Structured);
            sp.Value = dt;
            cm.ExecuteNonQuery();
        }
      
    }
}