ASP .NET - where should I dispose the connection ?

Author

where should I dispose the connection ?

ypul

2005-02-28, 8:58 am

the code below given is connection class ... now I want to use the
connection in another class , by using the getConnection method.

where should I call con.dispose() ?

in connection class or in the caller class ?

if I call con.dispose in connection class as given below ..will I be able to
use the connection in the other class where I am calling this method ..or I
will get a NULL connection ??

-------------------------Code -------------------------

Public Class Connection

Public Shared Function getConnection() As SqlConnection

Dim con As SqlConnection

Try

con = New
SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))

con.Open()

Return con

Catch ex As SqlException

Throw ex

Finally

con.Close()

con.Dispose()

End Try

End Function

Alexander Shirshov

2005-02-28, 8:58 am

Because of Finally block your method always return closed and disposed
connection, not very useful... Remember, Finally blocks ALWAYS execute.

You must return opened connection and let callers dispose it when they have
finished working with it.

HTH,
Alexander Shirshov

"ypul" <ypul@hotmail.com> wrote in message
news:ezvnNaYHFHA.2132@TK2MSFTNGP14.phx.gbl...
> the code below given is connection class ... now I want to use the
> connection in another class , by using the getConnection method.
>
> where should I call con.dispose() ?
>
> in connection class or in the caller class ?
>
> if I call con.dispose in connection class as given below ..will I be able
> to
> use the connection in the other class where I am calling this method ..or
> I
> will get a NULL connection ??
>
> -------------------------Code -------------------------
>
> Public Class Connection
>
> Public Shared Function getConnection() As SqlConnection
>
> Dim con As SqlConnection
>
> Try
>
> con = New
> SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
>
> con.Open()
>
> Return con
>
> Catch ex As SqlException
>
> Throw ex
>
> Finally
>
> con.Close()
>
> con.Dispose()
>
> End Try
>
> End Function
>
>

Kostadin Kostov

2005-02-28, 8:58 am

Hi
You may use it wherever you like, just be sure to close it after the work is
done or if an exception occured, because it may cause some problems.

"ypul" wrote:

> the code below given is connection class ... now I want to use the
> connection in another class , by using the getConnection method.
>
> where should I call con.dispose() ?
>
> in connection class or in the caller class ?
>
> if I call con.dispose in connection class as given below ..will I be able to
> use the connection in the other class where I am calling this method ..or I
> will get a NULL connection ??
>
> -------------------------Code -------------------------
>
> Public Class Connection
>
> Public Shared Function getConnection() As SqlConnection
>
> Dim con As SqlConnection
>
> Try
>
> con = New
> SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))
>
> con.Open()
>
> Return con
>
> Catch ex As SqlException
>
> Throw ex
>
> Finally
>
> con.Close()
>
> con.Dispose()
>
> End Try
>
> End Function
>
>
>

ypul

2005-02-28, 8:58 am

thanks alex
I have a doubt ..
when I return a connection to the caller class will it create a copy of the connection object or it will pass the same object ??

In the code below ...is the "Con" pointing to the same object of getconnection...??..

if yes then i should dispose "con" in caller1 class ... and there is no danger of undisposed object which I created in the "Connection" class, RIGHT ?

Public Class caller1

Public Function getAll() As DataTable

Dim dt As DataTable

Dim da As SqlDataAdapter

Dim dc As SqlCommand

Dim strsql As String

Dim con As SqlConnection

Try

' get the data from the dataLayer

dt = New DataTable()

con = Connection.getConnection

dc = New SqlCommand(strsql, con)

da = New SqlDataAdapter(dc)

da.Fill(dt)

Return dt

Catch ex As Exception

Throw ex

Finally

con.Close()

con.Dispose()

End Try

End Function

End Class

"Alexander Shirshov" <alexander@omnitalented.com> wrote in message news:utDvhfYHFHA.4060@TK2MSFTNGP14.phx.gbl...
> Because of Finally block your method always return closed and disposed
> connection, not very useful... Remember, Finally blocks ALWAYS execute.
>
> You must return opened connection and let callers dispose it when they have
> finished working with it.
>
> HTH,
> Alexander Shirshov
>
>
>
> "ypul" <ypul@hotmail.com> wrote in message
> news:ezvnNaYHFHA.2132@TK2MSFTNGP14.phx.gbl...
>
>

Alexander Shirshov

2005-02-28, 8:58 am

Con variable points to the same connection object you created in
GetConnection, sure.

Right now you're doing everything correctly... almost. You should check that
connection was opened before closing it otherwise you'll get the exception
in your Finally block:

If conn.State = ConnectionState.Open
conn.Close()
End If

"ypul" <ypul@hotmail.com> wrote in message
news:uXbhboYHFHA.3912@TK2MSFTNGP10.phx.gbl...
thanks alex
I have a doubt ..
when I return a connection to the caller class will it create a copy of the
connection object or it will pass the same object ??

In the code below ...is the "Con" pointing to the same object of
getconnection...??..
if yes then i should dispose "con" in caller1 class ... and there is no
danger of undisposed object which I created in the "Connection" class, RIGHT
?
Public Class caller1
Public Function getAll() As DataTable
Dim dt As DataTable
Dim da As SqlDataAdapter
Dim dc As SqlCommand
Dim strsql As String
Dim con As SqlConnection
Try
' get the data from the dataLayer
dt = New DataTable()
con = Connection.getConnection

dc = New SqlCommand(strsql, con)
da = New SqlDataAdapter(dc)
da.Fill(dt)
Return dt
Catch ex As Exception
Throw ex
Finally
con.Close()
con.Dispose()
End Try
End Function

End Class
"Alexander Shirshov" <alexander@omnitalented.com> wrote in message
news:utDvhfYHFHA.4060@TK2MSFTNGP14.phx.gbl...
> Because of Finally block your method always return closed and disposed
> connection, not very useful... Remember, Finally blocks ALWAYS execute.
>
> You must return opened connection and let callers dispose it when they
> have
> finished working with it.
>
> HTH,
> Alexander Shirshov
>
>
>
> "ypul" <ypul@hotmail.com> wrote in message
> news:ezvnNaYHFHA.2132@TK2MSFTNGP14.phx.gbl...
>
>

ypul

2005-02-28, 4:00 pm

Thanks alex ...u were a good help ...
cheers
vips
"Alexander Shirshov" <alexander@omnitalented.com> wrote in message
news:uWfNaAZHFHA.3484@TK2MSFTNGP12.phx.gbl...
> Con variable points to the same connection object you created in
> GetConnection, sure.
>
> Right now you're doing everything correctly... almost. You should check
that
> connection was opened before closing it otherwise you'll get the exception
> in your Finally block:
>
> If conn.State = ConnectionState.Open
> conn.Close()
> End If
>
>
>
> "ypul" <ypul@hotmail.com> wrote in message
> news:uXbhboYHFHA.3912@TK2MSFTNGP10.phx.gbl...
> thanks alex
> I have a doubt ..
> when I return a connection to the caller class will it create a copy of
the
> connection object or it will pass the same object ??
>
> In the code below ...is the "Con" pointing to the same object of
> getconnection...??..
> if yes then i should dispose "con" in caller1 class ... and there is no
> danger of undisposed object which I created in the "Connection" class,
RIGHT
> ?
> Public Class caller1
> Public Function getAll() As DataTable
> Dim dt As DataTable
> Dim da As SqlDataAdapter
> Dim dc As SqlCommand
> Dim strsql As String
> Dim con As SqlConnection
> Try
> ' get the data from the dataLayer
> dt = New DataTable()
> con = Connection.getConnection
>
> dc = New SqlCommand(strsql, con)
> da = New SqlDataAdapter(dc)
> da.Fill(dt)
> Return dt
> Catch ex As Exception
> Throw ex
> Finally
> con.Close()
> con.Dispose()
> End Try
> End Function
>
> End Class
> "Alexander Shirshov" <alexander@omnitalented.com> wrote in message
> news:utDvhfYHFHA.4060@TK2MSFTNGP14.phx.gbl...
SqlConnection(ConfigurationSettings.AppSettings.Get("ConnectionString"))[color=darkred]
>

Sponsored Links