ADO.NET
ADO.NET is a .NET data access technology.
It is used to make a call or to communicate with the database. As we know the application is developed, using various technologies like Angular.js,node.js, Kendo.js, ASP.NET, ASP.NET MVC, Java, PHP etc. needs to interact with the data base to perform certain operations like inserting the record into a table , updating the record or deleting a record.In order to perform these operations we need to interact with the data base from the User Interface of the application. Ado.net acts as a mediator or an interface betweenUser interface and the database.
Diagram to Understand the Architecture of Data Access Technology
UI / FrontEnd <----------> Data Access Object <-----------> BackEnd/database.
.Net <----------> ADO.NET <--------------> SQL Server Database.
Java <-------------> JDBC <------------> Oracle
Finally, we can say that in order to interact with the database using .NET Application, we need to make use of ADO.NET data access technnology, which is a part of .NET framework.
Data Access Object Architecture
In order to connect or communicate front end of the Application to the database, we have two different types of data access architecture, which acts as a mediator or an interface between the two components They are given below.
- Connected oriented architecture.
- Disconnected oriented architecture.
Connected oriented architecture
In Connected oriented architecture, for each and every operation to the database like insert/update/delete, the client Application or front end will be performing an operation on the central database directly.
In this architecture, the programmer or a developer needs to open SQL connection before performing the operation like inserting the record, updating the record or deleting the record from the database table, once the operations to a database are done then the programmer needs to close SQL connection explicitly.
Diagram to understand connected oriented architecture
![]()
Advantages of Connected Oriented Architecture
In this architecture, as we perform database operations on the centralized database, accessing the data will be faster. The data will be more secure, as it is communicating with the database directly.
Disadvantages of Connected Oriented Architecture
In this architecture, the programmer or developer needs to open the connection to the database before sending the queries and after the results are fetched, the programmer needs to close the connection explicitly. Every time connecting the centralised database will be more burden, as it degades the database performance. It increases the network traffic. For every operations on the database, it will hit the database everytime.
Disconnected Oriented Architecture
In Disconnected Oriented Architecture, the client Application or frontend will not interact or communicate with the centralized database but it will interact with the local database, which will be a part of the client machine.
Whatever operations are done to the local database, it will be reflected back to the centralized database automatically.
In this architecture, to open the connection to database or to close the connection to database, programmer/developer doesn't require any
thing to write, which would be done internally.
![]()
Advantages of using Disconnected oriented architecture
The programmer/developer does not need any code to write such as to open the connection or to close the connection to a database.
It reduces the network traffic. It reduces the burden on the database (sql server). As database operations are done to a local database(client machine) there will be no database hitting for every request.
Disadvantages of using Disconnected oriented architecture
As the data will be a part of local database or client machine, there will be less security, which can be hacked easily. For every request or operation to the database, local database will act as an interface between the user interface and the centralized database, the result of local database will be reflected to the centralized database, so in this, case accessing/performance will be slow compared to the connected oriented architecture.
In this architecture, a local database will be created within the client machine. Due to this reason, it will consume the client machine's memory, which effects the performance of the client machine.
History of Data Access Object Technology
Prior to Microsoft releasing .NET technology, we had various programming languages/ technologies, as shown below.
- Visual Basic (VB)
- Visual C++
- ASP
For the technologies stated above, Microsoft introduced data access objects, as shown below.
- DAO (Data Access Object)
- RDO (Remote Data Object)
- ADO (ActiveX data object)
All these data access objects supports only connected oriented architecture, which is Interacting with the centralized database directly instead of local database (client machine's).
Due to the above drawback, Microsoft introduced a seperate data access object, which is also called as ADO.NET, which will be supporting both the architectures, which are Connected oriented architecture and Disconnected oriented architecture.
To work with ADO.NET, Microsoft provided two different components, as shown below.
Data Provider
Data provider is a component, which plays a major role in communicating with SQL Server database. It is defined by Microsoft as a base class library or an API, which is a collection of classes and methods. To communicate with different kinds of database, Microsoft provides various data providers, as shown below.
- System.Data.SqlClient;
- System.Data.Oledb;
- System.Data.Odbc etc etc.
To communicate with SQL Server database, we need to use system.data.sqlclient as our namespace to access its classes, properties or the methods.
We have various classes in the above namespace i.e. system.data.sqlclient
- SqlConnection
- SqlCommand
- SqlDataReader
- SqlDataAdapter
Dataprovider will have the responsibilities given below.
- Maintaining the connection to the centralized database.
- Executing the query into the centralized database.
- Fetching the query result and forwarding to the front end Application (If it is Connected oriented architecture).
- Fetching the query result and filling into the local database(if it is Disconnected oriented architecture).
To use the above four classes i.e. Sqlconnection class, Sqlcommand class, Sqldatareader class and SQLdataAdapter class and their members; we need to create the object for each classes, as shown below.
- Connection Object.
- Command object
- SqldataReader object
- SqldataAdapter object.
Connection Object
Syntax
Sqlconnection con=new Sqlconnection();
This object is used to maintain the connection to centralized database, which is opening the connection, maintaining the connection and closing the connection to the database, which has to be done by the developer/ programmer explicitly.
To open the connection, we need to use open method. example : con.open();
To open the connection, we need to use close method. example : con.close();
Open() : This method is used to open the connection to the centralized datasbase in order to perform certain operations or queries.
Close() : Once the result is fetched, we need to close the connection to the database.
Command Object
Syntax
SqlCommand comm=new SqlCommand();
This object is used for executing the given sql commands / queries within the sql server database.
SqlDataReader object
syntax
SqlDatareader dr=cmd.ExecuteReader();
This object will fetch the results of SQL command/ queries and will send to the client Application / front end Application.
SqlDataAdapter object
syntax
SqldataAdapter da=new SqldataAdapter();
This sqldataAdapter is a part of System.data.SqlClient namespace and it is used for Disconnected oriented architecture. This object is used to fetch the results of the executed SQL commands/ queries and will be filling into the local database. Data Adapter object acts as an interface/ mediator between the centralized database and the client Applicaton.
Data Set
Data Set is used as part of Disconnected oriented architecture. In Sisconnected oriented architecture, we have a local database, where we perform the given SQL queries or SQL commands, which is a part of client machine.
Data Set is a local database, which is a predefined class defined by Microsoft with the System.Data namespace. Dataset contains the fetched records from the centralized database and will be stored into local database in XML table format.
Sntax for an object
Dataset ds=new Dataset();
Objects to be used in Connected Oriented Architecture
- Sqlconnection object
- Sqlcommand Objectbe
- SqlDataReader object.
Objects to be used in Disconnected Oriented Architecture
- SqlConnection Object
- Sqlcommand object
- SqlDataAdapter object
Data Set
Steps to communicate with SQL Server database in Disconnected oriented architecture are given below.
- Including/Importing data provider namespace like
Using system.data.sqlclient ;
- Declaring the connection string to connect to the database with userid , password , servername etc.
string ConneString= "Server=Servername;database=database-name;userid=sa;password=abc;";
Here Servername is the name of the Server, where exactly SQL Server is running.
The userid is the username of SQL Server database and by default, the userid of the database is sa.
In the password, we will initialize the password of SQL Server database. By default, Microsoft has provided the password as abc.
For the database name, we have to initialize the database with which, it would like to communicate with.
- In this step, we have to use SQLconnection object by initialising/using the connection string parameter, as given below.
Sqlconnection connection=new sqlconnection(conneString);
- After connecting to SQL Server database by providing the credentials. In the step given above, we need to use the command object by initialising
sql query/sql command with connection object reference name as
Sqlcommand cmd=new Sqlcommand("Query to database like CRUD",connection);
- In this step we need to create a local database by creating its object. Before creating any object, we need to import the namespace of Data Set, so that we can use all its classes, methods, properties etc.
Syntax
using System.data;
Dataset dds=new Dataset();
- In this step, we will be creating SqlDataAdapter object by initialising the SQLcommand object reference, as given below.
SqlDataAdapter daa=new SqlDataAdapter(cmd);
- After getting the results of the executed SQLcommand/SQL queries from SQLdataAdapter, we will be filling into our local database , which is available in the client machine in XML table format.
daa.Fill(dss,"NameoftheTable"); // Here fill method is part of sqldataAdapter which is used to fetch the records from centralized database and will fill/dump into local database.
Steps to communicate with SQL Server database in Connected oriented architecture are given below.
- Including data provider into namespace, as shown below.
Using system.data.sqlclient ;
- Declaring connection string for connecting to the database with the userid, password, servername etc.
string ConneString= "Server=Servername;database=database-name;userid=sa;password=abc;";
Here Servername is the name of the Server, where exactly SQL Server is running. The userid is the username of SQL Server database and by default, the userid of the database is sa. password. We will initialize the password of SQLServer database. By default, Microsoft has provided the password as abc. As far as database name is concerned, we have to initialise as the database, which we would like to communicate with.
- In this step, we have to use SQLconnection object by initialising/using the connection string parameter, as given below.
Sqlconnection connection=new sqlconnection(conneString);
Here programmer/ a developer needs to open the connection explicilty to perform certain operation to a centralized database, as shown below.
connection.Open();
- After connecting with SQL Server database by providing the credentials in the step given above, we need to use command object by initialising SQL query/SQL command with the connection object reference name, as given below.
Sqlcommand cmd=new Sqlcommand("Query to database like CRUD",connection);
- In this step, we need to create datareader object, as given below.
SqlDataReader drr=cmd.ExecuteReader();
Here, executeReader() method returns the data reader object.
- Once the data reader fetches the record from the centralized database, we will bind this data from dataReader object to the client Application.
GridView1.DataSource=dr;
- After the execution is completed, the programmer needs to close the connection explicitly.
connection.Close();
Program to understand disconnected oriented architecture
![]()
Thanks and I hope it helps.