• Connected and Disconnected Architecture – Bestdotnettraining.com

• ADO.NET (Active x Data Object) is a mediator between the front end and back end that interacts with the client-side application and server-side application which supports two types of Data Accessing models, one is Connection-oriented and Disconnected oriented.
• ADO.NET leverages the power of XML for accessing data using disconnected architecture. It was completely designed with XML classes in .NET Framework.

Connection string plays a very crucial role in connecting your front-end application and a back-end application. It’s a normal string which contains the information to establish the connection between the backend Database and application and secured information such as user Id and password.

Providers
System.Data.SqlClient
System.Data.OleDb
System.Data.Odbc
System.Data.OracleClient
System.Data.EntityClient
System.Data.SqlServerCe

SQL server Provider connection string:

OLEDB Provider connection string:

ODBC Provider connection string:

Connection Oriented Architecture

The architecture which needs an open connection to access the data from the database is known as Connection-Oriented Architecture.
Following are the list of classes on which Connection Oriented Arch is built on:

As Connected architecture needs a connection for every transaction and creates much traffic to the database since it does several trips. It shows a very high impact on performance when the transaction has many commands i.e. for larger transactions. If it’s doing smaller transactions it is normally much faster.

Ex:
SqlConnection con = new SqlConnection(“connection String”);
SqlCommand cmd = new SqlCommand(“query”,con);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
//cmd.ExecuteNonQuery(); or cmd.ExecuteScalar();
con.Close();

Disconnected Oriented Architecture

The architecture in which data can be retrieved from the database even when the connection gets disconnected or closed is known as Disconnected Oriented Architecture.
Following are the list of classes on which disconnected arch is built on:

• Disconnected architecture is a method of retrieving the result set from the database and giving the user the ability to perform all CRUD operations like insert, update and delete. No traffic issues will get occurred because even though the connection was gone data will be get already stored into dataset object.
• By keeping connections open for only a minimum period of time, ADO .NET conserves system resources and provides maximum security for databases and also has less impact on system performance.
• Any changes to data in DataSet doesn’t show the effect on the database directly, to save the changes need to use Update() method of Data Adapter.
• Can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application.

Da.Update(ds,”Table Name”);

Differences between connected and disconnected architecture