Database Programming with ADO.NET

# CHAPTER 25

Database Programming with ADO.NET #

1. Introduction #

2. Learning Objectives #

• Understand the Connection String.

• Use SqlConnection to connect to a database.

• Execute SQL queries using SqlCommand.

• Read database rows using SqlDataReader.

• Prevent SQL Injection using Parameters.

3. Required NuGet Package #

4. The Connection String #

// Example string for a local SQL Server Express database
string connString = "Server=localhost\\SQLEXPRESS;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

5. Connecting and Reading Data #

using System;
using Microsoft.Data.SqlClient; // Required!

class Program
{
    static void Main()
    {
        string connString = "Server=localhost;Database=SchoolDB;Integrated Security=True;TrustServerCertificate=True;";

        // 1. Create Connection inside a using block (auto-closes)
        using (SqlConnection conn = new SqlConnection(connString))
        {
            // 2. Open the connection
            conn.Open();
            Console.WriteLine("Database Connected!");

            // 3. Write the SQL Query
            string query = "SELECT Id, Name FROM Students";

            // 4. Execute the Command
            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                // 5. Read the Results
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // Access columns by Index or Name
                        Console.WriteLine($"ID: {reader[0]} | Name: {reader["Name"]}");
                    }
                }
            }
        } // Connection automatically closes here!
    }
}

6. Inserting Data (Executing Non-Queries) #

using (SqlConnection conn = new SqlConnection(connString))
{
    conn.Open();
    string insertQuery = "INSERT INTO Students (Name, Age) VALUES ('John', 20)";
    
    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    {
        // ExecuteNonQuery returns the number of rows affected
        int rowsAffected = cmd.ExecuteNonQuery();
        Console.WriteLine($"{rowsAffected} row(s) inserted.");
    }
}

7. Preventing SQL Injection (CRITICAL) #

The Solution: Parameterized Queries.

string insertQuery = "INSERT INTO Students (Name, Age) VALUES (@studentName, @studentAge)";

using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
{
    // Securely bind the user input to the @ parameters
    cmd.Parameters.AddWithValue("@studentName", "Alice");
    cmd.Parameters.AddWithValue("@studentAge", 22);
    
    cmd.ExecuteNonQuery();
}

8. Common Mistakes #

• Leaving Connections Open: If you forget to call conn.Close() (or forget the using block), you will run out of connections in the SQL Server Connection Pool, and your entire application will crash.

• SQL Injection Vulnerabilities: Failing to use cmd.Parameters.AddWithValue is the #1 cause of database hacks.

9. Best Practices #

• Never hardcode the connection string inside Program.cs. In real apps, store it securely in an appsettings.json file or Environment Variables.

• Always wrap ADO.NET objects (SqlConnection, SqlCommand, SqlDataReader) in using blocks.

10. Exercises #

1. 1. Write a C# program that connects to a local SQL database and runs an UPDATE query to change a student's age based on their ID. Use parameterized queries.

11. MCQs with Answers #

12. Interview Questions #

• Q: Differentiate between ExecuteReader(), ExecuteNonQuery(), and ExecuteScalar().

• Q: Explain what Connection Pooling is and why it matters in ADO.NET.

• Q: Demonstrate how to write a SQL query in C# that is 100% immune to SQL injection.

13. Summary #

14. Next Chapter Recommendation #