Click here to Skip to main content
Click here to Skip to main content

Database Object Model

, 21 Feb 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
A Sample ORM project

Sample Image - maximum width is 600 pixels

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

Lesson 2

References

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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

No Biography provided

Comments and Discussions

 
QuestionWay too short for an article on such a subject PinprofessionalJV999912-Dec-13 1:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141015.1 | Last Updated 21 Feb 2012
Article Copyright 2012 by databaseobjectmodel
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid