2832413 - How To: change the SQL for a Command in .NET using ClientDocument (Engine) or RAS ReportClientDocument (Report application Server)

I'm looking for a snippet of code that can change the Command SQL Statement in code

Crystal Reports Developer, for Visual Studio

Here's how to. A few things to note:

• The connection string only requires the Provider and basic connection info, do not add more properties.
• In C# the escape character must be the usual \" characters

{

String newTableName = "Command";

 CrystalDecisions.Shared.

 ConnectionInfo connectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

 DbConnectionAttributes dbconn = newDbConnectionAttributes();

 NameValuePairs2 propertyBag = newNameValuePairs2();

// OLE DB Connection

//propertyBag.Set("QE_DatabaseDLL", "crdb_ado.dll");

//propertyBag.Set("QE_Servertype", "OLE DB (ADO)");

//propertyBag.Set("QE_ConnectionString", "Provider=SQLNCLI11;User ID=;Initial Catalog=xtreme;Data Source=ServerName;");

//propertyBag.Set("QE_Servername", "ServerName");

//ODBC connection 

propertyBag.Set("QE_DatabaseDLL", "crdb_odbc.dll");

propertyBag.Set("QE_Servertype", "ODBC (RDO)");

propertyBag.Set("QE_ConnectionString", "Provider=ODBCTest;User ID=;Initial Catalog=xtreme;Data Source=10.161.27.110;");

propertyBag.Set("QE_Servername", "ODBCTest");

dbconn.Collection = propertyBag;

connectionInfo.Attributes = dbconn;

connectionInfo.UserID ="sa";

connectionInfo.Password ="Password";

// OLE DB Server Name or IP Connection

//connectionInfo.ServerName = "10.161.27.110";

//ODBC DSN Name

connectionInfo.ServerName ="ODBCTest";

connectionInfo.DatabaseName ="xtreme";

String sqlQueryString = "SELECT \"Orders_Detail\".\"Order ID\", \"Orders_Detail\".\"Product ID\", \"Orders_Detail\".\"Unit Price\", \"Orders_Detail\".\"Quantity\" FROM \"xtreme\".\"dbo\".\"Orders Detail\" \"Orders_Detail\" WHERE \"Orders_Detail\".\"Order ID\"<=1002";

try

{

 rpt.SetSQLCommandTable(connectionInfo, newTableName, sqlQueryString.ToString());

 btnSQLStatement.Text ="Command Table SQL updated: \n" + sqlQueryString;

}

catch (Exception ex)

{

 MessageBox.Show("ERROR: " + ex.Message);

 btnSQLStatement.Text = "Command Table SQL update failed";

}

//// this works do not alter

////Create a new Command Table to replace the reports current table.

//CrystalDecisions.ReportAppServer.DataDefModel.CommandTable boTable = new CrystalDecisions.ReportAppServer.DataDefModel.CommandTable();

////boMainPropertyBag: These hold the attributes of the tables ConnectionInfo object

//PropertyBag boMainPropertyBag = new PropertyBag();

////boInnerPropertyBag: These hold the attributes for the QE_LogonProperties

////In the main property bag (boMainPropertyBag)

//PropertyBag 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", "ServerName");

//boInnerPropertyBag.Add("DataTypeCompatibility", "0");

//boInnerPropertyBag.Add("General Timeout", "0");

//boInnerPropertyBag.Add("Initial Catalog", "xtreme");

//boInnerPropertyBag.Add("Integrated Security", "False");

//boInnerPropertyBag.Add("Locale Identifier", "1033");

//boInnerPropertyBag.Add("MARS Connection", "0");

//boInnerPropertyBag.Add("OLE DB Services", "-5");

//boInnerPropertyBag.Add("Provider", "SQLNCLI11");

//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", "xtreme");

//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", "ServerName");

//boMainPropertyBag.Add("QE_SQLDB", "True");

//boMainPropertyBag.Add("SSO Enabled", "False");

////Create a new ConnectionInfo object

//CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo boConnectionInfo = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();

////Pass the database properties to a connection info object

//boConnectionInfo.Attributes = boMainPropertyBag;

////Set the connection kind

//boConnectionInfo.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;

////**EDIT** Set the User Name and Password if required.

//boConnectionInfo.UserName = "sa";

//boConnectionInfo.Password = "Password";

////Pass the connection information to the table

//boTable.ConnectionInfo = boConnectionInfo;

////Get the Database Tables Collection for your report

//CrystalDecisions.ReportAppServer.DataDefModel.Tables boTables;

//boTables = rptClientDoc.DatabaseController.Database.Tables;

////For each table in the report:

//// - Set the Table Name properties.

//// - Set the Command table's command text.

//// - Set the table location in the report to use the new modified table

//boTable.Name = "Command";

//boTable.QualifiedName = "Command";

//boTable.Alias = "Command";

//boTable.CommandText = "SELECT \"Orders_Detail\".\"Order ID\", \"Orders_Detail\".\"Product ID\", \"Orders_Detail\".\"Unit Price\", \"Orders_Detail\".\"Quantity\" FROM \"xtreme\".\"dbo\".\"Orders Detail\" \"Orders_Detail\" WHERE \"Orders_Detail\".\"Order ID\"<=1002";

//CrystalDecisions.ReportAppServer.Controllers.DatabaseController databaseController = rptClientDoc.DatabaseController;

//ISCRTable oldTable = (ISCRTable)databaseController.Database.Tables[0];

//btnSQLStatement.Text += "After set SQL Command Object: \n" + ((dynamic)oldTable).CommandText.ToString();

//btnSQLStatement.Text += "\n";

//IsRpt = false;

////rptClientDoc.DatabaseController.VerifyTableConnectivity(0);

//////boTable.CommandText = "SELECT " + (char)34 + "Orders_Detail" + (char)34 + "." + (char)34 +

////// "Order ID" + (char)34 + ", " + (char)34 + "Orders_Detail" + (char)34 +

////// "." + (char)34 + "Product ID" + (char)34 + ", " + (char)34 + "Orders_Detail" + (char)34 +

////// "." + (char)34 + "Unit Price" + (char)34 + ", " + (char)34 + "Orders_Detail" + (char)34 +

////// "." + (char)34 + "Quantity" + (char)34 + " FROM " + (char)34 +

////// "xtreme" + (char)34 + "." + (char)34 + "dbo" + (char)34 + "." + (char)34 +

////// "Orders Detail" + (char)34 + " " + (char)34 + "Orders_Detail" + (char)34;

//rptClientDoc.DatabaseController.SetTableLocation(boTables[0], boTable);

//// this works do not alter

}

KBA - 1553921 - Is there a utility that would help in writing database logon code?

.net, Command SQL, CR for VS , KBA , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

SAP Crystal Reports XI