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.

Advertisements
This entry was posted in C#, Database and tagged . Bookmark the permalink.

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

  1. malool says:

    thanks good information .. waiting for any new updates the helps me in my work (^_^)

  2. Azra says:

    shall apply this in my new reports . hope it will solve lot of deployment issues of crystal report. thanks for the update.

  3. faraawla says:

    WoOoOoW this is so helpful …good job πŸ™‚

  4. Adnan Badar says:

    yeh cheez mairay azeez πŸ™‚

  5. farhan says:

    I have created web application in asp.net 2010 with crystal report.crystal rport is established using stored procedure with oledb connection.Crystal report works fine on local but not on live,it showes error failed to open connection database vendor code 17.want proper solution..

  6. smarty says:

    This comment has been removed by the author.

  7. It works perfectly well.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s