Post
Ask Question

Introduction To ADO.NET

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 database 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 database from the User Interface of the application. Ado.net acts as a mediator or an interface between the user 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 technology, which is a part of the .NET framework.
 
Data Access Object Architecture
 
In order to connect or communicate the 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.
  1. Connected oriented architecture.
  2. 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 centralized database will be more burdensome, as it degrades the database performance. It increases network traffic. For every operation on the database, it will hit the database every time.
 
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 the database or to close the connection to the database, the programmer/developer doesn't require any the 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 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 a local database or client machine, there will be less secure, which can be hacked easily. For every request or operation to the database, the local database will act as an interface between the user interface and the centralized database, the result of the local database will be reflected in the centralized database, so in this, case accessing/performance will be slow compared to the connection-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 affects 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 support only connection-oriented architecture, which is Interacting with the centralized database directly instead of the local database (client machines).
 
Due to the above drawback, Microsoft introduced a separate data access object, which is also called 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
  • DataSet 
Data Provider
 
 Data provider is a component, which plays a major role in communicating with the 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 databases, Microsoft provides various data providers, as shown below.
  • System.Data.SqlClient;
  • System.Data.Oledb;
  • System.Data.Odbc etc etc. 
To communicate with the SQL Server database, we need to use system.data.sqlclient as our namespace to access its classes, properties, or 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 class, as shown below.
  • Connection Object.
  • Command object
  • SqldataReader object
  • SqldataAdapter object.
Connection Object
 
Syntax
  1. Sqlconnection con=new Sqlconnection(); 
This object is used to maintain the connection to a 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.
  1. To open the connection, we need to use an open method. example : con.open();
  2. To open the connection, we need to use a close method. example : con.close();
  3. Open(): This method is used to open the connection to the centralized database in order to perform certain operations or queries.
  4. Close(): Once the result is fetched, we need to close the connection to the database. 
Command Object
 
Syntax
  1. SqlCommand comm=new SqlCommand(); 
This object is used for executing the given SQL commands/queries within the SQL server database. 
 
SqlDataReader object
 
Syntax
  1. SqlDatareader dr=cmd.ExecuteReader(); 
This object will fetch the results of SQL command/ queries and will send it to the client Application/front-end Application.
 
SqlDataAdapter object
 
Syntax
  1. 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 application. 
 
Data Set
 
Data Set is used as part of Disconnected oriented architecture. In Disconnected oriented architecture, we have a local database, where we perform the given SQL queries or SQL commands, which is a part of the 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 a local database in XML table format.
 
Syntax for an object
  1. 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 the SQL Server database in Disconnected oriented architecture are given below.
  1. Including/Importing data provider  namespace like
    1. Using system.data.sqlclient ; 
  2. Declaring the connection string to connect to the database with userid , password , servername, etc.
    1. 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 the SQL Server database and by default, the userid of the database is sa.
    In the password, we will initialize the password of the 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.
     
  3. In this step, we have to use SQLconnection object by initializing/using the connection string parameter, as given below.
    1. Sqlconnection connection=new sqlconnection(conneString); 
  4. After connecting to the SQL Server database by providing the credentials. In the step given above, we need to use the command object by initializing
     
    sql query/sql command with connection object reference name as
    1. Sqlcommand cmd=new Sqlcommand("Query to database like CRUD",connection); 
  5. 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
    1. using System.data;  
    2. Dataset dds=new Dataset(); 
  6. In this step, we will be creating SqlDataAdapter object by initializing the SQLcommand object reference, as given below.
     
    1. SqlDataAdapter daa=new SqlDataAdapter(cmd); 
       
  7. 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.
    1. daa.Fill(dss,"NameoftheTable"); // Here fill method is part of sqldataAdapter which is used to fetch the records from the centralized database and will fill/dump into a local database.  
Steps to communicate with SQL Server database in Connected oriented architecture are given below.
  1. Including data provider into a namespace, as shown below.
    1. Using system.data.sqlclient ; 
  2. Declaring connection string for connecting to the database with the userid, password, servername, etc.
    1. 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 the SQL Server database and by default, the userid of the database is sa. password. We will initialize the password of the SQLServer database. By default, Microsoft has provided the password as abc. As far as the database name is concerned, we have to initialize as the database, which we would like to communicate with.
     
  3. In this step, we have to use SQLconnection object by initializing/using the connection string parameter, as given below.
    1. Sqlconnection connection=new sqlconnection(conneString); 
    Here programmer/ a developer needs to open the connection explicitly to perform a certain operation to a centralized database, as shown below.
    1. connection.Open(); 
  4. After connecting with the SQL Server database by providing the credentials in the step given above, we need to use the command object by initializing the SQL query/SQL command with the connection object reference name, as given below.
    1. Sqlcommand cmd=new Sqlcommand("Query to database like CRUD",connection); 
  5. In this step, we need to create datareader object, as given below.
    1. SqlDataReader drr=cmd.ExecuteReader(); 
    Here, executeReader() method returns the data reader object.
     
  6. Once the data reader fetches the record from the centralized database, we will bind this data from dataReader object to the client Application.
    1. GridView1.DataSource=dr; 
  7. After the execution is completed, the programmer needs to close the connection explicitly.
    1. connection.Close();  
Program to understand disconnected oriented architecture
 
 
 
Thanks and I hope it helps.


Similar Articles