ClsSqlHelper Documentation

Comprehensive guide for developers on using ClsSqlHelper in Sense Softech ERP / Restaurant POS projects.

Constructor & Initialization

public ClsSqlHelper(IConfiguration configuration, IDbConnectionFactory connectionFactory, ILogger<ClsSqlHelper> logger, string connectionKey)

Public Methods

Task<int> ExecuteNonQueryAsync(string query, DynamicParameters? parameters = null, int? commandTimeout = null)

Task<T> ExecuteScalarAsync<T>(string query, object? parameters = null, int? commandTimeout = null)

Task<T?> ExecuteObjectAsync<T>(string query, object? parameters = null, int? commandTimeout = null)

Task<List<T>> ExecuteReaderAsync<T>(string query, object? parameters = null, int? commandTimeout = null)

Task<List<DataTable>> ExecuteDataSetAsync(string query, object? parameters = null, int? commandTimeout = null)

Task<List<object>> ExecuteMultipleAsync(string query, object? parameters = null, int? commandTimeout = null, params Type[] resultTypes)

Real-Time Examples (Sense Softech Projects)

1. Restaurant - Fetch Pending KOTs / Ready Orders

var orders = await _sqlHelper.ExecuteReaderAsync<PendingOrderDto>(
    "sp_GetPendingKOTs", 
    new { BranchId = branchId, TableId = tableId, Status = "Pending" }
);

2. Save KOT + Deduct Stock (Important for Restaurant)

var param = new DynamicParameters();
param.Add("@KOTNo", kotNo);
param.Add("@TableId", tableId);
param.Add("@ItemsXML", itemsXml);           // Pass as XML for multiple items
param.Add("@UserId", userId);

int result = await _sqlHelper.ExecuteNonQueryAsync("sp_SaveKOTAndDeductStock", param);

3. Inventory - Stock Summary Report

var stockList = await _sqlHelper.ExecuteReaderAsync<StockSummaryDto>(
    "sp_StockSummary", 
    new { 
        WarehouseId = whId, 
        FromDate = fromDate, 
        ToDate = toDate,
        ItemGroup = group 
    }
);

4. Production Module (Primarosa Flowers)

var workOrder = await _sqlHelper.ExecuteObjectAsync<WorkOrderDto>(
    "sp_GetWorkOrderById", 
    new { WorkOrderId = id }
);

var materialList = await _sqlHelper.ExecuteReaderAsync<MaterialIssueDto>(
    "sp_GetWorkOrderMaterials", new { WorkOrderId = id }
);

5. RDLC Report - Multiple Result Sets (Dashboard / Aging)

var tables = await _sqlHelper.ExecuteDataSetAsync("sp_GetSalesDashboard", 
    new { BranchId = branchId, DateFrom = fromDate, DateTo = toDate });

// tables[0] → Summary
// tables[1] → Detailed Sales
// tables[2] → Top Items
DataTable summary = tables[0];
DataTable details = tables[1];

6. Insert with Output Parameter (Get New ID)

var dp = new DynamicParameters();
dp.Add("@CustomerName", model.Name);
dp.Add("@CustomerId", dbType: DbType.Int32, direction: ParameterDirection.Output);

await _sqlHelper.ExecuteNonQueryAsync("sp_InsertCustomer", dp);

int newCustomerId = dp.Get<int>("@CustomerId");

Best Practices & Tips

• Always use Stored Procedures
• Pass multiple items using XML or Table-Valued Parameters
• Use DynamicParameters when you need Output parameters
• Keep commandTimeout for long-running reports (e.g., 120 seconds)
• Log errors are automatically captured via ILogger
• Prefer strongly typed DTOs over DataTable for business logic
• Register helper as Scoped in DI container