MSDN Documentation

MSDN > Articles > Database Access with ADO.NET

Database Access with ADO.NET

ADO.NET is a set of .NET Framework classes that exposes data access services to the .NET programmer. It is an integral part of the .NET Framework, providing consistent access to data sources such as SQL Server and XML, as well as data sources exposed by OLE DB. ADO.NET simplifies data access by providing a layered set of objects that allow you to work with data and to interact with data sources.

Introduction

ADO.NET provides a rich set of classes for working with data. It is designed to be extensible and to support a variety of data sources. The primary goals of ADO.NET are to provide:

Core Components

ADO.NET is built around a set of core objects that work together to facilitate data access. These include:

Connecting to a Database

The first step in accessing a database is to establish a connection. This is done using a Connection object appropriate for your data source. You typically provide a connection string that contains information like the server name, database name, and authentication details.

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        Console.WriteLine("Connection opened successfully.");
        // Perform database operations here
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"Error connecting to database: {ex.Message}");
    }
}

Executing Commands

Once a connection is established, you can execute SQL commands using the Command object. This can be a simple query, an INSERT, UPDATE, or DELETE statement, or a call to a stored procedure.

using System.Data.SqlClient;

// ...

string query = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        command.Parameters.AddWithValue("@City", "London"); // Parameterized query to prevent SQL injection

        // Execute the command (e.g., using ExecuteReader, ExecuteNonQuery, ExecuteScalar)
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
            }
        }
    }
}

Data Readers

DataReader objects are ideal for scenarios where you need to read data sequentially and efficiently. They are forward-only and read-only, making them performant for large result sets. The ExecuteReader() method of a Command object returns a DataReader.

DataSets and DataTables

DataSet and DataTable objects are used for disconnected data access. They hold data in memory, allowing you to work with it without keeping a connection to the database open. This is useful for UI applications or when you need to perform complex data manipulations offline.

A DataSet can contain multiple DataTable objects, representing different tables from your database. You can define relationships between these tables within the DataSet.

DataAdapters

DataAdapter objects act as a bridge between a DataSet (or DataTable) and a data source. They are responsible for filling the DataSet with data from the database using a SELECT command and for synchronizing changes made to the DataSet back to the database using INSERT, UPDATE, and DELETE commands.

Key methods of a DataAdapter include:

Transactions

For operations that require atomicity (all operations succeed or all fail), ADO.NET supports transactions. You can begin a transaction, execute multiple commands within that transaction, and then either commit or rollback the transaction.

using System.Data.SqlClient;

// ...

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        // Command 1
        string query1 = "UPDATE Products SET UnitPrice = UnitPrice * 1.1 WHERE ProductID = 1";
        using (SqlCommand command1 = new SqlCommand(query1, connection, transaction))
        {
            command1.ExecuteNonQuery();
        }

        // Command 2
        string query2 = "INSERT INTO AuditLog (Message) VALUES ('Price updated for ProductID 1')";
        using (SqlCommand command2 = new SqlCommand(query2, connection, transaction))
        {
            command2.ExecuteNonQuery();
        }

        transaction.Commit();
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (Exception ex)
    {
        transaction.Rollback();
        Console.WriteLine($"Transaction rolled back: {ex.Message}");
    }
}

Error Handling

Robust error handling is crucial when interacting with databases. ADO.NET exceptions, such as SqlException, provide detailed information about errors that occur during database operations. Always wrap your database code in try-catch blocks.

Note: Always use parameterized queries or stored procedures to prevent SQL injection vulnerabilities. Never concatenate user input directly into SQL strings.

Best Practices

Tip: Consider using Entity Framework or Dapper for higher-level Object-Relational Mapping (ORM) or micro-ORM capabilities when building modern .NET applications.