ADO.NET Basics

This tutorial covers the fundamental concepts and components of ADO.NET, the data access technology in the .NET Framework. ADO.NET provides a rich set of classes for working with data from various sources, primarily relational databases.

Key Components of ADO.NET

ADO.NET consists of several core namespaces and classes that work together to enable data access. The most important ones include:

• System.Data.Common: Provides base classes that are shared by managed data providers.
• System.Data.SqlClient: Specifically for interacting with Microsoft SQL Server.
• System.Data.OleDb: For accessing data sources through OLE DB, such as Microsoft Access.
• System.Data.Odbc: For accessing data sources through ODBC drivers.

The ADO.NET Object Model

The ADO.NET object model is designed around two main categories of objects:

1. Connected Data Access Objects: These objects are used to establish a connection to a data source, execute commands, and retrieve data in a forward-only, read-only manner.
2. Disconnected Data Access Objects: These objects allow you to retrieve data and then work with it without maintaining an active connection to the data source. This is useful for applications that need to perform operations on data independently of the database.

Connected Data Access Objects

The primary objects for connected data access are:

• Connection: Represents an open connection to a data source. It manages the connection string and provides methods for opening and closing the connection.
• Command: Represents a SQL statement or stored procedure to be executed against a data source.
• DataReader: Provides a way to retrieve a stream of rows from a data source. It's highly efficient for reading data sequentially.

Disconnected Data Access Objects

The primary objects for disconnected data access are:

• DataSet: A in-memory representation of data that can hold multiple tables, relationships, and constraints. It's an integral part of the disconnected model.
• DataAdapter: Acts as a bridge between a DataSet and a data source. It retrieves data from the data source to fill a DataSet and resolves changes made to the DataSet back to the data source.

A Simple ADO.NET Example

Let's look at a basic example of how to connect to a SQL Server database and retrieve data using a SqlDataReader.

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

public class AdonetBasics
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
        string queryString = "SELECT ProductID, ProductName, UnitPrice FROM dbo.Products;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);
            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }
                reader.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}

Common Operations

Key operations you'll perform with ADO.NET include:

• Establishing a connection to the data source.
• Executing SQL queries or stored procedures.
• Retrieving data using DataReader or DataSet.
• Updating data in the data source.
• Managing transactions to ensure data integrity.

This overview provides a foundation for understanding ADO.NET. The following sections will delve deeper into specific components and advanced techniques.