How to Send (Pass) DataTable as parameter to Stored Procedure in Asp.Net using C#

Description:

In this example we explain that how to send DatatTable as parameter to stored procedure in Asp.Net using C#.or how to pass Data Table as parameter in stored procedure in C#.or passing Data Table to stored procedure as an argument using C#.or how to pass Data Table to stored procedure in asp.net.

Below is the code to how to pass data table in stored procedure in SQL Server using C#.

Procedure:

CREATE TYPE [dbo].[CustomerType] AS TABLE(

      [Id] [int] NULL,

      [Name] [varchar](100) NULL,

      [Country] [varchar](50) NULL

)

GO

CREATE 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

Code:

protected void Bulk_Insert(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 GridView1.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)

            {

                string consString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

                using (SqlConnection con = new SqlConnection(consString))

                {

                    using (SqlCommand cmd = new SqlCommand("Insert_Customers"))

                    {

                        cmd.CommandType = CommandType.StoredProcedure;

                        cmd.Connection = con;

                        cmd.Parameters.AddWithValue("@tblCustomers", dt);

                        con.Open();

                        cmd.ExecuteNonQuery();

                        con.Close();

                    }

                }

            }

        }