ADO.NET Data Access

ADO.NET Data Access

ADO.NET is the .NET Framework's data access technology. Unlike classic ADO (which uses connected recordsets), ADO.NET uses a disconnected model with DataSets and DataReaders.

SqlConnection and SqlCommand

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

string connStr = "Server=SQLSERVER01;Database=FlamenetDB;" +
    "Integrated Security=true;";

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();

    // SqlCommand with parameters (prevents SQL injection)
    SqlCommand cmd = new SqlCommand(
        "SELECT EmployeeID, FirstName, LastName, Email " +
        "FROM Employees WHERE DeptID = @DeptID", conn);
    cmd.Parameters.AddWithValue("@DeptID", 3);

    // SqlDataReader (fast, forward-only)
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine("{0} {1} - {2}",
                reader["FirstName"],
                reader["LastName"],
                reader["Email"]);
        }
    }
}

DataSet and DataAdapter

// Disconnected data access with DataSet
DataSet ds = new DataSet();

using (SqlConnection conn = new SqlConnection(connStr))
{
    SqlDataAdapter adapter = new SqlDataAdapter(
        "SELECT * FROM Employees WHERE IsActive = 1", conn);

    // Fill the DataSet (opens and closes connection automatically)
    adapter.Fill(ds, "Employees");
}

// Work with data offline
DataTable dt = ds.Tables["Employees"];
foreach (DataRow row in dt.Rows)
{
    Console.WriteLine(row["FirstName"] + " " + row["LastName"]);
}

// Filter and sort
DataRow[] managers = dt.Select("Title = 'Manager'", "LastName ASC");

Insert with Parameters

using (SqlConnection conn = new SqlConnection(connStr))
{
    conn.Open();
    SqlCommand cmd = new SqlCommand(
        "INSERT INTO Employees (FirstName, LastName, Email, DeptID) " +
        "VALUES (@First, @Last, @Email, @Dept); " +
        "SELECT SCOPE_IDENTITY();", conn);

    cmd.Parameters.AddWithValue("@First", "Jane");
    cmd.Parameters.AddWithValue("@Last", "Doe");
    cmd.Parameters.AddWithValue("@Email", "jdoe@flamenet.io");
    cmd.Parameters.AddWithValue("@Dept", 2);

    int newId = Convert.ToInt32(cmd.ExecuteScalar());
    Console.WriteLine("New ID: " + newId);
}

ADO.NET vs. Classic ADO