C# – Switch from odbc to oledb in crystal reports at runtime

ccrystal-reports

I have report with subreports. Part of them are using OBDC database type.
I want to set all subreports to OLE DB with new connection parameters
I've tried the following but as I understand this isn't enough.

for (int i = 0; i <= rd.DataSourceConnections.Count - 1; i++)
{
  rd.DataSourceConnections[i].SetConnection(logonProps["Data Source"].ToString(),
                                                          logonProps["Initial Catalog"].ToString(), 
                                                          true);
 }
 for (int i = 0; i <= rd.Subreports.Count - 1; i++)
 {
   for (int x = 0; x <= rd.Subreports[i].DataSourceConnections.Count - 1; x++)
                {
                    rd.Subreports[i].DataSourceConnections[x].SetConnection(logonProps["Data Source"].ToString(),
                                                                            logonProps["Initial Catalog"].ToString(),
                                                                            true);
                }
            }

What property should I set to change database type?

Best Answer

i haven't answer for your question but i think you want create report from known data source. if its true you can read data into a c# data source like DataTable or List and set it as data source of sub report

rptMain.Subreport[0].SetDatasource(_dataTable);

PART 1

If you are connecting to PostgreSQL via ODBC (the only way Crystal Reports can pull data from PostgreSQL as of the time of this writing) using the data owner you then you can use the following code:

reportDoc.Load(report);
reportDoc.DataSourceConnections[0].SetConnection("Driver={PostgreSQL ANSI};Server=myServer;Port=5432;", "myDatabase", "myUser", "myPassword");
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);
// Depending on your application you may have more than one data source connection that needs to be changed.

This method only works if you are connecting as a user that owns the data that you are reporting on because the schema name does not need to be supplied.

PART 2

If you are connecting to PostgreSQL via ODBC with a user other than the data owner then you need to manually supply the schema name. This is accomplished with the following code.

reportDoc.Load(report);

ConnectionInfo connInfo = new ConnectionInfo();
connInfo.ServerName = "Driver={PostgreSQL ANSI};Server=myServer;Port=5432;";
connInfo.DatabaseName = "myDatabase";
connInfo.UserID = "myUser";
connInfo.Password = "myPassword";

TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();
tableLogOnInfo.ConnectionInfo = connInfo;

foreach (Table table in reportDoc.Database.Tables)
{
    table.ApplyLogOnInfo(tableLogOnInfo);
    table.LogOnInfo.ConnectionInfo.ServerName = connInfo.ServerName;
    table.LogOnInfo.ConnectionInfo.DatabaseName = connInfo.DatabaseName;
    table.LogOnInfo.ConnectionInfo.UserID = connInfo.UserID;
    table.LogOnInfo.ConnectionInfo.Password = connInfo.Password;

    // Apply the schema name to the table's location
    table.Location = "mySchema." + table.Location;
}

reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

Summary

There are two critical pieces of information here when trying to connect to a PostgreSQL database from Crystal Reports.

The driver, server, and port number must all be specified in the server name property.
If connecting as a user other than the data owner you must specify the schema name for each table you are pulling data from.

Sources

There were several sources used that did not have an answer that worked in my specific scenario but that led me in the right direction. These sources are listed below.

Crystal Reports Changing the Data Source

Its easy. Right click on the database fields in your field explorer. Click on 'Set datasource location' And then you will see two sections in a dialog. "Current datasource" and "replace with". In the current datasource - select the object you want to replace. Then in the "Replace with" section click on "create new connection">"OLE DB">Connect to the database object that you want by supplying driver and credential information.

Then click the 'update' button on the right side. That's it! You're done. Though the object name might not seem to change in the 'current datasource' section, still if u click on the (+) sign next to the object, the source name will show the new OLEDB object that you used

Best Answer

Related Solutions

Postgresql – How to change a Crystal Report’s ODBC database connection at runtime

PART 1

PART 2

Summary

Sources

Crystal Reports Changing the Data Source

Related Topic