DB-independent Data Access Model in APEnnead.net

 

Content of Document

• Summary
• Data Access Model
• DB Access Layer
• DB Structures Defined
• DB Access Grammars
• Business Logic Model
• Data Structures Defined
• Business Logic Layer
• Call
• Insert Command - How to insert data
• Update Command - How to update data
• Delete Command - How to delete data
• Select Command - How to query data
• Simple Query
• Simple Condition Query
• Multi-condition Combination Query
• Multi-table Joint Query
• Order By & Group By
• Specificed number of rows returned
• Paging Query

Summary

On APEnnead.net platform, database schema and basic access functions will be generated automatically, only if you defined data structure and the relationships as businessMode in APGen file. User can invoke the generated method directly to do database searching, and CRUD operation.
About the section of configuration, How to Configures APGen files.

APEnnead.net allow us accessing database like working with object. Through APLogicProvider, user could switch to different database by changing the APLogic section in web.config file without code change. So all your database accessing is DB independent.

In APEnnead.net, Data Access Model following figure.

A sample of businessMode section declared in .apgen file, see Samples of Business Mode

Keep in mind, Data Access Model is totally open. From Providers of database accessing to Business Mode, you could extend them to achieve your special requirements. You’ll know how to do this while you go in deep enough in your project.

Data Access Model

Data Access Model is the core of encapsulating data accessing operations in object.

DB Access Layer

DB Access Layer consists of 5 classes, APDatabase, APQuery, APDalProvider, APDal and APQueryParser.

APDatabase encapsulated database connection, and have transaction support. Based on the APLogic section of web.config, it supplies different database accessing. In our code, we’ll use it like this:

using (APDatabase db = new APDatabase())
{
  // Database access code here
}

If database transaction is required, you can do it in this way:

using (APDatabase db = new APDatabase())
{
  db.BeginTrans();
  try
  {
    // Database access code here
    db.Commit();
  }
  catch
  {
    db.Rollback();
    throw;
  }
}

After we created APDatabase object, we could access database with following methods it supplied.

public virtual int ExecuteNonQuery(APQuery query);
public virtual IDataReader ExecuteReader(APQuery query);
public virtual IDataReader ExecuteReader(APQuery query, APQueryPaging paging);
public virtual IDataReader ExecuteReader(APQuery query, int maxReturnCount);
public virtual object ExecuteScalar(APQuery query);

ExecuteNonQuery use for none-return value SQL call, Insert, Update and Delete etc.
ExecuteReader use for dataset.
ExecuteScalar use for return a scalar, most common use is to return the value of Count。
All above methods require a APQuery parameter.

APQuery - has Object-Oriented grammar for database accessing. It stands for a database operation, just like what a SQL command could do. Let’s take a look the constructor of APQuery first.

public APQuery(APSqlSelectCommand command, APSqlFromClause fromClause);
		 
public APQuery(APSqlSelectCommand command, APSqlFromClause fromClause,
               APSqlWhereClause whereClause);
		 
public APQuery(APSqlSelectCommand command, APSqlFromClause fromClause,
               APSqlGroupByClause groupByClause, APSqlWhereClause havingClause);
		 
public APQuery(APSqlSelectCommand command, APSqlFromClause fromClause,
               APSqlWhereClause whereClause, APSqlOrderByClause orderbyClause);
		 
public APQuery(APSqlSelectCommand command, APSqlFromClause fromClause,
               APSqlWhereClause whereClause, APSqlGroupByClause groupByClause,
               APSqlWhereClause havingClause);
		 
public APQuery(APSqlSelectCommand command, APSqlFromClause fromClause,
               APSqlWhereClause whereClause, APSqlGroupByClause groupByClause,
               APSqlWhereClause havingClause, APSqlOrderByClause orderbyClause);
		 
public APQuery(APSqlInsertCommand command, APSqlSetClause setClause);
		 
public APQuery(APSqlUpdateCommand command, APSqlSetClause setClause,
               APSqlWhereClause whereClause);
		 
public APQuery(APSqlDeleteCommand command, APSqlWhereClause whereClause);

For above constructors of APQuery we could see that it could express Select, Insert, Update and Delete operations with Order By, Group By, and Having clause supported. How to create APSqlSelectCommand, APSqlFromClause and other objects? Beside their native constructors, APDal also have lots of quick solutions for you to create these objects.

APDal - is the place where APEnnead.Net defines atom operations of database accessing. Each table should map to one APDal. And APEnnead.net could create all the APDal definition for you automatically based on the businessMode in .apeng file. And each method in it corresponds to one basic database accessing operation.

APDalProvider - is responsible for translate objective language of APQuery into real SQL language of a specific database. By default APEnnead.net supplied SqlAPDalProvider for Microsoft SqlServer. And you can download the OracleAPDalProvider for Oracle database.

APQueryParser - is used by APDalProvider. It is responsible for parse DB Access Grammars to SQL Commands. By default APEnnead.net has supplied SqlAPQueryParser for Mcrosoft SqlServer. And we also implemented OracleAPQueryParser for Oracle database.

DB Structures Defined

In the model, we use APTableDef, APColumnDef, APRelationDef to define DB Structures and relationships. Each APTableDef defines one table, each APColumnDef defines one column, and each APRelationDef defines the relationship between tables.

APEnnead.net will generate DB structures for you automatically according to the businessMode section defined in .apeng file. You could refer to them directly. All these classes have declared implicit, explicit and static methods by which you could create DB Access Grammars conveniently.

DB Access Grammars

In the model, DB Access Grammars are objects extending from APSqlPhrase class. APSqlPhrase is a uni-directional list. APQueryParser will parse Grammars to generate DB Access Command.

APSqlPhrase derives three kinds of grammars: Command, Clause, Phrase:

Command

APSqlSelectCommand	corresponding to SQL SELECT statement.
APSqlInsertCommand	corresponding to SQL INSERT statement.
APSqlUpdateCommand	corresponding to SQL UPDATE statement.
APSqlDeleteCommand	corresponding to SQL DELETE statement.

Clause

APSqlFromClause	corresponding to SQL FROM clause. Used table in the DELETE, SELECT, and UPDATE statements.
APSqlWhereClause	corresponding to SQL WHERE clause or HAVING clause.
APSqlGroupByClause	corresponding to SQL GROUP BY clause.
APSqlOrderByClause	corresponding to SQL ORDER BY clause.
APSqlSetClause	corresponding to SQL Set clauses. Used in the UPDATE statements.

Phrase

APSqlPhrase	A phrase in SELECT statement, an item of select_list.
APSqlAggregationPhrase	A aggregation phrase in SELECT statem, an item of select_list.
APSqlWherePhrase	A phrase in WHERE clause. Abstract class, inheritance class: APSqlConditionPhrase - Operate enumerations Equals NotEqual GreaterThan GreaterThanOrEqual LessThan LessThanOrEqual Like LeftLike RightLike In NotIn APSqlOrConditionGroup - OR operate between two APSqlWherePhrase APSqlAndConditionGroup - AND operate between two APSqlWherePhrase
APSqlFromPhrase	A phrase in FROM clause. Include join type define: None - Not join Inner - Inner join Left - Left join Right - Right join Full - Full join Cross - Cross join
APSqlSetClause	corresponding to SQL Set clauses. Used in the UPDATE statements.

Business Logic Model

Business Logic Model organizes object oriented DB access into the business logic for application use.

Data Structures Defined

APEnnead.net will generate Data structures for you automatically according to the businessMode section defined in .apeng file. You could refer to them directly. Each data structure maps to a table in Data Access Model.

Business Logic Layer

Business Logic Layer could organize database atom operations into business logic through Data Access Model. You could invoke simple atom operation or compose lots of atom operations with transactions.

APEnnead.net will generate most of the common functions for you based on the businessMode section defined in .apeng file. You could override or extend the generated methods when it is necessary.

Call

We’ll show you some code to help understand this topic. All SQLs in our example are generated for Microsoft SqlServer. If you are using other database, the grammar might be a little different.

Insert Command - How to insert data

When we create APQuery object, if the first parameter is APSqlInsertCommand, it means creating an Insert command. And we should use APSqlSetClause together with APSqlInsertCommand. APSqlInsertCommand and APSqlSetClause objects are created with Insert and Set methods of APDal class.

APQuery query = new APQuery(
  APDal.Insert(APDBDef.Category),
  APDal.Set(
    new APSqlSetPhrase(APDBDef.Category.CategoryId, "0001"),
    new APSqlSetPhrase(APDBDef.Category.Name, "Birds"),
    new APSqlSetPhrase(APDBDef.Category.Description, "Birds")));

Equal to following SQL:

INSERT (CategoryId, Name, Description)
  VALUES ('0001', 'Birds', 'Birds')

Update Command - How to update data

If we create APQuery object with APSqlUpdateCommand, it means creating a Update command. And Update command should work with APSqlSetClause and APSqlWhereClause.

APQuery query = new APQuery(
  APDal.Update(APDBDef.Category),
  APDal.Set(new APSqlSetPhrase(APDBDef.Category.Description, "Birds desc")),
  APDal.Where(APDBDef.Category.CategoryId == "0001"));

Equal to following SQL:

UPDATE Category
  SET Description = 'Birds desc'
  WHERE Category.CategoryId = '0001'

Delete Command - How to delete data

If we create APQuery object with APSqlDeleteCommand, it means creating a Delete command. And Delete command should work with APSqlWhereClause to specify deleted rows.

APQuery query = new APQuery(
  APDal.Delete(APDBDef.Category),
  APDal.Where(APDBDef.Category.CategoryId == "001"));

Equal to following SQL:

DELETE FROM Category
  WHERE Category.CategoryId = '0001'

Select Command - How to query data

If we create APQuery object with APSqlSelectCommand, it means creating a Query command.

Simple Query

First let’s implement a simple query

APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category));

Equal to following SQL:

SELECT * FROM Category

And we could specify return what fields:

APQuery query = new APQuery(
  APDal.Select(APDBDef.Category.CategoryId, APDBDef.Category.Name),
  APDal.From(APDBDef.Category));

Equal to following SQL:

SELECT CategoryId, Name FROM Category

And we could use aggregate functions in Select clause.

APQuery query = new APQuery(
  APDal.Select(APDal.CountAsterisk()),
  APDal.From(APDBDef.Category));

Equal to following SQL:

SELECT count(*) FROM Category

Simple Condition Query

Of cause we could use APSqlWhereClause to describe search conditions.

APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category),
  APDal.Where(APDBDef.Category.Name == "Birds"));

Equal to following SQL:

SELECT * FROM Category WHERE Name = 'Birds'

We could use APColumnDef to describe search condition too:

APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category),
  APDal.Where(APDBDef.Category.Name.Like("Birds")));

Equal to following SQL:

SELECT * FROM Category WHERE Name LIKE '%Birds%'

Multi-condition Combination Query

Through & and | operations we could combine search conditions easily.

APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category),
  APDal.Where(APDBDef.Category.CategoryId == "0001" 
    & APDBDef.Category.Name == "Birds"));

Equal to following SQL:

SELECT * FROM Category WHERE CategroyId = '0001' AND Name = 'Birds'

Combination conditions could be achieved by WhereAnd and WhereOr method of APDal. Following code did the same thing as above code.

APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category),
  APDal.WhereAnd(APDBDef.Category.CategoryId == "0001", 
  APDBDef.Category.Name == "Birds"));

Multi-table Joint Query

If we want to find all productions which are belong to Category “Birds”, we should join Category table and Product table to do Joint Query. For example:

APQuery query = new APQuery(
  APDal.Select(APDal.Product.Asterisk),
  APDal.From(APDBDef.Product, APDBDef.Category),
  APDal.Where(APDBDef.Product.CategoryId == APDBDef.Category.CategoryId
    & APDBDef.Category.Name == "Birds"));

Equal to following SQL:

SELECT Product.* 
  FROM Product, Category 
  WHERE Product.CategoryId = Category.CategoryID
    AND Category.Name = 'Birds'

In this example, we could remove APDBDef.Category from APSqlFromClause, because it will be filled in automatically.

Joint Query support inner-join, outer-join, left-join and right-join. Now let’s modify this example with inner-join.

APQuery query = new APQuery(
  APDal.Select(APDal.Product.Asterisk),
  APDal.From(APDBDef.Product,
    new APSqlFromPhrase(APDBDef.Category, APSqlJoinType.Inner,
    APDal.Where(APDBDef.Product.CategoryId == APDBDef.Category.CategoryId))),
  APDal.Where(APDBDef.Category.Name == "Birds"));

Equal to following SQL:

SELECT Product.* 
  FROM Product INNER JOIN Category on Product.CategoryId = Category.CategoryId
  WHERE Category.Name = 'Birds'

Order By & Group By

Sorting is very common features in SQL. APDal also has OrderBy method. Let’s take a look example about sorting:

APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category),
  null,
  APDal.OrderBy(APDBDef.Category.Name.Asc));

Equal to following SQL:

SELECT * FROM Category ORDER BY Name ASC

And APDal also have GrouBy method to implement grouping functions. By following code, we could count the amount of products in each category.

APQuery query = new APQuery(
  APDal.Select(APDal.Product.CategoryId, APDal.CountAsterisk("ProductCount")),
  APDal.From(APDBDef.Product), 
  APDal.GroupBy(APDBDef.Product.CategoryId),
  null);

Equal to following SQL:

SELECT Product.CategoryId, count(*) AS ProductCount
  FROM Product
  GROUP BY Product.CategoryId

Specificed number of rows returned

Once APQuery object is created, we could use ExecuteReader method of APDatabase class to query and get returned data set. ExecuteReader has an overload method to limit the returned rows. It works as the TOP keyword in SQL.

using (APDatabase db = new APDatabase())
{
  APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category));
  using (IDataReader reader = db.ExecuteReader(query, 20))
  {
    // read data
  }
}

Equal to following SQL:

SELECT TOP 20 * FROM Category

Paging Query

ExecuteReader method of APDatabase class also supports pagination. What we need is a APQueryPaging object.

using (APDatabase db = new APDatabase())
{
  APQuery query = new APQuery(
  APDal.Select(APDal.ColummAsterisk()),
  APDal.From(APDBDef.Category));
  query.PrimeryKeyColumnDef = APDBDef.Category.CategoryId;
  APQueryPaging paging = new APQueryPaging(20);
  paging.Current = 1;
  using (IDataReader reader = db.ExecuteReader(query, paging))
  {
    // read data
  }
}

Please keep in mind that PrimaryKeyColumnDef, which is required to specify the primary keys, for pagination. Primary key will be used to filter data so tables without primary key couldn’t do pagination.

Above code will return row 21 to 40 of Category table. It is equal to following SQL:

SELECT TOP 20 * 
  FROM Category
  WHERE Category.CategoryId NOT IN
    (SELECT TOP 20 Category.CategoryId FROM Category)

APEnnead.net is a development and deployment platform built on ASP.NET, which provides a different design concept. The goal is to provide you full range of support on project development, improve development productivity, and reduce cost.

With APEnnead.net save 30% - 70% person-months in each project. Here we show how to demonstration projects with QuickStep fast to customer.

Please view http://www.apennead.net get more informations and documents.