A Comprehensive Guide to ADO.NET: Data Access in C#

ADO.NET (Active Data Objects for .NET) is a powerful data access technology that provides a set of classes for connecting to databases, executing SQL queries, and manipulating data. This guide will walk you through the essentials of ADO.NET with multiple examples to help you effectively work with databases in C# applications.

ADO.NET consists of key classes like SqlConnection, SqlCommand, SqlDataReader, DataSet, and more.

1. Connecting to a Database

Connecting to a database is the first step in ADO.NET. You can use the SqlConnection class to establish a connection to a SQL Server database.

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=True";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            Console.WriteLine("Connected to the database!");
        }
    }
}

2. Executing SQL Queries

Once connected, you can execute SQL queries using the SqlCommand class. Here's an example of executing an INSERT query:

string insertQuery = "INSERT INTO Employees (FirstName, LastName) VALUES (@FirstName, @LastName)";
using (SqlCommand command = new SqlCommand(insertQuery, connection))
{
    command.Parameters.AddWithValue("@FirstName", "John");
    command.Parameters.AddWithValue("@LastName", "Doe");
    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"Rows affected: {rowsAffected}");
}

3. Retrieving Data with DataReaders

To retrieve data, you can use the SqlDataReader class. Here's an example of retrieving data from the Employees table:

string selectQuery = "SELECT FirstName, LastName FROM Employees";
using (SqlCommand command = new SqlCommand(selectQuery, connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"Name: {reader["FirstName"]} {reader["LastName"]}");
        }
    }
}

4. Working with DataSets and DataAdapters

DataSets and DataAdapters provide a disconnected way of working with data. DataAdapters are used to fill a DataSet with data from the database and update changes back to the database.

string selectQuery = "SELECT FirstName, LastName FROM Employees";
DataSet dataSet = new DataSet();
using (SqlDataAdapter adapter = new SqlDataAdapter(selectQuery, connection))
{
    adapter.Fill(dataSet, "Employees");
}

DataTable employeesTable = dataSet.Tables["Employees"];
foreach (DataRow row in employeesTable.Rows)
{
    Console.WriteLine($"Name: {row["FirstName"]} {row["LastName"]}");
}

5. Parameterized Queries and Stored Procedures

Using parameterized queries and stored procedures is crucial for security and performance. Here's an example of a parameterized query:

 var spName = "GetEmployeesByDepartment";
 using (SqlCommand command = new SqlCommand(spName, connection))
 {
     command.CommandType = CommandType.StoredProcedure;
     command.Parameters.AddWithValue("@Department", "IT");

     using (SqlDataReader reader = command.ExecuteReader())
     {
          while (reader.Read())
          {
              Console.WriteLine($"Name: {reader["FirstName"]} {reader["LastName"]}");
          }
     }
  }

6. Transactions in ADO.NET

Transactions ensure the atomicity of multiple database operations. Here's an example of using a transaction:

using (SqlTransaction transaction = connection.BeginTransaction())
{
    try
    {
        // Execute multiple queries within the transaction
        transaction.Commit(); // Commit the transaction if successful
    }
    catch (Exception)
    {
        transaction.Rollback(); // Rollback the transaction on failure
    }
}

7. Error Handling and Exception Management

Proper error handling is essential in ADO.NET to catch and manage exceptions. Use try-catch blocks to handle exceptions:

try
{
    // ADO.NET code
}
catch (SqlException ex)
{
    Console.WriteLine("SQL Exception: " + ex.Message);
}
catch (Exception ex)
{
    Console.WriteLine("General Exception: " + ex.Message);
}
finally
{
    // Clean up resources if needed
}

Conclusion

ADO.NET empowers C# developers with the tools to effectively work with databases. By mastering the core classes like SqlConnection, SqlCommand, and SqlDataReader, and understanding advanced concepts like transactions and error handling, you'll be equipped to build robust and efficient data-driven applications.