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