how to know if to close sqlreader

21 May 2009 3:07 PM

cj2

if I'm instigating a sqlreader like the line below and it throws and
exception, how do I know if the reader was instigated or not?

MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
account = '" & mact & "'", MySqlConnection).ExecuteReader

If it was I need instigated it needs to be closed. If it wasn't
instigated then trying to do anything with it throws another exception.

21 May 2009 4:32 PM

Armin Zingler

cj2 wrote:

> if I'm instigating a sqlreader like the line below and it throws and
> exception, how do I know if the reader was instigated or not?
>
> MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
> account = '" & mact & "'", MySqlConnection).ExecuteReader
>
> If it was I need instigated it needs to be closed. If it wasn't
> instigated then trying to do anything with it throws another
> exception.

If 'ExecuteReader' throws an exception, there is no SqlReader. Two things
I'd change: 1st, separate the New statement from the ExecuteReader call in
order to know where the exception happens. 2nd, use the SqlCommands
parameters property.

Armin

21 May 2009 4:43 PM

Ty

On May 21, 11:07 am, cj2 <c...@nospam.nospam> wrote:

> if I'm instigating a sqlreader like the line below and it throws and
> exception, how do I know if the reader was instigated or not?
>
> MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
> account = '" & mact & "'", MySqlConnection).ExecuteReader
>
> If it was I need instigated it needs to be closed. If it wasn't
> instigated then trying to do anything with it throws another exception.

I have always set mine up like this and have never had any issues.

Dim oReader As SqlDataReader
Dim oConnection As SqlConnection
Dim strSQL As String

'set up the connection and the command...
                oConnection = New SqlConnection("Data Source=.
\SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
Security=True;Connect Timeout=30;User Instance=true")

                strSQL = "SELECT * FROM COMPUTERS"
                Dim oCommand As SqlCommand = New SqlCommand(strSQL,
oConnection)

                'open the connection, and use the reader to populate
the combobox
                oConnection.Open()
                oReader = oCommand.ExecuteReader()

                Do While oReader.Read()

                Loop

        'Clean up
        oConnection.Close()
        oCommand = Nothing
        oReader = Nothing
        oConnection = Nothing

21 May 2009 7:49 PM

dunawayc

On May 21, 11:43 am, Ty <tbar***@lewistownhospital.org> wrote:
Show quoteHide quote

> On May 21, 11:07 am, cj2 <c...@nospam.nospam> wrote:
>
> > if I'm instigating a sqlreader like the line below and it throws and
> > exception, how do I know if the reader was instigated or not?
>
> > MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
> > account = '" & mact & "'", MySqlConnection).ExecuteReader
>
> > If it was I need instigated it needs to be closed. If it wasn't
> > instigated then trying to do anything with it throws another exception.
>
> I have always set mine up like this and have never had any issues.
>
> Dim oReader As SqlDataReader
> Dim oConnection As SqlConnection
> Dim strSQL As String
>
> 'set up the connection and the command...
>                 oConnection = New SqlConnection("Data Source=.
> \SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
> Security=True;Connect Timeout=30;User Instance=true")
>
>                 strSQL = "SELECT * FROM COMPUTERS"
>                 Dim oCommand As SqlCommand = New SqlCommand(strSQL,
> oConnection)
>
>                 'open the connection, and use the reader to populate
> the combobox
>                 oConnection.Open()
>                 oReader = oCommand.ExecuteReader()
>
>                 Do While oReader.Read()
>
>                 Loop
>
>         'Clean up
>         oConnection.Close()
>         oCommand = Nothing
>         oReader = Nothing
>         oConnection = Nothing

Setting your objects to Nothing when they're going out of scope does
not help in cleaning them up and in fact, may hinder the garbage
collector.

In addition, your SqlReader objects need to be disposed when you are
finish with them so the Using statement is ideal for this sort of
thing:

Using oConnection As new SqlConnection(...)
Using oReader As New SqlReader(...)
End Using
End Using

This will ensure that the items that need to be disposed will be.

Chris

21 May 2009 8:11 PM

cj2

I don't think you can use sql reader like that. It gives me
sqldatareader has no constructors

dunaw***@gmail.com wrote:
Show quoteHide quote

> On May 21, 11:43 am, Ty <tbar***@lewistownhospital.org> wrote:
>> On May 21, 11:07 am, cj2 <c...@nospam.nospam> wrote:
>>
>>> if I'm instigating a sqlreader like the line below and it throws and
>>> exception, how do I know if the reader was instigated or not?
>>> MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
>>> account = '" & mact & "'", MySqlConnection).ExecuteReader
>>> If it was I need instigated it needs to be closed. If it wasn't
>>> instigated then trying to do anything with it throws another exception.
>> I have always set mine up like this and have never had any issues.
>>
>> Dim oReader As SqlDataReader
>> Dim oConnection As SqlConnection
>> Dim strSQL As String
>>
>> 'set up the connection and the command...
>>                 oConnection = New SqlConnection("Data Source=.
>> \SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
>> Security=True;Connect Timeout=30;User Instance=true")
>>
>>                 strSQL = "SELECT * FROM COMPUTERS"
>>                 Dim oCommand As SqlCommand = New SqlCommand(strSQL,
>> oConnection)
>>
>>                 'open the connection, and use the reader to populate
>> the combobox
>>                 oConnection.Open()
>>                 oReader = oCommand.ExecuteReader()
>>
>>                 Do While oReader.Read()
>>
>>                 Loop
>>
>>         'Clean up
>>         oConnection.Close()
>>         oCommand = Nothing
>>         oReader = Nothing
>>         oConnection = Nothing
>
> Setting your objects to Nothing when they're going out of scope does
> not help in cleaning them up and in fact, may hinder the garbage
> collector.
>
> In addition, your SqlReader objects need to be disposed when you are
> finish with them so the Using statement is ideal for this sort of
> thing:
>
> Using oConnection As new SqlConnection(...)
>    Using oReader As New SqlReader(...)
>    End Using
> End Using
>
> This will ensure that the items that need to be disposed will be.
>
> Chris

21 May 2009 8:28 PM

Cor Ligthert[MVP]

I can assure you: it does,

Chris is for sure not a newbie and like Chris wrote: it is a better way.

(Although the AdoNet group has included in the Close of the datareader the
Dispose of the unmanaged resources and vice vers)

Cor

Show quoteHide quote

"cj2" <cj2@nospam.nospam> wrote in message
news:enYWWBl2JHA.140@TK2MSFTNGP03.phx.gbl...
>I don't think you can use sql reader like that. It gives me sqldatareader
>has no constructors
>
>
> dunaw***@gmail.com wrote:
>> On May 21, 11:43 am, Ty <tbar***@lewistownhospital.org> wrote:
>>> On May 21, 11:07 am, cj2 <c...@nospam.nospam> wrote:
>>>
>>>> if I'm instigating a sqlreader like the line below and it throws and
>>>> exception, how do I know if the reader was instigated or not?
>>>> MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
>>>> account = '" & mact & "'", MySqlConnection).ExecuteReader
>>>> If it was I need instigated it needs to be closed. If it wasn't
>>>> instigated then trying to do anything with it throws another exception.
>>> I have always set mine up like this and have never had any issues.
>>>
>>> Dim oReader As SqlDataReader
>>> Dim oConnection As SqlConnection
>>> Dim strSQL As String
>>>
>>> 'set up the connection and the command...
>>>                 oConnection = New SqlConnection("Data Source=.
>>> \SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
>>> Security=True;Connect Timeout=30;User Instance=true")
>>>
>>>                 strSQL = "SELECT * FROM COMPUTERS"
>>>                 Dim oCommand As SqlCommand = New SqlCommand(strSQL,
>>> oConnection)
>>>
>>>                 'open the connection, and use the reader to populate
>>> the combobox
>>>                 oConnection.Open()
>>>                 oReader = oCommand.ExecuteReader()
>>>
>>>                 Do While oReader.Read()
>>>
>>>                 Loop
>>>
>>>         'Clean up
>>>         oConnection.Close()
>>>         oCommand = Nothing
>>>         oReader = Nothing
>>>         oConnection = Nothing
>>
>> Setting your objects to Nothing when they're going out of scope does
>> not help in cleaning them up and in fact, may hinder the garbage
>> collector.
>>
>> In addition, your SqlReader objects need to be disposed when you are
>> finish with them so the Using statement is ideal for this sort of
>> thing:
>>
>> Using oConnection As new SqlConnection(...)
>>    Using oReader As New SqlReader(...)
>>    End Using
>> End Using
>>
>> This will ensure that the items that need to be disposed will be.
>>
>> Chris

21 May 2009 9:08 PM

dunawayc

On May 21, 3:11 pm, cj2 <c...@nospam.nospam> wrote:
Show quoteHide quote

> I don't think you can use sql reader like that. It gives me
> sqldatareader has no constructors
>
> dunaw***@gmail.com wrote:
> > On May 21, 11:43 am, Ty <tbar***@lewistownhospital.org> wrote:
> >> On May 21, 11:07 am, cj2 <c...@nospam.nospam> wrote:
>
> >>> if I'm instigating a sqlreader like the line below and it throws and
> >>> exception, how do I know if the reader was instigated or not?
> >>> MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
> >>> account = '" & mact & "'", MySqlConnection).ExecuteReader
> >>> If it was I need instigated it needs to be closed. If it wasn't
> >>> instigated then trying to do anything with it throws another exception.
> >> I have always set mine up like this and have never had any issues.
>
> >> Dim oReader As SqlDataReader
> >> Dim oConnection As SqlConnection
> >> Dim strSQL As String
>
> >> 'set up the connection and the command...
> >>                 oConnection = New SqlConnection("Data Source=.
> >> \SQLEXPRESS;AttachDbFilename=YOURDB LOCATION HERE.mdf;Integrated
> >> Security=True;Connect Timeout=30;User Instance=true")
>
> >>                 strSQL = "SELECT * FROM COMPUTERS"
> >>                 Dim oCommand As SqlCommand = New SqlCommand(strSQL,
> >> oConnection)
>
> >>                 'open the connection, and use the reader to populate
> >> the combobox
> >>                 oConnection.Open()
> >>                 oReader = oCommand.ExecuteReader()
>
> >>                 Do While oReader.Read()
>
> >>                 Loop
>
> >>         'Clean up
> >>         oConnection.Close()
> >>         oCommand = Nothing
> >>         oReader = Nothing
> >>         oConnection = Nothing
>
> > Setting your objects to Nothing when they're going out of scope does
> > not help in cleaning them up and in fact, may hinder the garbage
> > collector.
>
> > In addition, your SqlReader objects need to be disposed when you are
> > finish with them so the Using statement is ideal for this sort of
> > thing:
>
> > Using oConnection As new SqlConnection(...)
> >    Using oReader As New SqlReader(...)
> >    End Using
> > End Using
>
> > This will ensure that the items that need to be disposed will be.
>
> > Chris

You're right, my apologies. I was emphasizing the use of the Using
statement.

It would be something like:

Using oReader As SqlReader = oCommand.ExecuteReader(...)
End Using

Sorry about that.

Chris

21 May 2009 8:13 PM

cj2

I got it figured out. I put in a finally statement

If Not MySqlReader Is Nothing Then MySqlReader.Close()

I found finally executes even if in the catch statement I have return.
I was a bit surprised of that. And is nothing works for determining if
the reader has been instigated yet.

cj2 wrote:
Show quoteHide quote

> if I'm instigating a sqlreader like the line below and it throws and
> exception, how do I know if the reader was instigated or not?
>
> MySqlReader = New SqlClient.SqlCommand("select * from recentcust where
> account = '" & mact & "'", MySqlConnection).ExecuteReader
>
> If it was I need instigated it needs to be closed. If it wasn't
> instigated then trying to do anything with it throws another exception.