Chapter 20 : Data Access with ADO.NET - 2 ( Connection , Create Database and Table , CRUD , Transaction , Parameterized Queries )

Connection

string connStr = "Server=localhost;Port=3306;Database=inventory;User=root;Password=myPassword;";
using (MySqlConnection conn = new MySqlConnection(connStr))
 {
     conn.Open();
     Console.WriteLine($"Connected: {conn.Database}");
     Console.WriteLine($"Version: {conn.ServerVersion}");
     Console.WriteLine($"State: {conn.State}");
 }
// conn.Dispose() auto-called — connection returned to pool

Create Database and Table

conn.Open();
new MySqlCommand("CREATE DATABASE IF NOT EXISTS inventory;", conn)
 .ExecuteNonQuery();
conn.ChangeDatabase("inventory");
string createTable = @"
CREATE TABLE IF NOT EXISTS Cars (
CarId INT AUTO_INCREMENT PRIMARY KEY,
Make VARCHAR(50) NOT NULL,
Color VARCHAR(50) NOT NULL,
PetName VARCHAR(50),
Price DECIMAL(10,2) DEFAULT 0
);";
new MySqlCommand(createTable, conn).ExecuteNonQuery();

Command Object - 3 Execute Methods

// ExecuteNonQuery() -> INSERT/UPDATE/DELETE (returns row count)
int rows = cmd.ExecuteNonQuery();
// ExecuteScalar() -> single value (first col, first row)
object count = new MySqlCommand("SELECT COUNT(*) FROM Cars;", conn)
 .ExecuteScalar();
// ExecuteReader() -> multiple rows (DataReader)
MySqlDataReader reader = cmd.ExecuteReader();

DataReader — Connected, Forward-Only

Connection stays open while reading. Can only go forward. Fastest way to read data.

var cmd = new MySqlCommand("SELECT * FROM Cars;", conn);
using (MySqlDataReader reader = cmd.ExecuteReader())
 {
     while (reader.Read())
     {
         // By index:
         Console.WriteLine($"{reader[0]} | {reader[1]}");
         // By column name:
         Console.WriteLine($"{reader["CarId"]} | {reader["Make"]}");
         // Strongly typed:
         int id = reader.GetInt32("CarId");
         string make = reader.GetString("Make");
         decimal price = reader.GetDecimal("Price");
     }
 }

Parameterized Queries — SQL Injection Prevention

NEVER concatenate user input into SQL. ALWAYS use parameters.

// BAD — SQL Injection!
// string bad = $"SELECT * FROM Cars WHERE Make = '{userInput}'";
// GOOD — Parameterized:
var cmd = new MySqlCommand("SELECT * FROM Cars WHERE Make = @make;", conn);
cmd.Parameters.AddWithValue("@make", "BMW");
// INSERT with parameters:
var ins = new MySqlCommand(
"INSERT INTO Cars (Make, Color, PetName, Price) "
 + "VALUES (@make, @color, @name, @price);", conn);
ins.Parameters.AddWithValue("@make", "Toyota");
ins.Parameters.AddWithValue("@color", "White");
ins.Parameters.AddWithValue("@name", "Ghost");
ins.Parameters.AddWithValue("@price", 28000);
ins.ExecuteNonQuery();
// UPDATE:
var upd = new MySqlCommand(
"UPDATE Cars SET Color = @c WHERE CarId = @id;", conn);
upd.Parameters.AddWithValue("@c", "Red");
upd.Parameters.AddWithValue("@id", 1);
upd.ExecuteNonQuery();
// DELETE:
var del = new MySqlCommand(
"DELETE FROM Cars WHERE CarId = @id;", conn);
del.Parameters.AddWithValue("@id", 5);
del.ExecuteNonQuery();

Transactions — All or Nothing

conn.Open();
MySqlTransaction tx = conn.BeginTransaction();
try
 {
     var cmd1 = new MySqlCommand(
     "UPDATE Cars SET Price = Price - 5000 WHERE CarId = 1;",
     conn, tx);
     cmd1.ExecuteNonQuery();
     var cmd2 = new MySqlCommand(
     "UPDATE Cars SET Price = Price + 5000 WHERE CarId = 2;",
     conn, tx);
     cmd2.ExecuteNonQuery();
     tx.Commit(); // both succeeded
 }
catch (Exception ex)
 {
     tx.Rollback(); // something failed, undo everything
 }

Disconnected Layer — DataAdapter + DataSet

DataReader = connected, fast, forward-only. DataSet = disconnected, in-memory cache, editable.

MySqlDataAdapter adapter = new MySqlDataAdapter("SELECT * FROM Cars;", conn);
DataSet ds = new DataSet("InventoryDB");
adapter.Fill(ds, "Cars");
// Connection CLOSED — work with in-memory data
DataTable table = ds.Tables["Cars"];
foreach (DataRow row in table.Rows)
     Console.WriteLine($"{row["CarId"]} | {row["Make"]}");
// Modify in-memory:
table.Rows[0]["Color"] = "Purple";
// Push changes back:
MySqlCommandBuilder builder = new(adapter);
adapter.Update(ds, "Cars");

Async Versions (Modern Way)

using (var conn = new MySqlConnection(connStr))
 {
     await conn.OpenAsync();
     var cmd = new MySqlCommand("SELECT * FROM Cars;", conn);
     using (var reader = await cmd.ExecuteReaderAsync())
     {
         while (await reader.ReadAsync())
             Console.WriteLine($"{reader["Make"]}");
     }
 }

Helper Class — Repository Pattern

public class CarRepository
 {
     private readonly string _cs;
     public CarRepository(string cs) => _cs = cs;
     public async Task<List<Car>> GetAllAsync()
     {
         var cars = new List<Car>();
         using var conn = new MySqlConnection(_cs);
         await conn.OpenAsync();
         var cmd = new MySqlCommand("SELECT * FROM Cars;", conn);
         using var r = await cmd.ExecuteReaderAsync();
         while (await r.ReadAsync())
             cars.Add(new Car
             {
                 CarId = r.GetInt32("CarId"),
                 Make = r.GetString("Make"),
                 Color = r.GetString("Color")
             });
         return cars;
     }
     public async Task InsertAsync(Car c)
     {
         using var conn = new MySqlConnection(_cs);
         await conn.OpenAsync();
         var cmd = new MySqlCommand(
         "INSERT INTO Cars (Make,Color,PetName,Price) "
         + "VALUES (@m,@c,@n,@p);", conn);
         cmd.Parameters.AddWithValue("@m", c.Make);
         cmd.Parameters.AddWithValue("@c", c.Color);
         cmd.Parameters.AddWithValue("@n", c.PetName);
         cmd.Parameters.AddWithValue("@p", c.Price);
         await cmd.ExecuteNonQueryAsync();
     }
 }