Calling Table Valued Parameters from DotNetNuke Extensions

Recently I was working on a DotNetNuke module that had a need to communicate to a database using a Table Valued Parameter to do some advanced reporting functionality.  Researching this resulted in a number of times that I chased my tail and I thought I'd share here the way I got this working.

Expected Setup

In this post, I'm focusing on the actual data call and not the setup.  Therefore it is expected that you have already assembled the data that you would like to pass to the Table-Valued Parameter in a DataTable and that the stored procedure and custom type already exist.  If you have questions on this in the future I can write more on this.

Making the Database Call

At first glance you would think that you can just call a stored procedure with a Table-Valued Parameter using the standard DotNetNuke DAL methods, well sadly that isn't the case.  A number of errors were encountered when I attempted to do this.  The reason that this doesn't work out of the box is that calls to TVP's require additional configuration elements including the SqlDbType and the TypeName.  Before I explain more below is the working code to call a TVP.

using(var connection = new SqlConnection(DataProvider.Instance().ConnectionString))
using (var command = new SqlCommand("My Stored Procedure", connection))
{
    //Setup Parameters
    command.CommandType = CommandType.StoredProcedure;
    var tableParam = new SqlParameter("@MyTypeParam", myDataTable);
    tableParam.TypeName = "MyTableType";
    tableParam.SqlDbType = SqlDbType.Structured;
    command.Parameters.Add(tableParam);
    
    //Open Connection & Execute Call
    connection.Open();
    return CBO.FillCollection(command.ExecuteReader());
}

Now, looking at this there are a few key things to notice. First of all, since I cannot use the out of the box DAL methods I am forced to open my own connection and command so I wrap those in Using statements to ensure that no connection issues exist.  I'm using the built-in ConnectionString property of the DataProvider to obtain the ConnectionString to ensure that if future releases change the connection string that I will not be impacted. Finally, when adding the parameter to the command I supply the datatable, the type, and the SqlDbType as Structured.

Conclusion

With a little trial and error and a bit of extra code than normal you can easily work with TVP's from DotNetNuke while still respecting all standards.  Feel free to share comments below.