Data access - izznogooood/dotnet-wiki GitHub Wiki

ADO.NET provides the most direct method of data access. For a higher-level abstraction see the Entity framework section. ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. You use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain.

ADO.NET is a set of classes that expose data access services. The classes can be divided into those that are specific to a given data source - the data provider classes, and those that are independent of the various data sources - the DataSet classes.

.images/Ado.net.png

.NET data provider

A .NET data provider is used for connecting to a database, executing commands, and retrieving results. The provider is tailored towards a specific source, like SQL Server or Oracle, or generic to be used against any ODBC or OLE DB data source. We'll limit the scope to SQL server in the following.

The following sections lists the core components.

Connection

Establishes a connection to a specific data source. The SqlConnection class derives from the abstract base class DbConnection which defines the core behaviour of database connections.

If the SqlConnection goes out of scope, it won't be automatically closed. Therefore, you must explicitly close the connection. To ensure that connections are always closed, open the connection inside of a using block, as shown in the following code fragment. Doing so ensures that the connection is automatically closed when the code exits the block.

SqlConnectionStringBuilder connectionStringBuilder = new()
{
    DataSource = @"(LocalDb)\MSSQLLocalDB",
    InitialCatalog = "Library.Database"
};
using SqlConnection connection = new(connectionStringBuilder.ToString());
SqlCommand command = new("SELECT COUNT(*) FROM dbo.Books", connection);
connection.Open();
var res = command.ExecuteScalar();

The above sample code illustrates the use of the ConnectionStringBuilder helper class that provides a simple way to create and manage the contents of connection strings used by the connections.

Command

Executes a command against a data source. The base class for SqlCommand is the abstract DbCommand class.

Exposes a collection of parameters, an important technique to safeguard against SQL injection, see defensive programming

The command can execute in the scope of an SqlTransaction, transactions ensure that all or non of the database operations inside the transaction either completes successfully or are rolled back. The classical case is transferring money from account A to B, you include both the withdrawal from A and the deposit into B in a transaction to ensure that either both succeed (transfer ok) or both fail (no transfer) - you would not want to end up in some middle ground.

DataReader

Reads a forward-only, read-only stream of data from a data source. The base class for SqlDataReader is the abstract DbDataReader class.

SqlConnectionStringBuilder connectionStringBuilder = new() { DataSource = @"(LocalDb)\MSSQLLocalDB", InitialCatalog = "Library.Database" };
using SqlConnection connection = new(connectionStringBuilder.ToString());
SqlCommand command = new("SELECT BookId, Title FROM dbo.Books", connection);
connection.Open();
using SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
    Console.WriteLine($"Id: {reader[0]}, Title: {reader[1]}");

DataAdapter

Populates a DataSet and resolves updates with the data source. The base class for SqlDataAdapter is the abstract DbDataAdapter class.

Use the SqlCommandBuilder to automatically generate single-table commands for the DataAdapter to reconcile changes made to a DataSet with the associated SQL Server database.

The sample below illustrates the use of both the CommandBuilder and the DataAdapter to populate a DataSet and resolve updates in the data source.

SqlConnectionStringBuilder connectionStringBuilder = new() { DataSource = @"(LocalDb)\MSSQLLocalDB", InitialCatalog = "Library.Database" };
using SqlConnection connection = new(connectionStringBuilder.ToString());
DataSet dataSet = new();

SqlDataAdapter adapter = new() { SelectCommand = new SqlCommand("SELECT BookId, Title FROM dbo.Books", connection) };
adapter.Fill(dataSet, "Books");

dataSet.Tables["Books"].Rows.Add(new object[2] { null, "Captain America Comics #2" });

SqlCommandBuilder builder = new(adapter);
int affectedRows = adapter.Update(dataSet, "Books");

DataSet

The ADO.NET DataSet is explicitly designed for data access independent of any data source. As a result, it can be used with multiple and differing data sources, used with XML data, or used to manage data local to the application. The DataSet contains a collection of one or more DataTable objects consisting of rows and columns of data, and also primary key, foreign key, constraint, and relation information about the data in the DataTable objects, see figure above.

See section above on DataAdapter for samples on how to work with the DataSet.

In order to decide whether to use a DataReader or a DataSet, consider the type of functionality that your application requires and follow the recommendations in the Choosing a DataReader or a DataSet section.

Samples

Check out the ADO.NET code examples.