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.
Nice stuff, thx bro!