Using an ODBC connection to load a Crystal Report in Code-Behind

Question

I have a current set of code working to load a Crystal Report in c# with a server connection.

Here's the code for loading the report:

CrystalReportViewer1.ParameterFieldInfo.Clear();
string reportName = Request.QueryString["rpt"];
if (reportName != null)
{
    crystalReport = new ReportDocument();
    crystalReport.Load(@"\\esco-iis\crystalreports\purchasing\po.rpt");
    //crystalReport.Load(@"\\esco\crystalreports\purchasing\po.rpt");

    var connectionInfo = new ConnectionInfo();
    connectionInfo.ServerName = "server";
    connectionInfo.DatabaseName = "database";
    connectionInfo.Password = "pass";
    connectionInfo.UserID = "user";
    connectionInfo.IntegratedSecurity = false;
    crystalReport.SetDatabaseLogon("user", "pass", "server", "database");
    Tables CrTables = crystalReport.Database.Tables;
    TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
    foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in CrTables)
    {
        crtableLogoninfo = CrTable.LogOnInfo;
        crtableLogoninfo.ConnectionInfo = connectionInfo;
        CrTable.ApplyLogOnInfo(crtableLogoninfo);
        CrystalReportViewer1.LogOnInfo.Add(crtableLogoninfo);

    }

    foreach (ReportDocument report in crystalReport.Subreports)
    {
        report.SetDatabaseLogon("user", "pass", "server", "database");
        foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in report.Database.Tables)
        {
            crtableLogoninfo = CrTable.LogOnInfo;
            crtableLogoninfo.ConnectionInfo = connectionInfo;
            CrTable.ApplyLogOnInfo(crtableLogoninfo);
            CrystalReportViewer1.LogOnInfo.Add(crtableLogoninfo);
        }
    }

    string PO = Request.QueryString["PO"];
    if (PO != null)
    {
        ParameterField parameter = new ParameterField();
        ParameterDiscreteValue parameterValue = new ParameterDiscreteValue();
        parameterValue.Value = PO;
        parameter.Name = "@PO";
        parameter.CurrentValues.Add(parameterValue);
        CrystalReportViewer1.ParameterFieldInfo.Add(parameter);
    }
}

Current state: This works when the report is set up to connect to the database direct, but about half of them are set up to use an ODBC connection.

Attempt: I attempted to switch the server name to the name of the ODBC connection, but this still came back with the same Failed to open the connection. Failed to open the connection. error.

Problem: I can't seem to figure out what I can change in my ConnectionInfo to be able to make it use an ODBC connection successfully. I keep getting a Failed to open the connection. Failed to open the connection.

I'm aware of the solution to do a connection separately and set the reportsource, but this isn't feasible since there are many reports.

Thanks in advance!

Answer 1

Setting an ODBC connection at runtime is rather tricky, mostly because there are all these additional "logon properties" and "connection attributes" that need to be set:

// 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
           };

---

On the other hand, rather than creating a ConnectionInfo object from scratch, you might be able to use the existing one, especially if you already designed your report using a similar connection:

foreach (Table crTable in crTables)
{
    TableLogOnInfo tableLogOnInfo = crTable.LogOnInfo;
    var connectionInfo = tableLogOnInfo.ConnectionInfo;

    // set connection info stuff here

    crTable.ApplyLogOnInfo(tableLogOnInfo);
}

Answer 2

For legacy reports with SQL Server ODBC, using "SQL Server Native Driver 11.0" might be needed.

class CrystalDatabase
    {

        public static TableLogOnInfo GetODBCTableLogOnInfo(string ODBCName, string serverName, string databaseName, string userID, string password)
        {
            CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag connectionAttributes = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

            connectionAttributes.EnsureCapacity(3);
            connectionAttributes.Add(DbConnectionAttributes.CONNINFO_CONNECTION_STRING, string.Format("DSN={0};Driver={{SQL Server}}", ODBCName));
            connectionAttributes.Add("Server", serverName);
            connectionAttributes.Add("UseDSNProperties", false);

            DbConnectionAttributes attributes = new DbConnectionAttributes();
            attributes.Collection.Add(new NameValuePair2("Database DLL", "crdb_odbc.dll"));
            attributes.Collection.Add(new NameValuePair2("QE_DatabaseName", databaseName));
            attributes.Collection.Add(new NameValuePair2("QE_DatabaseType", "ODBC (RDO)"));
            attributes.Collection.Add(new NameValuePair2("QE_LogonProperties", connectionAttributes));
            attributes.Collection.Add(new NameValuePair2("QE_ServerDescription", serverName));
            attributes.Collection.Add(new NameValuePair2("QE_SQLDB", true));
            attributes.Collection.Add(new NameValuePair2("SSO Enabled", false));

            ConnectionInfo connectionInfo = GetConnectionInfo(serverName, databaseName, userID, password);
            connectionInfo.Attributes = attributes;
            connectionInfo.Type = ConnectionInfoType.CRQE;
            connectionInfo.IntegratedSecurity = false;
            TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
            tableLogOnInfo.ConnectionInfo = connectionInfo;
            return tableLogOnInfo;
        }

        public static ConnectionInfo GetConnectionInfo(string serverName, string databaseName, string userID, string password)
        {
            ConnectionInfo connectionInfo = new ConnectionInfo();
            connectionInfo.ServerName = serverName;
            connectionInfo.DatabaseName = databaseName;
            connectionInfo.UserID = userID;
            connectionInfo.Password = password;

            return connectionInfo;
        }
    }

This class should be combined with an implementation similar to this: crystal report failed to get data