ADO - Implementing the Repository Pattern with ADO.NET

The Repository Pattern is a software design pattern that acts as an intermediary between the application's business logic and the database. Instead of allowing different parts of the application to directly interact with ADO.NET classes such as SqlConnection, SqlCommand, and SqlDataReader, all database operations are managed through repository classes. This approach improves code organization, maintainability, scalability, and testability.

In applications that use ADO.NET, SQL queries are often written directly inside forms, controllers, or service classes. As the application grows, this leads to duplicated code, poor maintainability, and difficulty in modifying database logic. The Repository Pattern addresses these problems by placing all database-related operations into dedicated repository classes.

What is the Repository Pattern?

A repository is a class that provides methods for performing Create, Read, Update, and Delete (CRUD) operations on a specific database table or entity. It acts as a collection of objects, hiding the details of how data is stored or retrieved.

Instead of writing SQL statements throughout the application, the repository exposes simple methods such as:

GetAllEmployees()
GetEmployeeById()
AddEmployee()
UpdateEmployee()
DeleteEmployee()

The application simply calls these methods without worrying about the underlying SQL commands.

Why Use the Repository Pattern?

Using the Repository Pattern offers several advantages:

Better Code Organization

Database access code is separated from business logic. This creates a cleaner project structure where each class has a single responsibility.

Easier Maintenance

If a database query needs modification, the change is made only inside the repository instead of multiple files.

Improved Reusability

Repository methods can be reused by different parts of the application, reducing duplicate code.

Better Testability

Business logic can be tested independently by replacing the repository with mock or fake implementations during testing.

Scalability

As the application grows, additional repositories can be created for different entities without affecting the existing architecture.

Basic Project Structure

A typical application using the Repository Pattern may be organized as follows:

Project
│
├── Models
│     Employee.cs
│     Department.cs
│
├── Repositories
│     IEmployeeRepository.cs
│     EmployeeRepository.cs
│
├── Services
│     EmployeeService.cs
│
├── Database
│     DbConnection.cs
│
└── Program.cs

Each folder has a specific purpose:

Models represent database entities.
Repositories contain database access logic.
Services contain business rules.
Database folder manages database connections.

Creating the Model Class

Suppose there is an Employee table containing:

EmployeeID
Name
Department
Salary

The corresponding model class stores these properties.

public class Employee
{
    public int EmployeeID { get; set; }
    public string Name { get; set; }
    public string Department { get; set; }
    public decimal Salary { get; set; }
}

The model represents one record from the database.

Creating the Repository Interface

An interface defines the operations supported by the repository.

public interface IEmployeeRepository
{
    List<Employee> GetAllEmployees();
    Employee GetEmployeeById(int id);
    void AddEmployee(Employee employee);
    void UpdateEmployee(Employee employee);
    void DeleteEmployee(int id);
}

Using an interface allows different implementations without changing the business logic.

Implementing the Repository

The repository class implements the interface and performs actual database operations.

public class EmployeeRepository : IEmployeeRepository
{
    private readonly string connectionString;

    public EmployeeRepository(string connectionString)
    {
        this.connectionString = connectionString;
    }
}

The repository stores the connection string and uses it for database access.

Reading Records from the Database

The GetAllEmployees() method retrieves every employee.

The implementation generally follows these steps:

Open a database connection.
Create a SQL query.
Execute the query.
Read data using SqlDataReader.
Convert rows into Employee objects.
Return the collection.

Example:

public List<Employee> GetAllEmployees()
{
    List<Employee> employees = new List<Employee>();

    using(SqlConnection con = new SqlConnection(connectionString))
    {
        SqlCommand cmd = new SqlCommand(
            "SELECT * FROM Employee", con);

        con.Open();

        SqlDataReader reader = cmd.ExecuteReader();

        while(reader.Read())
        {
            Employee emp = new Employee();

            emp.EmployeeID = Convert.ToInt32(reader["EmployeeID"]);
            emp.Name = reader["Name"].ToString();
            emp.Department = reader["Department"].ToString();
            emp.Salary = Convert.ToDecimal(reader["Salary"]);

            employees.Add(emp);
        }
    }

    return employees;
}

The calling code receives a list of Employee objects instead of dealing with SQL statements.

Retrieving a Single Record

The repository can provide a method to retrieve one employee.

GetEmployeeById(int id)

Internally, this method uses a parameterized SQL query.

SELECT * FROM Employee
WHERE EmployeeID=@EmployeeID

Using parameters improves security and prevents SQL injection attacks.

Inserting Data

The AddEmployee() method inserts new records.

Typical SQL statement:

INSERT INTO Employee
(Name, Department, Salary)
VALUES
(@Name, @Department, @Salary)

The repository receives an Employee object, extracts its values, and executes the SQL command.

Updating Existing Records

The UpdateEmployee() method modifies existing information.

Example SQL:

UPDATE Employee
SET
Name=@Name,
Department=@Department,
Salary=@Salary
WHERE EmployeeID=@EmployeeID

The repository updates only the required record.

Deleting Records

The DeleteEmployee() method removes data.

SQL statement:

DELETE FROM Employee
WHERE EmployeeID=@EmployeeID

Again, parameters are used for security.

Using the Repository in Business Logic

Instead of writing SQL commands, business logic simply calls repository methods.

Example:

IEmployeeRepository repository =
new EmployeeRepository(connectionString);

List<Employee> employees =
repository.GetAllEmployees();

The business layer remains independent of database implementation details.

Using Services Alongside Repositories

A service layer often sits between the user interface and the repository.

User Interface
       │
       ▼
Service Layer
       │
       ▼
Repository
       │
       ▼
Database

For example:

Repository retrieves employee records.
Service calculates employee bonuses.
User interface displays the results.

Each layer performs its own responsibility.

Benefits of Using Interfaces

Interfaces provide flexibility.

For example:

IEmployeeRepository repository;

Later, the implementation can be changed to:

SQL Server
Oracle
MySQL
SQLite
Mock Repository for testing

without modifying business logic.

Exception Handling

Repository methods should handle database errors properly.

try
{
    // Database operation
}
catch(SqlException ex)
{
    Console.WriteLine(ex.Message);
}

Proper exception handling prevents unexpected application crashes.

Managing Database Connections

Repositories should always close connections automatically.

The using statement ensures:

Connections are released.
Resources are cleaned up.
Memory leaks are avoided.

Example:

using(SqlConnection con = new SqlConnection(connectionString))
{
    // Database code
}

This is considered the recommended practice in ADO.NET.

Using Transactions

Repositories can execute multiple SQL statements inside a transaction.

Example:

Insert Employee
Insert Payroll
Insert Attendance

If one operation fails, all changes are rolled back.

This maintains data consistency.

Dependency Injection

Instead of creating repositories manually, modern applications often use Dependency Injection.

Example:

public EmployeeService(IEmployeeRepository repository)
{
    this.repository = repository;
}

This approach reduces tight coupling and makes the application easier to test and maintain.

Repository Pattern vs Direct ADO.NET Access

Direct ADO.NET	Repository Pattern
SQL code scattered across the project	SQL code centralized in repositories
Difficult to maintain	Easy to maintain
High code duplication	Reusable methods
Hard to test	Easy to mock and test
Business logic mixed with data access	Clear separation of concerns
Difficult to scale	Easier to extend

Best Practices

When implementing the Repository Pattern with ADO.NET, follow these best practices:

Keep SQL queries inside repository classes only.
Use parameterized queries to prevent SQL injection.
Create separate repositories for different entities.
Use interfaces for all repositories.
Manage database connections with the using statement.
Handle exceptions appropriately.
Keep repositories focused solely on data access and move business rules to the service layer.
Use transactions when multiple related database operations must succeed or fail together.
Adopt Dependency Injection to reduce coupling and improve testability.
Write clean, reusable methods with descriptive names.

Conclusion

Implementing the Repository Pattern with ADO.NET is an effective way to build structured, maintainable, and scalable database-driven applications. By encapsulating all database operations within repository classes, developers achieve a clear separation between data access and business logic. This results in cleaner code, easier maintenance, improved reusability, stronger testability, and greater flexibility as the application evolves. Whether developing a small business application or a large enterprise system, combining ADO.NET with the Repository Pattern provides a robust foundation for managing database interactions efficiently while following sound software design principles.