AccessDataSource vs SqlDataSource Questions

[msch-prv]

I use an Access db in my application. So far I have been exclusively
working wtih the AccessDataSource controls to hook up to the db. I
know that Access db connections can also be established with
SqlDataSource.

What are the trade-offs between the AccessDataSource and SqlDataSource?

Also, I am confused about the connection state. What are the rules of
thumb for maintaining a db connection open vs closing it?

Thanks for any hints, Mark

 

[Teemu Keiski]

AccessDataSource class practically inherits from SqlDataSource. It's main
purpose is to make it easier (Access-like) to connect to the database, but
there's nothing that AccessDataSource can do but SqlDataSource couldn't
(actually it's opposite for example Access dbs protected with username or
password, must be connected with SqlDataSource, AccessDataSource cannot
handle them since it doesn't you to set the connection string)

Rules related to connection state are that you should open the connection as
late as possible (just before it's used) an close it as soon as possible
(after it's been used). You can use same connection for multiple db
operations (and keep connection open) if the operations are executed within
same method / scope so that connection isn't created muleiple times within
the scope, but there's no need to try to reduce instantiation of connections
for db operations with wider scope than that. For example say opening
connection in Page_Load and reusing it in all operations till Page_PreRender
runs, when you'd close the connection, you shouldn't do anything like that.
Connection pooling mechanism is very efficient, is also employed
automatically, and in fact is better controlling the resources than trying
to "help" the system with forcing connection to stay open is.

For more information about connection pooling,see:
http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx

 

[msch-prv]

Thanks for the feedback.

I kind of skimmed through the reference you provided, but as far as I
can judge it does not provide information about what takes place with
an AccessSource connection.

I would like to "unformize" db connections in my application by using a
single connection string. For instance, some pages already use ADO to
dynamically create the datasources with the web.config connection
string. Does it make sense to extend this to all dropdowns on the page
and forego thereby the datafile approach?

If so, how should I proceed? Define all drop down datasources in the
OnPageLoad event and close them after binding?

Another question. With an AccessDataSource how persistent is the
connection state? Does it automatically clear up after the data is
bound? There is no explicit disconnect function.

TIA for further precisions.

 

[msch-prv]

Thanks for the feedback.

I kind of skimmed through the reference you provided, but as far as I
can judge it does not provide information about what takes place with
an AccessSource connection.

I would like to "unformize" db connections in my application by using a
single connection string. For instance, some pages already use ADO to
dynamically create the datasources with the web.config connection
string. Does it make sense to extend this to all dropdowns on the page
and forego thereby the datafile approach?

If so, how should I proceed? Define all drop down datasources in the
OnPageLoad event and close them after binding?

Another question. With an AccessDataSource how persistent is the
connection state? Does it automatically clear up after the data is
bound? There is no explicit disconnect function.

TIA for further precisions.

 

[Teemu Keiski]

AccessDataSource uses OleDb connection to Access database. That is classes
from System.Data.OleDb namespace.

Data Source control handles all connection closing automatically except in
case you manually ask data reader from it (DataSourceMode is DataReader and
you manually call Select() yourself). In that case, connection can be closed
after data reader is closed.

Basically all you need to do is to specify ID of the data source control
into dataSourceID attribute/property of the databound control (DropDownList
in this case). With data bound controls by the way, you don't need to touch
the connection at all.