Working with DAL (data access layer), BLL(Business Logic Layer) in asp.net web applications

In this post I would like to show you step by step how to create a DAL and BLL for an ASP.NET web application.

Well, DAL stands for Data Access Layer and BLL, stands for Business Logic Layer.

We will use strongly Typed DataSets to create the DAL and class files to implement the BLL, which enforces the business rules of our application.

Many developers get puzzled when they come across these terms. I will try to explain these concepts to the best of my ability and I will show how easy is to implement these sort of layers with the tools we have in our disposal. I will also try to explain why we need to implement these layers when we develop a n-tier project that accesses a database.

The most common thing in web/windows development is to store data in a database. We write code to save the data in the database and write code to manipulate the data e.g to insert, update,delete data in the database.

Obviously for this example we will need a database. I will use the Pubs database. This is a well known database and many people are familiar with its schema.

In order to follow this example you need to download this database and attach it in your local instance of the SQL Server. You can download the Pubs database from this link.

If you need some help on how to install the database have a look here

You can download Visual Studio 2008 from here and SQL Server 2008 Express from this link.

It is for free!!! So I assume that by this point you have all the tools downloaded and installed. At this point you must have the Pubs database in the local instance of your SQL Server.

We will create the DAL first. But why we need a DAL? It is not a good idea-strategy (especially if we deal with a big-complex application) to have the data-specific logic put into the presentation layer.So it is wrong to write the ADO.NET code in the .aspx pages of our application. All the data related code should be in the DAL. From the presentation layer we should only make calls to the DAL for all data requests.

Let’s get started then…

1) Fire up Visual studio 2008/2005

2) Create a new ASP.Net web application using C# as the development language

3) Name your project as you wish

4) Go to the Server Explorer in Visual Studio, right-click on the Data Connections node, and choose Add Connection. This will bring up the Add Connection dialog box, where you can specify the server to connect to, the authentication information, and the database name.Have a look at the picture below.

Click the OK button. After configuring the database,  the database will be added as a node underneath the Data Connections node. You can expand the node to explore its tables, views, stored procedures of the database.

5) We must add a Typed DataSet to our project. To do this, right-click on the project node in the Solution Explorer and choose Add a New Item. Select the DataSet option from the list of templates and name it Pubs.xsd.

See the  picture below.

Typed DataSets are a set of generated classes (generated by Visual Studio) that inherit directly from the DataSet family of classes.The members of these classes are strongly-typed according to the database schema.

A Typed DataSet is composed of strongly-typed DataTable instances which represents any of the underlying database tables. We need to create a DataTable for the Authors table. Besides having our table properties as objects inside the DataTable object we need to use the TableAdapter class so we can have the necessary methods to access the data from the table.

6) Navigate to the Pubs.xsd, drag and drop a TableAdapter class. The configuration wizard fires up.

See the picture below

7) Save the connection string to the web.config as suggested.

8) Click Next. Have a look at the picture below

In this step we define the query(using the first option-ad hod sql queries) that returns the columns from the table Authors that we want reflected in our DataTable. At the end of the wizard we’ll give a method name to this query.We will use this method to populate the strongly-typed DataSet.

9) Click Next and then from the Query Builder select the Authors table and click Add. We use the Query Builder to graphically construct the query. You must select all the fields from the Authors table. Have a look at the picture below.

10) Click Next. Have a look at the picture below

Rename the Return a DataTable section from GetData to GetAuthors.

We will use this method from our presentation layer to create and fill the DataTable and return it.

11) Click Next and then Finish. The wizard closed. If we go to the DataSet Designer we can see

• The DataTable we just created, Pubs.authorsDataTable
• The PubsTableAdapters.AuthorsTableAdapter and the methods (Fill() and GetAuthors())

See the picture below

12) Switch to the Default.aspx and in the Page_Load event type the following lines of code.

PubsTableAdapters.authorsTableAdapter myadapter = new PubsTableAdapters.authorsTableAdapter();

Pubs.authorsDataTable myauthors;

myauthors = myadapter.GetAuthors();

foreach(Pubs.authorsRow authorrow in myauthors)

Response.Write(“Author: ” + authorrow.au_fname + ”  ” + authorrow.au_lname+  “<br />”);

13) As you can see in the first line we create an instance of our authorsTableAdapter.

Run your application and you will see all the authors.

14) Now we can bind the objects (data) contained in the DataTable to a Gridview web server control.Add a new page by adding a new item in your project.Name it GridviewDataTable.aspx.Set this page as Start Page.

15) Drag and Drop a Gridview control on the page. Leave the default name.

In the Page_Load event of this page type the following code

PubsTableAdapters.authorsTableAdapter myadapter = new PubsTableAdapters.authorsTableAdapter();

GridView1.DataSource = myadapter.GetAuthors();

GridView1.DataBind();

Run your application and you will see the results returned by the Authors table through the DataTable object in the GridView.

16) We need to select a specific row from the table Authors. In order to achieve that we need to create a new method that takes an argument. To create such a method, switch to the DataSet Designer and add a new query. Have a look at the picture below.

17) Click Next (Leave the first option selected). Click Next again, (Leave the first option selected)

18) Click Next in the wizard and add the following SQL statement

SELECT        au_id, au_lname, au_fname, phone, address, city, state, zip, contract
FROM            authors
where au_id=@au_id

Have a look at the picture below

19) Click Next. In the Return a DataTable, change the name to GetAuthorByID and then click Finish.

20) In the DataSet Designer you will see the new method we just created.

21) Right-Click on this new method (GetAuthorByID) and select Preview Data. In the Preview Data windows type this “172-32-1176” in the Value fields in the Parameters section. Click Preview.

22) Add a new page to your project. Call it GetAuthorsbyid.aspx.Set it as the Start Page

23) Run your application. In the web page you should see the following result

172-32-1176

White

Johnson

408 496-7223

10932 Bigge Rd.

Menlo Park

CA

94025

24) Obviously we want to insert,update,delete rows in the table. We can do that by using the Insert(), Update(), and Delete() methods of our TableAdapter class that are already created for us.

Have a look at the image below

25) To insert a new row in the Authors table, we switch to the GridviewDataTable.aspx (make it as a Start Page) and place a button on the page. Change the text property of the button to “Insert” (There is already a gridview in this page). Double click on the button and in the event handler routine type,

PubsTableAdapters.authorsTableAdapter myadapter = new PubsTableAdapters.authorsTableAdapter();

myadapter.Insert(“122-33-5667”, “Austen”, “Jane”, “33216321737”, “12 london street”, “London”, “SO”, “99029”, false);

GridView1.DataSource = myadapter.GetAuthors();

GridView1.DataBind();

Run your application and see the new row is inserted in the gridview and thus in the underlying table

26) Let’s try to update the record we just inserted. Just add a new button on the page. Change the text property of the button to “Update”. Double click on the button and in the event handler routine type,

PubsTableAdapters.authorsTableAdapter myadapter = new PubsTableAdapters.authorsTableAdapter();

myadapter.Update(“122-33-5667”, “Austen”, “Jane”, “33216321737”, “22 london street”, “London”, “SO”, “99029”, false, “122-33-5667”);

GridView1.DataSource = myadapter.GetAuthors();

GridView1.DataBind();

Run your application and see the row updated in the gridview and thus in the underlying table

27) Finally we will try to delete the row we inserted and updated in the previous steps.Just add a new button on the page. Change the text property of the button to “Delete”. Double click on the button and in the event handler routine type,

PubsTableAdapters.authorsTableAdapter myadapter = new PubsTableAdapters.authorsTableAdapter();myadapter.Delete(“122-33-5667”);
GridView1.DataSource = myadapter.GetAuthors();
GridView1.DataBind();

Run your application and see the row is deleted in the gridview and thus in the underlying table.

28) Now we will try to create a new functionality that does a mass update and not just updating a row like the previous example. We will implement the following scenario, for every author that lives in California (state=”CA” ) his contract will be valid (contract=true). Just add a new button on the page. Change the text property of the button to “Update multiple”. Double click on the button and in the event handler routine type,

PubsTableAdapters.authorsTableAdapter myadapter = new PubsTableAdapters.authorsTableAdapter();

Pubs.authorsDataTable myauthors;

myauthors = myadapter.GetAuthors();

foreach (Pubs.authorsRow author in myauthors)

if (author.state == “CA”)

author.contract = true;

myadapter.Update(myauthors);

GridView1.DataSource = myadapter.GetAuthors();

GridView1.DataBind();

29) Now I would like to move on and talk a bit about the BLL. We have covered enough on how to seperate the data access logic from the presentation logic.If we want to have certain business rules that define who is given the authority to do ( e.g delete,update authors) or that for certain states special discounts should be applied, a Business Logic Layer (BLL) that serves as an intermediary for data exchange between the presentation layer and the DAL must be implemented. I will implement the BLL as a  class file because we have one TableAdapter in the DAL. Inside this class we will have methods that map to the data access methods in the DAL.

30 ) Simply right-click in the Solution Explorer and then Add a New Item, and choose the Class template. Name the class BLLAuthors.cs. The first method we will create, is simply a wrapper method for the method GetAuthors() in the DAL and we will call it GetBLLAuthors(). We will create a second method which we will call GetBLLAuthorsByID. This method is still a wrapper method for the GetAuthorsByID in the DAL. In this first example I want to show you how to access the typed datasets of the DAL through BLL. We will see later, methods in the BLL that enforce business rules(which is the point of having a BLL). The code inside the BLLAuthors.cs is as following

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using DAL_BLL.PubsTableAdapters;

[System.ComponentModel.DataObject]

public class BLLAuthors

{

private authorsTableAdapter myAdapter = null;

protected authorsTableAdapter Adapter

{

get {

if (myAdapter == null) myAdapter = new authorsTableAdapter();

return myAdapter;

}

}

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]

public DAL_BLL.Pubs.authorsDataTable GetBLLAuthors()

{

return Adapter.GetAuthors();

}

[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select, true)]

public DAL_BLL.Pubs.authorsDataTable GetBLLAuthorsByID(string authorid)

{

return Adapter.GetAuthorByID(authorid);

}

}

31) Create a new web page and name it GridviewBLL.aspx. Set this page as the Start Page.

Add a Gridview control on the page. In the Page_Load event of this page type,

BLLAuthors authorsall = new BLLAuthors();

GridView1.DataSource = authorsall.GetBLLAuthors();

GridView1.DataBind();

As you can see in the first line we create an instance of our BLLAuthors class which we defined in our BLLAuthors.cs file. It is evident from the code above that from the presentation layer (.aspx page) we access the BLL and then the BLL accesses the DAL, which eventually  “talks” to the database.

32) Let’s see a little example of our second wrapper method in our BLL. Add a button control to the page.Change the text property of the to “SelectSingleAuthor”. Double click the button. In the event handling routine type,

BLLAuthors singleauthor = new BLLAuthors();
GridView1.DataSource = singleauthor.GetBLLAuthorsByID(“172-32-1176”);
GridView1.DataBind();

Run your application. In the web page you should see the following result

172-32-1176

White

Johnson

408 496-7223

10932 Bigge Rd.

Menlo Park

CA

94025

33) Let’s create a third method that applies this business rule (we used the same example in the DAL section),if  the state of the author is California “CA” then the contract should be true.

The code you should put in the BLLAuthors.cs is this

public bool UpdateAuthor()

{

DAL_BLL.Pubs.authorsDataTable authors = Adapter.GetAuthors();

if (authors.Count == 0)

return false;

foreach (DAL_BLL.Pubs.authorsRow authorrow in authors)

if (authorrow.state == “CA”)

authorrow.contract = true;

// Update the author record

int rowsAffected = Adapter.Update(authors);

return rowsAffected == 1;

}

Add a button control to the page.Change the text property of the to “UpDateState”. Double click the button. In the event handling routine type,

BLLAuthors upauthor = new BLLAuthors();
upauthor.UpdateAuthor();

GridView1.DataSource=upauthor.GetBLLAuthors();

GridView1.DataBind();

Run your application. In the web page you should see all records with state=”CA” to have the contract=”True”

You could insert the code above in a Try-Catch Statement….

try
{
BLLAuthors upauthor = new BLLAuthors();upauthor.UpdateAuthor();

GridView1.DataSource = upauthor.GetBLLAuthors();
GridView1.DataBind();
}
catch (ArgumentException te)

{
Response.Write(“problem: ” + te.Message);
}

Hope it helps and happy coding!!!

If you need the source code feel free to email me. You can find my email in this page