Introduction
A common design pattern in business applications is to separate the presentation layer, business logic layer and the data access layer. The data access layer encapsulates the underlying SQL statements for data manipulation. You can implement the components of this layer using strongly typed DataSet. The business logic layer enables specifying business entities, business rules and workflow associated to the underlying logic of your business. You can implement the components of this layer using strongly typed DataSet as well as custom business objects by defining classes in any language supported by .NET such as C#. The presentation layer enable display the data associated to business entities as well as control the interaction from the end user and the business application. This model results in a three-layer application architecture.
In this article, we're going to see how to illustrate the main principles, design patterns and techniques to implement a three-layer enterprise application using Visual Studio 2005 and SQL Server 2005. The business scenario is basically that AdventureWorks Inc. needs a portal site to manage information about its products anywhere within its corporate network. We're going to use the AdventureWorks database shipped with SQL Server 2005 and the table Production.Product.
Developing the solution
The first step is two create a solution with two projects in Visual Studio 2005: a Web Site project and a Class Library project. The Class Library project comprises of the Data Access components and Business Logic components representing the Product business entity. The Web Site project is the portal which enables managing information about the products in AdventureWorks.
Add a DataSet item to the Class Library project and name it DSProduct.xsd. Next add a TableAdapter to the DSProduct.xsd (see Figure 1). And finally compile the library.
Figure 1
Now let's configure the Optimistic Concurrency control by checking the field ModifiedDate to detect concurrency conflicts. Click on the ProductTableAdapter and go to the Properties window (see Figure 2).
Figure 2
Change the Insert, Update, Delete commands in order to support Optimistic Concurrency Control. The Insert command is shown in Listing 1, the Update command in Listing 2, and the Delete command in Listing 3.
INSERT INTO
Production.Product (Name, ProductNumber, MakeFlag, FinishedGoodsFlag, Color, SafetyStockLevel, ReorderPoint, StandardCost, ListPrice, Size, SizeUnitMeasureCode, WeightUnitMeasureCode, Weight, DaysToManufacture, ProductLine, Class, Style, ProductSubcategoryID, ProductModelID, SellStartDate, SellEndDate, DiscontinuedDate)
VALUES (@Name,@ProductNumber,@MakeFlag,@FinishedGoodsFlag,@Color,@SafetyStockLevel,@ReorderPoint,
@StandardCost,@ListPrice,@Size,@SizeUnitMeasureCode,@WeightUnitMeasureCode,@Weight,@DaysToManufacture,
@ProductLine,@Class,@Style,@ProductSubcategoryID,@ProductModelID,@SellStartDate,@SellEndDate,@DiscontinuedDate)
Listing 1
UPDATE Production.Product
SET Name = @Name, ProductNumber = @ProductNumber, MakeFlag = @MakeFlag,
FinishedGoodsFlag = @FinishedGoodsFlag, Color = @Color, SafetyStockLevel = @SafetyStockLevel,
ReorderPoint = @ReorderPoint, StandardCost = @StandardCost, ListPrice = @ListPrice,
Size = @Size, SizeUnitMeasureCode = @SizeUnitMeasureCode, WeightUnitMeasureCode = @WeightUnitMeasureCode,
Weight = @Weight, DaysToManufacture = @DaysToManufacture, ProductLine = @ProductLine,
Class = @Class, Style = @Style, ProductSubcategoryID = @ProductSubcategoryID, ProductModelID = @ProductModelID,
SellStartDate = @SellStartDate, SellEndDate = @SellEndDate, DiscontinuedDate = @DiscontinuedDate
WHERE (ProductID = @ProductID) AND (ModifiedDate = @ModifiedDate)
Listing 2
DELETE FROM Production.Product
WHERE (ProductID = @ProductID) AND (ModifiedDate = @ModifiedDate)
Listing 3
Now let's add a page to the Web Site Project and name it DataBindingPage.aspx as well as add a reference to the Class Library project in order to use the Product business entity.
Drag and drop a GridView control onto the DataBindingPage.aspx Web page and configure the data binding support. Controls that support data binding expose a property named DataSource and a method called DataBind. When the page is loaded, the GridView is initialized to some collection of data such as DataSet and then the method DataBind is invoked. At this point, the data are read from the underlying data source and are rendered in order to generate the HTML code for the browser. The code-bind implementing this logic is specified in Listing 4.
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using WSTest.BizObjects;
using WSTest.BizObjects.DSProductTableAdapters;
public partial class DataBindingPage : System.Web.UI.Page
{
protected void ptdDataBind()
{
DSProduct dsProduct = new DSProduct();
ProductTableAdapter taProduct = new ProductTableAdapter();
taProduct.Fill(dsProduct.Product);
this.GridView1.DataSource = dsProduct;
this.GridView1.DataMember = "Product";
this.GridView1.DataBind();
}
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.ptdDataBind();
}
}
}
Listing 4
Now let's design the user interface associated to the GridView control.
We're going to enable Editing and Deleting support on the GridView control as well as to display all the data fields Name, ProductNumber and Color of the Product entity. The Editing and Deleting feature is available using "Edit, Update, Cancel" CommandField the as well as the writing logic to handle the RowEditing, RowUpdating, RowCancelingEdit and RowDeleting event.
Click on the GridView control's smart tag, and select Edit Columns option. In the Fields window, add "Edit, Update, Cancel" and "Delete" items under the CommandField node from the Available Fields tree to the Selected Fields tree (see Figure 3).
Figure 3
In the same Fields window, add a BoundField item for each data field of the Product entity to be displayed (Name, ProductNumber and Color). Set the HeaderText and DataField properties to the field name, for example, for the Name field the HeaderText and DataField values are set to Name (see Figure 4).
Figure 4
In order to persist the value of the primary key for each row (not displayed in the GridView), we need to set the property DataKeyNames of the GridView1 to ProductID.
The event handler for RowUpdating will select the particular product (by retrieving the product identifier value from the DataKeys property of GridView control) and will update the new values typed in the GridView control. In order to select a particular product, we need to add query to the ProductTableAdapter (see Figure 5, Figure 6 and Figure 7).
Figure 5
Figure 6
Figure 7
The logic, for updating each row of data in the GridView control, is done by handling two events: RowEditing for establishing an editing environment (labels are converted into textbox for typing new data) (see Listing 5) and the RowUpdating for gathering the new values and updating to the underlying data source (see Listing 6).
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
this.ptdDataBind();
}
Listing 5
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
GridViewRow objRow = GridView1.Rows[e.RowIndex];
DataKey objKey = GridView1.DataKeys[e.RowIndex];
DSProduct dsProduct = new DSProduct();
ProductTableAdapter daProduct = new ProductTableAdapter();
daProduct.FillByProductID(dsProduct.Product, (int)objKey.Value);
string strName = ((TextBox)objRow.Cells[2].Controls[0]).Text;
string strProductNumber = ((TextBox)objRow.Cells[3].Controls[0]).Text;
string strColor = ((TextBox)objRow.Cells[4].Controls[0]).Text;
dsProduct.Product[0].Name = strName;
dsProduct.Product[0].ProductNumber = strProductNumber;
dsProduct.Product[0].Color = strColor;
daProduct.Update(dsProduct);
GridView1.EditIndex = -1;
this.ptdDataBind();
}
Listing 6
If you want to discard the new values, then you need to handle the event RowCancelingEdit (see Listing 7).
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
this.ptdDataBind();
}
Listing 7
And finally, we need to write the logic for deleting rows from the GridView control by handling the event RowDeleting (see Listing 8).
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
GridViewRow objRow = GridView1.Rows[e.RowIndex];
DataKey objKey = GridView1.DataKeys[e.RowIndex];
DSProduct dsProduct = new DSProduct();
ProductTableAdapter daProduct = new ProductTableAdapter();
daProduct.FillByProductID(dsProduct.Product, (int)objKey.Value);
dsProduct.Product[0].Delete();
daProduct.Update(dsProduct);
GridView1.EditIndex = -1;
this.ptdDataBind();
}
Listing 8
In this case, we can't delete a product due to referential integrity constraints associated to the Production.Product table. If you remove these constraints, you can use the code in Listing 8 to delete products.
Conclusion
In this article, I covered the principles and techniques of data binding with ASP.NET 2.0 and SQL Server 2005. Now you can adapt this solution to your own business scenarios.