Modify a report's data source at runtime in C# from Visual Studio

[danilo_mazzotti]

Hello everyone!

I have about 1000 reports developed with 32-bit Crystal Reports (from Crystal Report 9 to SAP Crystal Reports 2016) that use an xBase (dbf) data connection using the crdb_p2bxbse.dll 32 bit library (Crystal Reports database driver for xBase).

Now I need to upgrade to 64-bit Crystal Reports (SAP Crystal Reports 2025) and therefore migrate the reports to a JDBC data connection using CsvJdbc (a read-only JDBC driver that uses  CSV files or DBF files as database tables): using the same data source, the report fields will have a perfect match

I would like to know if it is possible to develop a C# program in Visual Studio for:

• load the report
• modify the data connection definition and table references
• save the report with the new connection

I can do this in SAP Crystal Reports 2025 (even without the crdb_p2bxbse.dll library that work only in 32 bit!), but I would have to do it for all the 1000 reports and I need to generate xBase (dbf) files for each report....

Is it possible to modify a report's data connection at runtime in C# from Visual Studio?

Thank you very much for your attention.

[DonWilliams]

FYI - Ido has been supporting Crystal Reports for 20+ years now and has a thorough understanding how CR works....

Also, do you still need to use DBF files? I highly recommend to convert your data sources to MS SQL Server or some other "real SQL DB data source". I realize it's not that easy to do but in the long term it may make things much easier to migrate for current and future reports.

To answer your questions....

What I always recommend to new developers using CR runtime is to do what you want in CR Designer first, then the same work flow is required when coding.

For example, open the Report in CRD, Set Location to the new Data Source and NOTE is the Field Mapping UI pops up, if so you'll need to set links in code. And also note if Verify Database message appears, you may or may not have to use the Verify() function, sometimes you don't want to use it... It can clear the updates you have applied....

NOTE: If the  mapping UI does pop up it indicates the JDBC driver uses a different file type than the DBF field type uses, CR JDBC dll can't map the field type to the source field type....

If you still want to code this yourself Book mark this page:

https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html?locale=en-US

On that page I wrote a sample app that will get you started:

how-to-parameters-in-crystal-reports-for-visual-studio-net

What you need to do is Clone() the new connection info, use a report you manually updated the connection info to the JDBC connection property. Then open the report and use ReplaceConnection() API to set the Old/New property bag info.

Fix any field mapping's, using the same Clone() API.

And as Ido suggested is save the new report to a new folder, don't update the connection info every time, it's time consuming.

I like to suggest setting a flag in the Reports Summary Info, from the File menu in CRD, you can read the various values to determine if the report has been update of not, set one of the with a comment it's been updated:

So it is possible to do what you want it is a question of programming this yourself or use Ido's solution, which he's done all the head scratching for you....

[danilo_mazzotti]

As suggested by ido_millet, I tried using the CrystalDecisions.CrystalReports.Engine.ReportDocument.ReportClientDocument.DatabaseController.ReplaceConnection method using:

• the old DBF connection (set by Crystal Report Designer)
• the new JDBC connection (set by Crystal Report Designer)

However, at runtime I get the "generic" exception System.ArgumentException: 'Incorrect parameter.' in "rptcontrollers.dll"

Below is the C# code

        static void Main()
        {
            CrystalDecisions.CrystalReports.Engine.ReportDocument cryRptDBF = new ReportDocument();
            CrystalDecisions.CrystalReports.Engine.ReportDocument cryRptJDBC = new ReportDocument();

            // Load old report with DBF connection
            cryRptDBF.Load("C:\\Report\\P10035\\P10035_DBF.RPT");
           

            // Load new report with JDBC connection
            cryRptJDBC.Load("C:\\Report\\P10035\\P10035_JDBC.RPT");


            foreach (Table table in cryRptJDBC.Database.Tables)
            {
                PrintTableProperties(cryRptDBF, table);

                // LogOnInfo clone of JDBC
                TableLogOnInfo liJDBC = (TableLogOnInfo)table.LogOnInfo.Clone();
                ConnectionInfo ciJDBC = liJDBC.ConnectionInfo;
                ciJDBC.UserID = "";

                // ReplaceConnection of connection info of DBF
                foreach (Table tabledbf in cryRptDBF.Database.Tables)
                {
                    if (String.Compare(tabledbf.Name, table.Name) == 0)
                    {
                        tabledbf.LogOnInfo.ConnectionInfo.UserID = "";
                        cryRptDBF.ReportClientDocument.DatabaseController.ReplaceConnection(tabledbf.LogOnInfo.ConnectionInfo, ciJDBC, null);
                        cryRptDBF.SaveAs("C:\\Report\\P10035\\P10035_DBF_NEW.RPT", false);
                    }
                }
            }
        }

        private static void PrintTableProperties(ReportDocument cryRptDBF, Table table)
        {
            Console.WriteLine(" table name: " + table.Name);
            Console.WriteLine(" location: " + table.Location);

            Console.WriteLine(" logon info report name: " + table.LogOnInfo.ReportName);
            Console.WriteLine(" logon info table name: " + table.LogOnInfo.TableName);

            Console.WriteLine(" logon info connection info database name: " + table.LogOnInfo.ConnectionInfo.DatabaseName);
            Console.WriteLine(" logon info connection info server name: " + table.LogOnInfo.ConnectionInfo.ServerName);
            Console.WriteLine(" logon info connection info type: " + table.LogOnInfo.ConnectionInfo.Type);

            foreach (NameValuePair2 a in table.LogOnInfo.ConnectionInfo.Attributes.Collection)
            {
                Console.WriteLine(" logon info connection info attributo: " + a.Name + " = " + a.Value);
                // l'attributo QE_LogonProperties è a sua volta un array associativo
                if (String.Compare("" + a.Name, "QE_LogonProperties") == 0)
                {
                    foreach (NameValuePair2 lppb in ((DbConnectionAttributes)a.Value).Collection)
                    {
                        Console.WriteLine("logon info connection info attributo logon properties attributo: " + lppb.Name + " = " + lppb.Value);
                    }
                }
            }
        }

Below is the output of the program execution

 table name: LEGENDA
 location: D510976L
 logon info report name: 
 logon info table name: D510976L
 logon info connection info database name: 
 logon info connection info server name: jdbc:relique:csv:./LEGENDA?fileExtension=.dbf
 logon info connection info type: CRQE
 logon info connection info attributo: Database DLL = crdb_jdbc.dll
 logon info connection info attributo: QE_DatabaseName = 
 logon info connection info attributo: QE_DatabaseType = JDBC (JNDI)
 logon info connection info attributo: QE_LogonProperties = CrystalDecisions.Shared.DbConnectionAttributes
logon info connection info attributo logon properties attributo: Connection URL = jdbc:relique:csv:./LEGENDA?fileExtension=.dbf
logon info connection info attributo logon properties attributo: Database = 
logon info connection info attributo logon properties attributo: Database Class Name = org.relique.jdbc.dbf.DbfReader
logon info connection info attributo logon properties attributo: JDBC Connection String = !org.relique.jdbc.dbf.DbfReader!jdbc:relique:csv:./LEGENDA?fileExtension=.dbf!user={userid}!password={password}
logon info connection info attributo logon properties attributo: Server = 
logon info connection info attributo logon properties attributo: Use JDBC = True
 logon info connection info attributo: QE_ServerDescription = jdbc:relique:csv:./LEGENDA?fileExtension=.dbf
 logon info connection info attributo: QE_SQLDB = True
 logon info connection info attributo: SSO Enabled = False
'CrystalReportsApplication3.exe' (CLR v4.0.30319: CrystalReportsApplication3.exe): caricamento di 'C:\WINDOWS\Microsoft.Net\assembly\GAC_64\CrystalDecisions.ReportAppServer.XmlSerialize\v4.0_13.0.4000.0__692fbea5521e1304\CrystalDecisions.ReportAppServer.XmlSerialize.dll' completato. Caricamento dei simboli disabilitato dall'impostazione Include/Exclude.
'CrystalReportsApplication3.exe' (CLR v4.0.30319: CrystalReportsApplication3.exe): caricamento di 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\mscorlib.resources\v4.0_4.0.0.0_it_b77a5c561934e089\mscorlib.resources.dll' completato. Modulo compilato senza simboli.
Eccezione generata: 'System.ArgumentException' in CrystalReportsApplication3.exe
Eccezione non gestita di tipo 'System.ArgumentException' in CrystalReportsApplication3.exe
Parametro non corretto.

At the following URL to my Google Drive

https://drive.google.com/drive/folders/10MmQPNqEblU26Qk1ilFVFwWeo0O3oAAY?usp=drive_link 

you can get:

• old report P10035_DBF.RPT
• new report P10035_JDBC.RPT
• folder LEGENDA with the file D510976L.dbf
• folder RUBRICA with the file D510976D.dbf
• the necessary jars to add to the classpath
  
  • csvjdbc-1.0.46.jar
  • dans-dbf-lib-1.0.0-beta-10.jar

[danilo_mazzotti]

TO DELETE

[danilo_mazzotti]

TO DELETE

[ido_millet · Accepted solution]

Yes, it is possible. You would need to use the RAS object model. If you Google the topic, you should find multiple discussions and code examples.

Given that you are permanently switching to a new data connection, it makes more sense to permanently change all the reports to use the new data connection instead of repeating the process each time you run the report.

Here is a video demo of my software doing a mass-update of data source across multiple Crystal Reports. 

[danilo_mazzotti · Accepted solution]

As suggested by ido_millet, I tried using the CrystalDecisions.CrystalReports.Engine.ReportDocument.ReportClientDocument.DatabaseController.ReplaceConnection method using:

• the old DBF connection (set by Crystal Report Designer)
• the new JDBC connection (set by Crystal Report Designer)

However, at runtime I get the "generic" exception System.ArgumentException: 'Incorrect parameter.' in "rptcontrollers.dll"

Below is the C# code

        static void Main()
        {
            CrystalDecisions.CrystalReports.Engine.ReportDocument cryRptDBF = new ReportDocument();
            CrystalDecisions.CrystalReports.Engine.ReportDocument cryRptJDBC = new ReportDocument();

            // Load old report with DBF connection
            cryRptDBF.Load("C:\\Report\\P10035\\P10035_DBF.RPT");
           

            // Load new report with JDBC connection
            cryRptJDBC.Load("C:\\Report\\P10035\\P10035_JDBC.RPT");


            foreach (Table table in cryRptJDBC.Database.Tables)
            {
                PrintTableProperties(cryRptDBF, table);

                // LogOnInfo clone of JDBC
                TableLogOnInfo liJDBC = (TableLogOnInfo)table.LogOnInfo.Clone();
                ConnectionInfo ciJDBC = liJDBC.ConnectionInfo;
                ciJDBC.UserID = "";

                // ReplaceConnection of connection info of DBF
                foreach (Table tabledbf in cryRptDBF.Database.Tables)
                {
                    if (String.Compare(tabledbf.Name, table.Name) == 0)
                    {
                        tabledbf.LogOnInfo.ConnectionInfo.UserID = "";
                        cryRptDBF.ReportClientDocument.DatabaseController.ReplaceConnection(tabledbf.LogOnInfo.ConnectionInfo, ciJDBC, null);
                        cryRptDBF.SaveAs("C:\\Report\\P10035\\P10035_DBF_NEW.RPT", false);
                    }
                }
            }
        }

        private static void PrintTableProperties(ReportDocument cryRptDBF, Table table)
        {
            Console.WriteLine(" table name: " + table.Name);
            Console.WriteLine(" location: " + table.Location);

            Console.WriteLine(" logon info report name: " + table.LogOnInfo.ReportName);
            Console.WriteLine(" logon info table name: " + table.LogOnInfo.TableName);

            Console.WriteLine(" logon info connection info database name: " + table.LogOnInfo.ConnectionInfo.DatabaseName);
            Console.WriteLine(" logon info connection info server name: " + table.LogOnInfo.ConnectionInfo.ServerName);
            Console.WriteLine(" logon info connection info type: " + table.LogOnInfo.ConnectionInfo.Type);

            foreach (NameValuePair2 a in table.LogOnInfo.ConnectionInfo.Attributes.Collection)
            {
                Console.WriteLine(" logon info connection info attributo: " + a.Name + " = " + a.Value);
                // l'attributo QE_LogonProperties è a sua volta un array associativo
                if (String.Compare("" + a.Name, "QE_LogonProperties") == 0)
                {
                    foreach (NameValuePair2 lppb in ((DbConnectionAttributes)a.Value).Collection)
                    {
                        Console.WriteLine("logon info connection info attributo logon properties attributo: " + lppb.Name + " = " + lppb.Value);
                    }
                }
            }
        }

Below is the output of the program execution

 table name: LEGENDA
 location: D510976L
 logon info report name: 
 logon info table name: D510976L
 logon info connection info database name: 
 logon info connection info server name: jdbc:relique:csv:./LEGENDA?fileExtension=.dbf
 logon info connection info type: CRQE
 logon info connection info attributo: Database DLL = crdb_jdbc.dll
 logon info connection info attributo: QE_DatabaseName = 
 logon info connection info attributo: QE_DatabaseType = JDBC (JNDI)
 logon info connection info attributo: QE_LogonProperties = CrystalDecisions.Shared.DbConnectionAttributes
logon info connection info attributo logon properties attributo: Connection URL = jdbc:relique:csv:./LEGENDA?fileExtension=.dbf
logon info connection info attributo logon properties attributo: Database = 
logon info connection info attributo logon properties attributo: Database Class Name = org.relique.jdbc.dbf.DbfReader
logon info connection info attributo logon properties attributo: JDBC Connection String = !org.relique.jdbc.dbf.DbfReader!jdbc:relique:csv:./LEGENDA?fileExtension=.dbf!user={userid}!password={password}
logon info connection info attributo logon properties attributo: Server = 
logon info connection info attributo logon properties attributo: Use JDBC = True
 logon info connection info attributo: QE_ServerDescription = jdbc:relique:csv:./LEGENDA?fileExtension=.dbf
 logon info connection info attributo: QE_SQLDB = True
 logon info connection info attributo: SSO Enabled = False
'CrystalReportsApplication3.exe' (CLR v4.0.30319: CrystalReportsApplication3.exe): caricamento di 'C:\WINDOWS\Microsoft.Net\assembly\GAC_64\CrystalDecisions.ReportAppServer.XmlSerialize\v4.0_13.0.4000.0__692fbea5521e1304\CrystalDecisions.ReportAppServer.XmlSerialize.dll' completato. Caricamento dei simboli disabilitato dall'impostazione Include/Exclude.
'CrystalReportsApplication3.exe' (CLR v4.0.30319: CrystalReportsApplication3.exe): caricamento di 'C:\WINDOWS\Microsoft.Net\assembly\GAC_MSIL\mscorlib.resources\v4.0_4.0.0.0_it_b77a5c561934e089\mscorlib.resources.dll' completato. Modulo compilato senza simboli.
Eccezione generata: 'System.ArgumentException' in CrystalReportsApplication3.exe
Eccezione non gestita di tipo 'System.ArgumentException' in CrystalReportsApplication3.exe
Parametro non corretto.

At the following URL to my Google Drive

https://drive.google.com/drive/folders/10MmQPNqEblU26Qk1ilFVFwWeo0O3oAAY?usp=drive_link  

you can get:

• old report P10035_DBF.RPT
• new report P10035_JDBC.RPT
• folder LEGENDA with the file D510976L.dbf
• folder RUBRICA with the file D510976D.dbf
• the necessary jars to add to the classpath
  • csvjdbc-1.0.46.jar
  • dans-dbf-lib-1.0.0-beta-10.jar

[DonWilliams · Accepted solution]

The Engine is not capable of replaceconnection, you must use: CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument rptClientDoc; See one of my samples on the info page to see how it's used : https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html?locale=en-US

[danilo_mazzotti · Accepted solution]

Hello everyone,

• The suggestion to use CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument DID NOT SOLVE THE PROBLEM
• I can't see any examples from the link https://help.sap.com/docs/SUPPORT_CONTENT/crystalreports/3354091173.html?locale=en-US (maybe the link is wrong)
•  It wasn't me who accepted the answer as a solution
• Are there any other suggestions?