仓储设计模式下C#数据集过滤及SQL查询格式转换咨询

Hey there! Let's tackle your two technical questions one by one, with practical examples and clear explanations.

The Repository Design Pattern abstracts data access logic, making your code more maintainable, testable, and decoupled from specific data sources. Here's a step-by-step implementation for your dataset filtering needs:

Step 1: Define the Repository Interface #

Start with an interface to declare the data operations you need—this keeps your data access logic abstract and flexible:

public interface IPurchaseItemRepository
{
    // Returns aggregated results matching your SQL query's intent, filtered by CompanyId
    IEnumerable<PurchaseItemAggregate> GetAggregatedItemsByCompanyId(int companyId);
}

// DTO to hold the aggregated query results (matches your SQL's SELECT columns)
public class PurchaseItemAggregate
{
    public int WareHouseId { get; set; }
    public int CompanyId { get; set; }
    public int ProductId { get; set; }
    public int AvailableQuantity { get; set; }
    public decimal PurchaseItemPrice { get; set; }
}

Step 2: Build the Concrete Repository Class #

Create a concrete repository that handles the actual data filtering and aggregation. We'll use ADO.NET here (easily adaptable to work with DataSet directly too):

public class PurchaseItemRepository : IPurchaseItemRepository
{
    private readonly string _connectionString;

    public PurchaseItemRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public IEnumerable<PurchaseItemAggregate> GetAggregatedItemsByCompanyId(int companyId)
    {
        var aggregatedItems = new List<PurchaseItemAggregate>();
        // Your target SQL query, with parameterization to prevent SQL injection
        var sql = @"SELECT MAX(WarehouseId) AS WareHouseId, MAX(CompanyId) AS CompanyId, 
                     MAX(ProductId) AS ProductId, SUM(AvailableQuantity) AS AvailableQuantity, 
                     PurchaseItemPrice 
                     FROM PurchaseItem 
                     WHERE CompanyId = @CompanyId 
                     GROUP BY PurchaseItemPrice 
                     ORDER BY MAX(ProductId) ASC";

        using (var conn = new SqlConnection(_connectionString))
        {
            conn.Open();
            using (var cmd = new SqlCommand(sql, conn))
            {
                cmd.Parameters.AddWithValue("@CompanyId", companyId);
                
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        aggregatedItems.Add(new PurchaseItemAggregate
                        {
                            WareHouseId = reader.GetInt32(reader.GetOrdinal("WareHouseId")),
                            CompanyId = reader.GetInt32(reader.GetOrdinal("CompanyId")),
                            ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
                            AvailableQuantity = reader.GetInt32(reader.GetOrdinal("AvailableQuantity")),
                            PurchaseItemPrice = reader.GetDecimal(reader.GetOrdinal("PurchaseItemPrice"))
                        });
                    }
                }
            }
        }
        return aggregatedItems;
    }
}

Step 3: Use the Repository in Business Logic #

Inject the repository into your services to keep data access separate from core business logic—this makes testing and refactoring easier:

public class InventoryService
{
    private readonly IPurchaseItemRepository _purchaseItemRepo;

    public InventoryService(IPurchaseItemRepository purchaseItemRepo)
    {
        _purchaseItemRepo = purchaseItemRepo;
    }

    public IEnumerable<PurchaseItemAggregate> GetCompanyInventorySummary(int companyId)
    {
        return _purchaseItemRepo.GetAggregatedItemsByCompanyId(companyId);
    }
}

Format Name: Raw ADO.NET (Native ADO.NET) #

This is the non-LINQ format you're asking for—it uses core ADO.NET classes (SqlConnection, SqlCommand, SqlDataReader) to execute raw SQL directly, without relying on LINQ to SQL or Entity Framework.

Converted Code Example #

Here's a standalone implementation of your SQL query using Raw ADO.NET:

public List<PurchaseItemAggregate> RunInventoryAggregateQuery(int companyId, string connectionString)
{
    var results = new List<PurchaseItemAggregate>();
    var sql = @"SELECT MAX(WarehouseId) AS WareHouseId, MAX(CompanyId) AS CompanyId, 
                 MAX(ProductId) AS ProductId, SUM(AvailableQuantity) AS AvailableQuantity, 
                 PurchaseItemPrice 
                 FROM PurchaseItem 
                 WHERE CompanyId = @CompanyId 
                 GROUP BY PurchaseItemPrice 
                 ORDER BY MAX(ProductId) ASC";

    using (var conn = new SqlConnection(connectionString))
    {
        conn.Open();
        using (var cmd = new SqlCommand(sql, conn))
        {
            cmd.Parameters.AddWithValue("@CompanyId", companyId);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    results.Add(new PurchaseItemAggregate
                    {
                        WareHouseId = reader.GetInt32(reader.GetOrdinal("WareHouseId")),
                        CompanyId = reader.GetInt32(reader.GetOrdinal("CompanyId")),
                        ProductId = reader.GetInt32(reader.GetOrdinal("ProductId")),
                        AvailableQuantity = reader.GetInt32(reader.GetOrdinal("AvailableQuantity")),
                        PurchaseItemPrice = reader.GetDecimal(reader.GetOrdinal("PurchaseItemPrice"))
                    });
                }
            }
        }
    }
    return results;
}

Applicability Evaluation #

When to Use Raw ADO.NET:

• Performance-Critical Workloads: You need full control over SQL execution to minimize overhead (no ORM abstraction layer slowing things down).
• Complex Queries: Your query uses advanced aggregations, grouping, or database-specific syntax that's hard to replicate with LINQ/ORMs.
• Legacy System Integration: Working with older codebases that don't use ORMs, or need to interact directly with stored procedures.

When to Avoid It:

• Cross-Database Compatibility: If you might switch databases (e.g., from SQL Server to PostgreSQL), raw SQL ties you to a specific database's syntax.
• Productivity: Manual mapping from DataReader to objects is tedious and error-prone—ORMs handle this automatically.
• Maintainability: Raw SQL scattered in code is harder to test and refactor compared to strongly-typed LINQ queries.

内容的提问来源于stack exchange，提问作者Ibtisam