Hey gang, quick question. Given an app where many classes will be acessing
a database, what is the best way to go?

a. A function that returns a connection string to each class, and lets them
do their own data access.
b. A function that returns an open SqlConnection.
c. A function that returns a SqlDataReader, after passing it a sproc and
parameters.
d. Something else.

Thanks!

Hii A,
Quick thought,
A class that returns the data or dataset that is needed.
Cor

Beware of data readers; they have a nasty habit of causing numerous
orphaned connections on database servers because of the way they carry an
open connection with them. You must call ExecuteReader with the
CommandBehavior.CloseConnection parameter to ensure that, once the
DataReader is closed, the connection it uses is closed. AND you must
remember to specifically call the Close method on the DataReader.

I don't really like the idea of a "connection factory" method that feeds
them out to various routines. I think that connections should be opened and
closed within the routines that access the data.

If you're careful, returning DataReaders will work but remember the caveat
that they carry with them an open connection, even if you called Close on
the connection you used to open them.

Steven Bras, MCSD
Microsoft Developer Support/Data Access Technologies

This posting is provided "AS IS" with no warranties, and confers no rights.

Microsoft Security Announcement: Have you installed the patch for Microsoft
Security Bulletin MS03-026?* If not Microsoft strongly advises you to
review the information at the following link regarding Microsoft Security
Bulletin MS03-026
http://www.microsoft.com/security/se...s/ms03-026.asp and/or to
visit Windows Update at http://windowsupdate.microsoft.com to install the
patch. Running the SCAN program from the Windows Update site will help to
insure you are current with all security patches, not just MS03-026.

IMHO:

a - does not truly separate data stores from your application. Your data
store is tightly coupled with your web app, which makes migration a PIA
later on.
b - passing around connections is dangerous business. they eventually get
hard to keep up with
c - this is OK, except that the datareader keeps the conection open until
youre done with your read. Its better to get yoru data as a disconected
strongly typed dataset, so the connection is closed as soon as it can.

If you create your classes where each constructor passes in a
ConnectionString as an argument, you can take advantage of Connection
Pooling, and you are a step closer to keeping your app and data access
loosely coupled (all you have to do is change a connection string in a
config file, for example, to move it from one Sql Server to another).

Check out the Microsoft Data Application Blocks. They are a set of classes
that take care of a lot of this for you. I thought it was overkill for my
app, so I wrote a mini-one, but its a great blueprint to start with.

http://msdn.microsoft.com/library/de...ml/daab-rm.asp


"a" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
> Hey gang, quick question. Given an app where many classes will be
acessing
> a database, what is the best way to go?
>
> a. A function that returns a connection string to each class, and lets
them
> do their own data access.
> b. A function that returns an open SqlConnection.
> c. A function that returns a SqlDataReader, after passing it a sproc and
> parameters.
> d. Something else.
>
> Thanks!
>
>

steven:

thanks for elaborating on the issues. i left out a bunch fo important stuff

"Steven Bras [MS]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Beware of data readers; they have a nasty habit of causing numerous
> orphaned connections on database servers because of the way they carry an
> open connection with them. You must call ExecuteReader with the
> CommandBehavior.CloseConnection parameter to ensure that, once the
> DataReader is closed, the connection it uses is closed. AND you must
> remember to specifically call the Close method on the DataReader.
>
> I don't really like the idea of a "connection factory" method that feeds
> them out to various routines. I think that connections should be opened
and
> closed within the routines that access the data.
>
> If you're careful, returning DataReaders will work but remember the caveat
> that they carry with them an open connection, even if you called Close on
> the connection you used to open them.
>
> Steven Bras, MCSD
> Microsoft Developer Support/Data Access Technologies
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> Microsoft Security Announcement: Have you installed the patch for
Microsoft
> Security Bulletin MS03-026? If not Microsoft strongly advises you to
> review the information at the following link regarding Microsoft Security
> Bulletin MS03-026
> http://www.microsoft.com/security/se...s/ms03-026.asp and/or
to
> visit Windows Update at http://windowsupdate.microsoft.com to install the
> patch. Running the SCAN program from the Windows Update site will help to
> insure you are current with all security patches, not just MS03-026.
>