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

问题描述

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

我已经找到了多种方法来更改数据库连接，包括以下：

  reportDoc.Load（report）; 
 reportDoc.DataSourceConnections [0] .SetConnection（server，database，user，pwd）; 
 reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）; 
  

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

我已通过成功连接验证了连接参数到pgAdmin III的数据库，所以我知道连接参数是正确的。此外，如果我删除SetConnection（...）行，代码看起来像这样：

  reportDoc.Load ）; 
 reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）; 
  

，那么报表将使用存储在报表中的连接参数正常运行。

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

h2> PART 1

如果您通过ODBC连接到PostgreSQL（Crystal Reports可以在撰写本文时从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）; 
 //根据您的应用程序，您可能有多个数据源连接需要更改。 
 

此方法仅在您以拥有报告数据的用户身份连接时才能使用，因为

第2部分

如果您要通过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（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; 
 
 //将模式名称应用于表的位置
 table.Location =mySchema。 + table.Location; 
} 
 
 reportDoc.ExportToDisk（CrystalDecisions.Shared.ExportFormatType.PortableDocFormat，path）; 
  

摘要

1. 必须指定驱动程序，服务器和端口号


2. 如果以数据所有者以外的用户身份连接，则必须为要从中提取数据的每个表指定模式名称。

来源

有几个来源没有答案在我的具体情况下有效，我在正确的方向。这些来源如下所示。

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.

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.

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:

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?

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

解决方案

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

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.

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.

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

这篇关于如何在运行时更改Crystal Report的ODBC数据库连接？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！