CsharpGears Framework : Object-Relational Mapping

Reasons for Object-Relational Mapping

Since the rapid development of the relational databases and the acceptance of the Object-Oriented Programming model, a new need rose: to quickly and correctly map the persistance medium model (namely, the SQL tables) to the business objects in some high level programming language (C# for instance). Developers noticed that they invest significant amount of time writing the Data Access Layer (DAL) when starting off a new project, so they started looking for ways to decrease that time and effort. As time went by, several companies started producing software to efficiently map the SQL tables into business classes. Today, numerous of them are fairly famous, such as the NHibernate, SubSonic, Microsoft's Entity Framework, LINQ, etc. You can find more detailed list of ORM software on wikipedia.

Benefits of the ORM

The main benefits from using ORM software can be distinguished in the following categories:

• Far less time spent on writing the DAL
• Less errors on the low level of your application
• Robustness and easy maintenance, cleaner code in the Business Access Layer (BAL)
  

Object-Relational Mapping (ORM) with C# Gears Framework

I have thought of a way to offer ORM functionalities from my framework, and I think it is quite efficient, although it does not yet offer all the bells and whistles of other frameworks that exist today. For now 5 projects are built on top of the framework, and its ORM capabilities have proven to be very easy to deploy, flexible and easy to use. Basically, the GenericDbEntity is able to map the columns from the SQL datareader to the columns of the C# class and as a result of the query execution, it returns a generic list with such business objects. The query result data is avaliable for business logic immediately after its fetch from the database. By that I have measured I spend in average 60 - 70 % less time on development of the DAL. However, this number will vary from project to project, and as I use it on more projects I will be able to give more accurate percentage of the time saving. The ORM part of the CsharpGears Framework utilizes the generics and code reuse capabilities of C#, which are one of the biggest strengths of the OOP.

Example: Using the ORM capabilities within the CsharpGears Framework

Lets consider the following situation. Imagine you are writing ASP.NET application for an e-shop. You have SQL table in the database that holds the products which looks like this:

As you can see, there is nothing unusual with this table. Let's say that we want to write a procedure that retrieves all products in given category:

CREATE PROCEDURE ProductsInCategory_Select
(
 @CategoryId BIGINT
)
AS
BEGIN    
    SELECT
            ProductID
            SUBSTRING(Description,1,15) + '...' AS Description,
            ImageUrl,
            Price,
            Date,
            Title,
            Product.CategoryID,
            Name AS categoryName,
            ThumbnailUrl AS thumbnailUrl
    FROM
            Product INNER JOIN Category ON Product.CategoryID = Category.CategoryID
    
    WHERE
            Product.CategoryID = @CategoryID
END

... and lets assume that we have the following business classes:

public class Category
    {
        #region Attributes
        long categoryId;
        string categoryName;
        #endregion Attributes

        #region Constructors
        public Category() { }
        #endregion Constructors

        #region Properties
        public string CategoryName
        {
            get { return categoryName; }
            set { categoryName = value; }
        }

        public long CategoryId
        {
            get { return categoryId; }
            set { categoryId = value; }
        }
        #endregion Properties
    }

public class Product
    {
        #region Attributes
        private long id;
        private string title;
        private string description;
        private double price;
        private DateTime date;
        private string imageUrl;
        private string thumbnailUrl;
        private Category category;
        private int quantity;
        #endregion Attributes


        #region Constructors
        public Product()
        {
            category = new Category();
        }
        #endregion Constructors

        
        #region Properties

        public long CategoryId
        {
            get { return category.CategoryId; }
            set { category.CategoryId = value; }
        }
        public string CategoryName
        {
            get { return category.CategoryName; }
            set { category.CategoryName = value; }
        }
        public string ThumbnailUrl
        {
            get { return thumbnailUrl; }
            set { thumbnailUrl = value; }
        }

        public string ImageUrl
        {
            get { return imageUrl; }
            set { imageUrl = value; }
        }
        public DateTime Date
        {
            get { return date; }
            set { date = value; }
        }

        public double Price
        {
            get { return price; }
            set { price = value; }
        }

        public string Description
        {
            get { return description; }
            set { description = value; }
        }


        public string Title
        {
            get { return title; }
            set { title = value; }
        }

        public long Id
        {
            get { return id; }
            set { id = value; }
        }

        public Category Category
        {
            get { return category; }
            set { category = value; }
        }

        public int Quantity
        {
            get { return quantity; }
            set { quantity = value; }
        }
        
        #endregion Properties
    }

Then, in order to retrieve the products as a generic list, I would just use the following code:

GenericDbEntity<Product> dbEntity = new GenericDbEntity<Product>("ProductsInCategory_Select", ConnectionString);
            dbEntity.IntAddCommandParameter("@CategoryId", 1);
            List<Product> resultSet = (List<Product>)dbEntity.Select();

And that is practically, it. I am immediately ready to work with my business objects (Products in this case). As you can observe, the CsharpGears easily handles class composition and once again proves flexibility. This ease of use comes from the fact that the framework directly maps the SQL columns into the properties of the C# class - no static mapping is requiered. Hence, by writing a simple property I could handle composition of objects without any unnecesary code. 

Summary Note

As stated in the example above, the ORM software offers time saving techniques when writing software applications and makes the software more flexible and robust. A number of good tools already exist, but the free CsharpGears Framework also offers ORM functionalities efficiently. This framework does not intend to compete with LINQ or Entity Framework, but can come in handy when someone is still unfamiliar with them, or just wants a simple way to access data from the database and work with it in the application as business objects. The CsharpGears Framework so far has been reported to cut time spent on developing DAL up to 70 % and I hope in future I will be able to give more accurate percentages.