Change Crystal Reports DataSource From MSACCESS to SQLSERVER at runtime

Question

I have a winforms application that is connecting to an MSACCESS Database located on a network. I am very keen to change the underlying database to sqlserver database on our local server. I am able to convert the database and get the application running (all table names, fields etc are preserved).

The application has over 300 crystal reports, which are configured to connect to the access datasource. I really dont want to have to manually reconfigure every report... so I am looking for a way to change the datasource at runtime

Answer 1

thanks for the assist, this solution seems to work for me though:

 private void getConnectionInfo(string serverName, string databaseName, string userID, string password)
    {

        connectionInfo.ServerName = serverName;
        connectionInfo.DatabaseName = databaseName;
        connectionInfo.UserID = userID;
        connectionInfo.Password = password;

    }

    private TableLogOnInfo GetSQLTableLogOnInfo(string serverName, string databaseName, string userID, string password)
    {
        CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag connectionAttributes = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
        connectionAttributes.EnsureCapacity(11);
        connectionAttributes.Add("Connect Timeout", "15");
        connectionAttributes.Add("Data Source", serverName);
        connectionAttributes.Add("General Timeout", "0");
        connectionAttributes.Add("Initial Catalog", databaseName);
        connectionAttributes.Add("Integrated Security", false);
        connectionAttributes.Add("Locale Identifier", "1033");
        connectionAttributes.Add("OLE DB Services", "-5");
        connectionAttributes.Add("Provider", "SQLOLEDB");
        connectionAttributes.Add("Tag with column collation when possible", "0");
        connectionAttributes.Add("Use DSN Default Properties", false);
        connectionAttributes.Add("Use Encryption for Data", "0");

        DbConnectionAttributes attributes = new DbConnectionAttributes();
        attributes.Collection.Add(new NameValuePair2("Database DLL", "crdb_ado.dll"));
        attributes.Collection.Add(new NameValuePair2("QE_DatabaseName", databaseName));
        attributes.Collection.Add(new NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"));
        attributes.Collection.Add(new NameValuePair2("QE_LogonProperties", connectionAttributes));
        attributes.Collection.Add(new NameValuePair2("QE_ServerDescription", serverName));
        attributes.Collection.Add(new NameValuePair2("SSO Enabled", false));

        getConnectionInfo(serverName, databaseName, userID, password);
        connectionInfo.Attributes = attributes;
        connectionInfo.Type = ConnectionInfoType.SQL;

        TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
        tableLogOnInfo.ConnectionInfo = connectionInfo;
        return tableLogOnInfo;
    }

    public ReportDocument GenerateReport(string reportPath, string report, string folder, List<ReportParameters> parameters)
    {

        ReportDocument crReport = new ReportDocument();
        TableLogOnInfo crTableLogoninfo = new TableLogOnInfo();
        Tables crTables;
        string path = reportPath + folder;
        crReport.Load(path + report);



            crTables = crReport.Database.Tables;
            TableLogOnInfo tableLogonInfo = this.GetSQLTableLogOnInfo(this.serverName, this.databaseName, this.userName, this.password );
            foreach (Table table in crTables)
            {
                table.LogOnInfo.ConnectionInfo = connectionInfo;
                table.ApplyLogOnInfo(table.LogOnInfo);
            }

            foreach (ReportDocument subrep in crReport.Subreports)
            {
                foreach (Table table in subrep.Database.Tables)
                {
                    table.LogOnInfo.ConnectionInfo = connectionInfo;
                    table.ApplyLogOnInfo(table.LogOnInfo);
                }
            }

        crReport.Refresh();
        foreach (ReportParameters r in parameters)
            crReport.SetParameterValue(r.parameter, r.value);
        return crReport;
    }