Unleashing Parallelism in ADO.NET: Can You Insert and Query Multiple Tables at Once?

Working with Multiple Repositories Simultaneously Using Tasks and ADO.NET in .NET Framework

Ever wondered if you can insert or query multiple tables at the same time in a .NET Framework application using ADO.NET?

You have:

• Three repositories (say, Customer, Product, and Order)

• One database

• Multiple insert or query operations that aren't dependent on each other

👉 The question is: Can you execute them in parallel?

Yes, you can. And it can boost performance — especially for read-heavy or write-heavy background operations.

In this blog, we’ll cover:

• Why parallelism matters

• Threading considerations in ADO.NET

• Practical examples of parallel insert and query

• When not to parallelize

⚙️ Real-World Scenario: 3 Independent Repositories

Let’s say you're building an e-commerce system with the following structure:

• CustomerRepository

• ProductRepository

• OrderRepository

Each repository is using ADO.NET to interact with the same SQL database.

🚀 Use Case 1: Parallel Insert

You want to insert a customer, a product, and an order at the same time—perhaps as part of an onboarding or batch-processing feature.

Since the inserts are independent, this is a perfect candidate for parallel execution.

✅ Code Snippet: Parallel Insert Using Task.WhenAll

public class CustomerRepository
{
    private readonly string _connStr;
    public CustomerRepository(string connStr) => _connStr = connStr;

    public async Task AddCustomerAsync(Customer customer)
    {
        using var conn = new SqlConnection(_connStr);
        using var cmd = new SqlCommand("INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)", conn);
        cmd.Parameters.AddWithValue("@Name", customer.Name);
        cmd.Parameters.AddWithValue("@Email", customer.Email);
        await conn.OpenAsync();
        await cmd.ExecuteNonQueryAsync();
    }
}

Similar methods for ProductRepository and OrderRepository.

📌 Now call them in parallel:

var customerRepo = new CustomerRepository(connStr);
var productRepo = new ProductRepository(connStr);
var orderRepo = new OrderRepository(connStr);

var addCustomerTask = customerRepo.AddCustomerAsync(new Customer { Name = "John", Email = "john@example.com" });
var addProductTask = productRepo.AddProductAsync(new Product { Name = "Monitor", Price = 199.99M });
var addOrderTask = orderRepo.AddOrderAsync(new Order { CustomerId = 1, ProductId = 1 });

await Task.WhenAll(addCustomerTask, addProductTask, addOrderTask);

Console.WriteLine("All insert operations completed.");

✅ All three insertions happen concurrently, leveraging available threads.

👋 Become 1% better at .NET Full Stack development every day.

👆 https://dotnet-fullstack-dev.blogspot.com/

♻ Restack it Vibe matches, help others to get it

🔍 Use Case 2: Parallel Query

Say you want to fetch all customers, all products, and all orders for a dashboard. Again, these queries are independent.

✅ Code Snippet: Parallel Query Using Tasks

public async Task<List<Customer>> GetAllCustomersAsync()
{
    var list = new List<Customer>();
    using var conn = new SqlConnection(_connStr);
    using var cmd = new SqlCommand("SELECT * FROM Customers", conn);
    await conn.OpenAsync();
    var reader = await cmd.ExecuteReaderAsync();
    while (await reader.ReadAsync())
    {
        list.Add(new Customer
        {
            Id = (int)reader["Id"],
            Name = reader["Name"].ToString(),
            Email = reader["Email"].ToString()
        });
    }
    return list;
}

🚀 This makes your dashboard much faster than doing each call sequentially!

⚠️ Things to Keep in Mind

Pro Tip: ADO.NET SqlConnection objects are not thread-safe. Each task must open its own connection.

🧠 Conclusion

Parallel operations with ADO.NET are absolutely possible and beneficial when:

• You're not bound by transactional constraints

• You want to boost responsiveness

• You're handling IO-bound operations

With Task.WhenAll, multiple inserts or queries can run side-by-side using separate SqlConnection instances — giving you clean, efficient, and scalable code.