Connected and Disconnected Architecture
- 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.
- Usually, Data Providers uses this connection string which contains parameters that are needed for establishing the connection.
- Let’s have a look at Data Providers that are supported by.Net Framework.
- List of Data providers which is supported by.Net Framework.
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:
– Used to establish the connection between the front end and back end.
– Enables access to database commands to return data, modify data, run stored procedures, and send or retrieve parameter information
– Used to read the data from Source. Provides a high-performance stream of data from data source.
–Mediator between the front end and back end which doesn’t have the feature of containing data within this, so it uses Dataset which is a result set.
–Also provides a bridge between DataSet and data source.
–Use Command Objects to execute commands.
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.
SqlConnection con = new SqlConnection(“connection String”);
SqlCommand cmd = new SqlCommand(“query”,con);
SqlDataReader dr = cmd.ExecuteReader();
//cmd.ExecuteNonQuery(); or cmd.ExecuteScalar();
DataSet ds = new DataSet( );
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:
• Data Set – Contains the set of Data Tables (Data Table is a set of Data Rows and Data Columns)• Data View – It’s a view of table available in DataSet. Can be used to perform insert, update and delete commands as in case of Data Set. Can be used to find, sort and filter the records.
• 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.
SqlConnection con = new SqlConnection("connection String");
SqlCommand cmd = new SqlCommand("query",con);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "table name");
Differences between connected and disconnected architecture