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

Disabling WCF’s Explicit Element Ordering

If you consume a SOAP web service via WCF, and the service doesn’t return its XML elements in the order specified by its WSDL, you may experience “mysterious null values” in the deserialized response. This is because WCF enforces element ordering for sequences as defined by the XSD standard. If it encounters an element that’s not in the right place it simply gives up.

This is unfortunate if you are consuming a third party web service and you do not have control over the element ordering.

As a workaround you can generate proxies that serialize with XmlSerializer instead of DataContractSerializer, and then remove the element ordering information from the proxy code. Fortunately (with a little help from sed) this can be automated. You’ll need to add a Pre-Build action to your project, and populate it like so:

set proxy_tool="C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\SvcUtil.exe" /nologo /t:code /ser:XmlSerializer /UseSerializerForFaults
set sed_tool="$(ProjectDir)sed.exe" -r -i "s/,?[[:space:]]*Order=[[:digit:]]+//"

%proxy_tool%  /o:"Proxy1.cs" /n:*,Namespaces.Name1 "Proxy1.wsdl"
%sed_tool% "Proxy1.cs"

%proxy_tool%  /o:"Proxy2.cs" /n:*,Namespaces.Name2 "Proxy2.wsdl"
%sed_tool% "Proxy2.cs"

...