Pass DataTable to Stored Procedure as Parameter in SQL Server

In SQL Server, when we have to pass multiple rows of data to be added or updated in a table the simplest way to send data from the front-end application to the database is by using DataTable as a parameter sent to a stored procedure. Any number of data records can be sent to the database table by this method. In this article, we will explore ‘Sending DataTable as a parameter to stored procedure’.

In this article, we explore the utilization of DataTables as parameters in relational database stored procedures. DataTables, in-memory representations of tables, offer an effective means to handle bulk data and structured datasets. We discuss their role in .NET and ADO.NET, emphasizing their benefits. Through a step-by-step example, we demonstrate how to send a DataTable to a stored procedure, covering the creation of a user-defined table type, modification of the stored procedure, and implementation in a C# application.

What is a Data Table?

A DataTable represents the relational data of a single table in memory. It comprises columns, rows, and cells with specific values. A DataTable is something similar to an Excel file or an XML file which can be passed to the database using a stored procedure. The data generally is local to the .Net application and can be populated from SQL data source using a DataAdapter object.

Uses of DataTable as Parameter to Stored Procedure

Sending a DataTable as a parameter to the stored procedure can help send a large list of data at a single call to the database. This can avoid multiple calls to the database with multiple parameters for each data. Generally, for sending the data of a student for example, we will need multiple parameters for each data value and when we need to send data of multiple students, each student’s data needs to be sent using multiple calls to a database, one call for each record. When we deal with large record sets this can be very inefficient and time-consuming. Using the DataTable is a very efficient quick way to send large lists of data from the back end to the database for adding, updating, or deleting any data.

Example of Sending DataTable as a Parameter to a Stored Procedure

Below are examples of using DataTable as a Parameter to a Stored Procedure using the .Net front end.

Example 1

Below are steps in creating a DataTable and sending it as a parameter to the Stored Procedure using the .NET front end.

Step 1: We need to create a user-defined Table Type that matches the structure of database table which will be used for adding, updating, or Deleting data.

CREATE TYPE dbo.CustomTableTypeDemo AS TABLE
(
    SNo INT,
    DataText NVARCHAR(MAX)
)

A custom table of Table Type is created with same structure ‘SNo and DataText’ as the table in the database where the data is being transacted using this Table Type. When the above statement executes sucessfully, ‘Commands completed successfully.’ is displayed in the results window.

From the below image you can see the new User Defined Table Type ‘CustomTableTypeDemo‘ being created:

Step 2: Then we need to create the Stored Procedure which has the Table Type parameter

CREATE PROCEDURE InsertDataDemo
    @DataToInsert dbo.CustomTableTypeDemo READONLY
AS
BEGIN
    INSERT INTO DataTableTest (SNo, DataText)
    SELECT SNo, DataText FROM @DataToInsert
END

Here the ‘InsertDataDemo‘ is the stored procedure with the Table Type parameter ‘CustomTableTypeDemo‘ created in step 1. On execution of the above code the stored procedure ‘InsertDataDemo’ is created when the output window shows the ‘Commands completed successfully.’

Step 3: From the .NET front end application we need to create a DataTable and add data to be passed to the database table.

DataTable myDataTable = new DataTable();
myDataTable.Columns.Add("SNo", typeof(int));
myDataTable.Columns.Add("DataText", typeof(string));
myDataTable.Rows.Add(105, "Row 105 Data");
myDataTable.Rows.Add(106, "Row 106 Data");

Here database objects from .NET, C# code is created to add data to the DataTable. This example is just a representation of static data, but user input from UI can be received and added to this DataTable for real-time scenarios.

Step 4: Call the stored procedure ‘InsertDataDemo’ with the Table Type parameter as below by creating database reference objects and executing query commands:

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.InsertDataDemo";
cmd.Connection = objconn; // an open SqlConnection
SqlParameter param = new SqlParameter();
param.ParameterName = "@DataToInsert";
param.SqlDbType = SqlDbType.Structured;
param.Value = myDataTable;
param.TypeName = "dbo.CustomTableTypeDemo";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();

The above code creates reference objects for connecting to the stored procedure and datatable parameter. The last statement ‘ExecuteNonQuery‘ executes query to connect and send data to the database. When we query the table ‘DataTableTest‘ from SQL Management studio, you can see the data of two records with data added from front end in step 3 as below:

Select * from DataTableTest
------------------------
SNo    DataText
105    Row 105 Data
106    Row 106 Data

Example 2

Below is another example of using DataTable as parameter to Stored procedure for updating data

Step 1: In this example we are going to update the ‘Grade_Level‘ column in the ‘Students‘ table which has ‘NULL’ value.

Students Table with ‘Null’ value before update

Create the User-Defined Table Type named ‘UpdateStudentData‘ to update ‘Students’ table

CREATE TYPE [dbo].[UpdateStudentData] AS TABLE(
    [Student_ID] [int] NULL,
    UpdateData1 [varchar] (100) NULL,
    UpdateData2 [varchar] (100) NULL
)

Step 2: Create the stored procedure with the Table Type parameter named ‘UpdateStudentData‘ to update data in ‘Students’ Table

CREATE PROCEDURE [dbo].[UpdateStudentData]
@StudDataToUpdate dbo.UpdateStudentData READONLY
AS
BEGIN
    Update  Students 
    Set Grade_Level=UpdateData1 
    FROM @StudDataToUpdate Stud
    where Students.Student_ID=Stud.Student_ID

END

Step 3: From the .NET front end application we need to create a DataTable and add data to be passed to update the ‘Students’ Table table.

DataTable myDataTable = new DataTable();
myDataTable.Columns.Add("Student_ID", typeof(int));
myDataTable.Columns.Add("UpdateData1", typeof(string));
myDataTable.Columns.Add("UpdateData2", typeof(string));
myDataTable.Rows.Add(501, "Higher Secondary", " ");
myDataTable.Rows.Add(502, "Higher Secondary", " ");
myDataTable.Rows.Add(503, "High School", " ");
myDataTable.Rows.Add(504, "Higher Secondary", " ");
myDataTable.Rows.Add(505, "High School", " ");

Step 4: Call the stored procedure ‘UpdateStudentData‘ with the Table Type parameter as below by creating database reference objects and execute query commands to update the ‘Students‘ table data:

SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.UpdateStudentData";
cmd.Connection = objconn; // an open SqlConnection
SqlParameter param = new SqlParameter();
param.ParameterName = "@StudDataToUpdate";
param.SqlDbType = SqlDbType.Structured;
param.Value = myDataTable;
param.TypeName = "dbo.UpdateStudentData";
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();

Below is the updated data in the ‘Students’ table after the above code executed from .NET application.

Updated Students table with data for ‘Grade_Level’ table ‘Null’ values

Important Terms to Note

• DataTable: A DataTable represents the relational data of a single table in memory.
• User-defined Table Type: A user-defined Table Type with the schema that matches the table used in the database
• Creating Stored procedure with DataTable as a parameter, the parameter of User Defined Table Type which matches the DataTable and Database Table.

Conclusion

The DataTable as a parameter to stored procedure in SQL Server is a very efficient and useful option to transfer large amounts of data at one go from front-end to back-end and do data manipulation for adding, updating, or deleting a table in a database. The DataTable can be considered as the best choice for bulk insert or complex data operations by passing the data as a parameter in a stored procedure. A DataTable can replace an XML file or long list of parameters used to send data to a stored procedure.