.NET Framework Documentation

ADO.NET Data Access

This tutorial explores how to use ADO.NET to access and manipulate data in your .NET Framework applications. ADO.NET provides a rich set of components for connecting to data sources, executing commands, and retrieving results.

Introduction to ADO.NET

ADO.NET is a set of classes that expose data access services to .NET Framework programming. It is an integral part of the .NET Framework, enabling you to build applications that access data from various data sources, including relational databases, XML, and files.

Key components of ADO.NET include:

• Connections: Establish a link to a data source.
• Commands: Represent SQL statements or stored procedures to be executed.
• DataReaders: Provide a forward-only, read-only stream of data.
• DataSets: In-memory cache of data, allowing for disconnected data manipulation.
• DataAdapters: Bridge between a DataSet and a data source, managing data retrieval and updates.

Connecting to a Data Source

The first step in data access is establishing a connection. The specific connection class depends on the data provider you are using (e.g., SqlConnection for SQL Server, OleDbConnection for OLE DB providers).

Here's a basic example using SqlConnection:

using System.Data.SqlClient;

...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    try
    {
        connection.Open();
        // Connection is open and ready to use
        Console.WriteLine("Connection opened successfully.");
    }
    catch (SqlException ex)
    {
        Console.WriteLine($"Error connecting to database: {ex.Message}");
    }
}

Executing Commands and Retrieving Data

Once connected, you can execute SQL commands. The SqlCommand class is used for this purpose. To retrieve data, you can use a SqlDataReader for efficient, forward-only access.

Using SqlDataReader

SqlDataReader is ideal when you need to read through a result set sequentially. It's highly performant as it doesn't load the entire dataset into memory.

using System.Data.SqlClient;

...

string connectionString = "..."; // As defined above
string query = "SELECT CustomerId, CompanyName FROM Customers";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        try
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader["CustomerId"]}, Name: {reader["CompanyName"]}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error executing query: {ex.Message}");
        }
    }
}

Using DataSets for Disconnected Scenarios

When you need to work with data offline or perform complex manipulations that require random access to rows, a DataSet is a suitable choice. DataSets are in-memory representations of data, containing one or more DataTable objects.

The SqlDataAdapter class is commonly used to populate a DataSet and update the data source.

Populating a DataSet

using System.Data.SqlClient;
using System.Data;

...

string connectionString = "...";
string query = "SELECT * FROM Products";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        DataSet dataset = new DataSet();
        try
        {
            // Open connection is not strictly necessary here for SqlDataAdapter
            // as it manages it internally for Fill operation.
            adapter.Fill(dataset, "ProductsTable");

            // Access data from the DataSet
            if (dataset.Tables.Contains("ProductsTable"))
            {
                DataTable productsTable = dataset.Tables["ProductsTable"];
                foreach (DataRow row in productsTable.Rows)
                {
                    Console.WriteLine($"Product: {row["ProductName"]}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error populating DataSet: {ex.Message}");
        }
    }
}

Executing Non-Query Commands

For operations that don't return a result set, such as INSERT, UPDATE, or DELETE statements, you can use the ExecuteNonQuery method of the SqlCommand class. This method returns the number of rows affected by the command.

using System.Data.SqlClient;

...

string connectionString = "...";
string updateQuery = "UPDATE Products SET UnitPrice = UnitPrice * 1.10 WHERE CategoryID = 1";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(updateQuery, connection))
    {
        try
        {
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"{rowsAffected} rows updated.");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error updating data: {ex.Message}");
        }
    }
}

Parameterized Queries

To prevent SQL injection attacks and improve performance, always use parameterized queries when incorporating user input into SQL statements.

using System.Data.SqlClient;

...

string connectionString = "...";
string query = "SELECT CustomerId, CompanyName FROM Customers WHERE Country = @CountryName";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        string country = "USA";
        command.Parameters.AddWithValue("@CountryName", country);

        try
        {
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"ID: {reader["CustomerId"]}, Name: {reader["CompanyName"]}");
                }
            }
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"Error executing parameterized query: {ex.Message}");
        }
    }
}

Error Handling

Robust error handling is crucial for data access applications. Use try-catch blocks to gracefully handle exceptions that may occur during database operations, such as connection failures, invalid SQL, or constraint violations.

Conclusion

ADO.NET provides a powerful and flexible framework for data access in the .NET Framework. By understanding its core components and best practices, you can build efficient and secure data-driven applications.

Continue exploring topics like transactions, connection pooling, and advanced data binding for more sophisticated data management.