Create Dynamic Connection with Crystal Report using Asp.net and Oracle Stored Procedure

Dynamic crystal report connection string require

d for transfer application dev to test.Normally developer working in devlopment server and after complet the development it will deploy the application into TEST or Production Server .In this case crystal report have not worked if you are specially using oracle stored procedure .Here I explain you how to create function for dynamic connection with stored procedure.

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

public static ReportDocument ConnectionInfo(ReportDocument rpt)
{

try
{
ReportDocument crSubreportDocument;
string[] strConnection = ConfigurationManager.ConnectionStrings(“ABC”)].ConnectionString.Split(new char[] { ‘;’ });
Database oCRDb = rpt.Database;
Tables oCRTables = oCRDb.Tables;

CrystalDecisions.CrystalReports.Engine.Table oCRTable = default(CrystalDecisions.CrystalReports.Engine.Table);
TableLogOnInfo oCRTableLogonInfo = default(CrystalDecisions.Shared.TableLogOnInfo);
ConnectionInfo oCRConnectionInfo = new CrystalDecisions.Shared.ConnectionInfo();

oCRConnectionInfo.ServerName = strConnection[0].Split(new char[] { ‘=’ }).GetValue(1).ToString();
oCRConnectionInfo.Password = strConnection[2].Split(new char[] { ‘=’ }).GetValue(1).ToString();
oCRConnectionInfo.UserID = strConnection[1].Split(new char[] { ‘=’ }).GetValue(1).ToString();

//Loop through all tables in the report and apply the
//connection information for each table.
for (int i = 0; i < oCRTables.Count; i++)
{
oCRTable = oCRTables[i];
oCRTableLogonInfo = oCRTable.LogOnInfo;
oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo;
oCRTable.ApplyLogOnInfo(oCRTableLogonInfo);
oCRTable.Location = “ABCUSER.” + oCRTable.Location;// this is a combination of Schema name and Stored procedure name

}

for (int i = 0; i < rpt.Subreports.Count; i++)
{
{
crSubreportDocument = rpt.OpenSubreport(rpt.Subreports[i].Name);
oCRDb = crSubreportDocument.Database;
oCRTables = oCRDb.Tables;
foreach (CrystalDecisions.CrystalReports.Engine.Table aTable in oCRTables)
{
oCRTableLogonInfo = aTable.LogOnInfo;
oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo;
aTable.ApplyLogOnInfo(oCRTableLogonInfo);
oCRTable.Location = “DMS_SYS_PROC.” + oCRTable.Location;
}

}

}

}
catch (Exception ex)
{
if (ExceptionPolicy.HandleException(ex, “General”))
throw;
}
return rpt;
}

This will not work with oracle packages.

Source : https://dotnetfarrukhabbas.wordpress.com/2010/10/12/create-dynamic-connection-with-crystal-report-using-asp-net-and-oracle-stored-procedure/