Understanding ADO.NET Architecture
ADO.NET is an extension of ADO objects and plays a significant role in data retrieval and manipulation in the .NET Framework. It features disconnected or XML-based data access, making it easier to retrieve data from different sources. Its architecture comprises various components such as data providers, data sets, and data connections. Data Providers handle the connection to the database and data retrieval, Data Sets hold data in a disconnected manner while Data Connections manage the connection with the database server. ADO.NET helps to simplify data processing by allowing developers to access database data in a more efficient and scalable manner.
ADO.NET Overview
ADO.NET is a framework that connects ASP.NET with backend databases, allowing for efficient data access. It uses a two-tier model, which can access data using either connected or disconnected objects. This architecture provides flexibility to connect to a SQL Server database using a different set of classes. The Microsoft .NET framework is a collection of classes that serve as the foundation for .NET.
Understanding ADO.NET
ADO.NET is a data access technology used to read, insert, update and delete data stored in a database. It operates in both connected and disconnected modes, with data being read in forward-only read-only mode. Connected mode allows a single table to be held, while multiple tables must be held separately.
In disconnected mode, data can be read and updated in any dimension. For instance, when a data reader is used to read data, the connection remains open until all records have been retrieved. A DataSet receives all its records at once and then closes the connection. Despite this, it is still available to the data sources connected in a disconnected architecture.
Understanding ADO.NET Architecture
ADO.NET architecture is made up of six important components: Connection, Command, DataReader, DataAdapter, DataSet, and DataView.
The Connection object is used to connect to a backend database and requires two things, namely, the database machine name or IP address, and authentication details.
The Command object is used to create SQL queries for databases like SQL Server, Oracle, MySQL. You can execute the created SQL queries using the Command object.
The DataReader object is used to read records in forward mode and is read-only, connected, and forward mode.
The DataSet object is used to browse a disconnected recordset in both directions, and you can insert, update, or delete datasets.
The DataAdapter object performs an operation on data from the command object and writes the dataset to the dataset object.
Finally, the DataView class enables modifying the display data stored in a DataTable. It is commonly used in data view applications to alter the sort order of data in a table or filter it based on row state or a filter expression.
ADO.NET Features
ADO.NET offers the following features:
* Exchange of data using XML, which is easily negotiable even for complex documents.
* Application can be modeled in separate layers.
* Word-based programming is available to construct assertions or evaluate expressions.
* Simple data architecture that is scalable as it involves only disconnected data on the server.
* Performance is enhanced as everything is handled on the client-side.
* Lock connections are used to accommodate the growing number of clients requiring degraded performance due to disconnected data access.
* Programmers can conserve resources and enable simultaneous user access to data.
// Example of Word-based programming to select "Ranks" column from "IQCode" in the "Student" table
DataSet.Student("IQCode").Ranks;
SQLCLIENT Code:
This code uses System.Data.SqlClient.SqlCommand to retrieve rows from the Products table connected with the Northwind sample database using System.Data.SqlClient.SqlConnection. After fetching results, it closes and disposes of resources. Use Microsoft.Data.SqlClient instead of System.Data.SqlClient.
Code:
“`
using System.Data.SqlClient;
using(var connection = new SqlConnection(“Server=(localdb)\\mssqllocaldb;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=true”))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = “SELECT * FROM Products WHERE UnitPrice > @price”;
command.Parameters.AddWithValue(“@price”, 5);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(“{0}\t{1}”, reader.GetInt32(0), reader.GetString(1));
}
}
}
}
“`
OLEDB Code:
This code uses OleDbCommand to retrieve information from the Products table of the Microsoft Access Northwind sample database. It displays the results and then closes the resource.
Code:
“`
using(var connection = new OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Northwind.accdb”))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = “SELECT * FROM Products WHERE UnitPrice > ?”;
command.Parameters.AddWithValue(“?”, 5);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(“{0}\t{1}”, reader.GetInt32(0), reader.GetString(1));
}
}
}
}
“`
ODBC Code:
This code uses OdbcCommand to retrieve information from the Products table of the Microsoft Access Northwind sample database. It displays the results and then closes the resource.
Code:
“`
using(var connection = new OdbcConnection(“Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=Northwind.accdb”))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = “SELECT * FROM Products WHERE UnitPrice > ?”;
command.Parameters.AddWithValue(“?”, 5);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(“{0}\t{1}”, reader.GetInt32(0), reader.GetString(1));
}
}
}
}
“`
ORACLECLIENT Code:
This code uses the System.Data.OracleClient.dll reference to establish a connection with DEMO.CUSTOMER on the Oracle server. It displays the data queried and closes the resource.
Code:
“`
using(var connection = new OracleConnection(connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = “SELECT * FROM MY_TABLE”;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(“{0}\t{1}”, reader.GetInt32(0), reader.GetString(1));
}
}
}
}
“`
LINQ to Entities Code:
This code returns the projection of Categories as an anonymous type with only the CategoryID and CategoryName properties. It works with NorthwindEntities and displays the data queried.
Code:
“`
using(var context = new NorthwindEntities())
{
try
{
var query = context.Categories.Select(category => 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);
}
}
“`
LINQ to SQL Code:
This code creates an anonymous type consisting of CategoryID and CategoryName properties and projects this type to an instance of Categories. It displays the queried data.
Code:
“`
using(var db = new NorthwindDataContext())
{
try
{
var query = db.Categories.Select(category => 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);
return;
}
}
“`
Advantages of ADO.NET Architecture
The ADO.NET architecture, available in Visual Studio, provides several benefits that make it easy and secure to build applications. The DataSet feature in ADO.NET, with its disconnected architecture, enables easy and quick access to multiple data sources without relying on data connections, ensuring exceptional performance.
ADO.NET is compatible with XML, a widely used format for sending data across a network. Though DataSet heavily relies on XML, other ADO.NET data types do not.
The SQL Server Data Provider, integrated with ADO.NET, is optimized for use with SQL Server, and the Advantage ADO.NET Data Provider facilitates SQL-based data modification.
The ADO.NET object model is based on class inheritance and interface implementation, making it easy to use.
Conclusion
In summary, this architecture is connection-oriented as the database is linked to the back end. Additionally, we utilize ADO.net as a mediator between the front end and back end, resulting in an interactive architecture. Please refer to the provided diagram for a visual representation.
ADDITIONAL RESOURCES
Here are some resources related to .NET and ADO.NET:
These links provide useful information regarding various aspects of .NET and can help you prepare for interviews or learn more about the technology.
