- ADO.NET (Active x Data Object) is mediator between front end and back end that interacts with 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 very crucial role in connecting your front end application and back end application. It’s a normal string which contains the information to establish the connection between 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 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 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 high performance stream of data from data source.
–Mediator between front end and back end which doesn’t have feature of containing data with in this , so it uses Dataset which is a result set.
–Also provides bridge between DataSet and data source.
–Use Command Objects to execute commands.
As Connected architecture needs connection for every transaction and creates much traffic to database since it do several trips. It shows 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 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 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 database and giving the user ability to perform all CRUD operations like insert, update and delete. No traffic issues will get occurred because even though 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 effect on 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