ADO.NET is a part of .NET Framework that provides access to different types of data sources. ADO.NET supports relational, XML and application data. ADO.NET resides in a layer between data sources and client applications and enables customers to retrieve, manipulate and update data through ADO.NET object model.
ADO.NET architecture
ADO.NET was introduced in the 10th version of the .NET framework as a method to address two ways of handling data access and manipulation. One way is to access data and iterate through the collection in a single instance where the connection to the data source remains open. Another way is to access data in a disconnected mode – it allows accessing a data source, retrieving data, and working without an open connection to the data source. ADO.NET uses multilayer architecture that is built around three main concepts:
- Connection
- Command
- DataSet
The connected mode uses connected classes to work with connected data. The classes include Connection, Command, DataReader, Transaction, ParameterCollection, and Parameter. These classes provide read-only access to the data source and the ability to execute commands against the data source. On the other hand, in a disconnected mode, a different set of classes is used. Those are DataSet, DataTable, DataColumn, DataRow, Constraint, DataRelationship, and DataView classes. They enable retrieving data from the data source, working with the data in memory, and updating the data source with the new data. ADO.NET also introduces the connected DataAdapter class which operated as a bridge between the data source and the disconnected representation of the data. The DataAdapter simplifies filling and updating the disconnected DataSet or DataTable classes with the data from the data source.
ADO.NET Features:
Maintainability
The multilayered architecture enables building application logic in a separate layer – it mitigates the risk and simplifies adding new features
Interoperability
Data is transferred to and from data sources and internal data representations in an XML format
Programmability
Strongly typed data and IntelliSense support in Microsoft Visual Studio simplifies writing statements
Scalability
High application scalability is possible thanks to a disconnected mode that enables users to work with the data without retaining an open connection. It conserves resources and allows multiple users to access the data simultaneously
Performance
Both connected and disconnected classes are optimized for performance, and the proper use of those classes enables maintaining application performance at a high level
Show me the code!
Let’s see ADO.NET in action through some simple examples. We will use an MS SQL database named FacultyDatabase. Data is organized in tables Students, Subjects, Teachers, and SubjectStudents. We need to include the following namespaces:
using System.Configuration; using System.Data; using System.Data.SqlClient;
We also need to define our connection string.
var connString = ConfigurationManager.ConnectionStrings["FacultyContext"].ConnectionString;
Let’s define our select SQL query used to retrieve data from the database.
var sqlQuery = "SELECT [Id], [FirstName], [LastName], [BirthYear], [Gender] FROM [dbo].[Students]";
Next, we need to create our connection to the database. Each .NET framework data provider has a DbConnection object. OLE DB data provider includes OleDbConnection object, ODBC data provider includes an OdbcConnection object, Oracle data provider includes OracleConnection object, and SQL Server data provider includes SqlConnection object. Since we are connecting to SQL Server, we will use the SqlConnection object.
var conn = new SqlConnection(connString);
We will also need our Command object. In our case, it is a SqlCommand object.
var cmd = new SqlCommand(sqlQuery, conn);
DataReader
DataReader object is created by calling the ExecuteReader method of the Command object.
var dr = cmd.ExecuteReader();
DataReader is a connected class and thus it requires an open connection to the database. While the DataReader is open, the database connection is used exclusively by that DataReader object. So, it is important to close the reader and the connection when it has no further use. Good practice to ensure it – is to use the Using statement.
using (var conn = new SqlConnection(connString))
{
conn.Open();
using (var cmd = new SqlCommand(sqlQuery, conn))
{
using (var dr = cmd.ExecuteReader())
{
while (dr.Read())
{
Console.WriteLine("{0} - {1} {2} - {3}",
dr["BirthYear"],
dr["FirstName"],
dr["LastName"],
(int)dr["Gender"] == 1 ? "Male" : "Female");
}
}
}
}
If there is no connection, or it had been lost when using the reader there will be an error:
System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.
The output of this code is something like this:
DataTable
DataTable is an in-memory representation of the data and is similar to Tables in SQL. DataTable is a central ADO.NET object which can be used independently or used by another object like DataSet or DataView.
As a bridge from the database to the DataTable object, we use the DataAdapter object.
var da = new SqlDataAdapter(sqlQuery, conn);
To populate the DataTable object with the data from the database we use the Fill method of the DataAdapter object.
var dt = new DataTable();
using (var conn = new SqlConnection(connString))
{
using (var da = new SqlDataAdapter(sqlQuery, conn))
{
da.Fill(dt);
};
}
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0} - {1} {2} - {3}",
dr["BirthYear"],
dr["FirstName"],
dr["LastName"],
(int)dr["Gender"] == 1 ? "Male" : "Female");
}
The output is the same as in the previous example.
Here we can notice a couple of distinctions, compared to the DataReader example. First is that there is no need to open the database connection before retrieving the data – DataAdapter does that for us. The second distinction is that the connection is not required after the data is loaded – data is available in the DataTable object after database connection, whereas the DataAdapter object is closed and disposed of.
To update the database with the modifications stored in our DataTable object we need to call the Update method of a DataAdapter object. For our example, we want to move our students to the future by adding 1000 years to their Birth Year. After the data is loaded to the DataTable object, we will simply iterate through the list of results and add 1000 years to the BirthYear value of each student.
foreach (DataRow dr in dt.Rows)
{
dr["BirthYear"] = (int)dr["BirthYear"] + 1000;
}
Before we update the database with these changes, the UpdateCommand parameter of the DataAdapter object must be defined.
var updateCommand = new SqlCommand(
"UPDATE [dbo].[Students] SET BirthYear = @BirthYear " +
"WHERE Id = @Id", conn);
updateCommand.Parameters.Add("@BirthYear", SqlDbType.Int,1, "BirthYear");
SqlParameter parameter = updateCommand.Parameters.Add(
"@Id", SqlDbType.Int, 1, "Id");
parameter.SourceVersion = DataRowVersion.Original;
da.UpdateCommand = updateCommand;
And then we can call the Update method of the DataAdapter object.
da.Update(dt);
If we again run any of the previous examples, the output will show that the database was updated with these changes.
Command Builder
A class that can simplify the tasks of writing insert, update, and delete commands is the DbCommandBuilder class. DbCommandBuilder object will automatically generate SQL statements for the single-table updates. The minimum requirement is that the SelectCommand is defined on a DataAdapter object. DbCommandBuilder is a base class so we will use a derived SqlCommandBuilder class that implements SQL data provider-specific behaviour.
using (var conn = new SqlConnection(connString))
{
using (var da = new SqlDataAdapter(sqlQuery, conn))
{
var builder = new SqlCommandBuilder(da);
Console.WriteLine("Insert query: {0}", builder.GetInsertCommand().CommandText);
Console.WriteLine("Update query: {0}", builder.GetUpdateCommand().CommandText);
Console.WriteLine("Delete query: {0}", builder.GetDeleteCommand().CommandText);
}
}
The output shows the command text of insert, update, and delete commands. The code from the update example can be simplified.
using (var conn = new SqlConnection(connString))
{
using (var da = new SqlDataAdapter(sqlQuery, conn))
{
var builder = new SqlCommandBuilder(da);
var dt = new DataTable();
da.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
dr["BirthYear"] = (int)dr["BirthYear"] + 1000;
}
da.Update(dt);
}
}
N.B. DbCommandBuilder must execute the select command in order to resolve the metadata required to generate other commands. That results in an additional call to the database and can impact performance.
DataSet
DataSet is a collection of DataTables. It is populated in a similar way as DataTable is.
using (var conn = new SqlConnection(connString))
{
conn.Open();
using (var da = new SqlDataAdapter(sqlQuery, conn))
{
var ds = new DataSet();
da.Fill(ds, "Students");
foreach(DataTable dt in ds.Tables)
{
Console.WriteLine("{0}", dt.TableName);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("\t{0} - {1} {2} - {3}",
dr["BirthYear"],
dr["FirstName"],
dr["LastName"],
(int)dr["Gender"] == 1 ? "Male" : "Female");
}
}
}
}
In the output, we can see the table name is now Students.
DataSet can contain multiple tables. In order to populate multiple tables at once, multiple Select queries need to be executed simultaneously.
var sqlQuery = "SELECT TOP 10 Name = [FirstName] + ' ' + [LastName] FROM [dbo].[Students]; " +
"SELECT [Name] FROM [dbo].[Subjects];" +
"SELECT Name = [FirstName] + ' ' + [LastName] FROM [dbo].[Teachers]";
using (var conn = new SqlConnection(connString))
{
conn.Open();
using (var da = new SqlDataAdapter(sqlQuery, conn))
{
var ds = new DataSet();
da.Fill(ds);
foreach(DataTable dt in ds.Tables)
{
Console.WriteLine("{0}", dt.TableName);
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("\t{0}", dr["Name"]);
}
}
}
}
ll examples in this article can be performed using dotConnect for SQL Server – an enhanced data provider allowing developers to create applications grounded on SQL Server databases.
Migrating to dotConnect for SQL Server from the SqlClient code is straightforward – in most cases, you only need to add the provider’s components and change the System.Data.SqlClient namespace to Devart.Data.SqlServer. Besides, this solution provides a powerful SqlDataTable component as an all-in-one solution for table data access, auxiliary components for SQL scripts and bulk data transfer, extra data binding capabilities, and many other features for effective performance with high productivity.
Conclusion
I hope the examples and the explanations we provided in this article gave you an insight into ADO.NET and its features. It is simple and easy to use, and we can always improve on top of a good foundation.
