Two- and Three-Tier Architecture

It the world of large business and enterprise applications, it is not surprising to hear talk of architecture, business layers, data layers, and so on. For single developers or small teams, however, the emphasis is more often on getting the job done quickly, and architectural decisions often get left behind. However, the use of tiers in application architecture is extremely simple to implement, and provides great benefits. You abstract all of the data logic into a single set of classes and provide a consistent interface from that set of classes. This means that should your underlying data storage change (perhaps from one database to another or just by adding features), any code that uses the data layer will continue to work.

The next benefit is in distribution of workload; people can be dedicated to particular areas of the application. This would enable a Web designer to work with the HTML and CSS, while the developer codes the other layers. This is an additional benefit if source code control is used (and it should be), because if the data layer is separated from the ASP.NET pages, they can be checked out of a source code control system separately and worked on by different peoplethere's no waiting for someone else to finish with the file.

It is important to understand that this talk of layers is purely a logical view. While there can be a physical separation between the layers, especially with the class files themselves, there doesn't have to be a physical separation between the deployment of those layersthey can all be hosted within the same Web site.

Business Layers, Data Layers, or Both?

Some questions that often get asked when a new development project starts are, "What sort of separation is required?" And, "Are multiple layers required, and how should they be split?" The answer, of course, is "it depends." It depends on how much business logic you have, and whether you want the extra overhead of an additional layer. Figure 4.1 shows the typical arrangement for three- and four-tier layers; with three tiers, the user interface calls the data layer directly; with four tiers, the interface calls the business layer, which in turn calls the data layer.

Figure 4.1. Three- and four-tier architectures

Object Data Mappers

With only a data layer, you will typically be returning DataSet or DataTable objects from your class methods. With an additional business layer, this approach can still be usedthe data layer returns a DataTable to the business layer, which performs any necessary logic and passes the DataTable to the interface layer. This is a perfectly acceptable scenario; it reduces the amount of code required and performs well. One issue with this solution is that the interface needs to know how the data is storedyou are returning DataTable objects, so the interface must know how to manipulate them, for binding purposes.

To alleviate the issue of the interface layer knowing about the actual storage mechanism, you can have the business layer expose the data in a different form, normally as a strongly typed business object. For example, you could expose the Shippers table as a Shipper object, with properties such as ShipperID, CompanyName, and Phone. Your interface then needs to know only about the objects, not where the data is stored or how it is fetched.

There are several ways of implementing a strongly typed business layer, one of which is typed data sets, which are covered later in the chapter. Others include object mappers and code generation tools, which take the database schema and create strongly typed code libraries that you can plug into your applications.

There are benefits to each solution, and for simplicity we're going to cover a simple data layer that the interface uses directly.

Creating a Data Layer

Creating a data layer is as simple as creating classes that expose public methods. These classes can be placed in the App_Code directory, a special directory where classes are automatically compiled. Next, you have to decide what goes into the classtypically there will be methods for each type of action you need to perform. For example, consider the actions required for a table: Get all rows, get a single row, insert a new row, update data in an existing row, and delete a row. There may be other actions, but these are core ones required.

Creating the Data Class

Let's consider these as individual methods of a class called ShipperData-Layer, starting with fetching all rows, as shown in Listing 4.1.

The first thing to notice is that this is a static method. This isn't a necessary part of data access classes, but reduces the need for calling routines to instantiate the class. Within the class a connection is made to the database, and a command is created using a SQL statementin reality this should be a stored procedure, but has been left as SQL here so that you can see exactly what's being run. A DataTable object is then created, and the command executed, with the output of the command being loaded into the DataTable object, which is returned. The using statement ensures that the SqlConnection object is closed and freed from memory, allowing the connection to be reused by another user.

Listing 4.1. Fetching All Shippers

public static DataTable GetShippers() {   using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))   {     conn.Open();     SqlCommand cmd = new SqlCommand("SELECT * FROM Shippers", conn);     DataTable tbl = new DataTable();     tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));     return tbl;   } }

A similar routine is shown in Listing 4.2, which shows the method to fetch a single shipper. Here the method accepts a parameter which is the ID of the shipper to fetch. The rest of the routine is similar, but this time the SQL statement features a WHERE clause, and a parameter is added to the command to contain the ShipperID value.

Listing 4.2. Fetching a Single Shipper

public static DataTable GetShipper(int ShipperID) {   using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))   {     conn.Open();     SqlCommand cmd = new SqlCommand(       "SELECT * FROM Shippers WHERE ShipperID=@ShipperID", conn);     cmd.Parameters.Add("@ShipperID", SqlDbType.Int).Value =       ShipperID;     DataTable tbl = new DataTable();     tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));     return tbl;   } }

Listing 4.3 shows the method to insert a new shipper, and it follows a familiar pattern. The parameters to the method are the columns of the table, but without the ID valuethis is automatically generated by SQL Server, so isn't required.

Listing 4.3. Inserting a New Shipper

public static int Insert(string CompanyName, string Phone) {   using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))   {     conn.Open();     SqlCommand cmd = new SqlCommand("INSERT INTO           Shippers(CompanyName, Phone)           VALUES(@CompanyName, @Phone)", conn);     cmd.Parameters.Add("@CompanyName",       SqlDbType.NVarChar, 40).Value = CompanyName;     cmd.Parameters.Add("@Phone",       SqlDbType.NVarChar, 24).Value = Phone;     return cmd.ExecuteNonQuery();   } }

Within the Insert method, the parameters are used as the values for an INSERT statement, which is run using the ExecuteNonQuery method. This indicates that no rows of data are being returned.

Listing 4.4 shows the Update method, which will update an existing shipper. The parameters are the ID value, which will identify the shipper being updated, and the CompanyName and Phone, which are the values to update.

Within the Update method, a simple UPDATE statement is run, using the parameters passed into the method.

Listing 4.5 shows the Delete method, which takes a single parameterthe ID of the shipper to delete.

These classes are all that's required for a simple data access layer for a single table. One thing that you'll notice is that there's a lot of similar code, and you can see how this would increase the size of classes once you start creating layers for each table. One way around this is to use code generation tools to automatically create the code for you, or you can simply create some classes to streamline the data layer.

Listing 4.4. Updating a Shipper

public static int Update(int ShipperID, string CompanyName,                          string Phone) {   using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"]       ConnectionString))   {     conn.Open();     SqlCommand cmd = new SqlCommand("UPDATE Shippers           SET CompanyName=@CompanyName, Phone=@Phone           WHERE ShipperID=@ShipperID", conn);     cmd.Parameters.Add("@ShipperID",       SqlDbType.Int).Value = ShipperID;     cmd.Parameters.Add("@CompanyName",       SqlDbType.NVarChar, 40).Value = CompanyName;     cmd.Parameters.Add("@Phone",       SqlDbType.NVarChar, 24).Value = Phone;     return cmd.ExecuteNonQuery();   } }

Creating Helper Classes

We said earlier that a data layer performs the following five basic operations:

• Fetching all rows

• Fetching a single row

• Inserting a row

• Updating a row

• Deleting a row

Listing 4.5. Deleting a Shipper

public static int Delete(int ShipperID) {   using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))   {     conn.Open();     SqlCommand cmd = new SqlCommand("DELETE FROM Shippers           WHERE ShipperID=@ShipperID", conn);     cmd.Parameters.Add("@ShipperID",       SqlDbType.Int).Value = ShipperID;     return cmd.ExecuteNonQuery();   } }

With these actions in mind, a helper class could be created, such as the one shown in Listing 4.6.

Listing 4.6. A Data Layer Helper Class

public static class DataLayerHelper {   public static DataTable Get(string cmdText)   {     using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))     {       conn.Open();       SqlCommand cmd = new SqlCommand(proc, conn);       DataTable tbl = new DataTable();       tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));       return tbl;     }   }   public static DataTable GetByID(string cmdText,                                   string IDName, int IDValue)   {     using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))     {       conn.Open();       SqlCommand cmd = new SqlCommand(cmdText, conn);       cmd.Parameters.Add(IDName, SqlDbType.Int).Value = IDValue;       DataTable tbl = new DataTable();       tbl.Load(cmd.ExecuteReader(CommandBehavior.CloseConnection));       return tbl;     }   }   public static int ExecuteNonQuery(string cmdText,     SqlParameter param)   {     using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))     {       conn.Open();       SqlCommand cmd = new SqlCommand(cmdText, conn);       cmd.Parameters.Add(param);       return cmd.ExecuteNonQuery();     }   }   public static int ExecuteNonQuery(string cmdText,     ref SqlParameter[] sqlParams)   {     using (SqlConnection conn = new SqlConnection(     ConfigurationManager.ConnectionStrings["NorthwindConnectString"].       ConnectionString))     {       conn.Open();       SqlCommand cmd = new SqlCommand(cmdText, conn);       cmd.Parameters.AddRange(sqlParams);       return cmd.ExecuteNonQuery();     }   } }

The first parameter of each method is the command text to run; this is the SQL statement (or it could be a stored procedure name, although the commands would need to have their CommandType property set to CommandType.StoredProcedure). The Get method simply executes the statement and returns a DataTable containing the fetched rows. The GetByID accepts two additional parameters: IDName and IDValue, which hold the name of the ID column and the value to fetch. The remaining method, ExecuteNonQuery, is overloaded and can accept either a single SqlParameter object or an array of SqlParameter objects. The array of parameters is passed by reference, so that any output parameters can be passed back to the data layer (this will be covered in Chapter 5). These methods simply execute a statement using the supplied parameters.

The data layer class can now be simplified, as shown in Listing 4.7.

The job of the simplified classes is now to define the commands to be run and the parameters that these commands require. While this isn't a great benefit for a single data class, the helper can be used for all subsequent data classes, making them easier and quicker to create.

Now that the data layer is defined, it's time to see how they can be used in ASP.NET pages.