Database Object Model

• Download demo project - 538.41 KB
• Download source - 467.08 KB

Introduction

The Database Object Model (DBOM) is an ORM (Object Relational Mapping Project) like LINQ. It has been developed for programmers to manage their projects related to database as quickly as possible.

Because of open source infrastructure of the project, programmers can generate their own code that they need so they don't repeat a lot of code in their projects.

The project uses strict type definition and does not allow programmers to code string built queries so it decreases making mistakes.

The project contains generated or base classes to represent database objects which programmers want to access.

Background

When the project is developing, almost all the queries in the MSDN website are tested.

Using the Code

When coding SQL queries by DBOM, programmers can start directly and they don't need any information except a few lightbulbs for example as operator, "|".

The sample code below shows how code structure is by DBOM. The code that starts with "string sql = " got from MSDN, and the code that starts with "var ... = " is DBOM equality of the MSDN code.

WriteValues is a method to write outputs to visual controls as seen in the picture above.

Sample Code

Customer, Store, SalesTerritory, SalesOrderHeader are classes that correspond to the tables on AdventureWorks2008R2 database.

Sql is a static class to generate related classes, for example, Sql.Select generates SqlSelect class.

string sql = @"
    SELECT T.[Group] AS N'Region', T.CountryRegionCode AS N'Country'
        ,DATEPART(yyyy,OrderDate) AS 'Year'
        ,DATEPART(mm,OrderDate) AS 'Month'
        ,SUM(TotalDue) AS N'Total Sales'
    FROM Sales.Customer C
        INNER JOIN Sales.Store S
            ON C.StoreID  = S.BusinessEntityID 
        INNER JOIN Sales.SalesTerritory T
            ON C.TerritoryID  = T.TerritoryID 
        INNER JOIN Sales.SalesOrderHeader H
            ON C.CustomerID = H.CustomerID
    WHERE T.[Group] = N'Europe'
        AND T.CountryRegionCode IN(N'DE', N'FR')
        AND DATEPART(yyyy,OrderDate) = '2006'
    GROUP BY 
        ROLLUP(T.[Group], T.CountryRegionCode)
        ,ROLLUP(DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate))
    ORDER BY T.[Group], T.CountryRegionCode
        ,DATEPART(yyyy,OrderDate), DATEPART(mm,OrderDate);";

Customer c = new Customer();
Store s = new Store();
SalesTerritory t = new SalesTerritory();
SalesOrderHeader h = new SalesOrderHeader();

var select = Sql.Select(
    Sql.SelectColumn(
        t.Group | "Region",
        t.CountryRegionCode | "Country",
        Sql.DatePart(SqlDatePartType.Year, h.OrderDate) | "Year",
        Sql.DatePart(SqlDatePartType.Month, h.OrderDate) | "Month",
        Sql.Sum(h.TotalDue) | "Total Sales"),
    Sql.From(c | "C",
        Sql.InnerJoin(s | "S", c.StoreID == s.BusinessEntityID),
        Sql.InnerJoin(t | "T", c.TerritoryID == t.TerritoryID),
        Sql.InnerJoin(h | "H", c.CustomerID == h.CustomerID)),
    t.Group == "Europe"
        & Sql.In(t.CountryRegionCode, "DE", "FR")
        & Sql.DatePart(SqlDatePartType.Year, h.OrderDate) == "2006",
    Sql.GroupBy(
        Sql.RollUp(t.Group, t.CountryRegionCode),
        Sql.RollUp(Sql.DatePart(SqlDatePartType.Year, h.OrderDate), _
    Sql.DatePart(SqlDatePartType.Month, h.OrderDate))),
    Sql.OrderBy(t.Group, t.CountryRegionCode, _
    Sql.DatePart(SqlDatePartType.Year, h.OrderDate), _
    Sql.DatePart(SqlDatePartType.Month, h.OrderDate)));

WriteValues(select, sql);

Lesson 1

• Click on http://www.youtube.com/embed/m20ToWTT53M

Lesson 2

• Click on http://www.youtube.com/embed/i1woo9VnuOk

References

• http://msdn.microsoft.com/en-us/library/bb510741.aspx
• http://msdn.microsoft.com/en-us/library/ms124501.aspx

About the Author

Fatih Doğanay

After working for over 2 years about CAD-CAM programming, I've turned to develop programming on database and worked for 6 years on many projects.

For any suggestions about the project, email me at databaseobjectmodel[at]hotmail[dot]com.