Why and When You Need a Generic Repository in ADO.NET (.NET Framework)

From Repeating Yourself to Reusing Smartly — Make ADO.NET Great Again 💡

ADO.NET is powerful, flexible, and still widely used in enterprise-grade applications on the .NET Framework. But let’s be honest — how many times have you written the same CRUD logic for different entities?

If your project has:

• 10+ tables
• 10+ repositories
• And each one has identical methods like Add, GetById, Delete, GetAll...

Then guess what? You’re repeating yourself. That’s where a Generic Repository becomes your best friend.

Let’s explore:

• The case for a Generic Repository
• When it’s overkill
• How to implement it in ADO.NET
• A real-world example
• Best practices

📌Explore more at: https://dotnet-fullstack-dev.blogspot.com/
🌟 Clapping would be appreciated! 🚀

🤯 Problem: Repetitive Repositories

Take a look at these two ADO.NET repository classes:

📁 CustomerRepository.cs

public Customer GetById(int id) { /* SqlCommand for Customer */ }
public void Add(Customer customer) { /* Insert logic */ }

📁 ProductRepository.cs

public Product GetById(int id) { /* SqlCommand for Product */ }
public void Add(Product product) { /* Insert logic */ }

Spot the similarity? 90% of the code is duplicated 😖

✅ Solution: A Generic Repository

Instead of repeating CRUD logic for every entity, create a reusable generic base repository.

💻 Generic Repository in ADO.NET — Implementation

🔧 Step 1: Define an Entity Interface

public interface IEntity
{
    int Id { get; set; }
}

👨‍💻 Step 2: Create Base Repository

public class GenericRepository<T> where T : class, IEntity, new()
{
    private readonly string _connectionString;
    private readonly string _tableName;

    public GenericRepository(string connectionString, string tableName)
    {
        _connectionString = connectionString;
        _tableName = tableName;
    }

    public List<T> GetAll()
    {
        var list = new List<T>();
        using (var conn = new SqlConnection(_connectionString))
        {
            var cmd = new SqlCommand($"SELECT * FROM {_tableName}", conn);
            conn.Open();
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                // Minimal mapping example
                var entity = new T();
                entity.Id = (int)reader["Id"];
                list.Add(entity);
            }
        }
        return list;
    }

   public void Add(T entity)
   {
      using (var connection = new SqlConnection(_connectionString))
      {
          connection.Open();

          if (entity is Customer customer)
          {
              var command = new SqlCommand("INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)", connection);
              command.Parameters.AddWithValue("@Name", customer.Name);
              command.Parameters.AddWithValue("@Email", customer.Email);
              command.ExecuteNonQuery();
          }
          else if (entity is Product product)
          {
              var command = new SqlCommand("INSERT INTO Products (Name, Price) VALUES (@Name, @Price)", connection);
              command.Parameters.AddWithValue("@Name", product.Name);
              command.Parameters.AddWithValue("@Price", product.Price);
              command.ExecuteNonQuery();
          }
          else
          {
              throw new NotSupportedException($"Add operation not supported for type {typeof(T).Name}");
          }
      }
  }
}

📁 Step 3: Use in Real Repository

public class Customer : IEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
}

var customerRepo = new GenericRepository<Customer>("your_connection_string", "Customers");
var allCustomers = customerRepo.GetAll();

var customerRepo = new GenericRepository<Customer>("your_connection_string", "Customers");
customerRepo.Add(new Customer { Name = "John Doe", Email = "john@example.com" });

var productRepo = new GenericRepository<Product>("your_connection_string", "Products");
productRepo.Add(new Product { Name = "Laptop", Price = 1200 });

📊 When a Generic Repository Helps

✅ When multiple tables have similar CRUD operations
✅ When your team values code reuse and maintainability
✅ When you want to centralize logging, retry logic, and DB access patterns

🛑 When NOT to Use It

❌ Complex joins or stored procedures per entity
❌ High customization per repository
❌ You only have 1–2 entities — no ROI from abstraction

📚 Bonus: Extending the Base

Want specific logic for CustomerRepository only?

public class CustomerRepository : GenericRepository<Customer>
{
    public CustomerRepository(string connStr)
        : base(connStr, "Customers") { }

    public List<Customer> GetCustomersByRegion(string region)
    {
        // Custom logic here
    }
}

🧠 Conclusion

The Generic Repository is not a must-have in every project — but when used correctly, especially in large ADO.NET-based codebases, it:

• Reduces code duplication
• Simplifies testing
• Keeps your repositories DRY and consistent

Use it strategically. Combine it with base classes, interfaces, and if needed, override for special cases. It’s one of those small decisions that pay big in scaling teams and systems.