Thursday, May 20, 2010

Dynamic Connection Strings With SSRS Data Processing Extension

It is my firm opinion that whoever came up with the names for various parts of SQL Server must be fired. "SQL Server Reporting Services", "SQL Server Analysis Services", "SQL Server Service Broker" do not exactly roll off the tongue. Just try using a few of these in a speech - you will immediately realize you need nicknames (SSRS sounds too much like USSR. Oh, well, I'll just call it Kevin). Anyway, this post wasn't supposed to be a rant, so let's move on.

SSRS supports ten data sources out of the box, including SQL Server (duh!), ODBC, OLE DB, and Oracle. Data Processing Extensions are usually recommended when you need to generate reports from non-standard data sources, for example, files in a proprietary format. You start by creating a .NET assembly with classes that implement a half-dozen or so interfaces defined in Microsoft.ReportingServices.Interfaces.dll. Once everything is working, you deploy the assembly to two separate locations: Visual Studio subfolder on a report developer's workstation, and a Reporting Services subfolder on a server. The process is well documented on MSDN and there is also a good article on The Code Project.

However, what if your database is a SQL Server, but you cannot rely on a static connection string? Suppose you maintain separate databases for your various customers and generate connection strings at runtime? Although SSRS allows us to use parameterized connection strings, sometimes this isn't an optimal solution, given the fact that those parameters are passed around openly inside URL. I found that Data Processing Extensions can be used very effectively in this scenario.

Rather than implementing all the interfaces required by DPE, we will encapsulate an existing class SqlConnectionWrapper in the Microsoft.ReportingServices.DataExtensions namespace (it is marked as "sealed", so you can't subclass it):

   1:  using Microsoft.ReportingServices.DataExtensions;
   2:  using Microsoft.ReportingServices.DataProcessing;
   4:  public class MySqlConnection : IDbConnectionExtension
   5:  {
   6:      private SqlConnectionWrapper _Connection;
   8:      public MySqlConnection()
   9:      {
  10:          _Connection = new SqlConnectionWrapper();
  11:      }
  12:  }
Right-click IDbConnectionExtension and choose "Implement Interface". This automatically implements three more interfaces, IDbConnection, IDisposable, and IExtension members. Most of the new methods and properties added to our class will be merely wrappers of the respected methods and properties of the _Connection. For example:
   1:      public string Password
   2:      {
   3:          set { _Connection.Password = value; }
   4:      }
   6:      public string UserName
   7:      {
   8:          set { _Connection.UserName = value; }
   9:      }
Of course, you still need to add the implementation.

Property LocalizedName should return the string that you want report developers to see in the list of data sources (e.g., on the "Select Data Source" screen of the new report wizard).
   1:      public string LocalizedName
   2:      {
   3:          get { return "Dynamic SQL Server Connection"; }
   4:      }
Arguably the most important implementation is ConnectionString setter. This is where you need to put your proprietary logic that dynamically generates a valid connection string. There are a couple of different approaches. If you don't need any additional information in order to generate the connection string, ignore the "value" and just call necessary methods:
   1:      public string ConnectionString
   2:      {
   3:          get
   4:          {
   5:              return _Connection.ConnectionString;
   6:          }
   7:          set
   8:          {
   9:              _Connection.ConnectionString = MyDataLayer.GenerateConnectionString();
  10:          }
  11:      }
If, on the other hand, your logic does require parameters, you will need to parse ConnectionString value that client code provided. Example below uses regular expression to extract CustomerID from the value (and skips property getter for brevity):
   1:      public string ConnectionString
   2:      {
   3:          set
   4:          {
   5:              Match m = Regex.Match(value, "CustomerID=([^;]+)", RegexOptions.IgnoreCase);
   6:              int custId = 0;
   7:              if (m.Success
   8:                 && int.TryParse(m.Groups[1].Captures[0].Value, out custId))
   9:              {
  10:                  _Connection.ConnectionString = MyDataLayer.GenerateConnectionString(custId);
  11:              }
  12:              else
  13:              {
  14:                  throw new ArgumentException("Valid CustomerID is missing");
  15:              }
  16:          }
  17:      }
Now the only thing left to do is deploy assembly containing MySqlConnection to both server and developer workstation.

*** UPDATED 11/22/2011 ****

Bad news for those of you using SQL 2008 R2 - the approach of encapsulating Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper I outlined in this post no longer works. Apparently, they changed class accessibility from "public" to "internal". Looks like the only opportunity is to implement all interfaces manually.

Tuesday, May 18, 2010

San Diego .NET User Group Presentation

Thanks to everyone who attended my presentation at San Diego .NET User Group. As promised, below are the links to slides I used and sample code we wrote.

Sunday, May 02, 2010

Code Generation For Auto-Implemented Properties

I recently ventured into one of the more obscure areas of .NET framework - code generation. The project involved rules engine manipulating properties of our internal domain objects. Long story short, I had to create a routine that converts our domain objects into .NET classes (derived from System.Workflow.Activity). These generated classes did not have much behavior - all methods were pushed to the base class - but they did carry so many properties that they in turn had to be grouped together into classes.

Writing code generation logic for a property turned out to be a lot of work: first, I had to add declaration for a private backing field, then a property declaration, including code expressions for both getter and setter. Here's sample code similar to what I ended up with:

var myType = new CodeTypeDeclaration("Person");

var field = new CodeMemberField()
    Name = "_LastName",
    Type = new CodeTypeReference("System.String"),
    Attributes = MemberAttributes.Private
var prop = new CodeMemberProperty()
    Name = "LastName",
    Type = new CodeTypeReference("System.String"),
    Attributes = MemberAttributes.Public
    new CodeMethodReturnStatement(
        new CodeFieldReferenceExpression(
            new CodeThisReferenceExpression(), "_LastName")));
    new CodeAssignStatement(
        new CodeFieldReferenceExpression(
            new CodeThisReferenceExpression(), "_LastName"), 
        new CodePropertySetValueReferenceExpression()));

And here is the code that was generated by the above fragment:

private string _LastName;
public string LastName
    get { return _LastName; }
    set { _LastName = value; }

Of course, C# 3.0 has introduced a much shorted way of declaring the same property: "public string LastName { get; set; }". This syntax is called "auto-implemented properties" and it puts the burden on the compiler to create a backing field and implement getter and setter logic. Naturally, I wanted generated classes to look cleaner, so being an optimist that I am I decided to change code generation logic to create auto-implemented properties instead.

That proved to be a mistake: after a while I realized that classes in System.CodeDom namespace do not support auto-implemented properties generation. The best I could come up with was a hack using CodeSnippetTypeMember:

var snippet = new CodeSnippetTypeMember("public string LastName { get; set; }");

This solution is pretty far from ideal. It goes against the spirit of code generation because it allows me to target just one programming language, C#. Still, it is pragmatic. Hopefully, Microsoft can bring CodeDom up to date in a future release.