ADO.NET Connection Oriented Architecture

ADO.NET provides the following two models for accessing data from a Data Source:

• Connection Oriented Architecture
• Disconnected Oriented Architecture

In this article, I would brief about ADO.Net Connection oriented architecture. Open  VS 2012 >New Projects > Visual C#  >  Windows> Console Application. Add new class file and name it as ConnectionOriented and we would develop methods as shown below.
Include System.Data and System.Data.SqlClient namespaces.

In InsertMethod we would create a connection object and try to execute Insert statement using command with ExecuteNonQuery statement. Note the connection remain in open state till the command is execute and finally closed. ExecuteNonQuery is used for Insert update and delete kind of operations.

In SelectMethod, we perform select operation with sqlconnection and sqlcommand by passing command parameter and we perform ExecuteReader to fetch the data from DB.

In SPApproachMethod, we try to insert and select the product information using store procedure and we pass commandtype as storeprocedure. In all the methods, we open the sql connection and close it after the operation is performed.

Below is store procedure that is executed from application. 

In  program class, create an instance of ConnectionOriented class and perform the operations as shown below.

Introduction To ADO.Net | Data Accessing Layer

ADO.NET is a .NET data access technology.

ADO.NET is an integral part of the .NET Compact Framework, providing access to relational data, XML documents, and application data. ADO.NET supports a variety of development needs. You can create database-client applications and middle-tier business objects used by applications, tools, languages or Internet browsers.

ADO.NET defines DataSet and DataTable objects which are optimized for moving disconnected sets of data across intranets and Internets, including through firewalls. It also includes the traditional Connection and Command objects, as well as an object called a DataReader that resembles a forward-only, read-only ADO recordset. If you create a new application, your application requires some form of data access most of the time.

ADO.NET provides data access services in the Microsoft .NET platform.


You can use ADO.NET to access data by using the new .NET Framework data providers which are:

• Data Provider for SQL Server (System.Data.SqlClient).
• Data Provider for OLEDB (System.Data.OleDb).
• Data Provider for ODBC (System.Data.Odbc).
• Data Provider for Oracle (System.Data.OracleClient).

ADO.NET is a set of classes that expose data access services to the .NET developer. The ADO.NET classes are found in System.Data.dll and are integrated with the XML classes in System.Xml.dll.

There are two central components of ADO.NET classes: the DataSet, and the .NET Framework Data Provider.

Data Provider is a set of components including:

• the Connection object (SqlConnection, OleDbConnection, OdbcConnection, OracleConnection)
• the Command object (SqlCommand, OleDbCommand, OdbcCommand, OracleCommand)
• the DataReader object (SqlDataReader, OleDbDataReader, OdbcDataReader, OracleDataReader)
• and the DataAdapter object (SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter, OracleDataAdapter).

DataSet object represents a disconnected cache of data which is made up of DataTables and DataRelations that represent the result of the command.