Parallel Insert & Query with ADO.NET Generic Repository in .NET Framework

Scaling Clean Code & Performance — One Generic Repo at a Time

In large-scale applications, maintaining separate data access logic for every entity often leads to code repetition and maintenance headaches. The Generic Repository pattern is a perfect solution when:

• You want reusability across tables
• You’re using ADO.NET (not EF)
• You want to perform parallel insertions or queries

Now, let’s apply this to a practical use case with Customer, Product, and Order repositories.

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

🧱 Step 1: Define a Base Entity Interface

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

📦 Step 2: Create Models

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

public class Product : IEntity
{
    public int Id { get; set; }
    public string Name { get; set; }
    public decimal Price { get; set; }
}

public class Order : IEntity
{
    public int Id { get; set; }
    public int CustomerId { get; set; }
    public int ProductId { get; set; }
}

🧠 Step 3: Create the Generic Repository

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

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

    public async Task AddAsync(T entity)
    {
        using var conn = new SqlConnection(_connectionString);
        using var cmd = new SqlCommand();
        cmd.Connection = conn;

        if (entity is Customer customer)
        {
            cmd.CommandText = $"INSERT INTO {_tableName} (Name, Email) VALUES (@Name, @Email)";
            cmd.Parameters.AddWithValue("@Name", customer.Name);
            cmd.Parameters.AddWithValue("@Email", customer.Email);
        }
        else if (entity is Product product)
        {
            cmd.CommandText = $"INSERT INTO {_tableName} (Name, Price) VALUES (@Name, @Price)";
            cmd.Parameters.AddWithValue("@Name", product.Name);
            cmd.Parameters.AddWithValue("@Price", product.Price);
        }
        else if (entity is Order order)
        {
            cmd.CommandText = $"INSERT INTO {_tableName} (CustomerId, ProductId) VALUES (@CustomerId, @ProductId)";
            cmd.Parameters.AddWithValue("@CustomerId", order.CustomerId);
            cmd.Parameters.AddWithValue("@ProductId", order.ProductId);
        }
        else
        {
            throw new NotSupportedException("Entity type not supported for insertion.");
        }

        await conn.OpenAsync();
        await cmd.ExecuteNonQueryAsync();
    }

    public async Task<List<T>> GetAllAsync(Func<SqlDataReader, T> map)
    {
        var results = new List<T>();
        using var conn = new SqlConnection(_connectionString);
        var cmd = new SqlCommand($"SELECT * FROM {_tableName}", conn);
        await conn.OpenAsync();
        using var reader = await cmd.ExecuteReaderAsync();

        while (await reader.ReadAsync())
        {
            results.Add(map(reader));
        }

        return results;
    }
}

⚙️ Step 4: Use Generic Repositories in Parallel

🛒 Insert Data in Parallel

var connStr = "your_connection_string";
var customerRepo = new GenericRepository<Customer>(connStr, "Customers");
var productRepo = new GenericRepository<Product>(connStr, "Products");
var orderRepo = new GenericRepository<Order>(connStr, "Orders");

var customerTask = customerRepo.AddAsync(new Customer { Name = "Alice", Email = "alice@demo.com" });
var productTask = productRepo.AddAsync(new Product { Name = "Headphones", Price = 499.99M });
var orderTask = orderRepo.AddAsync(new Order { CustomerId = 1, ProductId = 1 });

await Task.WhenAll(customerTask, productTask, orderTask);
Console.WriteLine("All insertions completed in parallel!");

🔎 Query Data in Parallel

var customersTask = customerRepo.GetAllAsync(reader => new Customer
{
    Id = (int)reader["Id"],
    Name = reader["Name"].ToString(),
    Email = reader["Email"].ToString()
});

var productsTask = productRepo.GetAllAsync(reader => new Product
{
    Id = (int)reader["Id"],
    Name = reader["Name"].ToString(),
    Price = (decimal)reader["Price"]
});

var ordersTask = orderRepo.GetAllAsync(reader => new Order
{
    Id = (int)reader["Id"],
    CustomerId = (int)reader["CustomerId"],
    ProductId = (int)reader["ProductId"]
});

await Task.WhenAll(customersTask, productsTask, ordersTask);

var customers = customersTask.Result;
var products = productsTask.Result;
var orders = ordersTask.Result;

🧠 Why This Works

• Each Task uses its own SqlConnection, so it’s safe to run in parallel.
• Generic repository gives you a single, maintainable structure.
• Custom logic (like entity-specific SQL) is still possible within the generic class.

⚠️ When Not to Parallelize

• When actions depend on one another (e.g., insert customer → insert order).
• When using shared transactions — you’d need a single SqlConnection and SqlTransaction.

✅ Conclusion

Using a Generic Repository with ADO.NET gives you:

• Cleaner code
• Better separation of concerns
• Flexibility to support multiple entities
• The power to run parallel inserts and queries efficiently

It’s perfect for mid to large-scale .NET Framework applications that need performance and maintainability without the weight of EF or Dapper.