Datareader issue

[JAKSupport]

Creating a db class .. was wondering why this code fails when I try to .executereader

 Public Function UseSQLDBExecuteReader() As SqlDataReader
        Dim lRead As SqlDataReader
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetSQLDBCommand to initialize SQLCommand object!")
            Exit Function
        End If
        If mSQLDBConnector Is Nothing  Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        else
            If Not IsSQLDBOpen()  then
                mSQLDBConnector.Open()
            end if
        End If
        Try
            With mSQLDBCmd
                .Connection = mSQLDBConnector
                lRead = .ExecuteReader()
            End With
            Return lRead
        Catch ex As Exception
            MsgBox("An error occurred:" & Environment.NewLine() _
                            & ex.ToString() & Environment.NewLine() _
                            & ex.StackTrace())
        Finally
            lRead.Close()
            lRead = Nothing
            mSQLDBConnector.Close()
            mSQLDBConnector = Nothing
        End Try
    End Function

mSQLDBConnector is an sql object, I debugged it and it goes through and connects to an sql connection object and it even opens the connection.
Then It goes to the with statement sets the connection for that, and right when it hits the execute reader it fails stating that the reader needs an available and open connection, which I thought it had both considering I debugged it?

The heck ?

[kleinma]

what is mSQLDBCmd's CommandText?

[JAKSupport]

what is mSQLDBCmd's CommandText?

Stored procedure type, however I think I found the error:

Originally I had done this:

  Public Sub AddSQLDBCmdParameter(ByVal pName As String, ByVal pType As SqlDbType, ByVal pVal As Object)
        If mSQLDBCmd Is Nothing Then
            Throw New Exception("Must use SetDBCommand to initialize SQLCommand object!")
            Exit Sub
        End If
        If mSQLDBConnector Is Nothing Then
            mSQLDBConnector = New SqlConnection(mSQLDBConnString)
            mSQLDBConnector.Open()
        Else
            If Not IsSQLDBOpen() Then
                mSQLDBConnector.Open()
            End If
        End If
        Try
            With mSQLDBCmd
                .Parameters.Add(pName, pType).Value = pVal
            End With
        Catch ex As Exception
            MsgBox("An error occurred:" & Environment.NewLine() _
                                & ex.ToString() & Environment.NewLine() _
                            & ex.StackTrace())
        Finally
              mSQLDBConnector.Close
              mSQLDBConnector = nothing
        End Try
    End Sub

I had to get rid of the finally portion:
Finally
mSQLDBConnector.Close
mSQLDBConnector = nothing

I guess even after I recreated the connection in the UseSQLDBExecuteReader and assigned the command object the new connection it did not like that? I wonder why, as soon as I got rid of the finally portion it worked fine...I thought that If I recreated the connection and set the command objects connection property that I Could reuse that connection ?

Im a bit confused, it works, however I would of thought I could close my connection and reopen it so that I dont lose any performance.

[kleinma]

make sure you call .dispose on both the command and connection when you are done with them.

[JAKSupport]

I think close will handle the disposing of any resource for the connection class.

[kleinma]

No close simply closes the open DB connection....

Dispose explicitly releases the unmanaged resources of the object.

That is why some objects have a dispose method, and some don't.

Any object that implements IDisposable, should have its dispose method called when it is no longer in use.

[JAKSupport]

You're right calling dispose can actually clean the resources then close the connection, one can have both though

Thanks

[JAKSupport]

No close simply closes the open DB connection....

Dispose explicitly releases the unmanaged resources of the object.

That is why some objects have a dispose method, and some don't.

Any object that implements IDisposable, should have its dispose method called when it is no longer in use.

Finally found my reason in this case not to use dispose:

Go here:

http://www.simple-talk.com/dotnet/.n...-access-layer/

Look at the second comment

"RE: How are you closing the connection?
Posted by: Damon
Message: Hey gafoor,

It's interesting that you mention that because it actually reveals a problem with the code that I'm suprised nobody has caught until now!

If you look in the DataServiceBase class, you will see a section of code that looks like this:

if (_isOwner)
{
cnx.Dispose(); //Implicitly calls cnx.Close()
}

This is what closes the connection. Unfortunately, it also removes the connection from the connection pool, which hurts performance. Luckily, it takes all of about two seconds to fix. The code should look like this:

if (_isOwner)
{
cnx.Close();
}

There are two places in the DataServiceBase class where you will need to fix this issue.

My appologies! "

Thats precisely why you hsould not always use the dispose method it actually slows the code down, had to find a good reference, I know I read it somewhere before but I needed some back up.

[kleinma]

Of course you shouldn't call dispose unless you are DONE with the object, which is what I stated originally...

[JAKSupport]

Of course you shouldn't call dispose unless you are DONE with the object, which is what I stated originally...

Not to be picky but you said:

"make sure you call .dispose on both the command and connection when you are done with them."

That is not the case, since dispose actually gets rid of the connection pooling itself, which degrades performance.

Of course you shouldn't call dispose unless you are DONE with the object, which is what I stated originally...

In that case Dispose should be called on what object ?

[kleinma]

yes, exactly. I said when YOU were done with them. Not when a subroutine or function ends or anything like that.. but when you are DONE with them. If you plan to need them again, then don't dispose of them. If you are closing the app, or doing something that no longer requires the objects anymore, then you should dispose of them.

[JAKSupport]

I think you have to distinguish between the actual objects in a function or the actual main object, in my case the abstract datatype that I created. If you dispose of an object that you may be using a connection to you lose the connection pool that it is associated with. In addition, closing a connection does not necessairly mean disposing of it, it means the closed connection no longer sits in the pool of connections that are available. Disposing the entire object gets rid of the entire pool of connections to the database.

Does that make a bit of sense? Maybe it helps to show code ?

[kleinma]

when it comes to a connection object, the only difference between dispose and close is that the connection string of the connection object is nulled out.

I was really just speaking in general terms that when you are totally done using an object, and that object implements idisposable, that you should call the dispose method on it.

[maged]

that you should call the dispose method on it.

i dont agree about this sentence regarding the connection object,
on a database running application closing a connection returns the connection to the pool of ready connections with the same signature (Exact connection string) , so next time when u try to open connection with the same signature ( connection string ) . it will get u the connection from the ready pool and will save time. disposing the connection inside the program will waste time twice. once to explictly dispose the object and Garbage collect it and second when u try to open a new connection later from scratch.

that is my opinion, but you have better experience anyway

rgds