I am using Visual Studio 2013 in my asp.net web application and using Crystal Reports heavily. My database is SQL Server (using AWS RDS). Everything is working perfectly. The only issue is, from the database side, the Crystal Report connections are not closing/disposing even after closing the browser window. It continuously increasing the number of connections.
This is my code:
ReportDocument cryRpt = new ReportDocument();
ParameterFields paramFields = new ParameterFields();
ParameterField paramField = new ParameterField();
cryRpt.Load(Server.MapPath("~/Reports/Report001.rpt"));
String host = System.Configuration.ConfigurationManager.AppSettings["SqlServer"];
String database = System.Configuration.ConfigurationManager.AppSettings["SqlDatabase"];
String user = System.Configuration.ConfigurationManager.AppSettings["SqlUsername"];
String password = System.Configuration.ConfigurationManager.AppSettings["SqlPassword"];
var connectionInfo = new ConnectionInfo
{
Type = ConnectionInfoType.SQL,
ServerName = host,
DatabaseName = database
};
connectionInfo.IntegratedSecurity = false;
connectionInfo.UserID = user;
connectionInfo.Password = password;
TableLogOnInfo newLogonInfo = null;
foreach (CrystalDecisions.CrystalReports.Engine.Table currentTable in cryRpt.Database.Tables)
{
newLogonInfo = currentTable.LogOnInfo;
newLogonInfo.ConnectionInfo = connectionInfo;
currentTable.ApplyLogOnInfo(newLogonInfo);
}
ParameterField pReportName = new ParameterField();
pReportName.ParameterFieldName = "REPONAME";
ParameterDiscreteValue dcpReportName = new ParameterDiscreteValue();
dcpReportName.Value = "REPORT";
pReportName.CurrentValues.Add(dcpReportName);
paramFields.Add(pReportName);
CrystalReportViewer1.ParameterFieldInfo = paramFields;
CrystalReportViewer1.Zoom(100);
CrystalReportViewer1.PrintMode = CrystalDecisions.Web.PrintMode.ActiveX;
CrystalReportViewer1.ReportSource = cryRpt;
CrystalReportViewer1.ReuseParameterValuesOnRefresh = true;
CrystalReportViewer1.ShowFirstPage();
// Disposing the report
foreach (CrystalDecisions.CrystalReports.Engine.Table currentTable in cryRpt.Database.Tables)
{
currentTable.Dispose();
}
CrystalReportViewer1.ReportSource = null;
cryRpt.Database.Dispose();
cryRpt.Close();
cryRpt.Dispose();
cryRpt = (ReportDocument)CrystalReportViewer1.ReportSource;
CrystalReportViewer1.Dispose();
connectionInfo.Attributes.Collection.Clear();
GC.Collect();
Tried to use the unload method also like this. but no luck.
protected void CrystalReportViewer1_Unload(object sender, EventArgs e)
{
cryRpt.Close();
cryRpt.Dispose();
CrystalReportViewer1.Dispose();
}
As a temporary solution, I'm manually killing the sleeping database connection from the database using a stored procedure.
I'm using ODBC connection to get the data. ODBC credentials are stored in the config file and retrieved as follows.
String host = System.Configuration.ConfigurationManager.AppSettings["SqlServer"];
String database = System.Configuration.ConfigurationManager.AppSettings["SqlDatabase"];
String user = System.Configuration.ConfigurationManager.AppSettings["SqlUsername"];
String password = System.Configuration.ConfigurationManager.AppSettings["SqlPassword"];
Kindly help me to come out of this issue.