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.
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
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.