postgresql 如何在运行时更改 Crystal Report 的 ODBC 数据库连接？

How do I change a Crystal Report's ODBC database connection at runtime?

提问by Ryan Taylor

I have a report made with Crystal Reports 2008 that I need to deploy a production system which means that I need to be able to change the database connection at runtime. The database is PostgreSQL 8.3.0 and the connection I use for creating the initial report is an ODBC connection.

我有一份用 Crystal Reports 2008 制作的报告，我需要部署一个生产系统，这意味着我需要能够在运行时更改数据库连接。数据库是 PostgreSQL 8.3.0，我用于创建初始报告的连接是 ODBC 连接。

I have found various ways to change the database connection including the following:

我找到了多种更改数据库连接的方法，包括：

reportDoc.Load(report);
reportDoc.DataSourceConnections[0].SetConnection("server", "database", "user", "pwd");
reportDoc.ExportToDisk(CrystalDecisions.Shared.ExportFormatType.PortableDocFormat, path);

However, this always fails with the following error message.

但是，这总是失败并显示以下错误消息。

Failed to open the connection.

无法打开连接。

I have validated the connection parameters by successfully connecting to the database with pgAdmin III so I know the connection parameters are correct. In addition, if I remove the SetConnection(...) line so the code looks like this:

我已经通过使用 pgAdmin III 成功连接到数据库来验证连接参数，所以我知道连接参数是正确的。此外，如果我删除 SetConnection(...) 行，则代码如下所示：

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

then the report runs fine using the connection parameters that are stored in the report. Is it possible that this method does not work for ODBC connections?

然后报告使用存储在报告中的连接参数运行良好。这种方法是否可能不适用于 ODBC 连接？

How do I change a Crystal Report's ODBC database connection at runtime?

如何在运行时更改 Crystal Report 的 ODBC 数据库连接？

回答by Ryan Taylor

After even moreresearch I found that there was a two part answer.

经过更多的研究，我发现有两个部分的答案。

PART 1

第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:

如果您使用数据所有者通过 ODBC（截至撰写本文时 Crystal Reports 可以从 PostgreSQL 提取数据的唯一方式）连接到 PostgreSQL，则您可以使用以下代码：

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

第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.

如果您通过 ODBC 与数据所有者以外的用户连接到 PostgreSQL，则您需要手动提供架构名称。这是通过以下代码完成的。

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.

尝试从 Crystal Reports 连接到 PostgreSQL 数据库时，这里有两个关键信息。

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

1. 驱动程序、服务器和端口号都必须在服务器名称属性中指定。
2. 如果以数据所有者以外的用户身份连接，则必须为要从中提取数据的每个表指定架构名称。

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.

使用的几个来源没有在我的特定场景中起作用的答案，但它们使我朝着正确的方向前进。下面列出了这些来源。

• Nathan Koop's Answer
• https://www.sdn.sap.com/irj/scn/thread?messageID=6913827#6913827
• Best Practices for Changing Databases at Runtime

• 内森·库普的回答
• https://www.sdn.sap.com/irj/scn/thread?messageID=6913827#6913827
• 在运行时更改数据库的最佳实践

回答by Nathan Koop

I just went through this same ordeal.

我刚刚经历了同样的磨难。

I set my connections like this (where sDataSource etc... are string with the information)

我这样设置我的连接（其中 sDataSource 等...是带有信息的字符串）

    Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
    myConnectionInfo.ServerName = sDataSource
    myConnectionInfo.DatabaseName = sInitialCatalog
    myConnectionInfo.UserID = sUserID
    myConnectionInfo.Password = sPassword

    Dim myTables As Tables = report.Database.Tables
    Dim myTableLogonInfo As TableLogOnInfo = New TableLogOnInfo()
    myTableLogonInfo.ConnectionInfo = myConnectionInfo
    For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
        myTable.ApplyLogOnInfo(myTableLogonInfo)

        myTable.LogOnInfo.ConnectionInfo.DatabaseName = myTableLogonInfo.ConnectionInfo.DatabaseName
        myTable.LogOnInfo.ConnectionInfo.ServerName = myTableLogonInfo.ConnectionInfo.ServerName
        myTable.LogOnInfo.ConnectionInfo.UserID = myTableLogonInfo.ConnectionInfo.UserID
        myTable.LogOnInfo.ConnectionInfo.Password = myTableLogonInfo.ConnectionInfo.Password

    Next

回答by Tony Cleveland

First of all thank you for this information!!!

首先感谢你提供这个信息！！！

I'm using MySQL/C#/Crystal Reports. After setting up the ODBC/DSN something as simple as this worked for me.

我正在使用 MySQL/C#/Crystal 报告。设置 ODBC/DSN 后，像这样简单的事情对我有用。

using CrystalDecisions.CrystalReports.Engine;

using CrystalDecisions.Shared;

using MySql.Data.MySqlClient;

.
.
.

ConnectionInfo connInfo = new ConnectionInfo();

connInfo.ServerName = "Driver={MySQL ODBC 3.51 Driver};DSN=MyODBCDatasourceName";

TableLogOnInfo tableLogOnInfo = new TableLogOnInfo();

tableLogOnInfo.ConnectionInfo = connInfo;

// rpt is my Crystal Reports ReportDocument

// Apply the schema name to the table's location

foreach (Table table in rpt.Database.Tables)

{

    table.ApplyLogOnInfo(tableLogOnInfo);

    table.Location = table.Location;

}

回答by patm

Updating the ODBC within a crystal report file.

在水晶报告文件中更新 ODBC。

We are using ODBC with MSSQL, we couldn't find how to update the ODBC within the crystal files within a C sharp project.

我们在 MSSQL 中使用 ODBC，我们找不到如何在 C 锐利项目中的水晶文件中更新 ODBC。

With the example provided here we were able to find the way to update the ODBC within MSSQL, and it is as simple as this:

通过此处提供的示例，我们能够找到在 MSSQL 中更新 ODBC 的方法，就像这样简单：

       Cursor.Current = Cursors.WaitCursor;

        CrystalDecisions.Windows.Forms.CrystalReportViewer CR_Viewer;
        CR_Viewer = new CrystalDecisions.Windows.Forms.CrystalReportViewer();
        this.Controls.Add(CR_Viewer);

        ConnectionInfo connInfo = new ConnectionInfo();
        connInfo.ServerName = "YOUR ODBC NAME"; 

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

         //THIS IS A CRYSTAL RPT FILE DIFINE AS A CLASS
        Facturation_Nord_Ouest.Reports.Facture_Fran?ais CrystalReportFr;


       CrystalReportFr = new Facturation_Nord_Ouest.Reports.Facture_Fran?ais();

            for (int i = 0; i < CrystalReportFr.Database.Tables.Count; i++)
            {
                CrystalReportFr.Database.Tables[i].ApplyLogOnInfo(tableLogOnInfo);
            }
            CR_Viewer.ReportSource = CrystalReportFr;

        CR_Viewer.ActiveViewIndex = 0;
        CR_Viewer.BorderStyle = System.Windows.Forms.BorderStyle.FixedSingle;
        CR_Viewer.Dock = System.Windows.Forms.DockStyle.Fill;
        CR_Viewer.Location = new System.Drawing.Point(0, 0);
        CR_Viewer.Size = new System.Drawing.Size(545, 379);
        CR_Viewer.TabIndex = 0;
        CR_Viewer.Name = "Invoice";
        CR_Viewer.Zoom(100);
        CR_Viewer.Show();

        Cursor.Current = Cursors.Default;

With this the ODBC in the crystal file is automaticaly updated.

这样，水晶文件中的 ODBC 就会自动更新。

回答by RicardoBalda

This Work for Sql Server 2008 R2 for DSN less connection.

这适用于 Sql Server 2008 R2，用于无 DSN 连接。

Dim myConnectionInfo As CrystalDecisions.Shared.ConnectionInfo = New CrystalDecisions.Shared.ConnectionInfo()

    myConnectionInfo.ServerName = "Driver={SQL Server Native Client 10.0};Server=P03\sqlrs1;"
    myConnectionInfo.DatabaseName = "RS1DB"
    myConnectionInfo.UserID = "user"
    myConnectionInfo.Password = "pwd"

    For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In mboReportDocument.Database.Tables

        Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
        myTableLogonInfo.ConnectionInfo = myConnectionInfo
        myTable.ApplyLogOnInfo(myTableLogonInfo)

    Next

回答by Sayed Moawad

protected void Page_Load(object sender, EventArgs e)
        {
            try
            {

                ReportDocument cryRpt = new ReportDocument();
                TableLogOnInfos crtableLogoninfos = new TableLogOnInfos();
                TableLogOnInfo crtableLogoninfo = new TableLogOnInfo();
                ConnectionInfo crConnectionInfo = new ConnectionInfo();
                cryRpt.Load(@"D:\tem\WebAppReport\WebAppReport\CrystalReport1.rpt");
                crConnectionInfo.ServerName = "misserver";
                crConnectionInfo.DatabaseName = "testAccountability_data";
                crConnectionInfo.UserID = "RW";
                crConnectionInfo.Password = "RW";


                foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in cryRpt.Database.Tables)
                {
                    crtableLogoninfo = CrTable.LogOnInfo;
                    crtableLogoninfo.ConnectionInfo = crConnectionInfo;
                    CrTable.ApplyLogOnInfo(crtableLogoninfo);
                }

                CrystalReportViewer1.ReportSource = cryRpt;
                CrystalReportViewer1.RefreshReport();
            }
            catch
            {
            }






        }