SQL Server  

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. 
  1. SqlParameter sqlParam= new SqlParameter();  
  2. sqlParam.ParameterName = "@StudentName";  
  3. sqlParam.DbType = DbType.String;  
  4. 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
  1. SqlParameter Parameter = new SqlParameter;  
  2. Parameter.ParameterName = "@PhoneBook";  
  3. Parameter.SqlDbType = SqlDbType.Structured;  
  4. 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. 
  1.    
  2. //Phone book list    
  3. List<PhoneBook> PhoneBooks    
  4.  //CReating Table    
  5.  DataTable PhoneTable = new DataTable();    
  6.     
  7.  // Adding Columns    
  8.  DataColumn COLUMN=new DataColumn();    
  9.  COLUMN.ColumnName="ID";    
  10.  COLUMN.DataType= typeof(int);    
  11.  PhoneTable.Columns.Add(COLUMN);    
  12.     
  13.  COLUMN = new DataColumn();    
  14.  COLUMN.ColumnName = "ContactNumber";    
  15.  COLUMN.DataType = typeof(string);    
  16.  PhoneTable.Columns.Add(COLUMN);    
  17.     
  18.  COLUMN = new DataColumn();    
  19.  COLUMN.ColumnName = "ContactName";    
  20.  COLUMN.DataType = typeof(string);    
  21.  PhoneTable.Columns.Add(COLUMN);    
  22.     
  23.  // INSERTING DATA    
  24.  foreach (UserPhoneBook UPB in PhoneBooks)    
  25.  {    
  26.     DataRow DR = PhoneTable.NewRow();    
  27.     DR[0] = UPB.UserName;    
  28.     DR[1] = UPB.ContactNumber;    
  29.     DR[2] = UPB.ContactName;    
  30.     PhoneTable.Rows.Add(DR);    
  31.  }    
  32.  //Parameter declaration    
  33.  SqlParameter[] Parameter = new SqlParameter[2];    
  34.  Parameter[0].ParameterName = "@PhoneBook";    
  35.  Parameter[0].SqlDbType = SqlDbType.Structured;    
  36.  Parameter[0].Value = PhoneTable;    
  37.     
  38.  Parameter[1].ParameterName = "@Return_Value";    
  39.  Parameter[1].Direction = ParameterDirection.ReturnValue;    
  40.  //Executing Procedure  
  41.  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#.

Membership not found