Corect place to declare DB connection ?

[BlueOfficeHead]

Hello,

I have a general question about the ADO connection object. In my project, I've create a module that's in
the app_code folder and looks something like this:

Public Module Database
Public MyConn As OleDb.OleDbConnection

Public Sub OPEN_DB()
Try
MyConn = New OleDb.OleDbConnection(ConfigurationManager.AppSettings("ConnectionString"))
If MyConn.State <> ConnectionState.Open Then MyConn.Open()
Catch ex As Exception
MyConn.Dispose()
HttpContext.Current.Response.Write(ex.Message)
HttpContext.Current.Response.End()
End Try
End Sub
End Module

Now in my pages I refer to this module when I need to open a new connection. Example:

Public Function GetSomething() As String
Try
Call OPEN_DB()
Dim SQL As String = "SELECT field FROM MySampleTable WHERE ID = 1"
Dim MyCMD As New OleDb.OleDbCommand(SQL, MyConn)
RES = CType(MyCMD.ExecuteScalar(), String)
MyCMD.Dispose()
Catch ex As Exception
Call READ_EXCEPTION(ex)
Finally
MyConn.Close
MyConn.Dispouse
End Try
Return RES
End Function

This works great except now I get occasional errors thrown by the application saying that either the connection
is not open, or is initializing or connection's current state is closed etc.

This does not happen all the time and simply refreshing the page with the error will get rid of it, which leads me
to believe that perhaps the declaration of the MyConn object in the module isn't such a good thing.

When the MyConn is declared the way I have it can I cause any problems ? Thanks for your time !

 

[jbenson001]

You are probably running into problems because the module is being called by different users and the connection object is being overwritten. I would change your module to return an instance of the connection object, this way if that function is called multiple times, the objects will not overwrite each other.

Jim

 

[BlueOfficeHead]

Instance as it make the module into a class ?

 

[jbenson001]

You can use a class, that's what I usally do. But what I mean is to make the Sub into a Function and return a connection object:

Public [b]FUNCTION[/b] OPEN_DB() As OleDbConnection
Try
Dim MyConn as oledb.oledbconnection = New OleDb.OleDbConnection(ConfigurationManager.AppSettings("ConnectionString"))
If MyConn.State <> ConnectionState.Open Then MyConn.Open()
        Catch ex As Exception
            MyConn.Dispose()
            HttpContext.Current.Response.Write(ex.Message)
            HttpContext.Current.Response.End()
        End Try

Return MyConn
    End Function

Then call it:

Public Function GetSomething() As String
     Try
         [b]Dim objConn as OleDbConnection
         objConn = OPEN_DB() [/b]
         Dim SQL As String = "SELECT field FROM MySampleTable WHERE ID = 1"
         Dim MyCMD As New OleDb.OleDbCommand(SQL, objConn )
         RES = CType(MyCMD.ExecuteScalar(), String) 
         MyCMD.Dispose()
     Catch ex As Exception
         Call READ_EXCEPTION(ex)
     Finally
         objConn.Close
         objConn.Dispouse
     End Try
     Return RES
 End Function

Jim