Skip to Content
0
Jul 30, 2019 at 12:51 PM

Change SQL query in CommandTable programmatically

245 Views Last edit Jul 30, 2019 at 01:49 PM 6 rev

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?

Attachments

before.png (1.9 kB)
after.png (1.3 kB)