Sample 3-Tier Application

ADO.NET Architecture

Let’s look at an example where all of the pieces of code utilize the built-in ADO.NET architecture. I assume you know how to access and work with data stored in a weakly-typed dataset and data table. ADO.NET already provides a set of objects for storing data in .NET; no add-on components are necessary for this to work. In addition to that, ADO.NET has been around since .NET framework 1.x, so this approach has been available from the beginning.

Let’s start from the presentation layer, and work in reverse. The presentation layer makes use of the DataTable object, which contains several rows of customer information. This customer information is simply bound to a control in the user interface:

Listing 1: ADO.NET Use in the UI
private void BindData()
{
CustomerBAL bal = new CustomerBAL();
DataTable customerInfo = bal.GetCustomers(20);
this.gvwCustomers.DataSource = customerInfo;
this.gvwCustomers.DataBind();
}

As you can see, the data table is bound to the UI control manually; however, the ObjectDataSource control could replace this approach. To retrieve the DataTable object with the Customer data, the code makes use of a CustomerBAL class. This class provides access to CRUD operations against customer data. The makeup of the GetCustomers method is as follows:


Listing 2: ADO.NET Business Layer – GetCustomers method

public class CustomerBAL
{
public DataTable GetCustomers(int pageCount)
{ 
if (pageCount < 0)
throw new ArgumentOutOfRangeException(“pageCount”);
CustomerDAL dal = new CustomerDAL();
DataTable results = dal.GetCustomers(pageCount);
if (results.HasErrors)
throw new Exception();
}
}

The business layer validated the input coming into the method, as well as the output, ensuring that the data didn’t have any erroring information. If not, the results are returned to the user. In my example, I throw an exception; but handle it anyway you want, by logging the errors, returning it to the caller, etc.

But how does the data actually get to the presentation layer? The data layer receives a request for data and actually queries the database, returning the data to the business layer, as shown below:
Listing 3: ADO.NET Data Layer – GetCustomers method

public class CustomerDAL
{
public DataTable GetCustomers(int pageCount)
{
SqlConnection connection = new SqlConnection( "your connection string");
SqlDataAdapter adapter = new SqlDataAdapter(“spSelectCustomers”,connection);
DataTable table = new DataTable();
adapter.Fill(table);
return table;
}
}

The next question is how are inserts, updates, and deletes performed? While retrieval of data is satisfied, other data manipulation topics weren’t covered. I cover the subject of data manipulation at the end of the article.
Strongly-Typed DataSets

Strongly-Typed Datasets work very similar to ADO.NET; however, with strongly-typed datasets, the designer generates custom DataTable and strongly-typed DataRow objects that match the database schema. The difference between this and the previous code is that any changes to the database require a change to the dataset, and therefore could break existing code.
To setup this approach, the examples below use a dataset named Samples.xsd, with a name of SamplesDataSet (the name property in the designer affects the dataset class name and namespace). The generated code is as follows:

Custom DataTable and DataRow that match the database system, accessible through the SamplesDataSet class.

Custom TableAdapter objects, stored in the TableAdapters namespace.


I’m not going to talk about the setup of each component; there are plenty of resources online. But, I used this as an illustration that this could be considered an equivalent to a data layer, meaning you wouldn’t have to create a physical data layer consisting of separate classes, but could rely on the strongly-typed dataset as the data layer itself.



To utilize this in the business layer, an approach is shown below:

Listing 4: Business Component for Retrieving Customer Data

public class CustomerBAL : BaseBusinessComponent
{

#region " Methods "
public SamplesDataSet.CustomersDataTable GetAll()
{
CustomersTableAdapter adapter = new CustomersTableAdapter();

SamplesDataSet.CustomersDataTable table =
 new SamplesDataSet.CustomersDataTable();
adapter.FillCustomers(table);
base.HandleErrors(table);
return table;
}
public SamplesDataSet.CustomersRow GetByAccountNumber(string accountNumber)
{
if (string.IsNullOrEmpty(accountNumber))
throw new ArgumentNullException(“accountNumber”);
CustomersTableAdapter adapter = new CustomersTableAdapter();
SamplesDataSet.CustomersDataTable table =
new SamplesDataSet.CustomersDataTable();
adapter.FillByAccountNumber(table, accountNumber);
base.HandleErrors(table);
if (table.Rows.Count == 0)
return null;
else
return (SamplesDataSet.CustomersRow)table.Rows[0];
}
#endregion
}

The code above uses the FillBX - rather than the GetX - method to retrieve the data. I do this because I can check the data for errors using the HandleErrors method (defined in the base class, which simply checks the HasErrors property), and if OK return the correct results back. Note that the input is validated in the business component.

Enterprise Library

Enterprise Library is an initiative by Microsoft to provide additional services that the .NET framework doesn’t have out-of-the-box. The Data Access Application Block provides a centralized way to access data across varying data source systems without having to specify the underlying database in your code. This works through the provider type setting for the connection string defined in the connection strings element of the configuration file; this provider type loads the correct database provider at runtime.



The key benefit is that you don’t have to rewrite your code to do work with a different provider; it works using a single approach. Also note that the Enterprise Library uses the ADO.NET mechanism to represent data. Let’s look at an example data component:



Listing 5: Enterprise Library

public class CustomerDAL : BaseDataAccessComponent
{
#region " Methods "
public DataTable GetAll()
{
Database database = DatabaseFactory.CreateDatabase();
DbCommand command = database.GetStoredProcCommand("CustomersGetAll");
DataSet dataset = new DataSet();
database.LoadDataSet(command, dataset, "Customers");
return dataset.Tables[0];
}
public DataTable GetByAccountNumber(string accountNumber)

{
Database database = DatabaseFactory.CreateDatabase();
DbCommand command = database.GetStoredProcCommand("CustomersGetByAcct");
DataSet dataset = new DataSet();
database.LoadDataSet(command, dataset, "Customers");
}

#endregion
}

Note the difference in the approach above; Enterprise Library uses a Database object as the central point of contact. DatabaseFactory.CreateDatabase() provides the way that Enterprise Library connects to the correct database through a provider. The CreateDatabase method uses either an empty constructor (pulls the database connection from the configuration file) or it takes the name of a connection string (using one of the connection strings in the element).



Rather than accessing this directly, the business layer connects to the data layer as below (only one of the methods is shown). Because the data is transported via a DataTable object, it uses the same approach as shown above.



Listing 6: Business Object Validating Input, Retrieving Data, Validating Output

public class CustomerBAL : BaseBusinessComponent
{
#region " Methods "

public DataRow GetByAccountNumber(string accountNumber)
{
if (string.IsNullOrEmpty(accountNumber))
throw new ArgumentNullException("accountNumber");
CustomerDAL dal = new CustomerDAL();
DataTable table = dal.GetByAccountNumber(accountNumber);
base.HandleErrors(table);
if (table.Rows.Count > 1)
throw new DataException(@"There are too many rows coming back with account number: " + accountNumber);
if (table.Rows.Count == 0)
return null;
else
return table.Rows[0];
}

#endregion
}

In the method above, the business layer validates the input and output received from the data layer. In this way, the business layer ensures the business rules of the system are intact and correct. It also improves the quality of the data.



LINQ to SQL

LINQ-to-SQL creates custom business objects through its designer, as I mentioned previously. It works very similarly to a strongly-typed dataset, but instead of custom DataRow objects, it uses business objects. LINQ to SQL business objects are intelligent enough to detect changes and track updates to its properties, inserts or deletions to records, and changes to the primary and foreign key relationships. LINQ to SQL relies upon the existence of a custom DataContext class, which is the key class for change tracking and change submission.



What this means is that a live instance of the DataContext should be passed along from the business layer to the data layer, so that only one instance of the DataContext class exists. This is because you can’t join or query data that’s created from different DataContext objects; this raises an exception. One of the key ideas is to maintain the same DataContext throughout the lifecycle of the business and data layer objects.



This means passing in a DataContext reference throughout, often passing it through a constructor, as such:



Listing 7: Data Layer Constructor

view sourceprint?1.public CustomerDAL(SamplesDataContext context) : base(context) { }

In the business layer, the DataContext needs to be passed in as well. There can be some variants to this approach, but unfortunately that’s out of scope. Getting back to the data layer, LINQ-to-SQL translates LINQ queries into SQL queries, and returns the data as a collection.



Listing 8: Data Layer LINQ queries

public IEnumerable GetAll()
{
var results = from c in this.Context.Customers
orderby c.LastName, c.FirstName
select c;
return results;
}

public Customer GetByAccountNumber(string accountNumber)
{
var results = from c in this.Context.Customers
where c.AccountNumber == accountNumber
select c;
return results.FirstOrDefault();
}

In the first method, the data returns to the caller as an enumerable list; queries are returned in IOrderedQueryable<> form; however, IEnumerable<> will work as well. In the second approach, a Customer object is returned by using the FirstOrDefault method, which returns the Customer if a match found and null if not found. The business layer calls the data layer, and returns the results. In the GetByAccountNumber method, the input is validated.



Listing 9: LINQ Data Access Layer

private CustomerDAL ConstructComponent()
{
return new CustomerDAL(this.Context);
}
public IEnumerable GetAll()
{

CustomerDAL dal = this.ConstructComponent();
return dal.GetAll();
}
public Customer GetByAccountNumber(string accountNumber)
{
if (string.IsNullOrEmpty(accountNumber))
throw new ArgumentNullException("accountNumber");
CustomerDAL dal = this.ConstructComponent();
return dal.GetByAccountNumber(accountNumber); }
Updates to Content

There are a couple of approaches for inserting, updating, and deleting new content. One of the approaches cold be to pass in all of the parameters to the method, as in the following:



Listing 10: Parameterized DML Method

Public Customer InsertNew(Guid customerKey, string customerName, string account) {
//Create new record
}

This works well in ASP.NET, where data source controls can utilize this approach. Personally, I don’t like this approach simply because future requirements or changes to the parameter list break the interface of the method. Although overloaded methods can be added, that’s not the best option.



I prefer to create a new instance of an object or record in the application, and let the business layer validate the input using a process for business rules. For instance, if creating a new customer, I prefer an approach like this:



Listing 11: Alternative DML Method Approach

Public Customer InsertNew(Customer customer)
{
//Validate properties, and submit to database
}

Using the strongly-typed dataset architecture, the newly created row in the user interface is passed in as a parameter to the Insert method (more on that in a moment). This method updates the inserted row. If there are any errors, an output string is created and is the source of the exception being thrown. Customer object references like this also work well with other validation tools like the Validation Application Block.



Listing 12: Insert Customer Row Approach

view sourceprint?01.public void Insert(SamplesDataSet.CustomersRow insertedRow)
{
CustomersTableAdapter adapter = new CustomersTableAdapter();
adapter.Update(insertedRow);
if (insertedRow.HasErrors)
{
DataColumn[] columns = insertedRow.GetColumnsInError();
string output = null;
foreach (DataColumn column in columns)
output += insertedRow.GetColumnError(column);
throw new DataException(output);
}

}

However, an exception doesn’t always have to be thrown. Instead, the alternative approach can be to store the error information in a property of the business object. This object can be a custom error object that you create, a string value containing the message, or a reference to the exception that was thrown.



Using this property, an ASP.NET page or windows form can use this to output a message to the screen. Take a look at a possible example using ASP.NET; note the code is in a custom page class:



Listing 13: Error Handling in ASP.NET

view sourceprint?01.private void InsertCustomer(Customer customer)
{
CustomerBAL bal = new CustomerBAL();
bal.InsertNew(customer);
if (bal.Error != null)
{

Label errorLabel = this.Master.FindControl(“lblError”) as Label; 

If (errorLabel != null)

errorLabel.Text = bal.Error.Message;
}

}

This may not be the most practical in your situation, but the choice is up to you how you want to handle errors that occur in your business layer.