Naweed Akram's Blog
<sharing: Knowledge />
XGENO.DBMapper - A new way of OR/DB Mapping
Posted: October 31, 2011

Before we dig into this particular component, lets us look at traditional way of doing things i.e. reading data from database (when ORM pattern is not in use).  

 

1) Open a SQL connection

2) Declare a SQL Command object (this is necessary when stored proc execution is required, else skip directly to next step

3) Declare a SQL DataAdapter, and get it to fill a DataSet

 

Just the above 3 steps consume around 5-10 lines of code and need to be done repetitively for all database calls. But the advantage is not from above. You can simply write a DB Layer class which encapsulates all this functionality. So let us look at the complex part.

 

4) You had obtained a DataSet with one or more tables. Now for each table, you tend to do something like this:

 

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….. 
}

Now, this is the usual code that you write for any database call and map your data to your .NET objects before you can use your .NET objects. What XGENO.DBMapper does is that it hides all the complexities between step 2 and 4, and with as minimum as 1 line of code, gives you the output of the database call as your .NET objects.

 

The attachment has a sample code (with SQL back-end) for you to test and explore advanced features of the component, but in a nutshell, this is how it is done (and very simply):

 

string _connString = @"server=.\SqlExpress;database=SampleDB;Integrated Security=SSPI;";
SqlConnection _dbConn = new SqlConnection(_connString);
_dbConn.Open();

var _employees = _dbConn.Query<Employee>(@"select * from Employees");
//The above is all you need to return a List<Employee> object 
//Now you can use it as: 

foreach (Employee _employee in _employees)
{
    Console.WriteLine("ID: " + _employee.ID + ", Name: " + _employee.Name + ", DOB: " +   _employee.DOB);
}  

 

Or, a bit more complex one (where an SP returns two resultsets: one is list of employees and other is a list of projects):

 

var _multi = _dbConn.SPMultiQuery("sp_GetEmployeesAndProjects");
var _allEmployees = _multi.Read<Employee>();
var _allProjects = _multi.Read<Project>();

Console.WriteLine("Displaying Employees");
foreach (Employee _employee in _allEmployees)
{
    Console.WriteLine("ID: " + _employee.ID + ", Name: " + _employee.Name);
}

Console.WriteLine("Displaying Projects");
foreach (Project _project in _allProjects)
{
    Console.WriteLine("ID: " + _project.ID + ", Name: " + _project.Name);
}

Post Comment
 *
Showing all comments


Often while looking for a solution, we always look whether it fits exactly to the requirement. Most of the cases, the abundant samples that are found need some customization or the other.

Found this new and easy way to map results coming from backend and to easily have it populate/update to/from the entity object is going to cut development costs (code once, use everywhere). Used in one of the projects while retrieve/upload data to CRM cloud appliaction, namely, Salesforce through serailizable formats

I give 4.25 out of 5

Posted: Jun 12, 2014 @01:10 AM

RSS Feed
Newsletter


Quick Links

Tags