Ado.net

[dhoomdon]

what is connected and disconnected mode ?

[Cimperiali]

to access a db, you need to open a connection
once you 've finished, you close the connection.

in between, you are in connected mode. Once, when we coded for a single machine, we used to work quite always in connected mode: we opened a connection at the beginning of the program and closed it at the end of program. The data retrieved form db where in containers still connected to db, so that any change made to the container were made on db.

As soon as you discover you could need to have a second connection, or a second container on same data or portion of it, you could find the connected mode easily lead to Deadlocks or in any case to heavy locks on data.
Related to connected mode is the concept of Pessimistic Concurrency

To solve the matter, the disconnected model take place: you open the connection, do what you need (read, or update or delete or insert) and as soon as you're done, you close the connection. If you need to retrieve data, you put them in a disconnected container, something that can survive even when the connection is closed.
so the disconnected model is done retrieving data, putting them in an in memory db that is a local copy of the data on server.
Advantage is that you reduce the locks. disadvantage is that you are always potentially working on an old copy of the data (someone else could have made an update after you retrieved the data, but before you refresh them).
Thus, related to disconnected model is the concept of Optimistic concurrency

Ado net has some connected object and some disconnected ones. Unless you use the generic providers (dbConnection, dbCommand,...) you can recognize the connected ones as they have a prefix that resemble the kind of db or technology used to connect to database:
ie: for the Connection object we have
SqlConnection, OracleConnection, OledbConnection,..
for the command object we have
SqlCommand, OracleCommand, OledbCommand,...
for the dataReader we have
sqlDataReader, OracleDatareader, OledbDataReader
while the disconnected objects do not have that prefix:
DataSet is DataSet, not sqlDataSet;
Datatable is Datatable;

some interesting readings on the subject

http://msdn.microsoft.com/en-us/magazine/cc188717.aspx

http://msdn.microsoft.com/en-us/library/e80y5yhx.aspx

http://www.codeguru.com/csharp/.net/...cle.php/c19655

http://msdn.microsoft.com/en-us/magazine/cc163924.aspx

http://www.codeguru.com/csharp/.net/...le.php/c19561/