Effectively using disconnected paradigm in data access layer

[Guest]

I'm having difficulty seeing how to effectively use ADO.NET's disconnected
datasets with n-tier systems. In particular, how/when would/should a data
access layer (DAL) class use disconnected data?

I can see how my DAL could load all the various "lookup" (eg, mostly
read-only) tables into one or more datasets, and then return them to its
app/business logic-layer clients upon request. For example, a GetStates()
method might return a dataset (or collection?) containing all the state
codes.

But what about tables that are frequently updated, like "Orders"? Assuming
the DAL also loads those tables into dataset(s), when should DAL invoke the
DataAdapter's .update method to update the underlying database? To be safe,
I assume that it would need to do so after any client call (eg, AddOrder)
that updates the dataset.

But is this really best practice? Or should heavily-updated (but rarely
queried) tables NOT be loaded into datasets, but rather be accessed in
"connected" mode, using command objects?

And I suppose this brings-up an even more basic question: what advantage is
the disconnected paradigm when the database is 1) only used by one app and 2)
local to the app ?

Any guidance would be greatly appreciated...

 

[Sahil Malik]

David,

The disconnected mode encourages - closing connections.
Closed connections encourage - better connection pooling.
Better connection pooling - could mean tens of times better performance.

That in essence is why the disconnected paradigm rocks.

Now what should be connected and what should be disconnected purely depends
on the exact needs. While datasets and datatables offer inbuilt support for
concurrency by preserving previous values when changes are done, datareaders
are insanely fast for forward only reads. While datareaders are fast, they
donot support random access to any row at any time.

Disconnected paradigm for only one app or for a database local to the app
brings up another question based on the first paragraph of my reply here.
Are you going to connection pool in a desktop application using an access
back end? i.e. should you connect, fetch and disconnect OR open a connection
and keep it open forever?

The answer is - well .. from a performance point of view it may not make
much difference in that particular case only, if you donot conn. pool in
that - but - now you will be responsible for ensuring closed connections if
your app crashes. And of course now you've locked yourself into an
architecture where you will always be stuck in a one user always connected
mode.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

 

[Sahil Malik]

One more reason - Disconnected paradigm gives you tonnes more flexibility. A
remote webservice user can't keep a db connection open.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik