ADO.NET is a part of the .NET Framework and represents an ADO object extension. It is crucial to data fetching and sending with ADO.NET. A supported functionality of ADO.NET is that it allows for disconnected or XML-based access to data. Using XML, you can retrieve data. The VB.NET or C# code or vice versa can be fetched from various data sources with the object. In this article, I will discuss ADO.NET architecture.
What is ADO.Net?
We create two-tier models that bridge ASP.NET with backend databases, enabling applications to access diverse types of data using the same methodology and to connect to a SQL Server database using a different set of classes. In ADO.net architecture, we use a two-tier model to create a bridge between ASP.net and the backend database, through which applications can access various types of data using the same methodology and connect to a SQL Server database using a different set of classes. We can keep connections with the database and access data using the connected model, and we can access data using disconnected objects with the other model, providing two different connections. The Microsoft .net framework is also included, and it is a set of classes that provides the foundation for .NET.
How Does ADO.NET Work?
- In connected mode, data is read in forward-only read-only mode and data is updated, deleted, inserted, and selected in disconnected mode, in which data is read and updated in both read and update modes.
- In connected mode, a single table can be held, but when there are multiple tables to be held, they must be held separately.
- In connected mode, objects are forward-only-read-only; while in disconnected mode, they can process data in any dimension.
Examples:
- When data is read from a data reader, it maintains the connection open until all records have been retrieved.
- When a DataSet receives all its records at once and closes the connection, it is still available to data sources that are connected in a disconnected architecture.
ADO.NET Architecture
The ADO.NET architecture comprises six important components. They are as follows:
- Connection
- Command
- DataReader
- DataAdapter
- DataSet
- DataView
The command and connection objects shown in the preceding diagram are two of the four components that must be present in a BDC command object. A BDC command object must include one of these two components. A connection object is needed regardless of what operations are performed on it, including Insert, Update, Delete, and Select. In the preceding diagram, have a look at the image.
Let’s take a look at each of the parts individually:
Connection: The connection object is the first important component of your application. It is required to connect to a backend database that may be SQL Server, Oracle, MySQL, etc. You must have two things to create a connection object. Your database Machine name or IP address or someplace where it is stored is where it is. The second thing is security credentials, such as whether it’s a Windows authentication or username and password-based authentication. The connection is created using the connection object and a backend data source must be connected to using the connection.
Command: The second important component is the command object. When we discuss databases such as SQL Server, Oracle, MySQL, then speak SQL, it is the command object that we use to create SQL queries. After you create your SQL queries, you can execute them over the connection using the command object. You can go either the DataSet or the DataReader way with DTS. In general, you should choose which method you require based on the situation. Note: You can go either the DataSet or the DataReader way with DTS.
DataReader: We can only read the records in the forward mode with DataReader. Here, you should familiarize yourself with three things: read-only, connected, and forward modes.
DataSet: A disconnected recordset can be browsed in both directions, and it is also possible to insert, update, or delete data sets. The DataAdapter fills a DataSet using data.
DataAdapter: The DataAdapter performs an operation on the data from the command object and then writes the data set to the dataset.
DataView Class: A DataView enables you to modify the appearance of the data stored in a DataTable, a data-binding skill that is frequently employed in data-view applications. You may alter the sort order of data in a table or filter it based on row state or on a filter expression using a DataView.
Features of ADO.NET
Features of ADO.NET include:
- When viewed as text-based formats, XML documents are obviously negotiable. ADO.NET exchanges data using XML, regardless of its complexity, and for internal purposes.
- We can model our application in separate layers, which is what ADO.NET is built around.
- A programming style in which words are used to construct assertions or evaluate expressions is called word-based programming. The following code fragment illustrates how to select the “Ranks” column from “Scaler” in the “Student” table using word-based programming:
DataSet.Student(“Scaler”).Ranks; - The data architecture is simple to scale as it involves only disconnected data on the server. Because everything is handled on the client-side, performance is improved.
- The growing number of clients requiring degraded performance as it uses disconnected data access is accommodated by the application’s use of lock connections that last longer. In addition, the application can afford to make the programmers conserve resources and allow users to access data simultaneously.
ADO.NET Code Examples
To retrieve data from a database, you can use the following ADO.NET technologies:
SqlClient: The code that follows assumes that you can connect to the Northwind sample database on Microsoft SQL Server. It creates a xref:System.Data.SqlClient.SqlCommand to fetch rows from the Products table, adds a xref:System.Data.SqlClient.SqlParameter to restrict the results to rows with a highest UnitPrice of 5, and connects using System.Data.SqlClient.SqlConnection. After the code exits, resources are closed and disposed, and the datareader is used to retrieve the results. In System.Data.SqlClient, Microsoft.Data.SqlClient should be upgraded instead for the time being. For more information, see Introducing the new Microsoft.Data.SqlClient.
OleDb: Assuming you can access the Microsoft Access Northwind sample database, the code creates a OleDbCommand to select records from the Products table, adds an OleDbParameter that restricts the result to rows with a price greater than 5, and uses a OleDbConnection to open a Datareader inside a using block. The code exits when the code finishes executing or closes the resources it opened. The results are displayed in the console window.
Odbc: This code assumes that you have access to the Microsoft Access Northwind sample database. It creates a System.Data.Odbc.OdbcCommand to select rows from the Products table, adds a System.Data.Odbc.OdbcParameter to restrict the results to rows with a higher UnitPrice than the specified parameter value, in this case 5, and opens a System.Data.Odbc.OdbcConnection inside a using block. After the code exits, resources are closed and disposed of and resources are reopened. A System.Data.Odbc.OdbcDataReader is used to read and display the data.
OracleClient: This example requires you to connect to DEMO.CUSTOMER on an Oracle server. You also need to include a reference to System.Data.OracleClient.dll. The code displays the data in an xref:System.Data.OracleClient.OracleDataReader.
LINQ to Entities: The code used in this example returns data as an object that represents Categories, which are projected as an anonymous type with only the CategoryID and CategoryName properties. For more information, see LINQ to Entities Overview.
using System;
using System.Linq;
using System.Data.Objects;
using NorthwindModel;
class LinqSample
{
public static void ExecuteQuery()
{
using (NorthwindEntities context = new NorthwindEntities())
{
try
{
var query = from category in context.Categories
select new
{
categoryID = category.CategoryID,
categoryName = category.CategoryName
};
foreach (var categoryInfo in query)
{
Console.WriteLine("\t{0}\t{1}",
categoryInfo.categoryID, categoryInfo.categoryName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Option Explicit On
Option Strict On
Imports System.Linq
Imports System.Data.Objects
Imports NorthwindModel
Class LinqSample
Public Shared Sub ExecuteQuery()
Using context As NorthwindEntities = New NorthwindEntities()
Try
Dim query = From category In context.Categories _
Select New With _
{ _
.categoryID = category.CategoryID, _
.categoryName = category.CategoryName _
}
For Each categoryInfo In query
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
categoryInfo.categoryID, categoryInfo.categoryName)
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
End Class
LINQ to SQL: This code creates an anonymous type that contains only the CategoryID and CategoryName properties. It then projects the type to an instance of Categories. The LINQ query returns data as Category objects.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Northwind;
class LinqSqlSample
{
public static void ExecuteQuery()
{
using (NorthwindDataContext db = new NorthwindDataContext())
{
try
{
var query = from category in db.Categories
select new
{
categoryID = category.CategoryID,
categoryName = category.CategoryName
};
foreach (var categoryInfo in query)
{
Console.WriteLine("vbTab {0} vbTab {1}",
categoryInfo.categoryID, categoryInfo.categoryName);
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
}
}
Option Explicit On
Option Strict On
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Northwind
Class LinqSqlSample
Public Shared Sub ExecuteQuery()
Using db As NorthwindDataContext = New NorthwindDataContext()
Try
Dim query = From category In db.Categories _
Select New With _
{ _
.categoryID = category.CategoryID, _
.categoryName = category.CategoryName _
}
For Each categoryInfo In query
Console.WriteLine(vbTab & "{0}" & vbTab & "{1}", _
categoryInfo.categoryID, categoryInfo.categoryName)
Next
Catch ex As Exception
Console.WriteLine(ex.Message)
End Try
End Using
End Sub
End Class
Advantages of ADO.Net Architecture
- A Data component in Visual Studio environment works to establish data access in a variety of ways to make it simple and safe to develop applications. ADO.NET data components in Visual Studio environment encapsulate data access functionality in various ways that make it simpler and safer to develop applications.
- As a result of its Disconnected Architecture, ADO.Net provides remarkable performance advantages by eliminating all data connection dependencies. DataSet functions in ADO.Net are completely disconnected, making it possible to plug an unlimited number of supported data sources into code without any difficulty in the future.
- Extensible Markup Language (XML) is the standard format for exchanging data across a network. Any component that can read XML can process data, so XML is a perfectly viable format for transmitting datasets. Although various ADO.NET data types like the DataSet are so intertwined with XML that they cannot exist or function without it, XML is not required.
- The advantage of using the Advantage ADO.NET Data Provider, is that it enables you to directly modify data using SQL. In order to provide interaction with SQL Server, the SQL Server Data Provider that is included with ADO.NET is highly optimized. It is utilized in conjunction with the tabular data stream (TDS) format that is employed by SQL Server. The advantage of using the Advantage ADO.NET Data Provider is that it is expertly crafted to facilitate data modification.
- The ADO.NET object model is another big advantage. ADO.NET is a complicated object model that is built using class inheritance and interface implementation. Once you look for things you need in this scope, you will realize that the logical base classes and features derived from the entire system are simple to use.
Conclusion
We conclude in the above article that this architecture is connection oriented, because the database is connected to the back end, therefore, we can also conclude that there is an ADO.net that is a mediator between the front end and back end, which makes for interactive architecture. We provide a diagram of its structure.
