Using Table Value Parameters (TVP) in the DNN Data Provider
Aug
23
Written by:
8/23/2011 2:29 PM
I have been working on a new web application and recently ran into an interesting situation where I wanted to add possibly hundreds of records from one data entry screen. In the past I have done this a few different ways:
- Loop through set of data and add each row one by one.
- Create some delimited string and create some SQL to parse it into a temp table.
Both of those methods work but have serious drawbacks.
Method one is very easy to program but all those insert statements cause a lot of traffic to and from the database, this can cause a lot of scaling issues. Method two works if you are only inserting one value at a time and that value is not complex like lots of text or image byte arrays.
A much easier method of doing this would be to create a table fill it with data and simply use a insert statement to select all records from the table into our datatable. Now with SQL 2008 we can pass Table Valued Parameters (TVP) to a stored procedure and use it just like any other table in the database.
Here's how: (VB code)
1. Create your datatable in code. (I make no claims this is the best way to make a datatable in code and in fact would love input here to make this more efficient)
1.Dim myTable As New DataTable
2.Dim col1 As New DataColumn("Col1", System.Type.GetType("System.Int32"))
3.Dim col2 As New DataColumn("Col2", System.Type.GetType("System.Int32"))
4.myTable.Columns.Add(col1)
5.myTable.Columns.Add(col2)
2. Fill the datatable with data. (in this case I am adding one row of data. You would loop or fill this datatable however is needed for your application.)
1.Dim vals(1) As Object
2.vals(0) = 'integer value'
3.vals(1) = 'integer value'
4.myTable.Rows.Add(vals)
3. Pass the datatable to your Controller and on to the Data Provider through to the SQL Data Provider. (I wont show those steps as they are standard calls.)
4. When calling the SQLHelper in the SQL Data Provider you will need to use the overload that asks for the command type and extra parameter array as SQL parameters.
Public Shared Function ExecuteNonQuery(ByVal connectionString As String, ByVal commandType As System.Data.CommandType, ByVal commandText As String, ByVal ParamArray commandParameters() As System.Data.SqlClient.SqlParameter) As Integer
Member of Microsoft.ApplicationBlocks.Data.SqlHelper
So the call for the example I have above may look like:
1.Public Overrides Sub Department_AddUsers2Department(ByVal CompleteTable As System.Data.DataTable)
2. Dim myParam1 As SqlParameter = New SqlParameter("@MyTable", SqlDbType.Structured)
3. myParam1.Value = CompleteTable
4. SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Example_StoredProc", myParam1)
5.End Sub
If you have other parameters you are passing in you will have to define those as specific parameters.
5. In the database create a User-Defined Table Type to match the table being passed into the stored procedure. (This is one area that I feel will be a maintenance nightmare if not well planned.)
1.CREATE TYPE [dbo].[tvp_ExampleTable] AS TABLE(
2. [Col1] [int] NOT NULL,
3. [Col2] [int] NOT NULL
4.)
5.GO
Now in the stored procedure define a parameter that is the table type. (Notice the READONLY qualifier after the table parameter. This is required because a copy of the user-defined table variable is not passed to the procedure. To be more efficient, a pointer is passed to the proc. Therefore, to eliminate changes to the variable inside the proc that would affect the original, no changes are allowed.)
1.@MyTable tvp_ExampleTable READONLY
From here just use the parameter as you would any other table.
Copyright ©2011 Dylan Barber