CODEWITHSUNDEEP
c#.netcrystal-reports
Maciej Jureczko
Maciej Jureczko

Reputation: 1598

SQL query in Crystal Reports doesn't update

I'm writing an application which changes Crystal Reports database access parameters in report files. I open reports witin a .NET windows forms app and apply the SDK functionality to change driver type (ODBC/OLEDB), server name, database name, user, password, authentication type etc. I'm having a problem with the database name. My code DOES change the specific properties of the table ConnectionInfo (in subreports too) but fails to update the general SQL Query within the report. This results in the report still accessing the old database.

So if the original report was configured to access database_1 and I'm changing it to database_2, it will have all table properties properly changed to database_2 (verifiable in the Designer). It will still have database_1 in the query though. The database name remains unchanged in both the SDK RowsetController.GetSQLStatement() result and in the Crystal Reports Developer query view (Database->Show SQL Query...).

Also I have to have both databases (database_1 and database_2) online while the conversion takes place, otherwise I get exceptions on either GetSQLStatement(when database_1 is offline; becuase it still refers to it) or SetTableLocation (when database_2 is offline - this is expected and acceptable behavior though). If both db are online, there are no errors.

Here is exactly what I'm using:

1) CrystalDecisions.CrystalReports.Engine.ReportDocument.Load(filePath, OpenReportMethod.OpenReportByTempCopy) (...)

2) Make and fill CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag

3) Iterate through CrystalDecisions.ReportAppServer.DataDefModel.Tables and apply all properties with SetTableLocaiton() for each one.

4) Repeat with each subreport

5) RowsetController.GetSQLStatement() to view the report's sql query.

Is there some way to update the query basing on the new table ConnectionInfos (which seem to be set properly)? I don't even see any possibility of manually updating the query (GET, search&replace, SET).

I'm using:

.NET 4.5, Visual Studio 2012, CR for VS 13.0.5, Crystal Reports Developer 9.2.2.693 for results verification (source reports are also created with it)

Upvotes: 0

Views: 2383

Answers (1)

Maciej Jureczko
Maciej Jureczko

Reputation: 1598

Answer: set propper QualifiedName for each table. The QualifiedName is the full name of the table including the DbName. This later appears in the SQL Query of the report. By qualified name we understand:

myDatabase.mySchema.myTableName

Code example:

CrystalDecisions.ReportAppServer.DataDefModel.Table boTable = new CrystalDecisions.ReportAppServer.DataDefModel.Table();
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boMainPropertyBag = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag boInnerPropertyBag = new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();

// Custom function to fill property bags with values which influence the table properties as seen in CR Developer
FillPropertyBags(boMainPropertyBag, boInnerPropertyBag);

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
boConnectionInfo.Attributes = boMainPropertyBag;
boConnectionInfo.Kind = CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

boTable.ConnectionInfo = boConnectionInfo;

CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables = boReportDocument.ReportClientDocument.DatabaseController.Database.Tables;

for (int i = 0; i < boTables.Count; i++)
{
   boTable.Name = boTables[i].Name;
   // the QualifiedName is directly taken into the CR general query so this is a quick fix to change it
   boTable.QualifiedName = boTables[i].QualifiedName.Replace("oldDbName", "newDbName"); 
   boTable.Alias = boTables[i].Alias;
   boReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(boTables[i], boTable);
}

Uhh...Researching for whole day and found answer after publishing question on SO.

Upvotes: 1

Related Questions