Developing Database Applications with an O-R Mapping Framework Part 1
Developing Database Applications with an O-R Mapping Framework Part 1
Download this article in Word format
Multi-Layered architecture has for a long time been proved to be an effective way to build reliable, maintainable and flexible enterprise applications. Originally, we had tree layers in the applications we built, they are Presentation (UI) layer, BL (Business Logic) layer and Data layer. As we proceeded, we found that another two layers are needed. One is User Interface Process layer between UI and BL layers, and the three together work as the MVC pattern which is a very good approach that can effectively separate UI and BL layers; another is Data Access layer that exists between BL and Data layers, and that Data Access layer is what the DAP Framework is all about.
Who Should Read This Article
This guide is targeted at software architects who has already been familiar with architecture design and can use this framework as an alternative way to realize the O-R mapping, or developers who is interested Data Access layer design and can get a first view of what the O-R mapping is primarily about.
Why O-R mapping?
Because of the prevalence and maturity of the relational databases, they are widely used in the database applications. A number of problems, however, arose due to the mismatch between the record-oriented and the object-oriented representations of data. And that’s the main reason why O-R mapping was introduced as a solution.
What Is an O-R mapping anyway?
Before you go any further, you need to get familiar with the following key ideas, and in the sections to come, each of them is to be described in detail. How they can be achieved and how we should employ them in the real-world enterprise applications will also be introduced.
Mapping: There must be some mapping between a class and its persistence store (for example, a table in a database), and between object attributes and the fields (columns) in a record. That is, there must be a schema mapping between the two schemas.
Now, you may be able to figure out what an O-R mapping is.
To make the most of the DAP Framework, you need to understand the design of it. The DAP Framework was built with C# and designed to be an extensible and reusable package that can be freely deployed in any project developed with the programming languages that conform to Common Language Specification (CLS). The DAP Framework works in Multi-Layered architecture as an O-R mapping framework through which records in database table can be easily mapped to objects, which also provide functionalities of object caching, object states management, distributed transaction, and etc.
How Does an O-R Mapping Work in the DAP Framework?
In spite of knowing what an O-R mapping is, some of you, especially those who are new in this area, may still be puzzled by how it works. What’s the secret behind an O-R mapping framework? To begin, let’s make a comparison between the following two diagrams in figure 1.
On the left side is a UML class diagram. Instances of the Project class are objects we work with in Business Logic layer. There are 4 fields that represent the attributes of the BO (Business Object) Project in the real world. On the right side, it is a RDB table diagram. It also has 4 fields which are used to save the attributes of Project objects, e.g. id, name, number and cost. Instances of this table schema are records that hold many projects, so we can see records in RDB table as the persistent format of BOs. Let’s proceed to see what the C# code of Project class looks like.
In this Project class, there are 4 fields and 4 properties each of which corresponds to one field, so we can now map these 4 properties to those 4 fields in that RDB table in figure 2.1.
Ordinarily, there are three ways to implement an O-R mapping framework.
Figure 2. illustrates the overall architecture of the DAP Framework.
There are two parts in that figure. Panes in olivedrab represent the components in the DAP Framework. They are the essential components that work together to realize the mapping between objects and RDB records. What are in turquoise color indicate a configuration file and code that will access this framework. The configuration file is extremely important to the DAP Framework, because it contains the necessary mete-data that the DAP framework will use to map objects to RDB records; and code in BL layer will use this framework to select objects from or update objects to Data layer.
Primarily because the DAP Framework is an independent layer in Multi-Layered system architecture, it needs to provide a facade, a common pattern to provide a unified interface to a subsystem, that upper layers can use consistently to access the Data layer underneath.
Notice that when you use Select method to retrieve object from the data layer, only one object will be returned, even if there are many records in the database are true of the passed conditions, and that one will be the first in the group. By contrast, when you use SelectCollection method, a collection will be retrieved, even though only one record in the database is true of the conditions passed, and it will be the only one element in the returned collection.
This component is the exact place where mapping between objects and records is realized. With the helps from other two components, Sql Factory and SqlParameter Factory, it knows how objects can be retrieved from databases and how those objects can be subsequently updated into databases. Reflection is used in this component to dematerialize objects to databases. An application block from Microsoft, called Data Access Block or SqlHelper, is used in this component. It wraps all ADO.NET database commands.
This component works as the factory that's used to generate SQL statements. For the performance reason, SQL statements generated from this factory will be cached, so no need to generate a new SQL statement if it has already been requested. This component internally calls other factories for getting the mete-data from the configuration file.
This components is used to create SqlParameter objects which will be passed as parameters to ADO.NET commands. This component will internally call other factories for getting the mete-data from the configuration file.
This component is used to read the mete-data from the configuration file. And at the very beginning, the format of the configuration will be validated against a predefined schema validation file. This is to make sure the format of the configuration is correct, not incomplete. If the validation fails, a meaningful exception will be thrown to inform developers what the problem is. Thus, less codes are needed to prevent the system built on the framework from being cracked down.
There is a heavy use of configuration file in the DAP Framework. The configuration file contains the mete-data that will be used dynamically at run time. Following is a sample configuration file.
<cachePolicies> that contains one or more child tags, each of which represents a cache policy that will be used to cache objects retrieved from databases. Each <cachePolicy> tag has three attributes. name is used to uniquely represent a cache policy, and a type can be related to a cache policy through this attribute. cacheExpirationMode will be used with cacheExpirationInterval together to indicate how long objects should be cached. There are following examples:
Cached objects with this cache policy will expire at 23:59:59 every day.
Cached objects with this cache policy will expire in 20 minutes.
Cached objects with this cache policy will never expire.
NOTE that if Absolute is used as the cache expiration mode, a time-format value, e.g. 23:59:59, should be assigned to the cache expiration interval attribute. But if Sliding is used as the cache expiration mode, an integer value should be assigned to the cache expiration interval attribute. No need to assign the cache expiration interval attribute if None is used as the cache expiration mode.
<databases> that contains one or more child tags, each of which represents a relational database that will be used. Each <database> tag has two attributes. name is used to uniquely represent a database, and a type can be related to a database through this attribute. conString is used to save the connection string of the database.
<types> that contains one or more child tags, each of which represents a business type or business class. Each <type> tag has four attributes and one child tag. name is used to indicate the name of the type, it is actually the class name. database is used to relate this type to a database specified in the <database> tag. table is used to indicate which table in the specified database is mapped to this type. cachePolicy is used to relate this type to a cache policy specified in the <cachePolicy> tag. This <type> tag has a child tag <properties> which also contains many child tags, each of which represents a property of this type. Each <property> tag can have at most five attributes. name is used to indicate the property name. field is used to indicate the name of the class field that this property will internally access. dbField is used to indicate the name of the field in the specified database table, and this database field is mapped to the class field just mentioned. type is used to indicate the property type, and it is also the type of the class field. This type will be converted to its counterpart type in the database at run time. The last attribute is isPrimary. If the database field just mentioned is the primary key in its table, this attribute will be set to true, otherwise, false.
That is all about the configuration file. Actually, you don't have to code this file manually, SuperType, an O-R mapping tool, can be used to generate this file from database automatically.
In the DAP Framework, each business class who wants its instances to be mapped to RDB records must inherit from the PersistentObject class which provides many implementations that derived class can use. Figure 4 is the UML diagram of the PersistentObject class.
The DAP Framework adopts the State pattern and each persistent object has its own state. There are in all five states:
New State which means a persistent object is new, not retrieved from the database.
Figure 5 shows the state chart for a persistent object. From the state chart, you may know that the response to an operation depends on the state of the persistent object. If a persistent object is selected from a database, its state is OldClean, and after it’s saved, its state transforms to OldDirty, then it’s committed, at this time, it’s updated to the database, and its state goes back to OldClean. If this persistent object is totally new, nothing will happen if it’s saved, and its state remains the same, then it's committed, at this time, it is inserted into the database, and its state is changed to OldClean.
In the DAP Framework, a persistent object created explicitly by its constructor has the default state New; and a persistent object retrieved from the database has the default state OldClean.
The DAP Framework provides developers the ability to clearly separate Data layer and Business Logic layer. Developers have a reasonable and convenient way to retrieve objects from and update objects to the Data layer through the facade of the framework. The framework can work with SuperType, an O-R mapping tool, to develop intricate enterprise application in a record time.
Feature List of the DAP Framework 2.0
The DAP Framework 2.0 has the following major features.
A Sample Class and a Sample Configuration File
In order to demonstrate the features of the DAP Framework, we need to at first have a sample class and a sample configuration file.
Following is a configuration file which has already been introduced.
NOTE that if the application you are building is a Windows application, this configuration file should be placed into the folder where the EXE file is; if you are building an ASP.NET Web application, this configuration file should be placed into the root folder of the Web application, and its extension should changed to config to prevent it from being access through client browsers. Also, the name of this configuration file should be configured in the application configuration file, app.config or web.config, as the following way:
The following code snippet first materializes an Author instance based on the property ID and its value passed to the database, then the first name of this author is changed to “Aaron”, and at last, this author is dematerialized to the database.
NOTE that if there are more than one records in the database are true of the passed condition, only the first one will be converted into an object and returned.
The following code is used to materialize a collection of Author instances. All authors whose first name contains the letter “a” will be returned. Subsequently their phone numbers are changed, and then they are dematerialized to the database.
NOTE that if there is no record in the database is true of the passed condition, the returned ArrayList will contain no author, but the list itself has already been instantiated.
The folowing code demonstrates how to retrieve those authors whose first name contains the letter "a", and also they must live in the state CA.
The folowing code demonstrates how to retrieve those authors whose first name or last name contains letter "a".
Paging is enabled in the DAP Framework 2.0. The following code demonstrate how to separately retrieve the first ten authors and the second ten authors.
In the following code, there are two authors, author1 and author2. One is constructed explicitly using the constructor, and the other is retrieved from the database. When both methods, Save and Commit, are invoked on these two object, the first one, author1, is inserted to the database, the second one, author2 however, is updated into the database. How could that happened? I mean how the framework is able to know which object should be inserted into the database and which object should be updated to the database. That's because these two Author objects have their states respectively. The Author object instantiated using the constructor has the default state New, and the Author object retrieved from the database has the default state OldClean, so the framework knows how to separately handle these two Author objects.
The following code shows that an Author object is retrieved from the database, and it is subsequently committed without any operation on that object. In this case, that object is not updated to the database, actually, no database access happens. Because the framework knows that the object's state is OldClean, no operation happened on it. For more information about the state management, please review the figure 5 State chart of a persistent object.
Objects retrieved from the database can be cached by the framework automatically. If some kind of objects are requested frequently, they can be cached to promote system performance. In order to cache objects of a specified type, you should configured that type in the configuration file. Take the Author type for example. Looking at the Author tag in the sample configuration file, you can see that an attribute called cachePolicy has the value Absolute, the name of a cache policy, and that cache policy, configured in the <cachePolicy> tag, tells that cached objects with this cache policy will expire at 23:59:59 every day.
The same result can be illustrated by the following code, which means that if an author is requested several times, the same cached author will always be returned no matter which query parameter is used.
If transaction were not supported by an O-R mapping framework, developers using it would finally come to a place where something is a mission impossible. Fortunately, distributed transaction is realized in the DAP Framework 2.0 in a reasonable and seemingless way.
NOTE that the sequence that objects are added into the transaction is the sequence that those objects are going to be committed into databases, so objects MUST be added into the transaction in a reasonable sequence to prevent exceptions from being thrown.
If the DAP Framework 2.0 is being used in an application that is running on a server, there might be the cases when many users will access this framework simultaneously. This concern was considered the first time this framework was designed. For example, there can be only one transaction being executed at a certain time while the framework is running to prevent databases from being locked; if objects of a specified type are configured to be cached, selecting those objects will be synchronized to prevent duplicated objects from being instantiated.
Pet Shop Built on the DAP Framework 2.0
The .NET Pet Shop was originally built by Microsoft as a sample application for .NET developers, and therefore many developers are so familiar with it, some of them even looked into the code of it. This is the main reason why it's been chosen as the sample application built on the DAP Framework. Actually only two days were used to rebuild this application, partially because that the UI and database remain changed, and partially because it is so straightforward to build database applications on the DAP Framework. In this newly created Pet Shop, new Data Access layer, Business Logic layer and controller were used, and almost all features the framework has have been used in it. I personally think that the new Business Logic layer is more reasonable than that of the original one, for it presents clearer associations among different classes. Figure 6 shows the architecture of this new Pet Shop.
UI and Data layers remain unchanged. In UI layer, there exist many ASP.NET web forms and some user and custom controls. And Data layer is the SQL-Server 2000 database.
This framework package has been applied in several real-world projects, and it has been fully tested in the following software environment, so you need to make sure that your system meets the following minimum software requirements:
Microsoft Windows 2000 / XP / 2003.
Database tables should not have fields that are auto-incremental, all fields must be inserted explicitly.
A major advantage of adopting an O-R mapping framework is that it can minimize the complexity of systems and thus help developers build real OO applications. Developers don’t have to care about how to write SQL statements, they can even have no knowledge of what ADO.NET is, they primarily work on how objects can work together to get something done.