Database Access design at the petshop data access layer

Anatomy petshop Series II

Ii. Database Access design at the petshop data access layer
In Series I, I analyzed the architecture design of petshop and mentioned the concept of hierarchy. Starting from this section, I will performCodeLevel Analysis for a more detailed and in-depth understanding. In petshop 4.0, the introduction of ASP. NET 2.0, so the content of the data layer is more extensive and complex, including database access, messaging, membership, and profile. In Series 2, I will introduce the design of database access.

In petshop, the database objects to be processed by the system are divided into two types: one is the data entity, which corresponds to the corresponding data table in the database. They have no behavior and are only used to represent the data of objects. These entity classes are put in the ModelProgramSet. For example, the object class orderinfo corresponding to the order table, the class diagram is as follows:

These objects do not have the persistence function. Simply put, they are the data carrier, facilitating the business logic to read/write data tables. Although the attributes of these classes map the columns of the data table, each object instance corresponds to each row of the data table, these entity classes do not have the corresponding database access capability.

Since both the data access layer and the business logic layer operate on these data entities, the assembly model will be referenced by the modules of these two layers.

The second type of database object is the business logic object of data. The business logic referred to here is not the domain business logic in the business logic layer (in this sense, I prefer to call the business logic layer "Domain logic layer "), in general, these business logics are basic database operations, including select, insert, update, and delete. Because these business logic objects only have behaviors and are irrelevant to data, they are abstracted as an independent interface module idal. For example, the iorder interface corresponding to the order table:

Separating data entities from related database operations is in line with the object-oriented spirit. First, it embodies the principle of "separation of duties. Separating the data entity from its behavior reduces the dependency between the two. When the data behavior changes, it does not affect the data entity objects in the model module, avoiding excessive and excessive responsibilities of a class, as a result, the quote of this class has a "catastrophic" effect. Secondly, it embodies the "abstract" spirit, or the best embodiment of "interface-oriented programming. Abstract interface module idal, which is completely isolated from specific database access. This implementation-independent design ensures system scalability and database portability. In petshop, SQL Server and Oracle are supported, so their implementations are placed in two different modules: sqlserverdal and oracledal.

Taking order as an example, there are different implementations in the sqlserverdal and oracledal modules, but they all implement the iorder interface at the same time,

From the implementation of the database, petshop shows the bloated and ugly architecture without the ORM framework. To perform insert and select operations on data tables, take SQL Server as an example and use objects such as sqlcommand, sqlparameter, and sqldatareader to complete these operations. It is especially complicated to pass parameter. In petshop, a large number of string constants are used to save the parameter name. In addition, petshop Provides abstract helper classes for SQL Server and Oracle, and encapsulates some common operations, such as executenonquery and executereader.

Without an Orm, using the helper class is a good strategy. using it to encapsulate basic database operations can reduce a lot of code related to database operations, this reflects the principle of Object reuse. Petshop puts these helper classes in the dbutility module. The methods exposed by the helper classes in different databases are basically the same, except for some special requirements, for example, in Oracle, the bool type processing method is different from that in SQL Server, which specifically provides orabit and orabool methods. In addition, the methods in the helper class are static methods to facilitate calls. The oraclehelper class diagram is as follows:

For the data access layer, the most headache is the processing of SQL statements. In the early CS structure, the data access layer and the business logic layer were closely integrated due to the absence of a three-tier architecture design. Therefore, SQL statements were distributed across every corner of the system. This brings great difficulties to program maintenance. In addition, since Oracle uses PL-SQL while SQL Server and Sybase use T-SQL, both follow the standard SQL syntax, but there are still differences in many details, if a large number of SQL statements are used in a program, it is undoubtedly difficult to port the database.

The best way is to use stored procedures. This method makes the program more clean and tidy. In addition, because the stored procedure can exist in the form of database scripts, It is also easy to transplant and modify. However, this method is still flawed. First, it is relatively difficult to test the stored procedure. Despite the corresponding debugging tools, code debugging is still complicated and inconvenient. Second, it brings obstacles to system updates. If the database access is completed by a program, On the. NET platform, we only need to modify the program and copy the re-compiled Assembly xcopy to the deployed server. If a stored procedure is used, a dedicated DBA script is required to re-run the Stored Procedure for security reasons. The deployment method is limited.

I used a special table to store SQL statements in a project. To use related SQL statements, you can use keywords to search for corresponding statements. This method is similar to the call of the stored procedure, but avoids deployment problems. However, this method cannot be guaranteed in terms of performance. It is only applicable to scenarios with few SQL statements. However, with good design, we can provide different tables for various businesses to store SQL statements. In the same way, these SQL statements can also be stored in XML files, which is more conducive to system expansion or modification. But the premise is that we need to provide it with a dedicated SQL statement management tool.

The use of SQL statements cannot be avoided, and there is no final conclusion on how to better apply SQL statements. However, there is a principle that deserves our attention, that is, "try to make SQL statements exist in the specific implementation of the data access layer ".

Of course, if ORM is applied, everything will be different. The ORM framework provides basic select, insert, update, and delete operations for data access. For example, in nhibsion, we can directly call the Save method of the isession object to insert (or create) A data entity object:
Public void insert (orderinfo order)
{
Isession S = sessions. getsession ();
Itransaction trans = NULL;
Try
{
Trans = S. begintransaction ();
S. Save (order );
Trans. Commit ();
}
Finally
{
S. Close ();
}
}

There are no SQL statements, no annoying parameters, or even no special consideration for transactions. In addition, such a design is irrelevant to the database. nhibect supports different databases through the dialect mechanism. The only thing we need to do is define the HBM file for orderinfo.

Of course, the ORM framework is not omnipotent. In the face of complicated business logic, it cannot completely eliminate SQL statements and replace complex database access logic, however, it is a good embodiment of the "80/20 (or 90/10) Law" (also known as the "Pareto Law"), that is: less flowers (10%-20%) the effort can solve most of the problems (80%-90%), while much effort is needed to solve the remaining few problems. At least, the crud operations that occupy the vast majority in the data access layer, by using the ORM framework, we only need to spend a very small amount of time and effort to solve them. This undoubtedly shortens the entire project development cycle.

Back to the petshop discussion. Now we have data entities and abstract interfaces and implementations of data objects. It can be said that the subject of database access has been completed. We still have two problems to solve:
1. Management of Data Object Creation
2. Facilitate database migration

In petshop, the data objects to be created include order, product, category, inventory, and item. In the previous design, these objects have been abstracted as corresponding interfaces, and their implementation varies with the database. That is to say, the created object has multiple categories, and each category has different implementations. This is a typical abstract factory model application scenario. The two problems mentioned above can also be solved through the abstract factory model. The standard abstract factory pattern class diagram is as follows:

For example, the Order object for creating SQL Server is as follows:
Petshopfactory factory = new sqlserverfactory ();
Iorder = factory. createorder ();

To ensure database portability, the factory must act as a global variable and be instantiated when the main program is running. However, although this design has achieved the goal of "encapsulation change", when creating a petshopfactory object, it is inevitable that a specific class of sqlserverfactory appears, that is, at this level, the program has a strong dependency with sqlserverfactory. Once the entire system requires support for Oracle, you also need to modify this line of code:
Petshopfactory factory = new oraclefactory ();

This behavior of modifying code is obviously unacceptable. The solution is "dependency injection ". The "dependency injection" function is usually provided by dedicated IOC containers. On the Java platform, such containers include spring and picocontainer. On the. NET platform, spring. NET is the most common. However, in the petshop system, there is no need for specialized containers to implement "dependency injection". A simple approach is to use the configuration file and reflection functions. In other words, we can configure the complete Class Name of the specific factory object in the web. config file. However, when we use the configuration file and reflection functions, the creation of a specific factory seems a little "Superfluous", and we can completely in the configuration file, direct to specific database object implementation classes, such as petshop. sqlserverdal. iorder. Then, the factory in the abstract factory model can be simplified into a factory class, so I call this model "Abstract Factory model with simple factory characteristics". The class diagram is as follows:

The dataaccess class completely replaces the previously created factory class system. It is a sealed class. The methods used to create various data objects are static methods. The purpose of using this class to achieve the abstract factory is because of the use of configuration files and reflection, as shown in the following code snippet:
Public sealed class dataaccess
{
// Look up the Dal implementation we shoshould be using
Private Static readonly string Path = configurationmanager. deleettings ["webdal"];
Private Static readonly string orderpath = configurationmanager. deleettings ["ordersdal"];

Public static petshop. idal. iorder createorder ()
{
String classname = orderpath + ". Order ";
Return (petshop. idal. iorder) Assembly. Load (orderpath). createinstance (classname );
}
}

In petshop, this dependency configuration file and reflection method are extremely common for object creation, including ibllstategy and cachedependencyfactory. These implementation logics are distributed throughout the petshop system. In my opinion, we can reconstruct them on this basis. That is to say, we can provide implementation similar to "service locator" for the entire system:
Public static class servicelocator
{< br> Private Static readonly string dalpath = configurationmanager. appsettings ["webdal"];
Private Static readonly string orderpath = configurationmanager. deleetask[ "ordersdal"];
//......
Private Static readonly string orderstategypath = configurationmanager. deleettings ["orderstrategyassembly"];

Public static object locatedalobject (string classname)
{< br> string fullpath = dalpath + ". "+ classname;
return assembly. load (dalpath ). createinstance (fullpath);
}< br> Public static object locatedalorderobject (string classname)
{< br> string fullpath = orderpath + ". "+ classname;
return assembly. load (orderpath ). createinstance (fullpath);
}< br> Public static object locateord Erstrategyobject (string classname)
{< br> string fullpath = orderstategypath + ". "+ classname;
return assembly. load (orderstategypath ). createinstance (fullpath);
}< br> //......
}

Therefore, servicelocator can be used for Code related to the so-called "dependency injection. For example, dataaccess can be simplified:
Public sealed class dataaccess
{
Public static petshop. idal. iorder createorder ()
{
Return (petshop. idal. iorder) servicelocator. locatedalorderobject ("order ");
}
}

Through servicelocator, all the namespace values related to the configuration file are managed in a unified manner, which facilitates the management and future maintenance of various dynamically created objects.