Passing DataTable to StoredProcedure as Parameter in C#

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#.