Data Access Layer

Introduction

The DAL project is the only layer that talks to SQL Server. Controllers and Area controllers instantiate *_DAL classes, pass BO objects and SqlParameter arrays, and receive DataTable, scalar values, or populated BO lists. All connections flow through BaseHelper and the MyConnectionString connection string.

Entity Framework and inline SQL in controllers are not part of the standard pattern.

Purpose

This page documents the canonical data access pattern so new features remain consistent with existing modules and benefit from centralized connection handling, timeouts, and procedure execution.

When to Use

Refer to this page when you:

• Add a new database operation for a module
• Debug connection string or timeout errors
• Map controller form data to stored procedure parameters
• Choose between GetDataTable, ExecuteProcedure, and related BaseHelper methods

How It Works

Layer flow

Connection resolution

BaseHelper.ResolveConnectionString():

1. Returns cached ConnectionString if already set
2. Otherwise reads ConfigurationManager.ConnectionStrings["MyConnectionString"]
3. Throws ConfigurationErrorsException if missing or empty

public static SqlConnection GetConnection()
{
    var cn = new SqlConnection(ResolveConnectionString());
    if (cn.State == ConnectionState.Closed)
        cn.Open();
    return cn;
}

Default command timeout: 120 seconds (DefaultCommandTimeoutSeconds).

Typical DAL class structure

Each feature has a dedicated DAL file under DAL/ or DAL/<Module>/:

namespace DAL
{
    public class Login_DAL : IDisposable
    {
        BaseHelper baseHl = new BaseHelper();

        struct Procedure
        {
            public static string SP_Read = "[dbo].[GetLoginUser]";
            public static string SP_Write = "[dbo].[InsertUpdateChangePassword]";
        }

        public User GetLoginUser(User objUser)
        {
            cmd = new SqlCommand(Procedure.SP_Read, BaseHelper.GetConnection());
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@loginid", objUser.LoginID));
            // ...
        }
    }
}

SqlParameter conventions

Practice	Detail
Prefix	@ parameter names matching SP definition
Types	Let ADO.NET infer types, or cast explicitly for nullable fields
Arrays	SqlParameter[] passed to BaseHelper.GetDataTable(procedureName, parameters)
Session context	Controller sets CompID, UserID, BranchCode on BO before DAL call

Example from permission reads:

SqlParameter[] Sp = {
    new SqlParameter("@UserAccountsID", UserPermissions.UserAccountsID),
    new SqlParameter("@ModuleID", UserPermissions.ModuleID),
    new SqlParameter("@MenuID", UserPermissions.MenuID),
};
datatable = baseHl.GetDataTable(Procedure.Sp_GetUserList, Sp);

BaseHelper capabilities

BaseHelper (in DAL/BaseHelper.cs) provides:

Method family	Use case
GetDataTable	Read lists and grids
ExecuteProcedure / IsExistRecordProcedure	Reads returning existence or single results
ExecuteNonQueryByQuery	Legacy direct SQL (avoid for new code)
Excel export helpers	ClosedXML integration
Crystal report helpers	Report document loading
Mail helpers	SMTP send using configured credentials

Prefer stored procedures over ExecuteNonQueryByQuery for new development.

Controller integration pattern

// 1. Session gate
if (string.IsNullOrEmpty(Convert.ToString(Session["CompID"])))
    return RedirectToAction("UserLogin", "Login", new { Area = "" });

// 2. Build BO from session + request
var model = new PayRollDashBoard_BO();
model.CompID = Convert.ToInt32(Session["CompID"]);

// 3. Call DAL
var dal = new PayRollDashBoard_DAL();
var data = dal.GetDashboardKpis(model);

// 4. Return
ViewBag.Kpis = data;
return View();

Result shapes

Return type	Typical use
DataTable	Razor tables, DataTables JSON serialization
List<T_BO>	Strongly typed grids
bool	Success/failure of InsertUpdate SPs
int	Identity or status code from SP output parameter
JsonResult	AJAX endpoints in controllers

Developer Notes

• Dispose pattern: Many DAL classes implement IDisposable and close connections in finally blocks. Prefer using with connections where refactored.
• CommandTimeout: Some legacy calls set CommandTimeout = 0 (no limit) for heavy reports — use cautiously.
• DatabaseHelper: ClearDBNullString, ClearDBNullBoolean, etc. safely map DBNull from readers.
• No repository abstraction: Each module owns concrete DAL classes; there is no generic IRepository<T>.

Connection pooling

Some legacy DAL methods call SqlConnection.ClearPool in finally. Avoid opening parallel connections on the same static helper without understanding pool behavior.

Examples

Read pattern (Payroll dashboard)

PayrollDashboardController
  → PayRollDashBoard_DAL.Get*(bo with CompID)
  → BaseHelper.GetDataTable("[dbo].[GetPayRollDashboard...]", parameters)
  → DataTable → View

Write pattern (typical master save)

Controller receives POST model
  → Map to BO
  → SomeMaster_DAL.InsertUpdate(bo)
  → BaseHelper.ExecuteProcedure("[dbo].[InsertUpdateSomeMaster]", parameters)
  → bool success → JSON { isError: false }

Login read (reference implementation)

See Login_DAL.GetLoginUser — encrypts password, calls [dbo].[GetLoginUser], maps reader to BO.User including module flags.

Related Pages

• Stored Procedures
• Business Objects
• System Overview
• Authentication Flow

Important Notes

MyConnectionString is mandatory

All standard DAL code paths expect MyConnectionString in Web.config. Missing configuration fails fast at first DB call.

CompID in every SP

When designing new stored procedures, accept @CompID (or @compid) and filter all queries by company. Session provides the value; never trust client-hidden fields alone.

Common Mistakes

Mistake	Consequence
Raw SQL in controller	Bypasses timeouts, untestable, SQL injection risk
Wrong parameter name vs SP	SqlException on execute
Forgetting CommandType.StoredProcedure	SP not found or wrong execution plan
Returning entities without null-safe mapping	InvalidCastException on DBNull
Using legacy connection strings for DAL	Wrong database or missing tables

Next Steps

1. Read Stored Procedures for naming conventions and the Procedure struct pattern.
2. Read Business Objects for DTO namespaces matching your DAL module.