Dynamic ODBC (RDO) Connection Strings in Crystal Reports

Apparently changing the connection string of an ODBC (RDO) connection in Crystal Reports dynamically (at runtime) is hard to do.

I had a crack at it and this is what I came up with:

private static ConnectionInfo GetConnectionInfo()
{
    // DbConnectionAttributes contains some, but not all, consts.
    var logonProperties = new DbConnectionAttributes();
    logonProperties.Collection.Set("Connection String", @"Driver={SQL Server};Server=TODD-PC\SQLEXPRESS2;Trusted_Connection=Yes;");
    logonProperties.Collection.Set("UseDSNProperties", false);

    var connectionAttributes = new DbConnectionAttributes();
    connectionAttributes.Collection.Set("Database DLL", "crdb_odbc.dll");
    connectionAttributes.Collection.Set("QE_DatabaseName", String.Empty);
    connectionAttributes.Collection.Set("QE_DatabaseType", "ODBC (RDO)");
    connectionAttributes.Collection.Set("QE_LogonProperties", logonProperties);
    connectionAttributes.Collection.Set("QE_ServerDescription", @"TODD-PC\SQLEXPRESS2");
    connectionAttributes.Collection.Set("QE_SQLDB", true);
    connectionAttributes.Collection.Set("SSO Enabled", false);

    return new ConnectionInfo
               {
                   Attributes = connectionAttributes,
                   // These don't seem necessary, but we'll include them anyway: ReportDocument.Load does
                   ServerName = @"TODD-PC\SQLEXPRESS2",
                   Type = ConnectionInfoType.CRQE
               };
}

This function imitates the ConnectionInfo object that I saw ReportDocument.Load produce.

Now we’ve got a ConnectionInfo we have to apply it. The correct way seems to be to iterate over:

  1. subreports and their tables
  2. your report and its tables

Once the ConnectionInfo has been applied you can set your parameters.

I made a small demo application to test my code. For simplicity’s sake the report has one connection and no parameters, so the code to configure the report’s connection is quite simple:

private void Form1_Load(object sender, EventArgs e)
{
    var reportDocument = new ReportDocument();
    reportDocument.Load(@"CrystalReport1.rpt");
    var table = reportDocument.Database.Tables[0];
    var logonInfo = table.LogOnInfo;
    logonInfo.ConnectionInfo = GetConnectionInfo();
    table.ApplyLogOnInfo(logonInfo);

    crystalReportViewer1.ReportSource = reportDocument;
}

Now if we take a quick look at the way the report is set up, you’ll see it has one command that asks for the server name:

The connection string in the report is configured to point to the first of two SQL Server instances on my machine. We can verify this by previewing the report:

But that’s not what you came here to see! If we run the application, we see Crystal Reports connect to the server we specified in the code behind:

Note that the retrieved server name is TODD-PC\SQLEXPRESS2.

Update

I’ve since found that ApplyLogonInfo uses the server name to determine which properties in the connection details to update. Depending on your circumstances, you may want to always generate a unique server name/description to ensure that as many properties as possible are changed.

Advertisements

Visual Studio 2010 SP1, Windows SDK 7.1 Install Order

Thanks to an issue between Visual Studio 2010 SP1 and the Windows SDK 7.1 installing and fully patching Visual Studio 2010 became a little bit more complicated and time consuming.

Not only is there a special installation order you should follow, but it seems that the Windows SDK 7.1 is a bit picky with the Visual C++ 2010 runtimes it will install beside.

If you happen to fully patch your computer before installing the Windows SDK 7.1 you’ll probably end up downloading KB2565063. This updates contains some of the things the Windows SDK 7.1 installer doesn’t like.

So, to improve upon the previously suggested install order:

  1. Install Visual Studio 2010 RTM
  2. Uninstall any Visual C++ 2010 runtime newer than 10.0.30319 (I’ve seen 10.0.30419 and 10.0.40219 block the installation)
  3. Install Windows SDK 7.1
  4. Install Visual Studio 2010 SP1
  5. Install Visual C++ 2010 Service Pack 1 Compiler Update

In my case, skipping step 2 resulted in the following message during Windows SDK 7.1 installation:

Installation of the “Microsoft Windows SDK for Windows 7” product has reported the following error: Please refer to Samples\Setup\HTML\ConfigDetails.htm document for further information

And the following in the error logs:

DDSet_Error: Patch Hooks: Missing required property 'ProductFamily': Setup cannot continue.
DDSet_Warning: Setup failed while calling 'getDLLName'. System error: Cannot create a file when that file already exists.

Update

I was installing Visual Studio 2008, 2010, 2012 and SQL Server 2012 on Windows 8, only to stuff up the installation by not following my own instructions! If anybody wants to go the whole hog with three side-by-side installations of Visual Studio, here’s the install order that worked for me:

  1. Visual Studio 2008, then SP1, then later updates
  2. Visual Studio 2010 and Windows SDK 7.1 as per the instructions above
  3. SQL Server 2012 (SQL Server Management Studio 2012 is based on Visual Studio 2010, so I thought it best to install this before Visual Studio 2012)
  4. Visual Studio 2012

If you’re successful, you should have yourself a very busy Start screen: