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.
