Pass User defined Table type to Stored Procedure C#

This post, walk through the C# code needed to call that procedure and pass table as parameter in stored procedure from c# using ADO.NET.

Pass User defined Table type to Stored Procedure C#

Getting Started

When working with large datasets or needing to pass multiple rows of data to a SQL Server stored procedure, User-Defined Table Types (UDTTs) are a powerful tool. They allow you to pass an entire table as a parameter.

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 mentions DbType in SqlParameter class 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

This article saw how to pass Table type to Stored Procedure c# using Ado.net , I hope you have enjoyed it a lot.

Thanks

Pass User defined Table type to Stored Procedure C#