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.