How To Pass A DataTable To A Stored Procedure Using C# and SQL Server

TODO:

Have you ever wanted to pass a DataTable from your C# application to a Stored Procedure in SQL Server?

SOLUTION:

Step 1:  Create the User-Definied Table Type in SQL Server

CREATE TYPE [dbo].[MyUserDefinedType] AS TABLE(
	[Id] [int] NULL,
	[FieldOne] [int] NULL,
	[FieldTwo] [int] NULL
)

Step 2:  Create the DataTable and call the Stored Procedure

using (DataTable myDataTable = new DataTable())
{
     myDataTable.Columns.Add(“FieldOne”, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldTwo, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldThree, System.Type.GetType("System.Int32"));
                
     DataRow dataRow = null;
                
     for (int i = 0; i < 100; i++)
     {
          dataRow = myDataTable.NewRow();  
          dataRow[“FieldOne] = 1;
          dataRow[“FieldTwo”] = 2;
          dataRow[“FieldThree] = 3;
          myDataTable.Rows.Add(dataRow);           
     }

     using (SqlConnection connection = new SqlConnection(“connectionstring”))
     {
          
          using (SqlCommand command = connection.CreateCommand())
          {
               connection.Open();
               command.CommandType = CommandType.StoredProcedure;
               command.CommandTimeout = 90;
               command.CommandText = “MyStoredProcedureName;
               command.Parameters.AddWithValue(“@IncomingDataTable”, myDataTable);
               command.ExecuteNonQuery();
          }
     }
}

Step 3:  Create the Table

CREATE TABLE MyTable(FieldOne int NULL, FieldTwo NULL, FieldThree NULL)

Step 4:  Create the Stored Procedure

CREATE PROCEDURE [dbo].[MyStoredProcedure]
@MyUserDefinedType MyUserDefinedType READONLY

AS
BEGIN 

    INSERT INTO dbo.MyTable
	([FieldOne], [FieldTwo], [FieldThree])
	
	SELECT [FieldOne], [FieldTwo], [FieldThree] FROM @MyUserDefinedType

END

NOTES:

Be sure that the DataTable columns are added in the SAME order as they are created in the SQL Server User Type.

How To Check DataRow Column To Correct Specified Cast Is Invalid

TODO:

Have you ever received a invalid cast exception, when checking the value of a column in a data row?

SOLUTION:

OrderNumber =  row.IsNull("ordernumber") ? "" : (string)row["ordernumber"];

NOTES:

OrderNumber above is a string, and row is a row in a DataTable

How To Convert A SqlDataReader To A DataTable Using C#

TODO:

Have you ever wanted to convert a SqlDataReader to a DataTable in C#?

SOLUTION:

DataTable datatable = null;
string ErrorMessage = "";

try
{
     using (SqlConnection myConnection = new SqlConnection(ConnectionString))
     {
           using (SqlCommand cmd = new SqlCommand("some sql here", myConnection))
           {
                 myConnection.Open();
                 using (SqlDataReader reader = cmd.ExecuteReader())
                 {
                       datatable = new DataTable();
                       datatable.Load(reader);
                 }
           }
      }
}
catch (Exception ex)
{
     ErrorMessage = ex.Message;
}

NOTES:

There are no notes on this topic.