473,322 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Managing SQLConnections

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

Nov 18 '05 #1
11 1156
You answered your own questions. Yes its recommended , advisable and doable,
No issues.

"Bijoy Naick" <b_*****@yahoo.ca> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
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


Nov 18 '05 #2
in general you should not return a datareader, but rather a datatable or
dataset to prevent resource leaks and locking problems.

-- bruce (sqlwork.com)
"Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:Os**************@TK2MSFTNGP14.phx.gbl...
| You answered your own questions. Yes its recommended , advisable and
doable,
| No issues.
|
| "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
| news:%2****************@TK2MSFTNGP11.phx.gbl...
| > 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
| >
| >
| >
| >
| >
|
|
Nov 18 '05 #3
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
"bruce barker" <no***********@safeco.com> wrote in message
news:uH**************@TK2MSFTNGP14.phx.gbl...
in general you should not return a datareader, but rather a datatable or
dataset to prevent resource leaks and locking problems.

-- bruce (sqlwork.com)
"Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:Os**************@TK2MSFTNGP14.phx.gbl...
| You answered your own questions. Yes its recommended , advisable and
doable,
| No issues.
|
| "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
| news:%2****************@TK2MSFTNGP11.phx.gbl...
| > 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
| >
| >
| >
| >
| >
|
|

Nov 18 '05 #4
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" wrote:
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


Nov 18 '05 #5
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...
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
"bruce barker" <no***********@safeco.com> wrote in message
news:uH**************@TK2MSFTNGP14.phx.gbl...
in general you should not return a datareader, but rather a datatable or
dataset to prevent resource leaks and locking problems.

-- bruce (sqlwork.com)
"Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:Os**************@TK2MSFTNGP14.phx.gbl...
| You answered your own questions. Yes its recommended , advisable and
doable,
| No issues.
|
| "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
| news:%2****************@TK2MSFTNGP11.phx.gbl...
| > 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
| >
| >
| >
| >
| >
|
|


Nov 18 '05 #6
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" <b_*****@yahoo.ca> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...
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
"bruce barker" <no***********@safeco.com> wrote in message
news:uH**************@TK2MSFTNGP14.phx.gbl...
in general you should not return a datareader, but rather a datatable or dataset to prevent resource leaks and locking problems.

-- bruce (sqlwork.com)
"Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:Os**************@TK2MSFTNGP14.phx.gbl...
| You answered your own questions. Yes its recommended , advisable and
doable,
| No issues.
|
| "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
| news:%2****************@TK2MSFTNGP11.phx.gbl...
| > 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
| >
| >
| >
| >
| >
|
|



Nov 18 '05 #7
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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" <b_*****@yahoo.ca> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...
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
"bruce barker" <no***********@safeco.com> wrote in message
news:uH**************@TK2MSFTNGP14.phx.gbl...
> in general you should not return a datareader, but rather a datatable or > dataset to prevent resource leaks and locking problems.
>
> -- bruce (sqlwork.com)
>
>
> "Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
> news:Os**************@TK2MSFTNGP14.phx.gbl...
> | You answered your own questions. Yes its recommended , advisable
and > doable,
> | No issues.
> |
> | "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
> | news:%2****************@TK2MSFTNGP11.phx.gbl...
> | > 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
> | >
> | >
> | >
> | >
> | >
> |
> |
>
>



Nov 18 '05 #8
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
myReader = dbConn.RunQuery("SELECT * FROM organizations") 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" <b_*****@yahoo.ca> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
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" <b_*****@yahoo.ca> wrote in message
news:%2******************@TK2MSFTNGP09.phx.gbl...
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:eX**************@TK2MSFTNGP12.phx.gbl...
> 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
> "bruce barker" <no***********@safeco.com> wrote in message
> news:uH**************@TK2MSFTNGP14.phx.gbl...
> > in general you should not return a datareader, but rather a datatable
or
> > dataset to prevent resource leaks and locking problems.
> >
> > -- bruce (sqlwork.com)
> >
> >
> > "Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
> > news:Os**************@TK2MSFTNGP14.phx.gbl...
> > | You answered your own questions. Yes its recommended , advisable

and > > doable,
> > | No issues.
> > |
> > | "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
> > | news:%2****************@TK2MSFTNGP11.phx.gbl...
> > | > 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
> > | >
> > | >
> > | >
> > | >
> > | >
> > |
> > |
> >
> >
>
>



Nov 18 '05 #9
Took a look at this.. looks real good.. thx

"Tampa .NET Koder" <Ta***********@discussions.microsoft.com> wrote in
message news:A9**********************************@microsof t.com...
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" wrote:
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


Nov 18 '05 #10
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
http://www.OdeToCode.com/blogs/scott/

On Fri, 12 Nov 2004 14:41:08 -0500, "Kumar Reddi"
<Ku********@REMOVETHIS.gmail.com> wrote:
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
myReader = dbConn.RunQuery("SELECT * FROM organizations")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" <b_*****@yahoo.ca> wrote in message
news:%2****************@TK2MSFTNGP15.phx.gbl...
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" <Ku********@REMOVETHIS.gmail.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
> 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 severaltimes, > never found a problem
>
> "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
> news:%2******************@TK2MSFTNGP09.phx.gbl...
> > I'e got it working but got me wondering about the resource leakissues.. > >
> > 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" <Ku********@REMOVETHIS.gmail.com> wrote in message
> > news:eX**************@TK2MSFTNGP12.phx.gbl...
> > > 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? orAm
I
> > > missing something
> > > "bruce barker" <no***********@safeco.com> wrote in message
> > > news:uH**************@TK2MSFTNGP14.phx.gbl...
> > > > in general you should not return a datareader, but rather a

datatable
> or
> > > > dataset to prevent resource leaks and locking problems.
> > > >
> > > > -- bruce (sqlwork.com)
> > > >
> > > >
> > > > "Kumar Reddi" <Ku********@REMOVETHIS.gmail.com> wrote in message
> > > > news:Os**************@TK2MSFTNGP14.phx.gbl...
> > > > | You answered your own questions. Yes its recommended , advisable

and
> > > > doable,
> > > > | No issues.
> > > > |
> > > > | "Bijoy Naick" <b_*****@yahoo.ca> wrote in message
> > > > | news:%2****************@TK2MSFTNGP11.phx.gbl...
> > > > | > 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 pagewill > 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 createthe > > > > | connection,
> > > > | > open it, create a new command object, run the query, thenclose > 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 RunQuerywhich > > > 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
> > > > | >
> > > > | >
> > > > | >
> > > > | >
> > > > | >
> > > > |
> > > > |
> > > >
> > > >
> > >
> > >
> >
> >
>
>



Nov 18 '05 #11
On Fri, 12 Nov 2004 14:41:08 -0500, "Kumar Reddi"
<Ku********@REMOVETHIS.gmail.com> wrote:
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.

--
Scott
http://www.OdeToCode.com/blogs/scott/
Nov 18 '05 #12

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Andrei D. | last post by:
Hello Python newsgroup, In the process of developing a big ssh wrapper for sending commands to multiple hosts over the last few months, I (almost accidentally, considering I'm really just an...
1
by: Saradhi | last post by:
I am hosing website using ASP on windows2003 standard server in IIS 6.0 sessions are getting expired immediately. Could any one help me in managing sessions in iis 6.0 and ASP?
1
by: Edward Stevens | last post by:
The IDE Start Page in .Net 2003 contains a list of the projects you have recently worked on. Clicking on any of these opens that particular project. Fine. Unfortunately, one of my projects has...
0
by: Eliezer Figueroa | last post by:
Managing Multiple Excel incoming files? I have this situation. I have a client which have several locations they work primary with excel forms and they are thinking in doing reports with them....
1
by: Cliff Williams | last post by:
How are people managing multiple, interdependent projects in the same solution? If I have to rebuild my solution file one more time, I think my head is going to spin around and pop off. I've...
3
by: Torsten Wiebesiek | last post by:
Hi, I am currently working on a computer vision system. At the moment I'm adding support for firewire cameras. Since there is only one firewire system on a computer, I have writen a firewire...
21
by: dub | last post by:
Hello web folks... I've been desigining web pages for 13 years using my trusty text editor (UltraEdit) and in depth knowledge of HTML. I'm truly a text editor ninja at this point. I am frequently...
7
by: tgh003 | last post by:
I would be interested to hear how others are managing their javascript (.js) files from the original code vs the obfuscated version they publish to their site/webapp. I currently manage 2 files,...
4
by: Ben | last post by:
Hi I have a question concerning SqlConnections and when they should be opened and closed. Right now I have a database utilities class. I instantiate it when in page_load and dispose it in...
2
by: fariba123 | last post by:
i have designed an employee information site. there is an option to generate pay slip. how can i show the employee related data based on the drop down list. i have found code example for...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.