How to Pass DataTable As Parameter to Stored Procedure in SQL Server

Hi friends, In this tutorial, I will show a complete example of passing DataTable as a parameter to Stored Procedure in C#.

How to Pass DataTable as a parameter to Stored Procedure in C#:

This is a very common scenario for any application that you need to insert bulk data to SQL Table or you need to pass whole DataTable as a parameter to SQL Server Stored Procedure in C#. For such a case, you need to create User Defined Table Type in SQL Server and it must have the same schema as that of the DataTable that we need to pass to the Stored Procedure as the parameter.

Example:

I am going to Create a Windows Application that will insert a list of products in SQL Server.

Create Database and Table:

CREATE DATABASE [Debugonweb]

USE [Debugonweb]


CREATE TABLE [dbo].[ProductDetails](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[ProductName] [varchar](100) NULL,
[Price] [float] NULL,
[Quantity] [float] NULL,
[TotalPrice] [float] NULL
) ON [PRIMARY]

GO

CREATE TABLE TYPE:

USE [Debugonweb]
GO
CREATE TYPE [dbo].[tblProductDetails] AS TABLE(
[ProductName] [varchar](100) NULL,
[Price] [float] NULL,
[Quantity] [float] NULL,
[TotalPrice] [float] NULL
)
GO

Create Stored Procedure:

USE [Debugonweb]
GO

CREATE PROCEDURE [dbo].[uspAddProductDetails]
@tblProductDetails AS tblProductDetails READONLY
AS
BEGIN
SET NOCOUNT ON;

INSERT INTO [dbo].[ProductDetails]
([ProductName]
,[Price]
,[Quantity]
,[TotalPrice])

SELECT 
[ProductName]
,[Price]
,[Quantity]
,[TotalPrice]
FROM @tblProductDetails


SET NOCOUNT OFF;
END
GO

Create a Windows Application Project:

Step #1: Open Visual Studio and Create a Windows Application Project called PassDataTable.

DataTable As Parameter

Step #2: Drag Four Labels, Four Textboxes, Three Buttons, and one DataGridView On Form Area.

Step #3: Change Text Property of Labels as:

Label1 → Product Name
Label2 → Price
Label3 → Quantity
Label4 → Total Price

Step #4: Change Name property of Textboxes as:

Textbox1→ txtProductName
Textbox2→ txtPrice
Textbox3→ txtQuantity
Textbox4→ txtTotalPrice

Step #4: Change Name property of Buttons as:

Button1→ btnAdd
Button2→ btnSaveAll
Button3→ btnClear

Step #5: Change the Name Property of DataGridView as :

DataGridView1→ dgvProduct

Step #6: Your Form should look like this.

DataTable As Parameter

Step #7: Double Click the Form and add the following code.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace PassDataTable
{
public partial class Form1 : Form
{
DataTable _dtProduct = new DataTable();
public Form1()
{
_dtProduct = DefineProductDataTable();
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
DefineProductDataTable();
dgvProduct.DataSource = _dtProduct;
}

private void btnAdd_Click(object sender, EventArgs e)
{
DataRow drNewRow = _dtProduct.NewRow();
drNewRow["ProductName"] = txtProductName.Text;
drNewRow["Price"] = txtPrice.Text;
drNewRow["Quantity"] = txtQuantity.Text;
drNewRow["TotalPrice"] = txtTotalPrice.Text;
_dtProduct.Rows.Add(drNewRow);
txtProductName.Clear();
txtPrice.Text = "0.0";
txtQuantity.Text = "0.0";
txtTotalPrice.Text = "0.0";
}
public DataTable DefineProductDataTable()
{
DataTable dtProduct = new DataTable();
DataColumn dcProductName = new DataColumn("ProductName");
DataColumn dcPrice = new DataColumn("Price");
DataColumn dcQuantity = new DataColumn("Quantity");
DataColumn dcTotalPrice = new DataColumn("TotalPrice");
dtProduct.Columns.Add(dcProductName);
dtProduct.Columns.Add(dcPrice);
dtProduct.Columns.Add(dcQuantity);
dtProduct.Columns.Add(dcTotalPrice);
return dtProduct;


}

private void txtQuantity_TextChanged(object sender, EventArgs e)
{
try
{
if (!String.IsNullOrEmpty(txtPrice.Text) && !String.IsNullOrEmpty(txtQuantity.Text))
{
double dQuantity = Convert.ToDouble(txtQuantity.Text);
double dPrice = Convert.ToDouble(txtPrice.Text);
double dTotalPrice = dQuantity * dPrice;
txtTotalPrice.Text = dTotalPrice.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("Invalid value.");
}
}

private void txtPrice_TextChanged(object sender, EventArgs e)
{
try
{
if (!String.IsNullOrEmpty(txtPrice.Text) && !String.IsNullOrEmpty(txtQuantity.Text))
{
double dQuantity = Convert.ToDouble(txtQuantity.Text);
double dPrice = Convert.ToDouble(txtPrice.Text);
double dTotalPrice = dQuantity * dPrice;
txtTotalPrice.Text = dTotalPrice.ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("Invalid value.");
}
}
public bool SaveAllRecord()
{
bool bFlag = false;
try
{
using (SqlConnection dbCon = new SqlConnection("Data Source=DESKTOP-CN5IQI9\\SQLEXPRESS;Initial Catalog=Debugonweb;Integrated Security=true"))
{
using (SqlCommand cmdAddProductDetails = new SqlCommand("[dbo].[uspAddProductDetails]", dbCon))
{
cmdAddProductDetails.CommandType = CommandType.StoredProcedure;
cmdAddProductDetails.Parameters.AddWithValue("@tblProductDetails", _dtProduct);
if (dbCon.State == ConnectionState.Closed)
dbCon.Open();
cmdAddProductDetails.ExecuteNonQuery();
bFlag = true;
}
}
}
catch (Exception ex)
{
MessageBox.Show("Error while saving records..!!");
}
return bFlag;
}
public void ClearFields()
{
txtProductName.Clear();
txtPrice.Text="0.0";
txtQuantity.Text = "0.0";
txtTotalPrice.Text = "0.0";
_dtProduct.Rows.Clear();
}

private void btnClear_Click(object sender, EventArgs e)
{
ClearFields();
}

private void btnSaveAll_Click(object sender, EventArgs e)
{
if (SaveAllRecord() == true)
{
MessageBox.Show("Records Saved successfully");
ClearFields();
}

}
}
}

Step #8: Bind the events to Form Controls.

Step #9: Replace Your-DataSource with your SQL Server Name.

Step #10: Run the application and verify the output.