Content of Document
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())
{
}
If database transaction is required, you can do it in this way:
using (APDatabase db = new APDatabase())
{
db.BeginTrans();
try
{
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))
{
}
}
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))
{
}
}
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.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.