One developer's SQL blog

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.

Twitter

Linkedin

Facebook