ADO.NET Fundamentals

Level: Intermediate

ℹ️ Where This Fits

ADO.NET is manual database access. Learn this to understand how databases work before moving to Dapper or EF Core ORMs. Essential for complex queries, stored procedures, and performance-critical code.

ℹ️ What You'll Learn

• ADO.NET purpose: Manual, low-level database access (full control, more code, requires manual mapping)
• SqlConnection: Open connection with using statement for automatic cleanup (using (var conn = new SqlConnection(connStr)))
• SqlCommand: Execute SQL query or stored procedure (cmd.CommandText = "SELECT * FROM Students")
• SqlParameter: Safe parameter binding prevents SQL injection (cmd.Parameters.AddWithValue("@name", name))
• SqlDataReader: Read query results row by row (lightweight, fast for large datasets, forward-only)
• ExecuteReader(): Returns SqlDataReader for SELECT queries (supports multiple rows)
• ExecuteScalar(): Returns single value (count, sum, ID, MAX date) from SELECT query
• ExecuteNonQuery(): Execute INSERT/UPDATE/DELETE, returns rows affected (use for validation)
• Connection pooling: Reuse connections instead of creating new (connection string controls pool size)
• SQL injection prevention: Always use parameters, never concatenate user input into SQL strings
• CommandType options: CommandType.Text (SQL query), CommandType.StoredProcedure (stored proc), CommandType.TableDirect
• Stored procedures: Call from C# with CommandType.StoredProcedure (sp_CreateStudent, sp_GetStudentsByClass)
• Output parameters: Capture return values from stored procedures (@@Identity, @outParam)
• Transactions: using (var trans = conn.BeginTransaction()) for multi-step operations (create student + fee account + attendance)
• Transaction isolation levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable
• Error handling: SqlException for connection errors, constraint violations, timeout errors (specific error codes)
• Performance tips: Use ExecuteScalar for counts, use DataReader for large result sets, close connections immediately
• School Management queries: Insert student with ID return, select students by class/section, batch update fee status
• Common mistakes: Not closing connections, hardcoded SQL, missing parameter binding, transaction deadlocks
• When to use ADO.NET: Complex queries, stored procedures, raw performance needs, legacy code integration

What is ADO.NET?

ADO = ActiveDataObjects

Manual database access. Direct SQL control. Lower-level than EF Core or Dapper.

ADO.NET ← SqlConnection, SqlCommand
Dapper ← Query builders
EF Core ← Object mapping

Connection String

string connString = "Server=localhost;Database=SmsDb;User Id=sa;Password=YourPassword;";

Store in appsettings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=SmsDb;User Id=sa;Password=..."
  }
}

Basic Query Pattern

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

public class StudentRepository
{
    private readonly string _connString;

    public StudentRepository(IConfiguration config)
    {
        _connString = config.GetConnectionString("DefaultConnection");
    }

    public List<Student> GetStudents()
    {
        var students = new List<Student>();

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

            string query = "SELECT Id, Name, RollNumber, ClassName FROM Students";
            SqlCommand cmd = new SqlCommand(query, conn);

            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    students.Add(new Student
                    {
                        Id = (int)reader["Id"],
                        Name = (string)reader["Name"],
                        RollNumber = (string)reader["RollNumber"],
                        ClassName = (string)reader["ClassName"]
                    });
                }
            }
        }

        return students;
    }
}

Parameterized Queries (SQL Injection Prevention)

public Student GetStudent(int id)
{
    using (SqlConnection conn = new SqlConnection(_connString))
    {
        conn.Open();

        string query = "SELECT Id, Name, RollNumber FROM Students WHERE Id = @Id";
        SqlCommand cmd = new SqlCommand(query, conn);
        
        // Add parameter safely
        cmd.Parameters.AddWithValue("@Id", id);

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader.Read())
            {
                return new Student
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"],
                    RollNumber = (string)reader["RollNumber"]
                };
            }
        }
    }

    return null;
}

Insert, Update, Delete

public void CreateStudent(Student student)
{
    using (SqlConnection conn = new SqlConnection(_connString))
    {
        conn.Open();

        string query = @"
            INSERT INTO Students (Name, RollNumber, ClassName, Status)
            VALUES (@Name, @RollNumber, @ClassName, @Status)";

        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@Name", student.Name);
        cmd.Parameters.AddWithValue("@RollNumber", student.RollNumber);
        cmd.Parameters.AddWithValue("@ClassName", student.ClassName);
        cmd.Parameters.AddWithValue("@Status", student.Status);

        cmd.ExecuteNonQuery();  // No result returned
    }
}

public void UpdateStudent(Student student)
{
    using (SqlConnection conn = new SqlConnection(_connString))
    {
        conn.Open();

        string query = @"
            UPDATE Students 
            SET Name = @Name, ClassName = @ClassName, Status = @Status
            WHERE Id = @Id";

        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@Id", student.Id);
        cmd.Parameters.AddWithValue("@Name", student.Name);
        cmd.Parameters.AddWithValue("@ClassName", student.ClassName);
        cmd.Parameters.AddWithValue("@Status", student.Status);

        cmd.ExecuteNonQuery();
    }
}

public void DeleteStudent(int id)
{
    using (SqlConnection conn = new SqlConnection(_connString))
    {
        conn.Open();

        string query = "DELETE FROM Students WHERE Id = @Id";
        SqlCommand cmd = new SqlCommand(query, conn);
        cmd.Parameters.AddWithValue("@Id", id);

        cmd.ExecuteNonQuery();
    }
}

Async Operations

public async Task<List<Student>> GetStudentsAsync()
{
    var students = new List<Student>();

    using (SqlConnection conn = new SqlConnection(_connString))
    {
        await conn.OpenAsync();

        string query = "SELECT Id, Name, RollNumber FROM Students";
        SqlCommand cmd = new SqlCommand(query, conn);

        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                students.Add(new Student
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"],
                    RollNumber = (string)reader["RollNumber"]
                });
            }
        }
    }

    return students;
}

Key Takeaways

• ADO.NET = low-level data access
• SqlConnection = establish connection
• SqlCommand = execute query
• Parameters = prevent SQL injection
• ExecuteReader = get data
• ExecuteNonQuery = insert/update/delete
• Async = non-blocking operations

💡 ADO.NET Tip

Always use parameters. Never concatenate SQL strings.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on ADO.NET Fundamentals. Try these prompts:

• "What's the difference between ExecuteReader and ExecuteNonQuery?"
• "How do I prevent SQL injection?"
• "When should I use async operations?"
• "Quiz me on ADO.NET"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.