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.
  2. //Phone book list
  3. List<PhoneBook> PhoneBooks
  4. //CReating Table
  5. DataTable PhoneTable = new DataTable();
  7. // Adding Columns
  8. DataColumn COLUMN=new DataColumn();
  9. COLUMN.ColumnName="ID";
  10. COLUMN.DataType= typeof(int);
  11. PhoneTable.Columns.Add(COLUMN);
  13. COLUMN = new DataColumn();
  14. COLUMN.ColumnName = "ContactNumber";
  15. COLUMN.DataType = typeof(string);
  16. PhoneTable.Columns.Add(COLUMN);
  18. COLUMN = new DataColumn();
  19. COLUMN.ColumnName = "ContactName";
  20. COLUMN.DataType = typeof(string);
  21. PhoneTable.Columns.Add(COLUMN);
  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;
  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#.