Change SQL query in CommandTable programmatically

[former_member1039335]

Hi all,
is it possible programmatically to change SQL text in the CommandTable of the report?

For study purposes, I created very simple report with only one CommandTable object which contains the following sql:

SELECT 1111 AS N FROM dual

Then I was trying to replace this sql to "SELECT 2222 AS N FROM dual" in the following way:

CrystalDecisions.CrystalReports.Engine.ReportDocument doc =
    new CrystalDecisions.CrystalReports.Engine.ReportDocument();
doc.Load("С:\\Temp\\Test.rpt", CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy);

CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag newMainPropertyBag =
    new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag newInnerPropertyBag =
    new CrystalDecisions.ReportAppServer.DataDefModel.PropertyBag();
foreach (CrystalDecisions.Shared.NameValuePair2 pr2
    in doc.Database.Tables[0].LogOnInfo.ConnectionInfo.Attributes.Collection)
{
    if (pr2.Name.ToString() == "QE_LogonProperties")
    {
        foreach (CrystalDecisions.Shared.NameValuePair2 SubPair2 in
            (pr2.Value as CrystalDecisions.Shared.DbConnectionAttributes).Collection)
            newInnerPropertyBag.Add(SubPair2.Name.ToString(), SubPair2.Value.ToString());
        newMainPropertyBag.Add("QE_LogonProperties", newInnerPropertyBag);
    }
    else
        newMainPropertyBag.Add(pr2.Name.ToString(), pr2.Value.ToString());
}

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo newConnectionInfo =
    new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
newConnectionInfo.Attributes = newMainPropertyBag;
newConnectionInfo.Kind =
    CrystalDecisions.ReportAppServer.DataDefModel.CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
newConnectionInfo.UserName = "scott";
newConnectionInfo.Password = "xxxxx";

CrystalDecisions.ReportAppServer.DataDefModel.CommandTable NewCmdTable =
    new CrystalDecisions.ReportAppServer.DataDefModel.CommandTable();
CrystalDecisions.ReportAppServer.DataDefModel.CommandTable OldCmdTable
    = doc.ReportClientDocument.DatabaseController.Database.Tables[0] 
        as CrystalDecisions.ReportAppServer.DataDefModel.CommandTable;
NewCmdTable.ConnectionInfo = newConnectionInfo;
NewCmdTable.Name = OldCmdTable.Name;
NewCmdTable.QualifiedName = OldCmdTable.QualifiedName;
NewCmdTable.Alias = OldCmdTable.Alias;
NewCmdTable.CommandText = "SELECT 2222 AS N FROM dual";

doc.ReportClientDocument.DatabaseController.SetTableLocationEx(OldCmdTable, NewCmdTable);
doc.SetDatabaseLogon("scott", "xxxxx");
doc.VerifyDatabase();
doc.SaveAs("С:\\Temp\\DoneTest.rpt");
doc.Close();

Unfortunately after this change, the stored report is not fully functional,

So, once I apply SetTableLocationEx method, ApplyLogOnInfo and ReplaceConnection methods do nothing and don't throw any exceptions.

Also when I was looking through database connection parameters of this report,
I found out that "Database Dll" parameter changed from "crdb_oracle.dll" to "crdb_quary.dll".

For example before sql changing:

After SetTableLocationEx

why this db connection was changed?

Is there a correct method how to change SQL in the CommandTable without db changes?

[former_member1039335]

Thanks Dell for your comment,
I tried to work via ReportClientDocument object model only.

In may case ISCDClientDocument.Open(object, int) does not help because i open my test report from my local disk.

Additional info.

I took official "NET-CS2005_RAS-Unmanaged_CR115_Modify_Command-Table-SQL.zip" example from the SAP resource and used it for the same change.
The following code from the SAP example was used in my test:

private void CRChangeSQLStatement()
{
    // Crystal Reports declarations
    CrystalDecisions.CrystalReports.Engine.ReportDocument boReportDocument =
        new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    CrystalDecisions.ReportAppServer.ClientDoc.ISCDReportClientDocument boReportClientDocument;
    CrystalDecisions.ReportAppServer.Controllers.DataDefController boDataDefController;
    CrystalDecisions.ReportAppServer.Controllers.DatabaseController boDatabaseController;
    CrystalDecisions.ReportAppServer.DataDefModel.Database boDatabase;
    CrystalDecisions.ReportAppServer.DataDefModel.CommandTable boCommandTable;
    CrystalDecisions.ReportAppServer.DataDefModel.CommandTable boNewCommandTable;

    // Load the report using the CR .NET SDK and get a handle on the ReportClientDocument
    boReportDocument.Load("C:\\Temp\\TestBefore.rpt");

    // Load the report using the CR .NET SDK and get a handle on the ReportClientDocument
    boReportClientDocument = boReportDocument.ReportClientDocument;

    // Use the DataDefController to access the database and the command table.        
    boDataDefController = boReportClientDocument.DataDefController;
    boDatabase = boDataDefController.Database;

    // To change the text of the command table you must clone the original command table.        
    boCommandTable = (CrystalDecisions.ReportAppServer.DataDefModel.CommandTable)boDatabase.Tables[0];
    boNewCommandTable = (CrystalDecisions.ReportAppServer.DataDefModel.CommandTable)boCommandTable.Clone(true);
    boNewCommandTable.CommandText = "SELECT 1111 AS N FROM dual";

    // Use the DatabaseController to set the new table location
    boDatabaseController = boReportClientDocument.DatabaseController;
    boDatabaseController.SetTableLocation(boCommandTable, boNewCommandTable);

    // Save the changed report
    boReportDocument.SaveAs("C:\\Temp\\sql_changed_report.rpt");
    boReportDocument.Close();
    boReportDocument.Dispose();
}

So, after that, I got "sql_changed_report.rpt" file and then tried to change LogOnInfo in the only table[0] by the following way:

private void ChangeLogOnInfo()
{
    CrystalDecisions.CrystalReports.Engine.ReportDocument doc =
        new CrystalDecisions.CrystalReports.Engine.ReportDocument();
    doc.Load("C:\\Temp\\sql_changed_report.rpt");
    CrystalDecisions.Shared.TableLogOnInfo loginf = 
        doc.Database.Tables[0].LogOnInfo.Clone() as CrystalDecisions.Shared.TableLogOnInfo;
    loginf.ConnectionInfo.ServerName = "localhostname_new";
    loginf.ConnectionInfo.DatabaseName = "db01_new";
    loginf.ConnectionInfo.UserID = "scott_new";
    loginf.ConnectionInfo.Password = "xxxx_new";

    /* Warning! The next method ApplyLogOnInfo do nothing,
     * and it becomes non-functional in this report
     * because previously SetTableLocation was done */
    doc.Database.Tables[0].ApplyLogOnInfo(loginf);

    doc.SaveAs("C:\\Temp\\logofinf_changed_report.rpt");
    doc.Close();
    doc.Dispose();
}

Unfortunately, this report has the same issues as in my first post above, as follows:

• ApplyLogOnInfo and ReplaceConnection methods for this stored report do nothing and don't throw any exceptions
• "Database Dll" parameter looks as "crdb_quary.dll" instead of needed "crdb_oracle.dll"

Could you please clarify, is it normal that these needed methods ApplyLogOnInfo and ReplaceConnection do not work after execution of SetTableLocation method ?

Could you please provide an example how programmatically to change SQL in the CommandTable and then got a working report?

Thanks,
Artem

[DellSC]

Yes, but I believe you need to use the ReportClientDocument object model (Unmanaged RAS) instead of ReportDocument.

-Dell

[former_member1039335]

Additional info: I don't encounter any problems if I use "DataDefModel.Table" object instead of "DataDefModel.CommonTable".