ADO.NET: Database Access, Connections & CRUD Operations

TAKEAWAYS:

• ADO.NET provides disconnected (DataSet) and connected (DataReader) models for database access, each suited to different scenarios (e.g., offline apps vs. high-performance queries).
• Connection strings authenticate and route your app to databases (SQL Server, MySQL, etc.), and parameterized queries prevent SQL injection attacks.
• CRUD operations (Create, Read, Update, Delete) are implemented using SqlCommand with ExecuteNonQuery() (for DML) and ExecuteReader()/ExecuteScalar() (for queries).
• Transactions ensure atomicity in multi-step database operations (e.g., transferring money between bank accounts).
• Stored procedures improve security and performance by offloading logic to the database server.
• Error handling with try-catch blocks is critical for graceful failure in database operations (e.g., network timeouts or constraint violations).

1. Introduction to ADO.NET

ADO.NET is a data access technology in .NET for interacting with databases. It sits between your C# application and databases (SQL Server, Oracle, MySQL, etc.) and provides:

• Data access components: Connection, Command, DataAdapter, DataReader, DataSet.
• Two models:
  • Connected model: Uses SqlConnection and SqlDataReader for real-time, forward-only data access (efficient for large datasets).
  • Disconnected model: Uses DataSet and DataAdapter to cache data locally (ideal for offline apps or complex operations).

Key Classes in ADO.NET

2. Connecting to a Database

To interact with a database, you first establish a connection using a connection string. A connection string includes:

• Server name (e.g., localhost or 127.0.0.1).
• Database name.
• Authentication credentials (username/password or Windows authentication).
• Connection protocol (e.g., SqlClient for SQL Server, MySqlConnection for MySQL).

Example Connection String for SQL Server

string connectionString =
    "Server=localhost;Database=Northwind;User Id=sa;Password=yourpassword;";

Visual: Connection String Anatomy

graph LR
    A["Connection String"] --> B["Server=localhost"]
    A --> C["Database=Northwind"]
    A --> D["User Id=sa"]
    A --> E["Password=yourpassword"]
    A --> F["TrustServerCertificate=True"] <!-- For dev environments -->
    B & C & D & E & F --> G["SqlConnection.Open()"]

Steps to Open a Connection

1. Create a SqlConnection object.
2. Assign the connection string.
3. Call Open() to establish the connection.
4. Use the connection to execute commands.
5. Always close the connection (or use using block) to free resources.

using System.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;...";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open(); // Opens the connection
    Console.WriteLine("Connected to database!");
    // Perform database operations here
} // Connection automatically closed

Why Use using?

• Ensures connection.Close() is called even if an exception occurs.
• Prevents connection leaks, which can exhaust database resources.

3. Executing Commands: SqlCommand

SqlCommand executes SQL statements or stored procedures. Key methods:

• ExecuteNonQuery(): For INSERT, UPDATE, DELETE (returns row count affected).
• ExecuteReader(): For SELECT queries (returns a SqlDataReader).
• ExecuteScalar(): For single-value queries (e.g., SELECT COUNT(*)).

Example: Inserting a Record

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "INSERT INTO Customers (CustomerName, Email) VALUES (@name, @email)";
    SqlCommand command = new SqlCommand(query, connection);

    // Add parameters to prevent SQL injection
    command.Parameters.AddWithValue("@name", "John Doe");
    command.Parameters.AddWithValue("@email", "john@example.com");

    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} row(s) inserted.");
}

Visual: Parameterized Query vs. SQL Injection

graph TD
    A["User Input: '; DROP TABLE Customers; --"] --> B["Unsafe Query"]
    B --> C["SQL Injection Executed"]
    A --> D["Safe Query with Parameters"]
    D --> E["Parameterized: @name = 'Hacker'"]
    E --> F["Query: INSERT INTO Customers (Name) VALUES (@name)"]
    F --> G["Safe Execution"]

Why Parameterized Queries?

• Prevents SQL injection: User input is treated as data, not executable SQL.
• Improves performance: Reuses execution plans for repeated queries.

4. Reading Data: SqlDataReader vs. DataSet

A. SqlDataReader (Connected Model)

• Forward-only, read-only cursor.
• Memory-efficient (streams data row-by-row).
• Best for: Large datasets or real-time processing (e.g., logging, reporting).

Example: Fetching Customers

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT CustomerID, CustomerName FROM Customers";
    SqlCommand command = new SqlCommand(query, connection);
    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CustomerName"]}");
    }
    reader.Close(); // Explicit close (optional if using 'using')
}

Visual: SqlDataReader Data Flow

sequenceDiagram
    participant App as C# Application
    participant DB as SQL Server
    App->>DB: Open Connection
    App->>DB: Execute Query (SELECT)
    loop While reader.Read()
        DB-->>App: Return Row 1
        App->>App: Process Data
        DB-->>App: Return Row 2
        App->>App: Process Data
        ...
    end
    App->>DB: Close Reader
    App->>DB: Close Connection

B. DataSet (Disconnected Model)

• In-memory cache of data (tables, relationships, constraints).
• Supports offline work (e.g., syncing data later).
• Best for: Complex operations (e.g., multi-table joins, sorting, filtering).

Example: Filling a DataSet

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT * FROM Customers";
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Customers"); // Fills "Customers" table in DataSet

    // Access data like a DataTable
    DataTable customersTable = dataSet.Tables["Customers"];
    foreach (DataRow row in customersTable.Rows)
    {
        Console.WriteLine(row["CustomerName"]);
    }
}

Visual: DataSet Structure

classDiagram
    class DataSet {
        +Tables: DataTableCollection
        +Fill() void
        +WriteXml() void
    }
    class DataTable {
        +Rows: DataRowCollection
        +Columns: DataColumnCollection
        +Select() DataRow[]
    }
    class DataRow {
        +Item[] Fields
        +BeginEdit() void
        +AcceptChanges() void
    }
    DataSet "1" --> "1..*" DataTable
    DataTable "1" --> "1..*" DataRow

Comparison: SqlDataReader vs. DataSet

5. CRUD Operations in ADO.NET

A. Create (Insert)

string insertQuery = "INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES (@id, @cid, @date)";
using (SqlCommand command = new SqlCommand(insertQuery, connection))
{
    command.Parameters.AddWithValue("@id", 1001);
    command.Parameters.AddWithValue("@cid", 1);
    command.Parameters.AddWithValue("@date", DateTime.Now);
    command.ExecuteNonQuery();
}

B. Read (Select)

string selectQuery = "SELECT * FROM Orders WHERE CustomerID = @cid";
using (SqlCommand command = new SqlCommand(selectQuery, connection))
{
    command.Parameters.AddWithValue("@cid", 1);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"OrderID: {reader["OrderID"]}, Date: {reader["OrderDate"]}");
    }
}

C. Update

string updateQuery = "UPDATE Orders SET Status = @status WHERE OrderID = @id";
using (SqlCommand command = new SqlCommand(updateQuery, connection))
{
    command.Parameters.AddWithValue("@status", "Shipped");
    command.Parameters.AddWithValue("@id", 1001);
    command.ExecuteNonQuery();
}

D. Delete

string deleteQuery = "DELETE FROM Orders WHERE OrderID = @id";
using (SqlCommand command = new SqlCommand(deleteQuery, connection))
{
    command.Parameters.AddWithValue("@id", 1001);
    command.ExecuteNonQuery();
}

Visual: CRUD Workflow

flowchart TD
    A["Start"] --> B["Create: INSERT"]
    B --> C["Read: SELECT"]
    C --> D["Update: UPDATE"]
    D --> E["Delete: DELETE"]
    E --> F["Loop or End"]

6. Transactions

Transactions ensure atomicity (all operations succeed or fail together). Use SqlTransaction:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlTransaction transaction = connection.BeginTransaction();

    try
    {
        string withdrawQuery = "UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1";
        string depositQuery = "UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2";

        using (SqlCommand cmd1 = new SqlCommand(withdrawQuery, connection, transaction))
        using (SqlCommand cmd2 = new SqlCommand(depositQuery, connection, transaction))
        {
            cmd1.ExecuteNonQuery();
            cmd2.ExecuteNonQuery();
            transaction.Commit(); // All good!
        }
    }
    catch (Exception ex)
    {
        transaction.Rollback(); // Undo changes
        Console.WriteLine($"Error: {ex.Message}");
    }
}

Real-World Example: Bank Transfer

• Scenario: Transfer ₹1000 from Account A to Account B.
• Problem: Without a transaction, Account A might be debited but Account B not credited (inconsistent state).
• Solution: Use a transaction to ensure both updates happen or neither does.

7. Stored Procedures

Stored procedures are precompiled SQL scripts stored in the database. Benefits:

• Security: Reduce exposure of database schema.
• Performance: Compiled once, executed faster.
• Reusability: Call from multiple applications.

Example: Calling a Stored Procedure

string spQuery = "EXEC GetCustomerOrders @customerId";
using (SqlCommand command = new SqlCommand(spQuery, connection))
{
    command.Parameters.AddWithValue("@customerId", 1);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        Console.WriteLine($"Order: {reader["OrderID"]}, Amount: {reader["Amount"]}");
    }
}

Visual: Stored Procedure Flow

sequenceDiagram
    participant App as C# App
    participant DB as SQL Server
    App->>DB: EXEC GetCustomerOrders @customerId
    DB-->>App: Returns Order1, Order2, ...
    App->>App: Display Orders

8. Error Handling

Database operations can fail due to:

• Network issues (connection timeouts).
• Constraint violations (e.g., duplicate key).
• Syntax errors (invalid SQL).

Best Practices:

1. Use try-catch-finally blocks.
2. Log errors for debugging.
3. Provide user-friendly messages.

try
{
    connection.Open();
    // Database operations here
}
catch (SqlException ex)
{
    Console.WriteLine($"Database Error: {ex.Number} - {ex.Message}");
    // Log to a file or error tracking system
}
catch (Exception ex)
{
    Console.WriteLine($"General Error: {ex.Message}");
}
finally
{
    connection.Close(); // Ensure connection is closed
}

In the Real World

ADO.NET powers critical systems in Nepal and globally. Here’s how companies use these concepts:

1. eSewa (Nepal)

   • Idea Used: Transactions and Stored Procedures
   • How: When you transfer money from your eSewa wallet to another user, the system uses a transaction to deduct from your balance and add to the recipient’s. Stored procedures validate the transfer (e.g., sufficient balance, correct recipient ID) before executing.

2. Khalti (Nepal)

   • Idea Used: Parameterized Queries and SqlDataReader
   • How: Khalti’s backend fetches user transaction history using SqlDataReader for real-time data streaming. Parameterized queries ensure hackers can’t inject malicious SQL (e.g., stealing all user data).

3. NTC (Nepal Telecommunications)

   • Idea Used: DataSet for Offline Sync
   • How: NTC’s mobile app caches customer complaints in a DataSet when offline. When the user reconnects, the app syncs changes back to the central database using DataAdapter.

4. Daraz (Global)

   • Idea Used: CRUD Operations and Transactions
   • How: When you place an order, Daraz’s system:
     • Creates a new order record (CRUD).
     • Updates inventory levels (transaction to avoid overselling).
     • Reads your shipping address from the database.
   • If inventory is insufficient, the transaction rolls back, and you see an "out of stock" error.

5. Banking Apps (Global)

   • Idea Used: Stored Procedures for Security
   • How: Banks use stored procedures for sensitive operations like password changes or fund transfers. This hides the underlying SQL logic, reducing the risk of exposure if the app’s code is leaked.

Exam Tip

ADO.NET is a high-weightage unit in TU exams. Focus on these common exam patterns:

1. Short Questions (2-5 marks)

   • Define SqlConnection, DataAdapter, or DataSet.
   • Difference between ExecuteNonQuery() and ExecuteReader().
   • When to use transactions.

2. Programming Questions (10-15 marks)

   • Write code to:
     • Connect to a database and execute a query.
     • Insert/update/delete records with parameters.
     • Fill a DataSet or use SqlDataReader.
     • Handle exceptions in database operations.
   • Trace the output for given inputs (e.g., show the DataTable after filling).

3. Scenario-Based Questions (5-10 marks)

   • Design a database access layer for a scenario (e.g., "A hospital management system needs to log patient visits").
   • Explain how you’d use transactions for a bank transfer.
   • Compare SqlDataReader and DataSet for a given use case.

Common Pitfalls to Avoid:

• Forgetting to close connections (leads to resource leaks).
• Using string concatenation for SQL queries (SQL injection risk).
• Not handling exceptions (e.g., SqlException).
• Confusing DataSet (disconnected) with SqlDataReader (connected).

Pro Tip:

• Memorize the syntax for connection strings, SqlCommand methods, and DataAdapter usage.
• Practice tracing code execution (e.g., what’s in the DataTable after adapter.Fill()?).
• Know the advantages/disadvantages of connected vs. disconnected models.

Worked Example: Order Processing System

Scenario: A retail app needs to:

1. Add a new order to the database.
2. Update inventory.
3. Send a confirmation email (simulated here).

Solution:

public void ProcessOrder(Order order)
{
    string connectionString = "Server=localhost;Database=RetailDB;...";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlTransaction transaction = connection.BeginTransaction();

        try
        {
            // 1. Insert order
            string insertOrderQuery = "INSERT INTO Orders (OrderID, CustomerID, TotalAmount) VALUES (@id, @cid, @amount)";
            using (SqlCommand cmd = new SqlCommand(insertOrderQuery, connection, transaction))
            {
                cmd.Parameters.AddWithValue("@id", order.OrderID);
                cmd.Parameters.AddWithValue("@cid", order.CustomerID);
                cmd.Parameters.AddWithValue("@amount", order.TotalAmount);
                cmd.ExecuteNonQuery();
            }

            // 2. Update inventory (for each item in the order)
            foreach (var item in order.Items)
            {
                string updateInventoryQuery = "UPDATE Products SET Stock = Stock - @quantity WHERE ProductID = @pid";
                using (SqlCommand cmd = new SqlCommand(updateInventoryQuery, connection, transaction))
                {
                    cmd.Parameters.AddWithValue("@quantity", item.Quantity);
                    cmd.Parameters.AddWithValue("@pid", item.ProductID);
                    cmd.ExecuteNonQuery();
                }
            }

            // 3. Simulate email sending (in a real app, this would be async)
            Console.WriteLine($"Order {order.OrderID} processed. Email sent to {order.CustomerID}.");

            transaction.Commit();
            Console.WriteLine("Transaction committed successfully.");
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            Console.WriteLine($"Error processing order: {ex.Message}");
            throw; // Re-throw for the caller to handle
        }
    }
}

Trace for Input:

Real-World Tie-In: This mirrors how Daraz or Amazon process orders:

• Atomicity: If inventory update fails, the order isn’t created (no partial orders).
• Performance: Transactions batch multiple operations for efficiency.
• Security: Parameterized queries prevent SQL injection from malicious order data.