Data Access Layer Continued

The process of separating data from presentation makes use of

• A Data Access Layer, using Typed DataSets
• A Business Logic Layer (BLL) that enforces custom business rules and
• A Presentation Layer consisting of ASP.NET pages with a common layout

In the last post, I outlined my strategy for using a DAL in a current project. Following are details of the DataSets used in the DAL.

In preparing the foundation of a DAL, it should be clear how to do the following (so I'm not going to dwell on them):

• Establish a DB Connection
• Add a DataSet from the template list
• Run through the TableAdapter Configuration Wizard

What may be less obvious is the way to structure your hand-written SQL queries so that they conform to your business logic layer. Here is a basic example:

SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued
FROM Products

Which results in two methods, the ability to

• fill a DataTable - takes in a DataTable as a parameter and populates it based on the query
  
• return a DataTable - creates and fills the DataTable for you and returns it as the method's return value

At this point there is one Typed DataSet (Northwind) with a single DataTable (Products) and a strongly-typed DataAdapter class (NorthwindTableAdapters.ProductsTableAdapter) with a GetProducts() method.

For instance, I create a named instance of a NorthwindTableAdapters.ProductsTableAdapter (Northwind is the ID of my Typed DataSet and Products is the DataTable):

NorthwindTableAdapters.ProductsTableAdapter productsAdapter = new NorthwindTableAdapters.ProductsTableAdapter();
Northwind.ProductsDataTable products;

products = productsAdapter.GetProducts();

foreach (Northwind.ProductsRow productRow in products)
Response.Write("Product: " + productRow.ProductName + "
");

This short but effective code will cycle through each record in the database table and create a list of all the names in the table.