Managing SQLConnections

[Bijoy Naick]

Hello,

I am writing an application which uses SQL Server as the database.. Some
questions...

1. Just want to confirm that using SQLConnection is the best way connect the
db..

2. The application will contain multiple pages.. Each page will have to
connect to the db and perform some actions against it. So, on each page I
will have to declare variables for the connection, command, reader, sql
statement and connection string. I will also have to create the connection,
open it, create a new command object, run the query, then close the reader
and connections.

Does it make sense to build a "MyDBConnection" class which encapsulates all
of this?? So that when I create an instance of this class, it opens the
connection.. I can have a public function called RunQuery which accepts a
SQL text string as input and returns a reader. Then all I have to do is
create an object of this class on every page and invoke the RunQuery method?

Is this recommeded, advisable, doable? Any issues?

Thx.

Bijoy

 

[Kumar Reddi]

You answered your own questions. Yes its recommended , advisable and doable,
No issues.

 

[bruce barker]

in general you should not return a datareader, but rather a datatable or
dataset to prevent resource leaks and locking problems.

-- bruce (sqlwork.com)


| You answered your own questions. Yes its recommended , advisable and
doable,
| No issues.
|
| | > Hello,
| >
| > I am writing an application which uses SQL Server as the database.. Some
| > questions...
| >
| > 1. Just want to confirm that using SQLConnection is the best way connect
| the
| > db..
| >
| > 2. The application will contain multiple pages.. Each page will have to
| > connect to the db and perform some actions against it. So, on each page
I
| > will have to declare variables for the connection, command, reader, sql
| > statement and connection string. I will also have to create the
| connection,
| > open it, create a new command object, run the query, then close the
reader
| > and connections.
| >
| > Does it make sense to build a "MyDBConnection" class which encapsulates
| all
| > of this?? So that when I create an instance of this class, it opens the
| > connection.. I can have a public function called RunQuery which accepts
a
| > SQL text string as input and returns a reader. Then all I have to do is
| > create an object of this class on every page and invoke the RunQuery
| method?
| >
| > Is this recommeded, advisable, doable? Any issues?
| >
| > Thx.
| >
| > Bijoy
| >
| >
| >
| >
| >
|
|

 

[Kumar Reddi]

Hi Bruce,
Why you think returning datareader causes any leaks or locking problems? I
have used this kind of approach before, but never had problems? or Am I
missing something

 

[Guest]

MS DataAccess Application Block does all this work for you and more. It uses
all of the best practices so you can't go wrong. I encourage you to use it in
your apps

 

[Bijoy Naick]

I'e got it working but got me wondering about the resource leak issues..

My code is below.. To run the query, I have to declare a reader within the
RunQuery function. This reader is "returned" to the calling page.. When this
happens, I can no longer "close" this reader. Wouldn't that be a problem..

But what we can do is make the reader a function level variable.. taht way
it can be closed when the "Close" function is called. But would that mean I
cannot reuse the reader? Me getting confused now..

----
Public Function RunQuery(ByVal sql As String) As SqlDataReader

Dim myReader As SqlDataReader

Try

If sql <> "" Then

myCommand = New SqlCommand(sql, myConnection)

myReader = myCommand.ExecuteReader()

End If

Return myReader

Catch ex As Exception

Throw ex

End Try

End Function

----------------

On another page, I make the following calls -

Dim dbConn As New MyDBConnection

Dim myReader As SqlDataReader

dbConn.OpenConnection()

myReader = dbConn.RunQuery("SELECT * FROM organizations")

orgList.DataSource = myReader

orgList.DataBind()

myReader.Close()

 

[Kumar Reddi]

Yeah you can close the reader in the calling function. What do you mean you
can not reuse the reader. DataReader is a connected object and forward only,
once you start accessing, you can only go forward in the data and you can
not do anything with the function unless you close the data reader. So, do
not think datareader in terms of dataset. Everytime you want to use the
query, call that function, it opens the db connection, gets the data, you
access the data and close the reader and the next function call would do the
same. Dont worry about locking issue, I used this approach several times,
never found a problem

 

[Bijoy Naick]

I can resuse the reader as long as it has been closed, right.. For example,
this wont wont..

reader = myCommand.ExecuteReader(sql, myConn)
list.datasource=reader
list.databind()

reader = myCommand.ExecuteReader(sql2, myConn).

If i close the reader after the databind the above will work fine. So bottom
line, the reader has to be closed after the query is run.. Do you agree?

So the question is, can u close teh reader AFTER the function does a
'RETURN'.. Can it?

BTW: Thanks for discussing this.. Appreciate it.

Bijoy

 

[Kumar Reddi]

Yeah you need to close the reader after you are done with it, before you use
the function that returns the reader again. Yes, you can close the reader in
the calling function
this reader is created in another page, but you should close the reader in
your function before you use that source function in another call

 

[Bijoy Naick]

Took a look at this.. looks real good.. thx

MS DataAccess Application Block does all this work for you and more. It uses
all of the best practices so you can't go wrong. I encourage you to use it in
your apps

 

[Scott Allen]

If you are returning data readers like this it is also a good idea to
use CommandBehavior.CloseConnection as a parameter to ExecuteReader.
By setting this the underlying connection for the reader will close
when the data reader closes. Very important behavior to have for
effective connection pooling and management.

 

[Scott Allen]

Yeah you need to close the reader after you are done with it, before you use
the function that returns the reader again.

There is nothing wrong with calling the method again with a previous
reader still open. This scenario happens all the time in asp.net apps.