Database Access using ADO.NET

📖 Theory

Reading and writing, for real

You know the ADO.NET cast; now put them to work on FestConnect's two everyday jobs: reading the list of events to show on a page, and writing a new registration when a student signs up.

Reading uses a Command's ExecuteReader with a DataReader; writing uses ExecuteNonQuery. Along the way comes the single most important habit in database code: use parameters for any value that came from a user. This lesson shows both operations and why that habit is non-negotiable.

🪜 Follow along

The connected read pattern

1. Open a Connection Create a SqlConnection with the connection string and open it (ideally inside a using block that closes it automatically).
2. Create a Command A SqlCommand holding your SELECT statement, tied to the connection.
3. ExecuteReader Call cmd.ExecuteReader() to get a SqlDataReader that streams the rows.
4. Loop with Read() while (reader.Read()) advances one row at a time; read columns by name.
5. Close The using block closes the connection when you are done, even if an error occurs.

⌨️ Practical

Reading FestConnect's events

using (var conn = new SqlConnection(connectionString))
{
    var cmd = new SqlCommand("SELECT Name, EventDate FROM Events", conn);
    conn.Open();

    SqlDataReader reader = cmd.ExecuteReader();
    while (reader.Read())          // true while there is another row
    {
        string name = reader["Name"].ToString();
        // ... add name to a list, or bind it to a control
    }
}   // connection closed automatically here

📖 Theory

Writing: ExecuteNonQuery

To change the database, insert a registration, update a detail, delete a row, you use a Command's ExecuteNonQuery. The name means 'execute a command that is not a query (not a SELECT)'. It runs the statement and returns the number of rows affected.

So a single INSERT that adds one registration returns 1. That return value is handy for confirming the write worked: if you expected to insert one row and ExecuteNonQuery returns 1, you know it succeeded.

⌨️ Practical

Saving a registration, with parameters

using (var conn = new SqlConnection(connectionString))
{
    // @name and @eventId are PLACEHOLDERS, not the actual values
    var cmd = new SqlCommand(
        "INSERT INTO Registrations (StudentName, EventId) VALUES (@name, @eventId)",
        conn);

    // Supply the user's values as parameters (safe)
    cmd.Parameters.AddWithValue("@name", txtName.Text);
    cmd.Parameters.AddWithValue("@eventId", selectedEventId);

    conn.Open();
    int rows = cmd.ExecuteNonQuery();   // returns 1 for one inserted row
    lblStatus.Text = rows == 1 ? "Registered!" : "Something went wrong.";
}

⚠️ Watch out

Never glue user input into SQL

The single most dangerous mistake in database code is building SQL by concatenating user input, like "... VALUES ('" + txtName.Text + "')". A visitor could type SQL of their own into that box and your query would run it, an attack called SQL injection (you met it in DBMS and in the PHP unit).

Always use parameters (@name, added via cmd.Parameters) instead. ADO.NET then sends the user's text strictly as a value, never as executable SQL, so injection cannot happen. Parameterise every user-supplied value, every time. And always close the connection (a using block guarantees it).

🧩 Quiz

To save one new registration row, you run an INSERT. Which method do you call, and what does it return?

1. ExecuteReader, which returns the inserted row as a DataReader
2. ExecuteNonQuery, which runs the INSERT and returns the number of rows affected (1 for a single insert)
3. ExecuteScalar, which returns the whole Registrations table
4. DataBind, which inserts the row and refreshes the page

💭 Think first

Exactly how do parameters stop SQL injection?

Why is @name safe when "'" + txtName.Text + "'" is dangerous, even though both end up using the user's text? Then tap.

✅ Summary

Key takeaways

• Read with Connection + Command + ExecuteReader: get a DataReader and loop while reader.Read() advances through the rows.
• Change data (insert, update, delete) with ExecuteNonQuery, which returns the number of rows affected: 1 for a single insert.
• ExecuteScalar returns a single value (like a COUNT); ExecuteReader returns rows; ExecuteNonQuery returns a count.
• Always use parameterised queries (@name via cmd.Parameters) for user input; never concatenate input into SQL.
• Concatenation allows SQL injection; parameters send user text strictly as data, so it can never run as SQL.
• Always close the connection; a using block does this automatically, even on error.
• Memory hook: read with ExecuteReader, change with ExecuteNonQuery, and parameterise every user value.