Passing DataTable to StoredProcedure as Parameter in C#

C#ADO.NETPass a DataTable to a Stored ProcedureStored Procedure as Parameter

15 Comments

Join the conversation! Your thoughts help the community grow.

ratnesh kumar6y ago
Useful knowledge.
usha lekkala6y ago
While clicking datagridview cell I want to call stored procedure and generate excel . can you help me.(c#)
usha lekkala6y ago
Hi kailash can you help me with ...
Nitin11y ago
Nice
- Former member11y ago
  thanks
Deepak Verma11y ago
Hi Kailash Chandra Behera, It'll be good if you tell, what will be the datatype of parameter '@PhoneBook' in MS Sql Server Stored Procedure and which versions are supported. Please provide any back-end sample.
- Former member11y ago
  Hi Deepak,Thanks for your interest, i have posted an article for you query , please visit the link: http://www.c-sharpcorner.com/UploadFile/87b416/create-a-store-procedure-that-datatableas-parameter-in-C-Sharp-1/
Syed Shanu11y ago
Good Work.Really a good and useful one for members.If its poosible for you to update with a sample source file with example, it will be more useful for the members.
- Former member11y ago
  Than shanu, thanks for your interest. i will update source code soon
Rahul Kumar Saxena11y ago
Good work
- Former member11y ago
  thanks
Khargesh Rajput11y ago
nice sir
- Former member11y ago
  thanks
Rakesh11y ago
Nice one
- Former member11y ago
  thanks

Introduction

SQL Server Stored Procedures support System.Data.DataTable as a parameter. We can pass the DataTable to the Stored Procedure using ADO.Net in the same way as we provided using the System.Data.SqlParameter class, but needs a few changes in the datatype.

Normally we provide DbType of SqlParameter for a normal parameter like varchar, nvarchar, int and so on as in the following code.

SqlParameter sqlParam= new SqlParameter();
sqlParam.ParameterName = "@StudentName";
sqlParam.DbType = DbType.String;
sqlParam.Value = StudentName;

But in the case of a Table parameter, we do not need to provide a DbType as the parameter data type. We need to provide SqlType rather then DbType.

Example

SqlParameter Parameter = new SqlParameter;
Parameter.ParameterName = "@PhoneBook";
Parameter.SqlDbType = SqlDbType.Structured;
Parameter.Value = PhoneTable;

The following example receives a list of phone books and stores them in a database using ADO.Net. The example retrieves the phone book details from the list and stores them into the DataTable and passes this table to the Stored Procedure named NewPhoneBook as a parameter.

//Phone book list
List<PhoneBook> PhoneBooks
//CReating Table
DataTable PhoneTable = new DataTable();
// Adding Columns
DataColumn COLUMN=new DataColumn();
COLUMN.ColumnName="ID";
COLUMN.DataType= typeof(int);
PhoneTable.Columns.Add(COLUMN);
COLUMN = new DataColumn();
COLUMN.ColumnName = "ContactNumber";
COLUMN.DataType = typeof(string);
PhoneTable.Columns.Add(COLUMN);
COLUMN = new DataColumn();
COLUMN.ColumnName = "ContactName";
COLUMN.DataType = typeof(string);
PhoneTable.Columns.Add(COLUMN);
// INSERTING DATA
foreach (UserPhoneBook UPB in PhoneBooks)
{
DataRow DR = PhoneTable.NewRow();
DR[0] = UPB.UserName;
DR[1] = UPB.ContactNumber;
DR[2] = UPB.ContactName;
PhoneTable.Rows.Add(DR);
}
//Parameter declaration
SqlParameter[] Parameter = new SqlParameter[2];
Parameter[0].ParameterName = "@PhoneBook";
Parameter[0].SqlDbType = SqlDbType.Structured;
Parameter[0].Value = PhoneTable;
Parameter[1].ParameterName = "@Return_Value";
Parameter[1].Direction = ParameterDirection.ReturnValue;
//Executing Procedure
SqlHelper.ExecuteNonQuery(this.ConnectionString, CommandType.StoredProcedure, "[NewPhoneBook]", Parameter);

Summary

I hope you have learned how to pass a table to a Stored Procedure as a parameter using ADO.Net in C#.