Crystal report database connections not disposing

Viewed 485

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.

1 Answers

In your above code, how do you exactly use the ODBC connection that is stored in a config file ? for example [Is it a DataSet?], because, if so! then you close the Database connection anyway before loading the report as in before this line, here :

cryRpt.Load(Server.MapPath("~/Reports/Report001.rpt"));

I think your problem lies within the Database connection itself with the application, not with Crystal Reports Components. Try to, close the connection Connection.Close before disposing the viewer CrystalReportViewer1.Dispose();, but caution :

Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, you should only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition. For more information, see Garbage Collection. source

Also, your situation is natural, because the client keeps the connection open, read this too

Here is a sample of Code that I use (without ODBC and with MS-Access) :

Imports CrystalDecisions.CrystalReports.Engine
Imports System.Data.OleDb
Imports CrystalDecisions.Shared

Public Class CrystalForm
    Dim cryRpt As New ReportDocument
    Dim crtableLogoninfos As New TableLogOnInfos
    Dim crtableLogoninfo As New TableLogOnInfo
    Dim crConnectionInfo As New ConnectionInfo
    Dim CrTables As Tables
    Dim CrTable As Table
    Private Sub CrystalForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim CMDSelect As String = ("SELECT * FROM Table_Name")
        Try
            Using DT As New DataTable
                Using CN As New OleDbConnection With {.ConnectionString = GetBuilderCNString()}
                    CN.Open()
                    Using DataAdapt As New OleDbDataAdapter(CMDSelect, CN)
                        DataAdapt.Fill(DT)
                    End Using
                End Using
                cryRpt.Load(IO.Path.Combine(Application.StartupPath, "Crystal_Report.rpt"))
                AssignConnection(cryRpt)
                cryRpt.SetDataSource(DT)
                CrystalReportViewer1.ReportSource = cryRpt
            End Using
        Catch ex As EngineException
            MsgBox("Report Load Error : " & ex.Message)
        End Try
    End Sub
    Private Sub AssignConnection(rpt As ReportDocument)
        Try
            Dim ThisConnection As New ConnectionInfo()
            With ThisConnection
                .DatabaseName = ""
                .ServerName = ""
                .UserID = "admin"
                .Password = "MyPassWord"
            End With
            For Each table As Table In rpt.Database.Tables
                AssignTableConnection(table, ThisConnection)
            Next
            For Each section As Section In rpt.ReportDefinition.Sections
                For Each reportObject As ReportObject In section.ReportObjects
                    If reportObject.Kind = ReportObjectKind.SubreportObject Then
                        Dim subReport As SubreportObject = DirectCast(reportObject, SubreportObject)
                        Dim subDocument As ReportDocument = subReport.OpenSubreport(subReport.SubreportName)
                        For Each table As Table In subDocument.Database.Tables
                            AssignTableConnection(table, ThisConnection)
                        Next
                        subDocument.SetDatabaseLogon(ThisConnection.UserID,
                                                     ThisConnection.Password,
                                                     ThisConnection.ServerName,
                                                     ThisConnection.DatabaseName)
                    End If
                Next
            Next
            rpt.SetDatabaseLogon(ThisConnection.UserID,
                                 ThisConnection.Password,
                                 ThisConnection.ServerName,
                                 ThisConnection.DatabaseName)
        Catch ex As EngineException
            MsgBox("Load Report Error : " & ex.Message)
        End Try
    End Sub
    Private Sub AssignTableConnection(ByVal table As Table, ByVal connection As ConnectionInfo)
        Try
            Dim logOnInfo As TableLogOnInfo = table.LogOnInfo
            connection.Type = logOnInfo.ConnectionInfo.Type
            logOnInfo.ConnectionInfo = connection
            With table.LogOnInfo.ConnectionInfo
                .DatabaseName = connection.DatabaseName
                .ServerName = connection.ServerName
                .UserID = connection.UserID
                .Password = connection.Password
                .Type = connection.Type
            End With
            table.ApplyLogOnInfo(logOnInfo)
        Catch ex As EngineException
            MsgBox("Load Table Error : " & ex.Message)
        End Try
    End Sub
End Class
Related