MSDN Documentation

ADO.NET Data Persistence

Data persistence refers to the ability of an application to store and retrieve data from a persistent storage medium, such as a database, file, or cloud service. ADO.NET provides a rich set of classes and functionalities to manage data persistence in .NET applications, enabling seamless interaction with various data sources.

Core Concepts of ADO.NET Data Persistence

At its heart, ADO.NET facilitates data persistence through several key components:

• Data Providers: These are specific classes that communicate with a particular data source. For example, SqlClient is used for SQL Server, OracleClient for Oracle, and OleDbClient for OLE DB data sources. Each provider exposes objects for connecting to the database, executing commands, and retrieving data.
• Connections: The DbConnection object (and its provider-specific implementations) represents an open connection to a data source. It manages the communication channel between your application and the database.
• Commands: The DbCommand object (and its provider-specific implementations) represents a SQL statement or a stored procedure to be executed against the data source.
• DataReaders: The DbDataReader object provides a forward-only, read-only stream of data from the data source. It's highly efficient for retrieving large result sets.
• DataSets and DataTables: These are in-memory representations of data, allowing you to work with data disconnected from the source. DataSet can hold multiple DataTable objects, representing tables, and relationships between them.

Common Data Persistence Scenarios

ADO.NET is used in a wide array of data persistence scenarios:

1. Retrieving Data

The most fundamental aspect of data persistence is retrieving data. Using DbDataReader is an efficient way to read data as it becomes available.

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

// ...

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();
    string query = "SELECT CustomerID, CompanyName FROM Customers";
    using (SqlCommand command = new SqlCommand(query, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
            }
        }
    }
}
                

2. Working with DataSets (Disconnected Data)

DataSet and DataTable are invaluable when you need to fetch data, manipulate it independently of the database, and then potentially update the database later. This is known as disconnected data access.

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

// ...

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    string query = "SELECT OrderID, OrderDate FROM Orders WHERE CustomerID = @CustomerID";
    using (SqlDataAdapter adapter = new SqlDataAdapter(query, connection))
    {
        adapter.SelectCommand.Parameters.AddWithValue("@CustomerID", "ALFKI");
        DataSet dataSet = new DataSet();
        adapter.Fill(dataSet, "Orders");

        DataTable ordersTable = dataSet.Tables["Orders"];
        foreach (DataRow row in ordersTable.Rows)
        {
            Console.WriteLine($"Order ID: {row["OrderID"]}, Date: {row["OrderDate"]}");
        }
    }
}
                

3. Inserting, Updating, and Deleting Data

Beyond reading, ADO.NET allows you to modify data in your database. This is often done using SqlCommand with `INSERT`, `UPDATE`, or `DELETE` statements, or by using the `Update` method of a DataAdapter with a DataSet.

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

// ...

string connectionString = "Your_Connection_String";
string insertQuery = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    using (SqlCommand command = new SqlCommand(insertQuery, connection))
    {
        command.Parameters.AddWithValue("@Name", "New Gadget");
        command.Parameters.AddWithValue("@Price", 19.99);

        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"{rowsAffected} row(s) inserted.");
    }
}
                

Advanced Data Persistence Techniques

• Stored Procedures: Executing stored procedures via SqlCommand can offer performance benefits and encapsulate business logic within the database.
• Batch Operations: For performance-critical scenarios, consider techniques like SqlBulkCopy (for SQL Server) to efficiently load large volumes of data.
• Transactions: ADO.NET supports database transactions using SqlTransaction (and its provider-specific counterparts) to ensure data consistency during complex operations that involve multiple steps.

Conclusion

ADO.NET is a foundational technology in the .NET ecosystem for data access and persistence. Understanding its core components and how to use them effectively is crucial for building robust and efficient data-driven applications.