Introducing DBHelpers – a new way of O/R Mapping for .NET projects

17 January 2021 .NET, CORE, Database

I spend a lot of time designing and developing .NET applications; from console apps to windows/web services and thick clients to web applications. Almost all of these are LoB applications, which means the majority of these applications revolve around working with data in databases (MS SQL in my case). And in most cases, the databases are already existing and we are just extending the application and/or database to cater to new business requirements. Even if it is a brand new .NET application, I always start with a Database-First approach. Consider me old school but I like to understand and validate the business requirements first, then move on to data modeling (logical/physical design and relationship with other/existing business data) before diving into C# coding. This means that I already have data models in place for the said requirements.

When it comes to connecting .NET applications with databases, the traditional way is as follows:

  1. Open a SQL connection
  2. Declare a SQL Command object (optional – if you want to work with stored procedures or custom queries)
  3. Declare a SQL DataReader or DataAdapter (to fill datasets)
  4. Populate your POCO objects with data from DataReader or DataSet

 

Step 3 itself can take up anywhere from 5-10 lines of code, and need to be done repetitively for all database calls. Of course, you can easily write an abstracted DB Layer class to encapsulate all this functionality, but the trouble doesn’t stop here. When it comes to Step 4, oh boy, all hell breaks loose. The DataSet or DataReader can have multiple result sets returned, and for each result set, you have to do something like this to map your data to .NET objects before consuming them:

List<Employee> _employees = new List<Employee>(); 

foreach(DataRow _dRow in _dataSet.Tables[0].Rows) 
{
       Employee _emp = new Employee();

       _emp.ID = Convert.ToInt32(_dRow[“id_Employee”]);
       _emp.Name = _dRow[“nam_Employee”].ToString();

       if (!string.IsNullOrEmpty(_dRow["dat_Birth"].ToString().Trim()))
               _emp.DOB = Convert.ToDateTime(_dRow["dat_Birth"]);
 
       //And it goes on……… 
}

 

Got your attention? Let’s move on and try to simplify this process. Enter DBHelpers library. This library hides all the above complexities (Steps 2 to 4) and provides you with mapped data in the form of .NET objects in as minimum as 1 line of code. 

So how do we do this. You can get the source from Github or add it to your projects via Nuget. Once done, it is as simple as this:

using (SqlConnection dbConn = new SqlConnection(@"<<MyConnectionString>>"))
{
    //This returns a List of Employee object
    var employees = await dbConn.ExecuteQuery<Employee>(@"select * from employees");
  
    //And consume it as required
    //foreach (var employee in employeesList1)
    //{
    //   Console.WriteLine(employee.EmployeeName + " - " + employee.EmailAddress);
    //}
}

How does this work, you may ask? DBHelpers exposes set of extension methods for your SQLConnection which allow you to execute queries and stored procedures and return mapped data in the form of List objects.

Scenario 1: Simple Usecase 

As mentioned above, execute simple queries as below:

List<Employee> employees = await dbConn.ExecuteQuery<Employee>(@"select * from employees");
 
List<Employee> employees = await dbConn.ExecuteQuery<Employee>(@"select * from employees 
where department_id = 2");

Scenario 2: Apply Filters

If you want to pass parameters to filter data, it is as simple as this:

SqlParameter dependentCountParam = new SqlParameter();
dependentCountParam.ParameterName = "@dependentCount";
dependentCountParam.Value = 3;
 
SqlParameter deptIDParam = new SqlParameter();
deptIDParam.ParameterName = "@deptID";
deptIDParam.Value = 2;

var sqlStatement = @"select * from employees 
where dependents >= @dependentCount and department_id = @deptID";

var employees = await dbConn.ExecuteQuery<Employee>(sqlStatement, 
dependentCountParam, deptIDParam);

Scenario 3: Apply Filters - Simplified

What??? That’s too much code in Scenario 2. Worry not, there is an extension method to simplify this as well.

sqlStatement = @"select * from employees 
where dependents >= @dependentCount and department_id = @deptID";

var employees = await dbConn.ExecuteQuery<Employee>(sqlStatement, 
3.ToSqlParam("@dependentCount"), 2.ToSqlParam("@deptID"));

Scenario 4: Complex Queries

You can write any complex query and execute it. Here is an example:

var sqlStatement = @"select A.Employee_Name, B.DepartmentName from employees A 
join departments B on A.Department_ID = B.DepartmentID
where A.dependents >= @dependentCount and A.department_id = @deptID";

var departmentID = 2;

var employees = await dbConn.ExecuteQuery<Employee>(sqlStatement, 
3.ToSqlParam("@dependentCount"), departmentID.ToSqlParam("@deptID"));

Scenario 5: Executing Multiple Select Statements

You are not limited to single SQL statement while using this library. You can write multiple SQL queries and get the result in different objects:

var sqlStatement = @"select * from Employees where dependents >= @dependentCount;
select * from departments where departmentname = @deptName;";

var dptCount = 3;
var departmentName = "Finance";

var multiResults = await dbConn.ExecuteMultiResultQuery(sqlStatement, 
dptCount.ToSqlParam("@dependentCount"), departmentName.ToSqlParam("@deptName"));

//This holds the first resultset
var employees = multiResults.Read<Employee>();
//This holds the second resultset
var departments = multiResults.Read<Department>();

Scenario 6: Executing Stored Procedures

Instead of writing inline SQL statements, you can also call stored procedures which execute complex logic and return single or multiple resultsets (and also pass parameters).

Here is a simple scenario which returns list of employees (single resultset):

var employeesList = await dbConn.ExecuteStoredProcedure<Employee>(@"sp_GetAllEmployees");

This one accepts a parameter passed to stored procedure to filter employees with date of birth greater than 01-Jan-2000 and working in Technology department (single resultset):

var employeesList = await dbConn.ExecuteStoredProcedure<Employee>("sp_GeFilteredEmployees", 
"Technology".ToSqlParam("@deptName"), "01-Jan-2000".ToSqlParam("@empDOB"));

This example returns multiple resultsets from stored procedure call:

var dptName = "Technology";
var empDOB = new DateTime(2000, 1, 1);

var multiResults = await dbConn.ExecuteMultiResultStoredProcedure(
"sp_GetFilteredEmployeesAndDepartments", 
dptName.ToSqlParam("@deptName"), empDOB.ToSqlParam("@empDOB"));

//This holds the first resultset
var employeesList = multiResults.Read<Employee>();
//This holds the second resultset
var departmentsList = multiResults.Read<Department>();

Scenario 7: Executing Queries which don’t return resultsets

Why not? It is also possible to execute SQL statements which don't return any resultset. ExecuteNonQueryText makes this possible:

//Insert a record in department and delete an employee
var sqlStatement = @"delete from employees 
where dependents >= @dependentCount and department_id = @deptID;
insert into departments values(@deptName)";

var dptCount = 3;
var deptID = 2;
var departmentToDelete = "Human Resources";

_ = await dbConn.ExecuteNonQueryText(sqlStatement, 
dptCount.ToSqlParam("@dependentCount"), 
deptID.ToSqlParam("@deptID"), 
departmentToDelete.ToSqlParam("@deptName"));

Scenario 8: Executing Stored Procedures which don’t return resultsets

Just like above, ExecuteNonQueryStoredProcedure extension method allows you to execute a stored procedure which does not return a resultset.

_ = await dbConn.ExecuteNonQueryStoredProcedure(@"sp_DeleteDepartment", 
"Human Resources".ToSqlParam("@deptName"));

Pre-requisites 

Simlpy speaking, there is not much that you need to do to get this to work. If the database column names are same as that defined in the POCO object, then you are good to go. E.g. consider the table tbl_Departments which has two columns:

Create Table dbo.tbl_Departments
(
    DepartmentID int PRIMARY KEY NOT NULL IDENTITY(1,1),
    DepartmentName varchar(100) NOT NULL
)

For this, you don’t need to do anything; just declare a class to hold this data and you are good to go:

public class Department
{
    //Auto Mappings - Same field names in database as in POCO
    public int DepartmentID { get; set; }
    public string DepartmentName { get; set; }
}

However, if your property names in POCO object are different, then you will need to provide this mapping by using the Column attribute as below. Consider the table tbl_Employees:

Create Table dbo.tbl_Employees
(
    id_Employee int PRIMARY KEY NOT NULL IDENTITY(1,1),
    EmployeeName varchar(100) NOT NULL,
    Department_ID int NOT NULL,
    IsActive bit default(1),
    EmployeeDOB date,
    EmailAddress varchar(100) NOT NULL,
    Dependents int
)

Assume that for this scenario, we want to map the id_Employee field from database to EmployeeID, and Department_ID field to DepartmentID in our object. This can be done as follows:

public class Employee
{
    //Custom Mappings

    // id_Employee in database gets mapped to EmployeeID field in our POCO
    [Column(Name = "id_Employee")]
    public int EmployeeID { get; set; }

    // Department_ID in database gets mapped to DepartmentID field in our POCO
    [Column("Department_ID")]
    public int DepartmentID { get; set; }

    //Auto Mappings - Same field names in database as in POCO
    public string EmployeeName { get; set; }
    public bool IsActive { get; set; }
    public DateTime? EmployeeDOB { get; set; }
    public string EmailAddress { get; set; }
    public int Dependents { get; set; }

    //A field not in Employee table, but will be used in custom query
    public string DepartmentName { get; set; }
}

Limitations

This library currently provides extension methods for SQL Server Connection only. Oracle and other database extensions will be added in future.

 

 

Add comment

Loading