Re: Reusing Parameters

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Chris Hayes (cp.hayesATsbcglobal.net_at_nospam.nospam)
Date: 01/26/05 

Date: Tue, 25 Jan 2005 19:52:53 -0600

Hi Tom,

While the Garbage Collector is supposed to help clean up memory, I don't
fully trust it.

I always close my connections and dispose of my objects after I have used
them. My philosophy is to only instantiate an object when needed and keep it
in memory for only as long as it needed and then when it is not to Dispose
of it. Of course the Dispose method only marks an object for the Garbage
Collector to deal with, but at least it's marked and the Garbage Collector
doesn't have to figure it out.

I normally wrap my dataaccess code in a try catch finally statement and in
the finally I check my dataaccess objects, if they are instantiated (not
equal to null/nothing) then I check the connection state if it's a
connection object, if it's not closed, I close it then dispose of it.

Chris

Here's a simple example of some clean up code I use:
Try

'logic

Catch ex As Exception

'handle the error

Finally

If Not da Is Nothing Then da.Dispose()

If Not cmd Is Nothing Then cmd.Dispose()

If Not conn Is Nothing Then

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

conn.Dispose()

End If

da = Nothing

cmd = Nothing

conn = Nothing

End Try

"tshad" <tscheiderich@ftsolutions.com> wrote in message
news:ONRsawxAFHA.2196@TK2MSFTNGP14.phx.gbl...
> "Chris Hayes" <cp.hayesATsbcglobal.net@nospam.nospam> wrote in message
> news:%23PdW25oAFHA.3616@TK2MSFTNGP11.phx.gbl...
>> I've included a VB.NET code sample to illustrate.
>>
>> "tshad" <tscheiderich@ftsolutions.com> wrote in message
>> news:erXs%23MoAFHA.2624@TK2MSFTNGP11.phx.gbl...
>>> "Chris Hayes" <cp.hayesATsbcglobal.net@nospam.nospam> wrote in message
>>> news:%23w8uaDoAFHA.3988@TK2MSFTNGP11.phx.gbl...
>>>> Hi Tom,
>>>>
>>>> There are a couple of ways to to handle this...
>>>>
>>>> One is that you can reset the objCmd with objCmd = New
>>>> System.Data.SqlClient.SqlCommand() as the first line in the loop...this
>>>> will necessitate you resetting the Connection object for the command.
>>>>
>>>
>>> Will I need to close and reopen the connection here, since I am
>>> resetting the connection?
>>>
>>
>> You will need to assign the connection only:
>> objCommand.Connection = conn
>> As long as you instantiated the connection object as its own object, you
>> will not need to reopen the connection .
>
> When I close the the page, will it close the connection at this point if I
> haven't alrea done it?
>
>>
>>>> Or Two before you start adding parameters after you invoke the
>>>> objCmd.Parameters.Clear() method. The Clear Method does exactly what it
>>>> says it clears/deletes all parameters in the Parameters Collection.
>>>>
>>>> And yes you can put the Connection.Open() and Connection.Close() method
>>>> outside the loop.
>>>> Just objConn.Open() before your loop and invoke objConn.Close() after
>>>> the loop.
>>>>
>>>
>>> So each time I can do multiple objCmd.ExecuteNonQuery commands on the
>>> same connection, what about DataAdapters or ExecuteReaders which return
>>> data. Will it clear the old results for each execution or append the
>>> results?
>>>
>>
>> From my experiences:
>> If you are using the DataAdapter to "Fill" a DataTable or a DataSet, it
>> will append to the DataTable or DataSet. If you are doing .ExecuteReader
>> it will not append as only one DataReader can be assigned at a time from
>> the .ExecuteReader method.
>>
>> I hope this helps,
>>
>> Chris
>>
>> CODE SAMPLE:
>> Private Sub btnAppend_Click(ByVal sender As System.Object, ByVal e As
>> System.EventArgs) Handles btnAppend.Click
>>
>> Dim da As SqlClient.SqlDataAdapter
>>
>> Dim conn As SqlClient.SqlConnection
>>
>> Dim cmd As SqlClient.SqlCommand
>>
>> Dim i As Int32
>>
>> Dim ds As DataSet
>>
>> ds = New DataSet
>>
>> da = New SqlClient.SqlDataAdapter
>>
>> conn = New SqlClient.SqlConnection("Server=(local);Initial
>> Catalog=Test;Integrated Security=SSPI;")
>>
>> conn.Open()
>>
>> For i = 1 To 5
>>
>> cmd = New SqlClient.SqlCommand("procAppend", conn)
>>
>> cmd.CommandType = CommandType.StoredProcedure
>>
>> cmd.Parameters.Add("@ID", SqlDbType.Int).Value = i
>>
>> da.SelectCommand = cmd
>>
>> da.Fill(ds)
>>
>> cmd.Dispose()
>>
>> Next
>>
>> conn.Close()
>>
>> conn.Dispose()
>>
>> datagrid2.DataSource = ds
>>
>> End Sub
>
> Thanks,
>
> Tom
>

Relevant Pages

  • Re: How to close connection?
    ... If you are going to dispose the connection, then that will cause it to be ... Dim myConn As New SqlConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: datareader - connection leaks in DB
    ... It may be enough to simply close/dispose the connection, ... > Dim conn As SqlConnection = New ... > Is it enough to dispose the connection or do I need to close the ... > datareader too? ...
    (microsoft.public.dotnet.framework.aspnet)
  • RE: Orphaned Oracle database sessions with OracleDataAdapter
    ... If you are implementing IDisposable, call Dispose on the connection, not ... > Dim cn As OracleConnection ... > Sub New ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Question about memory and DB connections
    ... Calling Close (or Dispose) on an open connection object closes the ... even if the garbage collector frees up the memory used by the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: OleDbCommand should be disposed? Holding conn reference...
    ... You do not need to dispose of a Connection object to return the ... But the OleDbCommand is not disposed after the ... Dim oDataTable As New DataTable ...
    (microsoft.public.dotnet.framework.adonet)