c# - Crystal Reports - 将提供程序从 OLEDBSQL 更改为 MSOLEDBSQL 以通过代码支持 TLS 1.2

Question

我正在尝试从 OLEDBSQL 更改我的 Crystal Reports 的提供者至 MSOLEDBSQL如 OLEDBSQL不支持TLS 1.2 我的代码:

Private Sub ProcessReport()
    Dim crxRpt As New CrystalDecisions.CrystalReports.Engine.ReportDocument
    crxRpt.Load("D:\RND\Crystal Reports - Connector\CrystallReportConvertVB\SummaryReport.rpt")

     ChangeOleDbConnectionInfo(crxRpt, "SERVER", "DBNAME", "MSOLEDBSQL", False, "USERID", "PASSWORD")
End Sub

Friend Shared Sub ChangeOleDbConnectionInfo(ByVal reportDocument As ReportDocument, ByVal server As String, ByVal database As String, ByVal oledbProvider As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String)

    Dim boMainPropertyBag = New NameValuePairs2()
    Dim boInnerPropertyBag = New NameValuePairs2()
    boInnerPropertyBag.Add(New NameValuePair2("Application Intent", "READWRITE"))
    boInnerPropertyBag.Add(New NameValuePair2("Auto Translate", "-1"))
    boInnerPropertyBag.Add(New NameValuePair2("Connect Timeout", "15"))
    boInnerPropertyBag.Add(New NameValuePair2("Data Source", server))
    boInnerPropertyBag.Add(New NameValuePair2("DataTypeCompatibility", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("General Timeout", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("Initial Catalog", database))
    boInnerPropertyBag.Add(New NameValuePair2("Integrated Security", If(integratedSecurity, "True", "False")))
    boInnerPropertyBag.Add(New NameValuePair2("Locale Identifier", "1033"))
    boInnerPropertyBag.Add(New NameValuePair2("MARS Connection", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("OLE DB Services", "-5"))
    boInnerPropertyBag.Add(New NameValuePair2("Provider", oledbProvider))
    boInnerPropertyBag.Add(New NameValuePair2("Tag with column collation when possible", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("Trust Server Certificate", "0"))
    boInnerPropertyBag.Add(New NameValuePair2("Use DSN Default Properties", "False"))
    boInnerPropertyBag.Add(New NameValuePair2("Use Encryption for Data", "0"))


    boMainPropertyBag.Add(New NameValuePair2("Database DLL", "crdb_ado.dll"))
    boMainPropertyBag.Add(New NameValuePair2("QE_DatabaseName", database))
    boMainPropertyBag.Add(New NameValuePair2("QE_DatabaseType", "OLE DB (ADO)"))
    boMainPropertyBag.Add(New NameValuePair2("QE_LogonProperties", boInnerPropertyBag))
    boMainPropertyBag.Add(New NameValuePair2("QE_ServerDescription", server))
    boMainPropertyBag.Add(New NameValuePair2("QE_SQLDB", "True"))
    boMainPropertyBag.Add(New NameValuePair2("SSO Enabled", "False"))

    Dim conAttributes As New CrystalDecisions.Shared.DbConnectionAttributes()
    conAttributes.Collection = boMainPropertyBag

    Dim boConnectionInfo = New ConnectionInfo With {
        .Attributes = conAttributes,
        .DatabaseName = database,
        .ServerName = server
    }

    If Not integratedSecurity Then
        boConnectionInfo.UserID = userId
        boConnectionInfo.Password = password
    End If

    Dim myTables As CrystalDecisions.CrystalReports.Engine.Tables = reportDocument.Database.Tables

    For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.Tables
        Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
        myTableLogonInfo.ConnectionInfo.Attributes.Collection.Clear()
        myTableLogonInfo.ConnectionInfo.Attributes = boConnectionInfo.Attributes
    Next

    Dim mySections As CrystalDecisions.CrystalReports.Engine.Sections = reportDocument.ReportDefinition.Sections
    For Each mySection As CrystalDecisions.CrystalReports.Engine.Section In mySections
        Dim myReportObjects As CrystalDecisions.CrystalReports.Engine.ReportObjects = mySection.ReportObjects
        For Each myReportObject As CrystalDecisions.CrystalReports.Engine.ReportObject In myReportObjects
            If myReportObject.Kind = CrystalDecisions.Shared.ReportObjectKind.SubreportObject Then
                Dim mySubreportObject As CrystalDecisions.CrystalReports.Engine.SubreportObject = CType(myReportObject, CrystalDecisions.CrystalReports.Engine.SubreportObject)
                Dim subReportDocument As CrystalDecisions.CrystalReports.Engine.ReportDocument = mySubreportObject.OpenSubreport(mySubreportObject.SubreportName)
                Dim mytablessub As CrystalDecisions.CrystalReports.Engine.Tables = subReportDocument.Database.Tables
                For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In mytablessub
                    Dim myTableLogonInfo As CrystalDecisions.Shared.TableLogOnInfo = myTable.LogOnInfo
                    myTableLogonInfo.ConnectionInfo.Attributes.Collection.Clear()

                    myTableLogonInfo.ConnectionInfo.Attributes = boConnectionInfo.Attributes
                    
                Next
            End If
        Next
    Next

    reportDocument.VerifyDatabase()

    reportDocument.SaveAs("D:\RND\Crystal Reports - Connector\CrystallReportConvertVB\CrystallReportConvertVB\Rpt-changed3.rpt")
End Sub

我使用以下 Crystal 报表版本:

我注意到以下

调用 verifyDatabase()或 refresh()在 ReportDocument将 provider 属性更改回原始属性。

我试过 ApplyLogOnInfo() - 发现它只重置了用户名和密码，而不是提供者。调用此方法还会重置已更改的提供程序。

现在我已经设法通过以下代码更改了提供者，但它总是将提供者重置为原始提供者。

如何才能做到这一点？
是否有任何解决方法？
进一步更新
通过使用 Frank 建议的代码，我能够在主报告中将提供程序更改为 MSOLEDBSQL。 (感谢)下面。但是，子报告仍然失败并给出以下错误。

Not supported within subreports

进一步更新 (2) - 关于子报告提供程序更改
我能够通过以下代码更改 Crystal Report 子报表的提供程序。

Friend Shared Function ChangeOleDbConnectionInfoSubReports(ByVal reportDocument As ReportDocument, ByVal server As String, ByVal database As String, ByVal schema As String, ByVal oledbProvider As String, ByVal integratedSecurity As Boolean, ByVal userId As String, ByVal password As String) As ReportDocument

    Dim boInnerPropertyBag = New PropertyBag()
    boInnerPropertyBag.Add("Application Intent", "READWRITE")
    boInnerPropertyBag.Add("Auto Translate", "-1")
    boInnerPropertyBag.Add("Connect Timeout", "15")
    boInnerPropertyBag.Add("Data Source", server)
    boInnerPropertyBag.Add("DataTypeCompatibility", "0")
    boInnerPropertyBag.Add("General Timeout", "0")
    boInnerPropertyBag.Add("Initial Catalog", database)
    boInnerPropertyBag.Add("Integrated Security", If(integratedSecurity, "True", "False"))
    boInnerPropertyBag.Add("Locale Identifier", "1033")
    boInnerPropertyBag.Add("MARS Connection", "0")
    boInnerPropertyBag.Add("OLE DB Services", "-5")
    boInnerPropertyBag.Add("Provider", oledbProvider)
    boInnerPropertyBag.Add("Tag with column collation when possible", "0")
    boInnerPropertyBag.Add("Trust Server Certificate", "0")
    boInnerPropertyBag.Add("Use DSN Default Properties", "False")
    boInnerPropertyBag.Add("Use Encryption for Data", "0")
    Dim boMainPropertyBag = New PropertyBag()
    boMainPropertyBag.Add("Database DLL", "crdb_ado.dll")
    boMainPropertyBag.Add("QE_DatabaseName", database)
    boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)")
    boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag)
    boMainPropertyBag.Add("QE_ServerDescription", server)
    boMainPropertyBag.Add("QE_SQLDB", "True")
    boMainPropertyBag.Add("SSO Enabled", "False")

    Dim boConnectionInfo = New CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo With {
        .Attributes = boMainPropertyBag,
        .Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
    }

    If Not integratedSecurity Then
        boConnectionInfo.UserName = userId
        boConnectionInfo.Password = password
    End If

    For Each subreport As ReportDocument In reportDocument.Subreports
        For Each table As CrystalDecisions.ReportAppServer.DataDefModel.Table In reportDocument.ReportClientDocument.SubreportController.GetSubreportDatabase(subreport.Name).Tables

            Dim boTable = New CrystalDecisions.ReportAppServer.DataDefModel.Table With {
                .ConnectionInfo = boConnectionInfo,
                .Name = table.Name,
                .QualifiedName = schema & "." & table.Name,
                .[Alias] = table.[Alias]
            }

            reportDocument.ReportClientDocument.SubreportController.SetTableLocation(subreport.Name, table, boTable)
        Next
    Next

    reportDocument.VerifyDatabase()
    Return reportDocument

End Function

经验教训

Noticed that, I can not update the driver if the installed CrystalReport Runtime does not match with the Visual studio references (I have taken the DLLs from some Nuget packages that are not official releases). ICouldn't make the code work with Crystal Reports 10.5.3700 that I usedand I updated the runtime to 13.0.2000 (As per this article)

Crystal Reports also have tight rules with the processor architecture.Since I'm using a 32bit version, I have set all the settings in VisualStudio to compile the project as a 32bit app.

Answer 1

您可以通过循环遍历 ConnectionInfos 以编程方式替换报告的连接信息。并更换供应商。有一个(IMO hack-y)关于添加连接/驱动程序信息的解释here它引用了一个帮助编写数据库登录代码的 SAP KB，但文章中的链接已失效(相反，请使用 this one - 这对您的需求可能更有用)。
2020 年 7 月 7 日更新 (包括子报表处理)

// Add these using's (for readability in here):
using RasTables = CrystalDecisions.ReportAppServer.DataDefModel.Tables;
using RasTable = CrystalDecisions.ReportAppServer.DataDefModel.Table;
using RasConnectionInfo = CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo;
// ...

/// <summary>
/// Updates all of the tables contained within the given Crystal Report with new OLEDB connection parameters 
/// </summary>
/// <param name="reportDocument">A <see cref="CrystalDecisions.CrystalReports.Engine.ReportDocument"/> of the Crystal Report</param>
/// <param name="server">The new server (Data Source) to use</param>
/// <param name="database">The new database (Initial Catalog) to use</param>
/// <param name="oledbProvider">The new OLEDB provider (see remarks)</param>
/// <param name="integratedSecurity">Whether to use a trusted connection</param>
/// <param name="userId">The new user id to use</param>
/// <param name="password">The new user&apos;s password</param>
/// <remarks>Some valid values for a SQL Server OLEDB provider (note: matching vendor components <b>must</b> be installed)<br/>
/// <p>
/// <dt>OLEDBSQL</dt>
/// <dd>Legacy OLE DB provider for Microsoft SQL Server</dd>
/// <dt>SQLNCLI11</dt>
/// <dd>SQL Native Client v11</dd>
/// <dt>MSOLEDBSQL</dt>
/// <dd>latest OLE DB provider for Microsoft SQL Server (use this if TLS 1.0/1.1 are disabled on the SQL Server).</dd>
/// </remarks>
internal static void ChangeOleDbConnectionInfo(ReportDocument reportDocument, string server, string database, 
    string oledbProvider, bool integratedSecurity, string userId, string password)
{
    // boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object
    var boMainPropertyBag = new PropertyBag();
    // boInnerPropertyBag: These hold the attributes for the QE_LogonProperties
    // In the main property bag (boMainPropertyBag)
    var boInnerPropertyBag = new PropertyBag();

    // Set the attributes for the boInnerPropertyBag
    boInnerPropertyBag.Add("Application Intent", "READWRITE");
    boInnerPropertyBag.Add("Auto Translate", "-1");
    boInnerPropertyBag.Add("Connect Timeout", "15");
    boInnerPropertyBag.Add("Data Source", server);
    boInnerPropertyBag.Add("DataTypeCompatibility", "0");
    boInnerPropertyBag.Add("General Timeout", "0");
    boInnerPropertyBag.Add("Initial Catalog", database);
    boInnerPropertyBag.Add("Integrated Security", integratedSecurity ? "True" : "False");
    boInnerPropertyBag.Add("Locale Identifier", "1033");
    boInnerPropertyBag.Add("MARS Connection", "0");
    boInnerPropertyBag.Add("OLE DB Services", "-5");
    boInnerPropertyBag.Add("Provider", oledbProvider);
    boInnerPropertyBag.Add("Tag with column collation when possible", "0");
    boInnerPropertyBag.Add("Trust Server Certificate", "0");
    boInnerPropertyBag.Add("Use DSN Default Properties", "False");
    boInnerPropertyBag.Add("Use Encryption for Data", "0");

    // Set the attributes for the boMainPropertyBag
    boMainPropertyBag.Add("Database DLL", "crdb_ado.dll");
    boMainPropertyBag.Add("QE_DatabaseName", database);
    boMainPropertyBag.Add("QE_DatabaseType", "OLE DB (ADO)");
    // Add the QE_LogonProperties we set in the boInnerPropertyBag Object
    boMainPropertyBag.Add("QE_LogonProperties", boInnerPropertyBag);
    boMainPropertyBag.Add("QE_ServerDescription", server);
    boMainPropertyBag.Add("QE_SQLDB", "True");
    boMainPropertyBag.Add("SSO Enabled", "False");

    // Create a new ConnectionInfo object
    var boConnectionInfo = new RasConnectionInfo
    {
        // Pass the database properties to a connection info object
        Attributes = boMainPropertyBag,
        // Set the connection kind
        Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE
    };

    if (!integratedSecurity)
    {
        boConnectionInfo.UserName = userId;
        boConnectionInfo.Password = password;
    }

    // Local function will return a copy of the given table using the provided connection info
    RasTable CreateBoTableCopy(RasTable originalTable, RasConnectionInfo connectionInfo)
    {
        // Create a new Database Table to replace the reports current table.
        return new RasTable
        {
            // Pass the connection information to the table
            ConnectionInfo = connectionInfo,

            Name = originalTable.Name,
            QualifiedName = originalTable.QualifiedName,
            Alias = originalTable.Alias
        };
    }

    var reportClientDocument = reportDocument.ReportClientDocument;

    // Get the Database Tables Collection for your report
    RasTables boTables = reportClientDocument.DatabaseController.Database.Tables;

    // For each table in the report, set the report's table location to use a newly-modified
    // copy of itself with the new connection info
    foreach (RasTable table in boTables)
    {
        reportClientDocument.DatabaseController.SetTableLocation(table, CreateBoTableCopy(table, boConnectionInfo));
    }

    // Now drop this same science on all of the subreports
    foreach (ReportDocument subreport in reportDocument.Subreports)
    {
        var subreportName = subreport.Name;
        foreach (RasTable table in reportClientDocument.SubreportController.GetSubreportDatabase(subreportName).Tables)
        {
            reportClientDocument.SubreportController.SetTableLocation(subreportName, table, CreateBoTableCopy(table, boConnectionInfo));
        }
    }

    // Verify the database after adding substituting the new table to ensure that the table 
    // updates properly when adding Command tables or Stored Procedures.
    reportDocument.VerifyDatabase();
}

用法:

var newSqlProvider = "MSOLEDBSQL";
if (!string.IsNullOrWhiteSpace(overrideSqlProvider))
    ChangeOleDbConnectionInfo(myReportDocument, newServerName, newDatabase, newSqlProvider, false, userName, userPassword);