MSDN Documentation > .NET > ADO.NET

ADO.NET Documentation

Introduction to ADO.NET

ADO.NET is a set of .NET Framework classes that expose data access services to the .NET programmer. ADO.NET is part of the .NET Framework, enabling applications to access data in a data source. It provides a consistent view of relational and XML data. ADO.NET is designed to work with multiple data sources, including relational databases, such as SQL Server, Oracle, and MySQL, as well as non-relational data sources.

The primary purpose of ADO.NET is to facilitate data access from .NET applications. It allows developers to connect to data sources, retrieve data, manipulate it, and then update the data source with changes. ADO.NET consists of a collection of classes that represent the data, the data provider, and the data commands.

Core ADO.NET Components

ADO.NET provides a rich set of classes for data access. The most fundamental components include:

  • Connection: Establishes a connection to a data source. Each data provider has its own Connection object (e.g., SqlConnection for SQL Server, OracleConnection for Oracle).
  • Command: Represents a SQL statement or stored procedure to be executed against the data source. It works in conjunction with a Connection object.
  • DataReader: Provides a way to read a forward-only stream of data rows from the data source. It's efficient for retrieving large amounts of data without loading it entirely into memory.
  • DataSet: An in-memory representation of data that can hold multiple tables, relationships, and constraints. It's useful for working with disconnected data or when you need to perform complex data manipulations locally.
  • DataAdapter: Acts as a bridge between a DataSet and a data source. It fills a DataSet with data from a data source and resolves changes to the DataSet back to the data source.

Data Providers

ADO.NET uses data providers (also known as data providers or data source providers) to access data. Each data provider exposes a set of classes that are specific to a particular data source. For example:

  • SQL Server: System.Data.SqlClient namespace.
  • ODBC: System.Data.Odbc namespace.
  • OLE DB: System.Data.OleDb namespace.
  • Oracle: System.Data.OracleClient namespace (deprecated in favor of Oracle Data Provider for .NET).

You typically use the classes from the appropriate namespace to interact with your specific data source. The provider model allows ADO.NET to be extensible and support a wide range of data sources without modifying the core ADO.NET classes.

Connecting to Data

Establishing a connection is the first step in accessing data. This is done using the Connection object specific to your data provider. A connection string is used to specify the details of the data source, such as the server name, database name, and authentication credentials.


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!");
        // Proceed with data operations
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"Error connecting to database: {ex.Message}");
    }
}

Always use the using statement for connection objects to ensure that resources are properly disposed of, even if exceptions occur.

Executing Commands

Once a connection is established, you can execute SQL commands using the Command object. This can include SELECT, INSERT, UPDATE, and DELETE statements, as well as stored procedures.


string queryString = "SELECT ProductID, Name FROM Production.Product WHERE ProductID = @ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);
    command.Parameters.AddWithValue("@ID", 101); // Example parameter

    try
    {
        connection.Open();
        // Execute non-query, reader, or scalar
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"Error executing command: {ex.Message}");
    }
}

Common methods for executing commands:

  • ExecuteNonQuery(): Executes a command that returns no rows (e.g., INSERT, UPDATE, DELETE). Returns the number of rows affected.
  • ExecuteReader(): Executes a command and returns a DataReader object.
  • ExecuteScalar(): Executes a command and returns the first column of the first row in the result set. Useful for fetching a single value.

Data Readers

DataReader objects provide a fast, forward-only way to retrieve data. They are highly efficient for reading large result sets because they read data one row at a time.


string queryString = "SELECT ProductID, Name, ListPrice FROM Production.Product;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(queryString, connection);

    try
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["Name"]}, Price: {reader["ListPrice"]}");
            }
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"Error reading data: {ex.Message}");
    }
}

Remember to close the DataReader when you are finished with it (or use a using statement).

DataSets

A DataSet is an in-memory cache of data. It can hold multiple tables, each with its own rows and columns, and define relationships between them. This makes it ideal for working with disconnected data or for applications that need to present complex data structures.


// Assume 'connectionString' is defined

DataSet dataSet = new DataSet("MyData");

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string productsQuery = "SELECT ProductID, Name FROM Production.Product;";
    SqlDataAdapter productsAdapter = new SqlDataAdapter(productsQuery, connection);
    productsAdapter.Fill(dataSet, "Products"); // Fills a DataTable named "Products"

    string categoriesQuery = "SELECT ProductCategoryID, Name FROM Production.ProductCategory;";
    SqlDataAdapter categoriesAdapter = new SqlDataAdapter(categoriesQuery, connection);
    categoriesAdapter.Fill(dataSet, "Categories"); // Fills a DataTable named "Categories"

    // You can now access dataTables, rows, and columns within the dataSet
    DataTable productsTable = dataSet.Tables["Products"];
    foreach (DataRow row in productsTable.Rows)
    {
        Console.WriteLine($"Product: {row["Name"]}");
    }
}

DataSet objects can consume more memory than DataReaders, so use them judiciously.

DataAdapters

A DataAdapter is used to fill a DataSet with data from a data source and to resolve updates to the data back to the data source. It acts as a bridge between the disconnected DataSet and the connected data source.

DataAdapters have properties for defining SELECT, INSERT, UPDATE, and DELETE commands, allowing for synchronization of data.


// Assume 'connectionString' and 'dataSet' are defined, and 'dataSet' has a "Products" DataTable

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter();

    // SELECT command
    adapter.SelectCommand = new SqlCommand("SELECT ProductID, Name, ListPrice FROM Production.Product;", connection);

    // INSERT command (example)
    adapter.InsertCommand = new SqlCommand("INSERT INTO Production.Product (Name, ListPrice) VALUES (@Name, @ListPrice);", connection);
    adapter.InsertCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name");
    adapter.InsertCommand.Parameters.Add("@ListPrice", SqlDbType.Money, 0, "ListPrice");

    // UPDATE command (example)
    adapter.UpdateCommand = new SqlCommand("UPDATE Production.Product SET Name = @Name, ListPrice = @ListPrice WHERE ProductID = @ProductID;", connection);
    adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name");
    adapter.UpdateCommand.Parameters.Add("@ListPrice", SqlDbType.Money, 0, "ListPrice");
    SqlParameter parameter = adapter.UpdateCommand.Parameters.Add("@ProductID", SqlDbType.Int, 4, "ProductID");
    parameter.SourceVersion = DataRowVersion.Original; // Key for identifying the row to update

    // DELETE command (example)
    adapter.DeleteCommand = new SqlCommand("DELETE FROM Production.Product WHERE ProductID = @ProductID;", connection);
    parameter = adapter.DeleteCommand.Parameters.Add("@ProductID", SqlDbType.Int, 4, "ProductID");
    parameter.SourceVersion = DataRowVersion.Original;

    // Apply changes from the DataSet back to the data source
    DataTable productsTable = dataSet.Tables["Products"];
    if (productsTable != null)
    {
        connection.Open();
        adapter.Update(productsTable);
        Console.WriteLine($"Updated {productsTable.Rows.Count} rows.");
    }
}

Transactions

Transactions are crucial for ensuring data integrity. They allow you to group multiple database operations into a single unit of work. If any operation within the transaction fails, the entire transaction can be rolled back, leaving the database in its original state.


// Assume 'connectionString' is defined

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

    try
    {
        transaction = connection.BeginTransaction("MyTransaction"); // Start transaction

        SqlCommand command1 = new SqlCommand("UPDATE Production.Product SET ListPrice = ListPrice * 1.1 WHERE ProductID = 1;", connection, transaction);
        command1.ExecuteNonQuery();

        SqlCommand command2 = new SqlCommand("INSERT INTO Production.Product (Name, ListPrice) VALUES ('New Gadget', 199.99);", connection, transaction);
        command2.ExecuteNonQuery();

        transaction.Commit(); // Commit the transaction if all commands succeed
        Console.WriteLine("Transaction committed successfully.");
    }
    catch (SqlException ex)
    {
        if (transaction != null)
        {
            transaction.Rollback(); // Rollback the transaction on error
            Console.WriteLine($"Transaction rolled back. Error: {ex.Message}");
        }
    }
}

Always handle exceptions and perform rollbacks to maintain data consistency.

Advanced Topics

ADO.NET offers many more features for complex data scenarios:

  • Asynchronous Operations: Improve application responsiveness by performing data operations in the background.
  • Connection Pooling: Reuses database connections to improve performance.
  • Data Binding: Easily bind data to UI controls in Windows Forms or ASP.NET applications.
  • XML Integration: Support for reading and writing data in XML format.
  • Entity Framework: A higher-level Object-Relational Mapper (ORM) built on top of ADO.NET that simplifies data access further.