A flip answer may be; "When ever your current code does not work and you can not figure out why not...". And to some extent, this is true. However there are certain instances when using replace connection is highly recommended and perhaps required. Before diving in, a word on Crystal Reports SDK for .NET and InProc RAS ADK for .NET. The runtime for both SDKs is free. InProc RAS SDK is included in all "full" versions of Crystal Reports as well as in Crystal Reports for Visual Studio 2010 CRVS2010). The main differences between the two SDKs;
For more details see Choosing the Right Business Objects SDK for Your Needs. For an introductory read on InProc RAS SDK see How to Use The RAS SDK .NET With In-Process RAS Server.
Finally, this blog assumes that certain steps have been taken towards troubleshooting database connection issues. These steps include but are not limited to searching the KBase, forums and Developer Help files. As well, reading the article Troubleshooting Guide to Database Connectivity Issues with Crystal Reports in Visual Studio .NET App... is also highly recommended.
Upgrading reports created in versions of CR older than version 9 may turn out to be more problematic than it possibly should be.
The whole Crystal Reports Database engine was rewritten in CR 9 which introduced new linking methods and new UI. As well as many “hacks” introduced to work around issues caused by database client issues were removed, thus pushing responsibility of fixing the database client issues to the database vendor.
Since version 9, the recommendation when updating reports was to open each report in the report designer and set location using then new CRDB_*.dll DB drivers. Doing so causes the database engine to use the Verify Database function in the Designer and thus to clean up the connection info saved in the RPT file. While this process can also be done using code, the results are more consistent and successful if done in the designer.
Here I must insert a word of thanks to Don Williams whose Replace Connection method code is used bellow:
private void ReplaceConnection_Click(object sender, EventArgs e)
{ CrystalDecisions.CrystalReports.Engine.ReportDocument rpt = new CrystalDecisions.CrystalReports.Engine.ReportDocument();
ISCDReportClientDocument rcd;
rcd = rptClientDoc;
rptClientDoc.DatabaseController.LogonEx("dwcb12003", "xtreme", "sb", "pw");
//Create the logon propertybag for the connection we wish to use
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag logonDetails = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
logonDetails.Add("Auto Translate", -1);
logonDetails.Add("Connect Timeout", 15);
logonDetails.Add("Data Source", "dwcb12003");
logonDetails.Add("General Timeout", 0);
ogonDetails.Add("Initial Catalog", "Orders");
logonDetails.Add("Integrated Security", "True");
logonDetails.Add("Locale Identifier", 1033);
logonDetails.Add("OLE DB Services", -5);
logonDetails.Add("Provider", "SQLOLEDB");
logonDetails.Add("Use Encryption for Data", 0);
logonDetails.Add("Owner", "dbo");
// schema
//Create the QE (query engine) propertybag with the provider details and logon property bag.
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag QE_Details = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
QE_Details.Add("Database DLL", "crdb_ado.dll");
QE_Details.Add("QE_DatabaseName", "Orders");
QE_Details.Add("QE_DatabaseType", "OLE DB (ADO)");
QE_Details.Add("QE_LogonProperties", logonDetails);
QE_Details.Add("QE_ServerDescription", "dwcb12003");
QE_Details.Add("QE_SQLDB", "True");
QE_Details.Add("SSO Enabled", "False");
QE_Details.Add("Owner", "dbo");
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo oldConnInfo;
CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfos oldConnInfos;
oldConnInfos = rcd.DatabaseController.GetConnectionInfos(null);
for (int I = 0; I<oldConnInfos.Count; I++) { oldConnInfo = oldConnInfos;
newConnInfo.Attributes = QE_Details;
newConnInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
rcd.DatabaseController.ReplaceConnection(oldConnInfo, newConnInfo, null, CrystalDecisions.ReportAppServer.DataDefModel.CrDBOptionsEnum.crDBOptionDoNotVerifyDB);
}
}
The next question is; how do I know what connection to use when doing the above replace? Use KBase 1553921 - Is there a utility that would help in writing database logon code? . The KB contains a link to a utility that will write out the code for your report - in VB and C#.
Any time you need to do any table level modifications. For example, setting a Fully Qualified Table Name. Use SetTableLocation in this instance. Basic code is bellow:
Dim myDataSourceConnections As DataSourceConnections = northwindCustomersReport.DataSourceConnections
For Each myConnectInfo As IConnectionInfo In myDataSourceConnections
If myConnectInfo.ServerName = "database"
Then
'SQL Server
myConnectInfo.SetLogon("username", "password")
ElseIf myConnectInfo.ServerName = "database2" Then 'Visual FoxPro
myConnectInfo.SetConnection("database2", "", "", "")
End If
Next
The SAP Crystal Reports, version for Visual Studio and the .NET SDK Application Development forums are monitored and you will get a reply there - 90% of the time within the same day and often within a few hours. For critical issues, I suggest creating a phone case here:
Have a look at the following:
KB 1553921 - Is there a utility that would help in writing database logon code?
How to Use The RAS ...
Troubleshooting Guide to Database Connectivity Issues with Crystal Reports in Visual Studio .NET App...
And of course, don't forget to search the KBase, use the Developer Help files for your version of Crystal Reports and look at the NET RAS SDK Samples.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
38 | |
19 | |
13 | |
13 | |
11 | |
10 | |
10 | |
10 | |
8 | |
8 |