Passing a DataTable as table parameter from C# to stored procedures using table type

So may times I’ve seen useless loops to save many rows of data into a table, where data was captured in the front end part of the application. And sometimes, the row number was quite high – for example, where user was filtering some rows available to him, or imported from a data source like excel or access mdb file.
One of the options I’ve seen to replace loops was to transform the data into an XML, and pass the XML to a stored procedure that would handle it – but there are other things you need to take care of when doing this.
What I like to use, and this is not used as much as it should be in my opinion, is a table value type parameter passed to a stored procedure.
Firstly, I need a table type declared:

CREATE TYPE dbo.MY_TABLE_TYPE AS TABLE (
	Id INT,
	Value VARCHAR(100)
)
GO

After this is on, I need to create the procedure that will handle the data:

CREATE PROCEDURE dbo.prc_ProcedureUsingTableTypeParameter
	@dt MY_TABLE_TYPE READONLY
AS 
BEGIN
	SELECT *
	FROM	@dt
END
GO

You don’t necessarily need a stored procedure, you can use the parameter passed directly in your sql statement.

Ok, now the c# part, for demonstration purposes, let’s create a DataTable variable matching our MY_TABLE_TYPE table type above:

var table = new DataTable();
table.Columns.Add(new DataColumn("Id", System.Type.GetType("System.Int32")));
table.Columns.Add(new DataColumn("Value", System.Type.GetType("System.String")));

table.Rows.Add(table.NewRow());
table.Rows[0]["Id"] = 1;
table.Rows[0]["Value"] = "Abc";

Now, if I want to add this DataTable variable as a SqlParameter, and pass it to the stored procedure above, I need to specify for the SqlParameter the following:
– name and value – which are self explanatory
– SqlDbType = SqlDbType.Structured – which indicates it’s a table value parameter
– TypeName – the name of the table value type we defined = MY_TABLE_TYPE

new SqlParameter
        {
            ParameterName = "table",
            Value = table,
            SqlDbType = SqlDbType.Structured,
            TypeName = "dbo.MY_TABLE_TYPE"
        }
    }

That’s all!

Next thing, you can use the parameter to call the stored procedure. Here’s how I’ve used it as a whole:

// create a DataTable
var table = new DataTable();
table.Columns.Add(new DataColumn("Id", System.Type.GetType("System.Int32")));
table.Columns.Add(new DataColumn("Value", System.Type.GetType("System.String")));

// add a new dummy row to our DataTable
table.Rows.Add(table.NewRow());
table.Rows[0]["Id"] = 1;
table.Rows[0]["Value"] = "Abc";

// add the sql statement to execute
const string sql = @"
    EXECUTE dbo.prc_ProcedureUsingTableTypeParameter @table
";

// add the parameter
var parameters = new[]{   
            new SqlParameter
                    {
                        ParameterName = "table",
                        Value = table,
                        SqlDbType = SqlDbType.Structured,
                        TypeName = "dbo.MY_TABLE_TYPE"
                    }
                };

// execute the query - I will attach DbUtil.cs class to this post.
var resultDataSet = DbUtil.ExecuteSqlCommand(sql, parameters);

This will replace the need to loop through your DataTable / List, and process lines one by one, which would mean one call to your database per line, and when there are a lot of rows there, it means improvements to your application. I also prefer this solution over XML solution because is cleaner, and I don’t need to build the xml inside the application, and query it inside the database afterwards.

Here’s the Program.cs and DbUtil.cs to download if you’d like.

  1. Nice stuff, thx bro!

Leave a Comment