ADO.NET API Reference

Introduction to ADO.NET

ADO.NET is a set of classes that expose data access services, such as data manipulation and retrieval, to the .NET Framework. It is an integral part of the .NET Framework and provides a rich set of components for creating distributed applications and tools. ADO.NET can be used to connect to many data sources, including relational databases like SQL Server, Oracle, and MySQL, as well as non-relational data sources like XML files.

Key features of ADO.NET include:

• Connecting to data sources using Data Providers.
• Retrieving data using DataReaders.
• Working with disconnected data using DataSets.
• Managing transactions and concurrency.
• Support for XML data.

Data Providers

ADO.NET uses data providers to access data sources. Each data provider is designed to work with a specific type of data source. Common .NET data providers include:

Core ADO.NET Classes

These classes represent an in-memory cache of data and provide a programming model for manipulating data.

DataSet

A DataSet is an in-memory representation of a database. It is a collection of DataTable objects, each representing a table of data.

DataSet supports features such as:

• Working with multiple tables and relations.
• Handling primary keys and foreign keys.
• Maintaining row states (Added, Modified, Deleted, Unchanged).
• Merging and synchronizing data.

using System.Data;

DataSet dataSet = new DataSet("MyDataSet");
DataTable dataTable = new DataTable("Customers");
dataSet.Tables.Add(dataTable);

// Add columns
dataTable.Columns.Add("CustomerID", typeof(int));
dataTable.Columns.Add("CompanyName", typeof(string));

// Add rows
DataRow newRow = dataTable.NewRow();
newRow["CustomerID"] = 1;
newRow["CompanyName"] = "Acme Corporation";
dataTable.Rows.Add(newRow);
                

DataTable

A DataTable represents a single table of data in memory. It contains a collection of DataRow objects and DataColumn objects.

DataTable is crucial for building disconnected data objects and for working with data that has been retrieved from a data source.

DataRow

A DataRow represents a single row of data within a DataTable. It allows you to access and manipulate the values of individual cells in that row.

DataColumn

A DataColumn represents a column in a DataTable. It defines the data type, constraints, and properties of the data within that column.

DataView

A DataView provides a way to view and interact with data in a DataTable. It allows you to sort, filter, and search rows without modifying the underlying DataTable.

Data Access Classes

These classes are used to connect to a data source, execute commands, and retrieve data.

DbConnection

The base class for ADO.NET connection objects. It represents a connection to a data source.

• SqlConnection (for SQL Server)
• OleDbConnection (for OLE DB providers)
• OdbcConnection (for ODBC providers)

using System.Data.SqlClient;

string connectionString = "Server=myServer;Database=myDatabase;User Id=myUser;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    // Perform database operations
    Console.WriteLine("Connection opened successfully.");
}
                

DbCommand

The base class for ADO.NET command objects. It represents a command to execute against a data source.

• SqlCommand
• OleDbCommand
• OdbcCommand

Commands can be SQL statements, stored procedures, or other commands understood by the data source.

using System.Data.SqlClient;

// Assuming 'connection' is an open SqlConnection

using (SqlCommand command = new SqlCommand("SELECT COUNT(*) FROM Customers", connection))
{
    object result = command.ExecuteScalar();
    Console.WriteLine($"Number of customers: {result}");
}
                

DbDataReader

The base class for ADO.NET data reader objects. It provides a forward-only, read-only stream of data from the data source.

• SqlDataReader
• OleDbDataReader
• OdbcDataReader

DbDataReader is highly efficient for retrieving large result sets.

using System.Data.SqlClient;

// Assuming 'connection' is an open SqlConnection

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

DbParameter

The base class for ADO.NET parameter objects. Parameters are used to pass values into and out of SQL statements or stored procedures, helping to prevent SQL injection attacks.

• SqlParameter
• OleDbParameter
• OdbcParameter

using System.Data.SqlClient;

// Assuming 'connection' is an open SqlConnection

using (SqlCommand command = new SqlCommand("INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)", connection))
{
    command.Parameters.AddWithValue("@Name", "Chai");
    command.Parameters.AddWithValue("@Price", 18.00m);
    command.ExecuteNonQuery();
}
                

Transactions

ADO.NET supports database transactions, ensuring that a series of operations are performed as a single unit of work. Either all operations succeed, or none of them do.

Key classes include DbTransaction and methods like BeginTransaction, Commit, and Rollback.

using System.Data.SqlClient;

// Assuming 'connection' is an open SqlConnection

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        // First operation
        using (SqlCommand command1 = new SqlCommand("UPDATE Stock SET Quantity = Quantity - 1 WHERE ProductID = 1", connection, transaction))
        {
            command1.ExecuteNonQuery();
        }

        // Second operation
        using (SqlCommand command2 = new SqlCommand("INSERT INTO Orders (CustomerID, OrderDate) VALUES (10, GETDATE())", connection, transaction))
        {
            command2.ExecuteNonQuery();
        }

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

XML Integration

ADO.NET provides seamless integration with XML. You can load data directly from an XML file into a DataSet or write a DataSet's contents to an XML file.

Methods like DataSet.ReadXml() and DataSet.WriteXml() are commonly used.

Asynchronous Operations

Modern ADO.NET supports asynchronous operations, allowing you to perform data access without blocking the main thread. This is crucial for responsive applications, especially in UI and web scenarios.

Look for methods ending in Async, such as OpenAsync(), ExecuteReaderAsync(), and SaveChangesAsync().