ADO.NET: Database Access in .NET with Connections, Commands, and Data Adapters

TAKEAWAYS:

• ADO.NET uses Connection, Command, DataReader, and DataAdapter objects to interact with databases like SQL Server or MySQL.
• Disconnected architecture (DataSet/DataTable) is ideal for offline processing, while connected (DataReader) is faster for read-heavy apps.
• Transactions ensure atomicity in multi-step operations (e.g., bank transfers).
• Parameterized queries prevent SQL injection—critical for security in apps like eSewa.
• Stored procedures improve performance and reduce network traffic.
• LINQ to SQL bridges C# and SQL for type-safe queries.

---

### Core Concepts: ADO.NET Architecture
ADO.NET is Microsoft’s data access technology for .NET apps. It provides a **disconnected** model (DataSet/DataTable) and a **connected** model (DataReader) to interact with databases. The key components are:

```figure
{"type":"network","nodes":["Connection","Command","DataReader","DataAdapter","DataSet"],"edges":[["Connection","Command","Creates"],["Command","DataReader","Returns"],["Command","DataAdapter","Used by"],["DataAdapter","DataSet","Fills/Updates"]],"directed":true,"caption":"ADO.NET component interactions (disconnected model: DataSet/DataTable; connected model: DataReader)"}

1. Connection Object

• Represents a live connection to a database (SQL Server, MySQL, Oracle).
• Key properties:
  • ConnectionString: Contains server, database, credentials (e.g., "Server=localhost;Database=Northwind;User Id=sa;Password=123").
  • State: Tracks if the connection is open/closed.
• Example:

  SqlConnection conn = new SqlConnection("Server=localhost;Database=TestDB;Integrated Security=True;");
  conn.Open(); // Opens the connection
  

2. Command Object

• Executes SQL commands or stored procedures.
• Methods:
  • ExecuteNonQuery(): For INSERT, UPDATE, DELETE (returns row count).
  • ExecuteReader(): For SELECT (returns a DataReader).
  • ExecuteScalar(): For single-value queries (e.g., COUNT(*)).
• Parameterized Queries (prevent SQL injection):

  SqlCommand cmd = new SqlCommand("SELECT * FROM Users WHERE Username=@user", conn);
  cmd.Parameters.AddWithValue("@user", "john_doe");
  

In the Real World

1. eSewa (Nepal)

   • Idea Used: Transactions and Parameterized Queries
   • How: When you pay a bill, eSewa deducts money from your account atomically (either both deduction and credit succeed, or neither does). It uses SqlTransaction to group multiple SQL commands into a single unit.
   • Example:

     BEGIN TRANSACTION
     UPDATE Accounts SET Balance = Balance - 1000 WHERE UserId = 1;
     UPDATE MerchantAccounts SET Balance = Balance + 1000 WHERE MerchantId = 5;
     COMMIT;
     

2. Ncell Recharge System

   • Idea Used: Stored Procedures and DataReader
   • How: Ncell’s backend uses stored procedures to validate recharge requests quickly. A DataReader streams recharge data (e.g., customer ID, amount) without loading entire tables into memory.
   • Example Stored Procedure:

     CREATE PROCEDURE ValidateRecharge
         @CustomerId INT, @Amount DECIMAL(10,2)
     AS
     BEGIN
         IF EXISTS (SELECT 1 FROM Customers WHERE Id = @CustomerId AND Balance >= @Amount)
             PRINT 'Success';
         ELSE
             PRINT 'Insufficient Balance';
     END
     

3. Daraz Order Processing

   • Idea Used: DataAdapter + DataSet (Disconnected Model)
   • How: Daraz’s mobile app fetches order details offline (using DataSet) and syncs with the server later. This avoids constant network calls in low-connectivity areas.
   • Code Trace:

     SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Orders WHERE UserId=1", conn);
     DataSet ds = new DataSet();
     adapter.Fill(ds, "Orders"); // Fills DataSet offline
     

Connected vs. Disconnected Models

Step-by-Step: Fetching Data with SqlDataReader

Scenario: Display all users from a Users table in a console app.

using System;
using System.Data.SqlClient;

class Program {
    static void Main() {
        string connStr = "Server=localhost;Database=TestDB;Integrated Security=True;";
        using (SqlConnection conn = new SqlConnection(connStr)) {
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn);
            SqlDataReader reader = cmd.ExecuteReader();

            // Step 1: Check if there’s data
            if (reader.HasRows) {
                while (reader.Read()) { // Moves to next row
                    Console.WriteLine($"ID: {reader["Id"]}, Name: {reader["Username"]}");
                }
            }
            reader.Close(); // Critical: Releases resources
        }
    }
}

State After Each Step

1. After conn.Open():

flowchart LR
  A["SqlConnection"] -->|"State=Open"| B[Database
(Connection Established)]

2. After ExecuteReader():

flowchart TD
  A[SqlCommand
(ExecuteReader(""))] -->|"Opens Stream"| B[SqlDataReader
(Forward-only, Read-only)]
  B -->|"Current Position"| C[Users Table
Row 1: john_doe]

3. After reader.Read() (first iteration):

   Id	Username	Email
   2	jane_smith	jane@example.com

Handling Transactions (Bank Transfer Example)

Scenario: Transfer ₹1000 from Account A to Account B in a bank app.

using (SqlConnection conn = new SqlConnection(connStr)) {
    conn.Open();
    SqlTransaction transaction = conn.BeginTransaction(); // Start transaction

    try {
        SqlCommand cmd1 = new SqlCommand(
            "UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountId = 1",
            conn, transaction);
        SqlCommand cmd2 = new SqlCommand(
            "UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountId = 2",
            conn, transaction);

        cmd1.ExecuteNonQuery();
        cmd2.ExecuteNonQuery();
        transaction.Commit(); // All commands succeed
        Console.WriteLine("Transfer successful!");
    }
    catch (Exception) {
        transaction.Rollback(); // Undo if any command fails
        Console.WriteLine("Transfer failed!");
    }
}

State After Each Step

1. After BeginTransaction():

flowchart TD
  A[SqlConnection
(BeginTransaction(""))] -->|Transaction
Pending| B[Database
(Locks Acquired)]
  B -->|"Uncommitted"| C[Accounts Table
(No Changes)]

2. After Commit() (Success):

   AccountId	Balance

Stored Procedures: Why Use Them?

Example: Stored procedure for user login.

CREATE PROCEDURE AuthenticateUser
    @Username NVARCHAR(50), @Password NVARCHAR(50)
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Users WHERE Username = @Username AND Password = @Password)
        SELECT 'Success' AS Result;
    ELSE
        SELECT 'Failed' AS Result;
END

C# Call:

SqlCommand cmd = new SqlCommand("AuthenticateUser", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", "admin");
cmd.Parameters.AddWithValue("@Password", "pass123");
object result = cmd.ExecuteScalar();
Console.WriteLine(result); // "Success" or "Failed"

LINQ to SQL: Bridging C# and SQL

LINQ to SQL maps C# classes to database tables, enabling type-safe queries.

Example: Query users with balance > ₹5000.

// 1. Define a DataContext (maps to database)
DataContext db = new DataContext(connStr);

// 2. Define a LINQ query
var highBalanceUsers = from user in db.GetTable<User>()
                       where user.Balance > 5000
                       select user;

// 3. Execute and display
foreach (var user in highBalanceUsers) {
    Console.WriteLine(user.Username);
}

Generated SQL:

SELECT * FROM Users WHERE Balance > 5000

Exam Tip

1. Connection Management:

   • Always use using blocks to auto-close connections (avoids memory leaks).
   • Example:

     using (SqlConnection conn = new SqlConnection(connStr)) { ... }
     

2. Parameterized Queries:

   • Never concatenate strings for SQL (vulnerable to injection).
   • Use @param syntax every time.

3. Transactions:

   • Remember: Commit only if all steps succeed; otherwise, Rollback.

4. DataReader vs. DataAdapter:

   • DataReader: For forward-only, read-heavy operations.
   • DataAdapter: For disconnected scenarios (e.g., WinForms apps).

5. Stored Procedures:

   • Know how to call them in C# (CommandType.StoredProcedure).
   • Understand their advantages (security, performance).

6. LINQ to SQL:

   • Recognize how C# queries translate to SQL.
   • Example: where clauses map to WHERE in SQL.

Visual Summary:

mindmap
  root((ADO.NET))
    Connection
      Open/Close
      ConnectionString
    Command
      ExecuteNonQuery
      ExecuteReader
      Parameters
    DataReader
      Read()
      GetXXX()
    DataAdapter
      Fill(DataSet)
      Update(DataSet)
    Transactions
      BeginTransaction
      Commit/Rollback
    Stored Procedures
      Pre-compiled SQL
      Security
    LINQ to SQL
      Type-safe queries
      DataContext