The purpose of the data tier is to manage all interaction with the database. Any form of creation, retrieval, alteration and deletion of data in the database is thus handled by the data tier.
By encapsulating all database interactions within a separate tier one obtains among other things that the other parts of the application are not troubled with the quibblings and issues usually involved with interacting with a database. The other application parts just use the interfaces provided by the data tier.
The data tier first and foremost contains classes that represent the individual tables of the database. That is, a one-to-one mapping exists between the tables in the database and classes in the data tier as illustrated by figure 5.2.
T able Class OO
(1:1)
Database T able
Figure 5.2:Illustration of the one-to-one mapping between a table class and a database table.
In addition to the table classes the data tier contains a number of common classes that support the table classes. These common classes are described in the subsections below.
The data tier is organized within the following namespace:
StudyPlanning.DAL
This namespace is divided up into a number of sub-namespaces. Confer appendix A for an overview of how the data tier is organized and brief descriptions of the individual classes in the tier.
The source code of the classes in the data tier can be found in chapter 1 in volume II.
5.4.1 Base Class
All the classes in the data tier that represent a table in the database inherit from a base class namedDbObject. It is beneficial to use a base class seeing that it becomes very easy to add or change an aspect common to all of the table classes.
The DbObject class is fairly plain in that it simply creates a connection to the database.
Hereby all subclasses have a database connection once instantiated.
5.4 Data Tier 69
5.4.2 Interaction with Stored Procedures
The table classes do not contain any inline SQL statements. We have chosen an approach where the table classes only operates on the database through stored procedures10 as illus-trated in figure 5.3.
Figure 5.3:Illustration of the communication between data tier, stored procedures and the database.
One can speak of inserting a separate tier between the data tier and the database, however, we conceive the stored procedures as part of the database.
The reasons for the chosen approach are several11: 1. Security:
(a) Parameterized Queries: Use of parameterized queries is enforced by the use of stored procedures. If one wants to invoke a stored procedure with some input it has to be done by means of input parameters. Similarly, as to output. By exclu-sively using parameterized queries the system is not vulnerable to security flaws like SQL and XPath injections. Use of parameterized queries thus complies with the security requirements posed in section 4.3.4.
(b) Fine-grained Authorization: The majority of database systems, including SQL Server12, provide security mechanisms that make it possible on a fine-grained level to restrict the use of a stored procedure. Use of stored procedures thus freely makes an extra level of security available.
2. Reuse: Two or more methods in a table class may have the need for the same query.
Placing the query in a stored procedure allows for reuse of queries.
3. Convenience: SQL is an independent language and it seems convenient to have T-SQL statements placed in a self-contained media – the alternative being a cumbersome build-up of strings (holding the queries) in the table classes.
The drawback of the chosen approach is that not all database systems support stored pro-cedures for which reason it may be more difficult to switch over to some database systems than others.
10A stored procedure is a set of SQL statements stored within the database in compiled form.
11Additional advantages of stored procedures is discussed at pages 255-257 and 352-355 in [16].
12http://msdn.microsoft.com/library/en-us/adminsql/ad security 3whf.asp
In our implementation each individual stored procedure only operates on a single database table. The individual table class may interact with several stored procedures and several methods of a given table class may interact with the same stored procedure.
The T-SQL statements of the stored procedures can be found in chapter 2 in volume III.
5.4.3 Attribute Types
Every table class has public properties corresponding to the attributes of the table which the class represents.
These attributes are declared as one of the types listed in the following table:
Class Name Description
DalBool Models an attribute (of a database table) representing a boolean.
DalDateTime Models an attribute (of a database table) representing date and time of day.
DalFloat Models an attribute (of a database table) representing floating point data.
DalGuid Models an attribute (of a database table) representing a globally unique identifier.
DalInt Models an attribute (of a database table) representing an integer.
DalString Models an attribute (of a database table) representing a string.
DalStringLocalizable Models an attribute (of a database table) representing a localizable string. This class is described in more detail in section 5.4.4.
The types in the above table are referred to as “attribute types”. We have been obliged to introduce the attribute types in the data tier due to the special NULL value that exists in databases. As is well known it is possible to assign aNULLvalue to an attribute of a database table in addition to assigning other values. A NULL value is assigned if the value is e.g.
unknown or inapplicable13. The built-in types in the .NET Framework (e.g. System.Int32 orSystem.String) can justly not have assignedNULL values to them and for this reason we have introduced the attributes types.
The attribute types are basically minor extensions of the built-in types – one of the extensions being the possibility of representing aNULLvalue. All the attribute types inherit from a base class namedDalTypeas shown in figure 5.4. TheDalTypeclass contains a few properties and validation methods common to the attribute types.
5.4.4 The DalStringLocalizable Class
In section 5.2.5 we described how we have chosen to store semi-structured data in those attributes that need to be culture versioned i.e. localized.
13Interpretations ofNULLvalues can be found at pages 239-240 in [27].
5.4 Data Tier 71
Figure 5.4:Illustration of how the various attribute types inherit from theDalType class.
TheDalStringLocalizableclass represents a text attribute that can be localized. The class is modelled as a hashtable – also known as a collection of key-and-value pairs. The key is the ID of a certain culture and the value is the text in that culture.
A localizable attribute may for instance be the name of a course:
DalStringLocalizable CourseName = new DalStringLocalizable(false);
, the false parameter indicating that the attribute may not be NULL. The Add method is used in order to localize the course name into a new culture:
Name.Add("da-DK", "Afløbssystemer");
Name.Add("en-GB", "Urban drainage systems");
In order to get the English name of a course one uses the following syntax:
string message;
message = "You have just signed up for course ";
message += Name["en-GB"];
Similarly, for other cultures.
5.4.5 The DataLog Class
As described in section 5.2.4 the attributes Created, CreatedBy, Updated and UpdatedBy occur on every single database table in the system on account of change tracking.
TheDataLogclass has been introduced as an obvious representation of the four attributes.
Accordingly, each table class has a publicLogproperty which is declared as aDataLogobject.
The class has public properties corresponding to the afore-mentioned four attributes besides which a number of methods are offered in order to make it convenient and efficient for developers of table classes to work with the change tracking attributes.
5.4.6 Basic Methods
The various table classes most often implement one or more of the basic methods described in the table below.
Method Name Description
public void Create() Creates a new row in the current table based on the values of the properties of the current object.
public void Retrieve() Retrieves a row from the current table using the value of property that represents the primary key of the table.
public bool Update(...) Updates a row in the current table using the values of prop-erties of the current object.
public bool Delete() Deletes a row in the current table using the value of the property that represents the primary key of the table.
The listed methods correspond to the four basic statements in SQL as follows:INSERT, SE-LECT,UPDATEandDELETE. Invocation of one of the methods essentially leads to the execution of the corresponding SQL statement.
A great many of the table classes implement all four methods. Table classes representing a type table in most cases merely implement theRetrievemethod. Naturally, the table classes implement other methods than the basic ones – e.g. several table classes implement methods that retrieve lists based on different criteria.