Alternate to sql server table values parameter

I have created an ASP.Net project where i have implemented Bulk Insert operation.

While working with Entity Framework in ASP.Net WebForms, MVC and Windows Forms, you can use SqlParameter class to make Table accepted as table-valued parameter (TVP) by defining the Name of parameter and DB Type of parameter.

And its object is passed as parameter to ExecuteSqlCommand method of Entity Framework called using DBML (Database Markup Language) class.

For more information on DBML, please refer below reply https://www.aspsnippets.com/questions/520746/Question520746/replies/2

Refer below code:

Database

You can download the database table SQL by clicking the download link below. Download SQL file

Then, I have created a User Defined Table Type in SQL Server using the following query.

CREATE TYPE [dbo].[CustomerType] AS TABLE(
      [Id] [int] NULL,
      [Name] [varchar](100) NULL,
      [Country] [varchar](50) NULL
)

Stored Procedure is created which will accept the DataTable as parameter and then will insert all records into the table.

CRTEATE PROCEDURE [dbo].[Insert_Customers]
      @tblCustomers CustomerType READONLY
AS
BEGIN
      SET NOCOUNT ON;
     
      INSERT INTO Customers(CustomerId, Name, Country)
      SELECT Id, Name, Country FROM @tblCustomers
END

XML File URL

The following XML file I used for populating GridView.

https://raw.githubusercontent.com/aspsnippets/test/master/Customers.xml

HTML Markup

<asp:GridView ID="gvCustomers" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="CheckBox1" runat="server" />
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="Id" HeaderText="Customer Id" />
        <asp:BoundField DataField="Name" HeaderText="Name" />
        <asp:BoundField DataField="Country" HeaderText="Country" />
    </Columns>
</asp:GridView>
<br />
<asp:Button ID="btnInsert" runat="server" Text="Bulk Insert" OnClick="OnInsert" />

Code

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("~/Customers.xml"));
        gvCustomers.DataSource = ds.Tables[0];
        gvCustomers.DataBind();
    }
}

protected void OnInsert(object sender, EventArgs e)
{
    DataTable dt = new DataTable();
    dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                                            new DataColumn("Name", typeof(string)),
                                            new DataColumn("Country",typeof(string)) });
    foreach (GridViewRow row in gvCustomers.Rows)
    {
        if ((row.FindControl("CheckBox1") as CheckBox).Checked)
        {
            int id = int.Parse(row.Cells[1].Text);
            string name = row.Cells[2].Text;
            string country = row.Cells[3].Text;
            dt.Rows.Add(id, name, country);
        }
    }
    if (dt.Rows.Count > 0)
    {
        //Defining Table Valued Parameter
        SqlParameter sqlParameter = new SqlParameter("@tblCustomers", SqlDbType.Structured)
        {
            TypeName = "dbo.CustomerType",
            Value = dt
        };

        //Creating DBML Class
        SampleEntities entities = new SampleEntities();

        //Executing Stored Procedure
        entities.Database.ExecuteSqlCommand("EXEC Insert_Customers @tblCustomers", sqlParameter);
    }
}

Screenshot