July 26, 2011

Calling Table Valued Parameters from DotNetNuke Modules

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.

   1:  using(var connection = new SqlConnection(DataProvider.Instance().ConnectionString))
   2:  using (var command = new SqlCommand("My Stored Procedure", connection))
   3:  {
   4:      //Setup Parameters
   5:      command.CommandType = CommandType.StoredProcedure;
   6:      var tableParam = new SqlParameter("@MyTypeParam", myDataTable);
   7:      tableParam.TypeName = "MyTableType";
   8:      tableParam.SqlDbType = SqlDbType.Structured;
   9:      command.Parameters.Add(tableParam);
  11:      //Open connection and call
  12:      connection.Open();
  13:      return CBO.FillCollection<MyObject>(command.ExecuteReader());
  14:  }

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.


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.

tags: DNN Development, Quick Tips
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.