dotnet datatools: Thus far, my experience in connecting to databases in my applications
has been somewhat limited. Loads have been low, so performance hasn't
really been an issue. But, I'd like to do things "the right way"
anyway, even if it's not immediately necessary.

To that end, I'm wondering if the method I use to retrieve data via a
SELECT statement is the best way of doing things. For my current web
applications, all data is retrieved from a seperate web service which
connects to the database. Ultimately, every time data is retrieved, it
boils down to the following function:

mySqlConnection = new MySqlConnection(stringDatabaseConnection);
mySqlDataAdapter = new MySqlDataAdapter(stringSelect, mySqlConnection);
dataSetSQL = new DataSet();
mySqlDataAdapter.Fill(dataSetSQL);
return dataSetSQL;

This function is called by pretty much every page in the web
application, often multiple times in a single page load. Is what I'm
doing here causing too much overhead? I notice that each call is its
own new connection to the database. Is there a better way that has just
one running connection that handles all the queries?

If someone could advise me on this or point me to some good tutorials
(code samples are best) I'd really appreciate it. Thanks.


Regards,
David P. Donahue

Hello David,

The things you have to draw an attention are caching and connection pool.
You should not connect each time to DB (moreover "multiple times in a single
page load") to get data, use cache
to keep selected data. Only if data changes - you need to update your cache
from DB.
Second is connection pool - it save time to create new connection to DB

PS: if u use SQLServer see at notify services. SQL can notify you when data
in tables is changed, without constant refreshing state of tables

DD> To that end, I'm wondering if the method I use to retrieve data via
DD> a SELECT statement is the best way of doing things. For my current
DD> web applications, all data is retrieved from a seperate web service
DD> which connects to the database. Ultimately, every time data is
DD> retrieved, it boils down to the following function:
DD>
DD> mySqlConnection = new MySqlConnection(stringDatabaseConnection);
DD> mySqlDataAdapter = new MySqlDataAdapter(stringSelect,
DD> mySqlConnection);
DD> dataSetSQL = new DataSet();
DD> mySqlDataAdapter.Fill(dataSetSQL);
DD> return dataSetSQL;
DD> This function is called by pretty much every page in the web
DD> application, often multiple times in a single page load. Is what
DD> I'm doing here causing too much overhead? I notice that each call
DD> is its own new connection to the database. Is there a better way
DD> that has just one running connection that handles all the queries?
DD>
DD> If someone could advise me on this or point me to some good
DD> tutorials (code samples are best) I'd really appreciate it. Thanks.
DD>
DD> Regards,
DD> David P. Donahue
DD> ddonahue@ccs.neu.edu
---
WBR,
Michael Nemtsev :: blog: http://spaces.msn.com/laflour

"At times one remains faithful to a cause only because its opponents do not
cease to be insipid." (c) Friedrich Nietzsche

I agree, caching is great, but only for rarely changing data.
Generally this is a lookup list like states, countries, etc... Then
again you can cache other non-critical data for short periods, such as
15 minutes. If you do that make sure you put a notice on your page
that data may be delayed up to 15 minutes.

The more effective route would be to use connection pooling or object
pooling. Enterprise services has methods for object pooling:

http://support.microsoft.com/default.aspx?scid=kb;en-us;317336

Also I've created an open source component that includes object pooling
along with other great data access features like database abstraction
and database entity mapping.

See a full announcement here:
http://groups.google.com/group/microsoft.public.dotnet.datatools/browse_frm/thread/0605fb3028d5aa37?hl=en

You can get the component here:
http://sourceforge.net/projects/xqs-data/

The help file is here:
http://www.xquisoft.com/xqsdn/documentation/index.html
See XQuiSoft.Data.DataManager, and XQuiSoft.Data.IDataFactory
or
http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.DataManager.html
http://www.xquisoft.com/xqsdn/documentation/XQuiSoft.Data.IDataFactory.html


Michael Lang
XQuiSoft LLC
http://www.xquisoft.com/