Filtering Crystal Reports with Transact-SQL in Asp.NET MVC

I have more than two thousand Reports with Crystal Reports XI in my legacy system. I and all my Clients use SQL Server 2008 R2 database (different servers and database names)

So, I have my new application in ASP.NET MVC 5 that I would like to use my Crystal Reports. In my ASP.NET MVC application I have Grids with filters, and these filters I use to filter the Grid view and the Reports, my filters were developed using Linq to Sql.

My Problem is:

If the Report Select Command is:

select
Cart.Description, Cart.Value, Cart.Date,
CartItems.Description, CartItems.Value, CartItems.Quantity
from Cart inner join CartItems on Cart.codCart = CartItems.codCart

So, the Cart and CartItems tables have columns with the same name, which are: Description and Value. And when viewing the report, the result considers the Description and Value columns from the first table.

For example, I have these records on Sql Server database:

Cart.Description Cart.Value Cart.Date | CartItems.Description CartItems.Value
Sale One $100 01/01/2015 | Product One $60
Sale One $100 01/01/2015 | Product Two $40
Big Sale $8000 02/01/2015 | Product Three $8000

But the Crystal Reports data results is (see that the Description and Value columns show the data of first table -> Cart):

Cart.Description Cart.Value Cart.Date | CartItems.Description CartItems.Value
Sale One $100 01/01/2015 | Sale One $100
Sale One $100 01/01/2015 | Sale One $100
Big Sale $8000 02/01/2015 | Big Sale $8000

I used this tutorial to open Crystal Reports in ASP.NET MVC Crystal report as a PDF ActionResult in ASP.Net MVC

But, instead of use the reportDocument.SetDataSource(dataSet) method, I did it in a different way:

1.Change the connection string in Report to connection string of the Client

pReportDocument.SetDatabaseLogon("user", "Password", "server", "database");

PropertyBag connectionAttributes = new PropertyBag();
connectionAttributes.Add("Auto Translate", "-1");
connectionAttributes.Add("Connect Timeout", "45");
connectionAttributes.Add("Data Source", "server");
connectionAttributes.Add("General Timeout", "0");
connectionAttributes.Add("Initial Catalog", "database");
connectionAttributes.Add("Integrated Security", false);
connectionAttributes.Add("Locale Identifier", "1040");
connectionAttributes.Add("OLE DB Services", "-5");
connectionAttributes.Add("Provider", "SQLOLEDB");
connectionAttributes.Add("Tag with column collation when possible", "0");
connectionAttributes.Add("Use DSN Default Properties", false);
connectionAttributes.Add("Use Encryption for Data", "0");

PropertyBag attributes = new PropertyBag();
attributes.Add("Database DLL", "crdb_ado.dll");
attributes.Add("QE_DatabaseName", "database");
attributes.Add("QE_DatabaseType", "OLE DB (ADO)");
attributes.Add("QE_LogonProperties", connectionAttributes);
attributes.Add("QE_ServerDescription", "server");
attributes.Add("QESQLDB", true);
attributes.Add("SSO Enabled", false);

CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo ci = new CrystalDecisions.ReportAppServer.DataDefModel.ConnectionInfo();
ci.Attributes = attributes;
ci.Kind = CrConnectionInfoKindEnum.crConnectionInfoKindCRQE;
ci.UserName = "user";
ci.Password = "password";

//Update Conn on Tables
foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in pReportDocument.ReportClientDocument.DatabaseController.Database.Tables)
{
table.QualifiedName = "database" + ".dbo." + table.Name;
table.ConnectionInfo = ci;

pReportDocument.ReportClientDocument.DatabaseController.SetTableLocation(table, table);
}

//Update Conn on Tables of SubReports
foreach (ReportDocument subreport in pReportDocument.Subreports)
{
foreach (CrystalDecisions.ReportAppServer.DataDefModel.Table table in pReportDocument.ReportClientDocument.SubreportController.GetSubreportDatabase(subreport.Name).Tables)
{
table.QualifiedName = "database" + ".dbo." + table.Name;
table.ConnectionInfo = ci;

pReportDocument.ReportClientDocument.SubreportController.SetTableLocation(subreport.Name, table, table);
}
}

2. Get the complete T-SQL from Report, with Columns, Tables, Where, Group By and Order By

CrystalDecisions.ReportAppServer.Controllers.RowsetController objRowsetController = pReportDocument.ReportClientDocument.RowsetController;

//ISCRGroupPath iscrGroupPath;
CrystalDecisions.ReportAppServer.DataDefModel.GroupPath groupPath = new GroupPath();

string strSQL;
string strSQLGroup;

strSQL = objRowsetController.GetSQLStatement(groupPath, out strSQLGroup);

strSQL = strSQL.Replace("\r\n", "");

3. Merge the T-SQL from Report with the Where Clause of the Filters

I have a parser of SQL to do this

strSQL = strSQL.Replace("\"", "");

//Parser Query of Report
SqlParser.SqlParser objSqlParserReport = new SqlParser.SqlParser();
objSqlParserReport.Parse(strSQL);

//Parts of Query
string strSelectReport;
string strFromReport;
string strWhereReport;
string strGroupByReport;
string strOrderByReport;

strSelectReport = objSqlParserReport.SelectClause;
strFromReport = objSqlParserReport.FromClause;
strWhereReport = objSqlParserReport.WhereClause;
strGroupByReport = objSqlParserReport.GroupByClause;
strOrderByReport = objSqlParserReport.OrderByClause;

string strQueryReportComplete;
string strWhereComplete = "";

//If exist filter in Report
if (string.IsNullOrEmpty(strWhereReport) == false)
{
if (string.IsNullOrEmpty(strWhereComplete) == false)
{
strWhereComplete = strWhereComplete + " and ";
}

strWhereComplete = strWhereComplete + " " + strWhereReport;
}

//If exist filter in Grid
if (string.IsNullOrEmpty(strWhereFilterGrid) == false)
{
if (string.IsNullOrEmpty(strWhereComplete) == false)
{
strWhereComplete = strWhereComplete + " and ";
}

strWhereComplete = strWhereComplete + " " + strWhereFilterGrid;
}

//Build complete query
strQueryReportComplete = "SELECT " + strSelectReport;

strQueryReportComplete = strQueryReportComplete + " FROM " + strFromReport;

if (string.IsNullOrEmpty(strWhereComplete.Trim()) == false)
{
strQueryReportComplete = strQueryReportComplete + " WHERE " + strWhereComplete;
}

if (string.IsNullOrEmpty(strGroupByReport.Trim()) == false)
{
strQueryReportComplete = strQueryReportComplete + " GROUP BY " + strGroupByReport;
}

if (string.IsNullOrEmpty(strOrderByReport.Trim()) == false)
{
strQueryReportComplete = strQueryReportComplete + " ORDER BY " + strOrderByReport;
}

4. And finally, I call the SetSQLCommandTable method to update the T-SQL in the report

CrystalDecisions.Shared.ConnectionInfo objConnectionInfo = pReportDocument.Database.Tables[0].LogOnInfo.ConnectionInfo;

pReportDocument.SetSQLCommandTable(objConnectionInfo, "Command", strQueryReportComplete);

So how can I filter the report, using the T-SQL filter?

Note:

- All My reports are developed using the Report Designer, where the database connection is to the Development Database, so I need to change the connection to my Clients database.

- I Could use reportDocument.RecordSelectionFormula method, but I have not found a way to convert the syntax of T-SQL to Crystal syntax.

- Instead of use the solution adopted by me, I could have used the option to reportDocument.SetDataSource(dataSet), but I'd have to do the steps 1 and 2 to take the Query Report, and call the reportDocument.SetDataSource(dataSet) and _contentBytes = StreamToBytes(reportDocument.ExportToStream(ExportFormatType.PortableDocFormat)) methods occurs a overflow memory exception.

This same question I did in the StackOverflow (http://stackoverflow.com/questions/32103173/filtering-crystal-reports-with-transact-sql-in-asp-net-mvc) without result.