Central Data Access Class

[tomme]

Hi all

I have created a community site using VB and asp.net with SQL Server as data provider. All pages are importing the public class DataAccess for communication with the SQL Server. For data binding the public shared function GetDataReader is used. All binding is using this function and the problem is that this function is setting up a new data connection every time accessed, with the result that these are not getting closed.

My question is if its a good idea using only ONE data connection in this class? How will this affect performance? This would mean that all pages are using the same data connection for all data access.

If this is not a good idea, how can I close the connections after data binding? I would prefer not to rewrite all code (a lot) as all are using the same concept with one central publi shared class (clean and nice).

Regards//Tom

[mendhak]

A datareader's connection stays open as long as it is in use. Once the processing is complete, simply close the connection.

I don't think it's a huge risk considering that you open a new connection each time, because each access to the page creates connections, and once the page ends, all objects are destroyed.

Unless they're static.

[tomme]

I already faced problems due to lack of available connections! I got the following message:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

This I guess its due to the fact that the connections don't get closed.

I have made one class called DataAccess. In all pages I import it. The public shared function GetDataReader is used for all binding activities. When this function is executed a new connection is opened. The problem is that I don't know how to close this connection created!

The code looke like this:

Dim hsh As New System.Collections.Hashtable

hsh.Add("@account_id", GetCookie("member_id"))
hsh.Add("@mode", 8)

objNews1.DataSource = GetDataReader("Merus_DNNC_Notes_List", hsh, 1)
objNews1.DataBind()

The question is how and when I can close the DataReader and related connection..?

The code for the function look like this:

Public Shared Function GetDataReader(ByVal sSQL As String, ByVal oParams As System.Collections.Hashtable, ByVal iSource As Integer) As System.Data.SqlClient.SqlDataReader

Dim i As Integer
Dim sParams As String

Dim ConnectionString As String = SharedFunctions.GetSetting(Dimension.Global, "ConnectionString" & CStr(iSource), 0, 0)
dbConnection = New SqlConnection(ConnectionString)
dbConnection.Open()

Dim cmdCommand As New SqlCommand(sSQL, dbConnection)
cmdCommand.CommandType = CommandType.StoredProcedure

Dim myEnumerator As System.Collections.IDictionaryEnumerator = oParams.GetEnumerator()

While myEnumerator.MoveNext()
Select Case myEnumerator.Value.GetType.ToString
Case "System.String", "System.Int32"
cmdCommand.Parameters.Add(New SqlParameter(CStr(myEnumerator.Key), SqlDbType.VarChar))
Case "System.Integer"
cmdCommand.Parameters.Add(New SqlParameter(CStr(myEnumerator.Key), SqlDbType.Int))
End Select
cmdCommand.Parameters(CStr(myEnumerator.Key)).Value = myEnumerator.Value
sParams = sParams & myEnumerator.Value & " "
End While

GetDataReader = cmdCommand.ExecuteReader()
Try

Log("SQL Execution", sSQL & " - " & sParams, LogLevel.ExtendedInfo)
Catch ex As Exception
Log("SQL ERROR", sSQL & " - " & sParams, LogLevel.CriticalError)
End Try

End Function