Tuesday, October 12, 2010

Dynamic Crystal Report Connection String Using Asp.net



 



Dynamic crystal report connection string required 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 ReportDocument ConnectionInfo(ReportDocument rpt)
{
ReportDocument crSubreportDocument;
string[] strConnection = ConfigurationManager.ConnectionStrings[("AppConn")].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();

for (int i = 0; i < oCRTables.Count; i++)
{
oCRTable = oCRTables[i];
oCRTableLogonInfo = oCRTable.LogOnInfo;
oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo;
oCRTable.ApplyLogOnInfo(oCRTableLogonInfo);
if (oCRTable.TestConnectivity())
//' If there is a "." in the location then remove the
// ' beginning of the fully qualified location.
//' Example "dbo.northwind.customers" would become
//' "customers".
oCRTable.Location = oCRTable.Location.Substring(oCRTable.Location.LastIndexOf(".") + 1);


}

for (int i = 0; i < rpt.Subreports.Count; i++)
{

{
// crSubreportObject = (SubreportObject);
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);
if (aTable.TestConnectivity())
//' If there is a "." in the location then remove the
// ' beginning of the fully qualified location.
//' Example "dbo.northwind.customers" would become
//' "customers".
aTable.Location = aTable.Location.Substring(aTable.Location.LastIndexOf(".") + 1);

}
}
}
// }

rpt.Refresh();
return rpt;
}



 






This will not work with oracle packages.

10 comments:

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

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

    ReplyDelete
  3. WoOoOoW this is so helpful ...
    good job :)

    ReplyDelete
  4. 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..

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. can we change provider from SQLOLEDB to SQLNCLI11 dynamically using vb.net?

    ReplyDelete

PDF Arabic watermark using MVC and iTextSharp

PDF full page Arabic watermark using MVC and iTextSharp Download :  Source Code Most of the time we have requirement to  gen...