Key Outcomes from Learning ADO.NET

• Master the fundamental techniques for C# applications to connect to and disconnect from databases.
• Gain proficiency in various data querying methods, including Create, Read, Update, and Delete (CRUD) operations.
• Comprehend the application of Object-Oriented Programming (OOP) principles in data access, such as the significance of writing generic data handling classes.
• Understand and apply object-oriented querying and object encapsulation, which is the most frequently used pattern.

Deployment Architecture for Management Information Systems

A complete application comprises both a client and a database server.

• Client Application: Installed on end-user machines, following a Client/Server (C/S) architecture. There can be multiple clients.
• Database Server: Such as SQL Server, Oracle, or MySQL.
• ADO.NET: A data access technology that facilitates communication between the client and the server.

Understanding ADO.NET

ADO.NET is a collection of object-oriented class libraries within the .NET Framework for interaction between applications and data sources. These data sources are not limited to databases and can include files like Excel spreadsheets, INI files, and text files. In essence, it is a data access component.

Core Components of ADO.NET

ADO.NET primarily consists of two parts:

1. .NET Data Providers

These are used to connect to a database, execute commands, and retrieve results. The four main objects are:

• Connection: Establishes a connection to the data source.
• Command: Executes commands against the data source (e.g., SQL statements).
• DataReader: Provides a forward-only, read-only stream of data from the data source.
• DataAdapter: Populates a DataSet with data from the source and resolves updates back to it.

2. DataSet

An in-memory, disconnected representation of data, independent of any specific data source.

.NET Framework Data Providers

Different databases require different provider classes.

• SQL Server: System.Data.SqlClient
• Access, Excel: System.Data.OleDb

Database Connection Prerequisites

Before connecting, ensure the SQL Server instance is accessible. Using SQL Server Configuraton Manager, verify that the TCP/IP protocol for your MSSQLSERVER instance is enabled and note the port number (default is 1433).

Establishing a Connection

Four essential elements are required: Server IP address/name, Database name, Login username, and Login password. These are combined into a connection string used by the ADO.NET SqlConnection objecct.

The Connection Object

Purpose: Creates a point-to-point link between the application and the database.

Key Property: ConnectionString - encapsulates the connection details.

// SQL Server Authentication (Commonly Used)
string connStr = "Server=192.168.1.5;Database=StudentDB;User Id=appUser;Password=securePwd!";

// Windows Integrated Authentication (Local machine only)
string winAuthConnStr = "Data Source=.;Initial Catalog=StudentDB;Integrated Security=True";

Notes on Connection Strings:

• For a named SQL Server instance, specify the full server name (e.g., ServerName\InstanceName).
• In C# strings, escape backslashes (\\) or use a verbatim string literal prefixed with @.
• For a default instance, you can use . or localhost as the server name.

Key Methods:

• Open(): Opens the database connection.
• Close(): Closes the database connection.

Example: Connecting and Disconnecting

using System.Data.SqlClient;

class DatabaseConnector
{
    static void PerformConnection()
    {
        string connectionString = "Server=localhost;Database=Inventory;User Id=admin;Password=admin123";
        using (SqlConnection dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();
            Console.WriteLine("Database connection established successfully.");
            // ... perform database operations ...
            // Connection is automatically closed when the 'using' block exits.
        }
        Console.WriteLine("Database connection is now closed.");
    }
}

The Command Object

Purpose: Sends SQL statements or stored procedure calls to the database. It encapsulates both a Connection object and the SQL command text.

Key Properties:

• CommandText: The SQL statement or stored procedure name to execute.
• Connection: The SqlConnection object this command uses.

Key Methods:

• ExecuteNonQuery(): Executes INSERT, UPDATE, DELETE statements. Returns the number of rows affected (int).
• ExecuteScalar(): Executes a query and returns the first column of the first row as an object. Ideal for aggregate functions like COUNT(*).
• ExecuteReader(): Executes a query and returns a SqlDataReader for reading result sets.

Using ExecuteNonQuery() for Data Modification

string updateSql = @"UPDATE Products 
                      SET Price = Price * 1.1 
                      WHERE CategoryId = @CategoryId";

using (SqlCommand modifyCommand = new SqlCommand(updateSql, dbConnection))
{
    modifyCommand.Parameters.AddWithValue("@CategoryId", 5);
    int rowsUpdated = modifyCommand.ExecuteNonQuery();
    Console.WriteLine($"Updated {rowsUpdated} product(s).");
}

Debugging Tip: While debugging C# code, you can inspect the final CommandText string, copy it, and run it directly in SQL Server Management Studio to verify its correctness.

Retrieving Auto-Generated Identity Values

After inserting a record into a table with an identity column (like an auto-incrementing ID), you often need to retrieve the newly generated value.

Solution: Append SELECT SCOPE_IDENTITY() to your INSERT statement and execute it with ExecuteScalar().

-- SQL Statement
INSERT INTO Customers (Name, Email) VALUES ('Acme Corp', 'contact@acme.com');
SELECT SCOPE_IDENTITY();

string insertSql = @"INSERT INTO Customers (Name, Email) 
                     VALUES ('Acme Corp', 'contact@acme.com');
                     SELECT SCOPE_IDENTITY();";

object newId = cmd.ExecuteScalar();
int customerId = Convert.ToInt32(newId);

SCOPE_IDENTITY() returns the last identity value generated within the current session and scope.

General Steps for CRUD Operations

1. Instantiate a SqlConnection object with a connection string.
2. Construct the SQL statement (INSERT, UPDATE, DELETE, SELECT).
3. Create a SqlCommand object, associating it with the connection and SQL.
4. Open the connection.
5. Execute the appropriate command method (ExecuteNonQuery, ExecuteScalar, ExecuteReader).
6. Process the results (if any).
7. Close the connection (handled automatically by the using statement).

Common Errors and Troubleshooting

• Connection Failures: Verify SQL Server service is running, connection string server name is correct, firewall settings allow the connection, and login credentials are accurate.
• SQL Statement Errors: Ensure column names are spelled correctly, string and date values are enclosed in single quotes, date formats are valid (YYYY-MM-DD), and the number of parameters matches the columns.

Querying Data

Single-Value Queries with ExecuteScalar()

Use for queries that return a single value, such as COUNT, SUM, or fetching a specific column value.

string countSql = "SELECT COUNT(*) FROM Orders WHERE Status = 'Pending'";
using (SqlCommand countCmd = new SqlCommand(countSql, dbConnection))
{
    dbConnection.Open();
    int pendingCount = (int)countCmd.ExecuteScalar();
    Console.WriteLine($"Pending orders: {pendingCount}");
}

Result Set Queries with ExecuteReader()

Use for queries that return multiple rows. The SqlDataReader provides a fast, forward-only stream of data. Crucial: The connection remains open while reading. Close the DataReader (and then the connection) when finished.

string query = "SELECT ProductId, ProductName, UnitPrice FROM Products";
using (SqlCommand queryCmd = new SqlCommand(query, dbConnection))
{
    dbConnection.Open();
    using (SqlDataReader productReader = queryCmd.ExecuteReader())
    {
        while (productReader.Read())
        {
            int id = (int)productReader["ProductId"];
            string name = (string)productReader["ProductName"];
            decimal price = (decimal)productReader["UnitPrice"];
            Console.WriteLine($"{id}: {name} - ${price}");
        }
    } // DataReader is closed here
} // Connection is closed here

Multiple Result Sets

Execute multiple SELECT statements separated by semicolons. Use NextResult() on the DataReader to advance to the next set.

string multiQuery = "SELECT ProductId, Name FROM Products; SELECT CategoryId, CategoryName FROM Categories";
using (SqlDataReader dr = cmd.ExecuteReader())
{
    // Read first result set (Products)
    while (dr.Read()) { /* ... process products ... */ }

    // Move to the next result set
    if (dr.NextResult())
    {
        // Read second result set (Categories)
        while (dr.Read()) { /* ... process categories ... */ }
    }
}

Code Reusability: Implementing a SQL Helper Class

Repeatedly writing connection and command logic is inefficient. A helper class centralizes this logic.

Principle: Extract constant code into the method body and variable parts into method parameters.

using System.Data.SqlClient;

public static class DbUtility
{
    private static readonly string ConnectionString = "Server=.;Database=AppDB;Integrated Security=True;";

    // Executes a query returning a single value.
    public static object ExecuteScalarQuery(string sqlText)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        using (SqlCommand cmd = new SqlCommand(sqlText, conn))
        {
            conn.Open();
            return cmd.ExecuteScalar();
        }
    }

    // Executes an update (INSERT, UPDATE, DELETE) and returns rows affected.
    public static int ExecuteDataModification(string sqlText)
    {
        using (SqlConnection conn = new SqlConnection(ConnectionString))
        using (SqlCommand cmd = new SqlCommand(sqlText, conn))
        {
            conn.Open();
            return cmd.ExecuteNonQuery();
        }
    }

    // Executes a query and returns a DataReader.
    // Note: The caller is responsible for closing the DataReader and connection.
    // Using CommandBehavior.CloseConnection ensures the connection closes when the reader does.
    public static SqlDataReader ExecuteReaderQuery(string sqlText)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand(sqlText, conn);
        conn.Open();
        return cmd.ExecuteReader(CommandBehavior.CloseConnection);
    }
}

Separation of Concerns: Data Access Layers and Entity Classes

Mixing user interface (UI) code with direct data access logic (SQL statements) leads to maintenance difficulties, violates the Single Responsibility Principle, and tightly couples the UI to the database schema.

Solution: Separate code into distinct layers.

1. Entity Classes (Model Layer): Simple classes that mirror database tables, containing only properties and constructors. They encapsulate data for transfer between layers.

   // Models/Student.cs
   namespace Application.Models
   {
       public class Student
       {
           public int StudentId { get; set; }
           public string FullName { get; set; }
           public DateTime DateOfBirth { get; set; }
           public string Email { get; set; }
           // ... other properties matching the Students table ...
       }
   }
   

2. Data Access Classes (Data Access Layer - DAL): Contain methods for CRUD operations specific to an entity. They use the helper class (DbUtility) and operate on entity objects, not raw SQL strings from the UI.

   // DAL/StudentRepository.cs
   using Application.Models;
   
   namespace Application.DAL
   {
       public class StudentRepository
       {
           public int AddNewStudent(Student newStudent)
           {
               string sql = @"INSERT INTO Students (FullName, DateOfBirth, Email) 
                              VALUES (@Name, @DoB, @Email);
                              SELECT SCOPE_IDENTITY();";
               // Use parameters to prevent SQL injection
               // Execute using DbUtility.ExecuteScalarQuery(...) with parameters
               // Return the new ID
           }
   
           public List<Student> GetStudentsByCourse(int courseId)
           {
               string sql = "SELECT * FROM Students WHERE CourseId = @CId";
               // Use DbUtility.ExecuteReaderQuery, map results to Student objects
               // Return a list of Student entities
           }
       }
   }
   

3. User Interface Layer: References the Model and DAL layers. It works with Student objects and calls methods on StudentRepository, remaining completely unaware of SQL.

Benefits of This Architecture:

• Maintainability: Changes to the database or business logic are isolated to specific layers.
• Testability: Layers can be tested independently (e.g., mock the DAL to test UI logic).
• Security: Centralized data access allows for consistent implementation of security practices like parameterized queries (preventing SQL injection).
• Developer Specialization: UI developers and database/backend developers can work concurrently with a clear contract (the Antity classes).

Core OOP Principles Applied:

• Single Responsibility: Each class has a clear, focused purpose.
• Open/Closed Principle: The DAL is open for extension (new methods) but closed for modification of existing, working code.
• Dependency Inversion: High-level UI modules depend on abstractions (entity interfaces/repository interfaces), not low-level database details.