Creating a Log Visit Report

oops.. today I’d decided to post a new article but surprisingly noticed that the blog was suspended! Thanks to Matt, it is alive and kicking now!

Anyway, lets see how we can generate a log visit report for our ASP.NET web site. By the term of Log Visit, I mean the statistics of a website which shows at which time, which page (or file) has been viewed by who!

The good news is that IIS can record the above information for us. It can log all activities done by your web site in the following ways and formats:

W3C Extended log file format

World Wide Web Consortium (W3C) Extended format is customizable and is saved in an ASCII text file. You can select your desired fields among a lot of choices.

IIS log file format

IIS log file format has  fixed columns and is stored as an ASCII text file. This file includes more information than W3C format.

NCSA Common log file format

National Center for Supercomputing Applications (NCSA) Common log file format is a fixed ASCII format that is available for Web sites, but not for FTP sites

ODBC logging

Open Database Connectivity (ODBC) logging format is a record of a fixed set of data properties in a database.

Logging into text files is quicker than logging into a database. It does not have any performance pitfall but when it comes to reporting, you probably should import the text file into a database.

Logging by ODBC will slow down your website to some extent  because it records very much of (sometimes unnecessary) information. For example, in W3C format, only information about pages who have been loaded successfully is stored. While in ODBC method, all referred objects, like .js or image files, are recorded, no metter if the resource have been loaded successfully or not.

By the way, since ODBC will make this post shorter, I will choose ODBC method for my sample program.

To enable logging with ODBC, first you must create a table for storing log information and then create an ODBC data source thorough which IIS can store logs.

To create the log table, which is usually called INETLOG, go to System32\InetSrv folder and find LogTemp.Sql file. This file includes a script that creates the required table into your database. This script has been written for Sql Server, but you can modify it to be executed on other databases like MS Access or even My Sql.

In the next step, create a SYSTEM DNS that points to the above table. You must create a System DNS because IIS will use the DNS with a user different from the one you have logged into your computer (usually NetWork Service account).

To create a new System DSN, go to Control Panel-> Administrative Tools and then double-click Data Sources (ODBC) . Then, go to System DNS tab and click Add. Select SQL Server, enter the name of your server and database and specify a user/password. You would better enter a SQL SERVER login (do not choose Windows Authentication) and specify a password that will not be changed.

After doing so, change the default database of this DSN to the database that logging table resides on. Click OK and close the dialog box.

Now go to Control Panel -> Administrative Tools  and double-click on Internet Information Services Manager.

Open Web Sites, and then find your Virtual Directory or Web Site. Right-click on it, choose properties and under the Home Directory tab, find Enable Logging check box. Enable the check box and In the Active log format list box, select ODBC. Afterwards, click on Configure button to view the ODBC settings. In this dialog box, enter the name of ODBC DSN you created in the previous step, name of the table (inetlog), user name and password of logging data base.

For example:

ODBC: WordPressLogDSN

Table: InetLog

Login: sa

Password: 123

In the above sample I used “SA” SQL SERVER’s default login name. This is a risky job and I do not recommend you do it! You would better use a user name with no administrative permission which only has access to logging table or database.

From now on, any visit to a page will be recorded!

NOTICE: The logging table will be filled with tons of records, therefore, I strongly recommend you to store InetLog table on a seperate FileGroup. Also, You would better to schedule a script that removes unnecessary records from inetlog table.

Writing The Code

In the rest of the post I am going to write a sample page that displays how many times each .aspx or .html page has been visited. I will show the stats in numeric and bar chart format.

As I mentioned, data about any referred resources will be stored in InetLog table. Thus, we should be able to determine if a record belongs to a Page or not. Only pages whose ServiceStatus is equal to 200 have been loaded or access with no error. So, I need a Sql function that determines if a resource is a page (i.e. .aspx) and if it has been accessed successfully.

I will call the function IsPage. The code is very simple so I just bring the script body here:

create function IsPage(   @PageName varchar(200) )
returns int
Declare @len int
set @len = len(@PageName)

if Substring(@PageName, @Len, 1) = ‘/’
set @PageName = Substring(@PageName,1,@Len-1)

Declare @Extension  Varchar(4)
Set @Extension = Upper(Substring(@PageName,@Len – 3 , 4))

if @Extension = ‘ASPX’ or @Extension = ‘HTML’
return 1
return  0

I will use this function to create my stats and also to remove records of non-page resources like .js files (if I don’t need them).

At the next step I will write a stored procedure that determines how many times each page has been visited. To let the script run on older versions of Sql Server, I have written it as simple as possible:

create procedure rep_LogVisit
@BeginDate smalldatetime,
@EndDate smalldatetime
select Count(1) as FileCount, target as PageName
from inetlog
where ServiceStatus = 200 and dbo.isPage(target) =1
and LogTime>=@BeginDate and LogTime<=@EndDate
group by dbo.GetFileName(target)

As the above code indicates, rep_LogVisit counts the records who’s ServiceStatus equals to 200 (successful) , the resource is a page and the log has been recorded between a given date.

In order to display the result, I use a GridView control, having two columns. One for the page name, and one for the visit count:

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False”

CellPadding=”4″ ForeColor=”#333333″



<asp:BoundField DataField=”target” HeaderText=”Page Name” />

<asp:TemplateField HeaderText=”Visit Count”>


<asp:Panel runat=”server” ID=”divBar” style=”background-color:Red” Width='<%#GetLength((int)Eval(“TotalCount”)) %>’>

<asp:Label runat=”server” ID=”lblCount” text='<%#Eval(“TotalCount”) %>’ ForeColor=”White”></asp:Label>






Well, I will call  rep_LogVisit stored procedure, bind the grid to it’s result. I also have to write a function called GetLength in order to calculate the lenth of each bar:

int MaxCount = 0;

protected void Page_Load(object sender, EventArgs e)


if (!IsPostBack)


SqlConnection conn = new SqlConnection(“Your Connection String Here”);

SqlCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = “rep_LogVIsit”;

// Add date parameters here. skipped to simplify the code.

DataTable table = new DataTable();

SqlDataAdapter adapter = new SqlDataAdapter(cmd);


// Get the bigger visit count

MaxCount = (int)(from C in table.AsEnumerable()

select C).Max(c=>c[“TotalCount”]);

GridView1.DataSource = table;




// Calculate the length of each bar

internal int GetLength(int TotalCount)


if (MaxCount == 0)

return 0;


return Convert.ToInt32(Math.Round(((double)TotalCount / MaxCount)*300));


At run-time, you will see something like the image bellow:



Reading and writing images from/to database

This post will show you how to save/load images to/from a database. This approach lets you do it without having to save the image on to disk. On a real hosting computer, you probably will not have write permissions. Therefore, it is sometimes vital to do image saving/loading on the fly.

In this post we will work on a database named Personnel, in which there is a table called Personnel:

create table Personnel
id int identity(1,1) not null primary key,
Fillname varchar(100) not null,
Picture image null

We are going to store out personnel’s name and picture into this table. On the application side, create a .aspx page with the following four controls:
1-a TextBox
2-a RequiredFieldValidator
3-a FileUpload control
4-a Button
5-a DataList control

Your page will look like this:

As the above image indicates, we will save each person’s full name and picture to database and the personnel information will be viewd in a DetailList control.

Behind the AddRecord button, we read the image into a Byte[] object. Then transmit the bytes to database. I strongly recommend you to use a stored procedure (if you are working with ADO.NET) because you won’t face any problems with converting bytes to string.

In Asp.NET there is a class claeed HttpPostedFile. This class lets us get full information about the file that is about to upload.  The code bellow show how we read the file:

if (FileUpload1.HasFile)
HttpPostedFile myFile = FileUpload1.PostedFile;
int Length = myFile.ContentLength;
string ContentType = myFile.ContentType.ToUpper();

if (Length == 0)
throw new Exception(“File size must be greater than zero!”);
if (ContentType.CompareTo(“IMAGE/PJPEG”) != 0 && ContentType.CompareTo(“IMAGE/JPEG”) != 0)
throw new Exception(“Only JPEG files are welcome!”);

Byte[] myFileBytes = new byte[Length];
myFile.InputStream.Read(myFileBytes, 0, Length);

In the above code I just allow JPEG files to be uploaded. Actually, I wanted to show you how to control the image type and infact, you can upload any file type.

As the last line of this code shows, HttpPostedFile class contains an inner stream from which we can read the image bytes. After reading the image file, we simply save it into db. I have created a stored procedure for doing this and I call this procedure from my C# code:

ALTER PROCEDURE dbo.SavePersonnel
@FullName Varchar(100),
@Picture Image

Insert into Personnel (FullName,Picture) Values (@FullName,@Picture )


string cnStr = ConfigurationManager.ConnectionStrings[“ConnectionString”].ToString();
using (SqlConnection connection = new SqlConnection(cnStr))
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = “dbo.SavePersonnel”;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue(“FullName”, txtFullName.Text.Trim());
cmd.Parameters.AddWithValue(“Picture”, myFileBytes);

Well, now how to read the image and show it using a System.Web.UI.WebControls.Image control. Unfortunately, despite System.Drawing.Image class, ASP.NET Image class does not provide a public stream. Therefore we can not create the image content withouth an image url.

Thanks to ASP.NET Generic Handlers, we can overcome this problem easily. We may develop a .aspx or .ashx (generic handler) file and use it to read the file from database and write the image bytes into the response output stream. A generic handler (.ashx) file is much more light weight than a .aspx file. Therefore, I will develop a .ashx file. We will pass the ID of each person to this .ashx file. The generic handler then finds the person at DB, reads the image and writes it to the current HttpContext instance :

Here is the full source code of the .ashx file:

<%@ WebHandler Language=”C#” Class=”GetPersonnelImage” %>

using System;
using System.Web;
using System.Data.SqlClient;

public class GetPersonnelImage : IHttpHandler {

public void ProcessRequest (HttpContext context) {
context.Response.ContentType = “image/jpeg”;
if (context.Request.QueryString[“id”] == null || context.Request.QueryString[“id”] == “”)
string cnStr = System.Configuration.ConfigurationManager.ConnectionStrings[“ConnectionString”].ToString();
using (SqlConnection connection = new SqlConnection(cnStr))
SqlCommand cmd = connection.CreateCommand();
cmd.CommandText = “Select Picture from dbo.Personnel Where Id=” + context.Request.QueryString[“id”];
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
byte[] image = reader.GetValue(0) as byte[];
System.IO.MemoryStream ms = new System.IO.MemoryStream(image);

public bool IsReusable {
get {
return true;

As is clear, we read the person’s record with a SqlDataReader and then get the image bytes using it’s GetValue method.

To let a Image control show the image we have to set it’s ImageUrl’s property in ths way:

Image img = new Image();

img.ImageUrl = “~/GetPersonnelImage.ashx?id=10″;

Inside a GridView or DataList, we can create a Template field with an Image control and bind the Image control’s ImageUrl property to the mentioned generic handler. For example:

<asp:Image ID=”Image1″ runat=”server” ImageUrl='<%#Eval(“id”,”GetPersonnelImage.ashx?id={0}”)%>’

You can download a full sample program from here. To run the project, you must have Sql Server 2005 Express edition on your machine. If you don’t have SQL Server 2005 Express, change the connection string existing in Web.config file to your own database.

Implementing a 3-Tier architecture with C# – Part 5

Hi back,

In this post we will finalize the implementation of our 3-tier architecture, by developing a Web-based presentation layer.

So far, we have developed the data access and business layers. The implementation of UI, consists of methods and approaches for handling business and crud operations thorough a Web UI. In fact, there are several ways to present business objects and reflect the changes back to data base. Like:

  1. Binding controls (i.e. GridView) directly to DataTables coming from business classes (i.e. by FetchAll method)
  2. Binding controls (.e. GridView) to business classes using an IDataSource, like ObjectDataSouce control.
  3. Mixing up the above methods.

If you opt the first option, you need to handle sorting and paging manually. It means that you have to write code for PageIndexChanged and Sorted methods. This might take several minutes for you to handle every details and make your development process too long and not productive.

A better approach is using ObjectDataSource. For using it, we have to prepare our business classes first. ObjectDataSoruce works with classes that DataObject attribute has been applied to them. Thus, open your business class (i.e. PersonBiz.cs), and apply DataObject attribute on it:


public class PersonBiz
{ …

Then we might specify four methods for doing Select/Insert/Update/Delete operations. However, I personally prefer to use Select method only and do the other operations manually. My reason is that, if I declare a method for Insert/Update methods, I will have to use GridView’s (or similar controls) editing features and have to pass a lot of params to the Insert/Update methods. and I do not like it! 🙂

Anyway, to mark a method as a Select method, we have to apply DataObjectMethod attribute on it. This attribute takes a parameter indicating which kind of method is it. For example, a select method can be declared like this:
public DataTable FetchAll(…

If you are intended to use ObjectDataSource, your Select method must have a return value of type IENumerable, and preferably, it would better return a DataTable or DataView.Thus, FetchAll method returns a DataTable. It also has two parameters as follows:

public DataTable FetchAll(int startRowIndex, int maximumRows)
PersonEntity entity = new PersonEntity();
innerDAL.Fill(entity, startRowIndex, maximumRows);
return entity;

As you see, the startRowIndex and maximumRows are used for paging. If you do not wish to manage paging, you may omit these two arguments.

To bind your UI control (i.e. GridView) to this method, put an ObjectDataSource on your web form. Then, choose Configure DataSource from it’s smart tag. The Configure Data Source window will open:

Check the “Show only data components” and open the drop down list beside it. If nothing is seen, you have to build the BusinessLayer.Dll and put it in your website’s Bin folder.

Select your business class and click Next. At the next step, you have to specify which methods are used for Select, Insert, Update and Delete operations. The following image is an example :

After choosing all your methods, click Finish to close this dialog. If you are going to let the ObjectDataSource handle the paging operation, go to your page’s source code, find the code of GridView control and remove the SelectParameters. This is necessary because startRowIndex and maximumRows will be provided by ObjectDataSource control. If you do not remove them, you will get an error message.

In order to activate paging in ObejctDataSource, you have to set its EnablePaging property to True. you also have to set the AllowPaging property of your GridView control to true. In order for ObjectDataSource to do paging operation correctly, we have to tell it how many records are in database. Therefore, there must be a public method in our business class that return the total number of rows in DB. What we need is to assign it’s name to SelectCountMethod property of ObjectDataSource. You do not need to flag this property with an attribute:

public int GetTotalRecordCount()
return innerDAL.GetTotalRecordCount();

Notice that if you are filtering the results of your SelectMethod, for example by passing a filter string to it, you have to perform the same filter on the result of SelectCountMethod.

Now build and run your website. you will see that the data is being shown and the grid supports sorting and paging very well. To ensure that paging is working correctly, run Microsoft Sql Server Profiler, and check to see if the Select SQL statement has correct startRowIndex and maximumRowCount values.

To insert a new record, you may create a new entity (i.e. PersonEntity) , fill it and save it to DB.

To update a record, retrieve the record from database using it’s Primary Key, edit it and save it back to db.

To delete a record, perform a delete operation on database using the records PK.

But how to obtain the record’s Primary Key? You may save it somewhere in UI when the UI control is getting bound. To do this, I usually write such a code in RowDataBound event :

if (e.Row.RowType == DataControlRowType.DataRow)
int Serial = DataBinder.Eval(e.Row.DataItem, “Serial”);
e.Row.Cells[0].Attributes.Add(“Serial”, Serial.ToString());

In the above code, if the row being bound is a data row (not footer or header), we get the value of “Serial” column and store it in the 1st cell of the current row. Notice that whatever toy add yo .Attributes collection will be available to the rendered html page. Thus, never save passwords or other sort of crucial data with this method.

Now, for each row, you have the Primary Key value. Therefore, we may perform update and delete operations easily. Not keeping the whole data in memory (or in ViewState), increases the access to data base, but reduces the usage of server’s resources. However, it is up to you and your project’s conditions to decide that method should be used.

You may download a full example of this implementation HERE. Please note that the file is password protected and it’s password is :

Bye for now..

Implementing a 3-Tier architecture with C# – Part 4

Up to now, we have finished implementing the data access layer. However, we can reduce the dependency between the deriver DAL classes and Web.config file. As you remember, we have specified the DB provider name in Web.config file so that DAL classes must have access to this file to read the provider name. Another method to specify the provider type is using custom attributes.

Firstly, in the DataAccessLayerBase namespace, we declare a public enum type named ProviderType:

public enum ProviderType

We also remove the ProviderName property from DALHelper class, and instead, add a new internal method, as bellow, to get a string representation of each ProviderType member:

internal static string GetProviderTypeName(ProviderType providerType)
switch (providerType)
case ProviderType.SqlServer: return “System.Data.SqlClient”;
case ProviderType.Odbc: return “System.Data.Odbc”;
case ProviderType.OleDb: return “System.Data.OleDb”;
default: return “System.Data.SqlClient”;

The custom attribute we need for specifying the provider type, must have a public property of type ProviderType. We also add a constructor, with a positional parameter of type ProviderType, to our custom attribute class:

public sealed class DbProviderTypeAttribute : Attribute
public ProviderType PrType

The AttributeUsage attribute indicates that this custom attribute can be applied to classes only.

To make this attribute effective, we need to alter the base DAL class’s constructor as bellow:

first we must check to see if the DbProviderTypeAttribute attribute has been applied to the derived DAL class:

if (!this.GetType().IsDefined(typeof(DbProviderTypeAttribute), false))
throw new System.Exception(“DbProviderTypeAttribute must be applied to DAL class”);

The above code examines the existence of DbProviderTypeAttribute and throws an exception of it does not exist.

Then, the following piece of code, extracts the instance of DbProviderTypeAttribute from the class’s metadata, and uses the value of it’s PrType property to set the ProviderName :

List<object> t = this.GetType().GetCustomAttributes(typeof(DbProviderTypeAttribute), false).ToList();
DbProviderTypeAttribute Provider = t[0] as DbProviderTypeAttribute;
ProviderName = DALHelper.GetProviderTypeName(Provider.PrType);
_ProviderFactory = DbProviderFactories.GetFactory(ProviderName);

That’s it. This way we have reduced the coupling between our UI (web.config file) and the DAL class. When developing a derived DAL class, we have to apply DbProviderType attribute to it:

public class PersonDAL : DALBase
{ …

Implementing the business classes

The implementation of business classes are fairly easy. A business class is a bridge between the presentation layer and the data access layer, perform business checks, controls concurrencies and even might control the business transactions.

The business classes can be either static or instance classes. Each business method can create one or more instances of the required DAL classes. However, since each DAL class has it’s own connection object (innerConnection property), system transactions would better be controlled in business methods. This may even solve the issue that is raised when a layered architecture needs to control both business and system transactions.

The easiest way to control the transactions, is using System.Transaction name space and TransactionScope class. The illustration of TransactionScope requires an indipendent post, but for now, lets say that transaction begins when an instance of TransactionScope class is created and it is commited when the .Complete() method is called:

using (TransactionScope scope = new TransactionScope())





if an exception is thrown, the transaction will roll back :

using (TransactionScope scope = new TransactionScope())


PersonDAL PDAL= new PersonDAL();


ChildDAL CDAL= new ChildDAL();





Note that MSDTC service must be running on the server machine.

so far, the definition of business classes requires no additional settings. however, when developing the presentaion layer, we will come back and make some minor changes to make our bisuness classes compatible with UI components.

A sample business class might look like this:

public class PersonBiz

private PersonDAL innerDAL = new PersonDAL();
public DataTable FetchAll(int startRowIndex, int maximumRows)
PersonEntity entity = new PersonEntity();
innerDAL.Fill(entity, startRowIndex, maximumRows);
return entity;

public void Update(System.Data.DataTable table)
// validate fields/perform business checks and throw an exception if a criteria is not met



Implementing a 3-Tier architecture with C# – Part 3

The Fill method we have developed so far, executes the Select command on the underlaying server and picks up RowCount records from startRowIndex position. For example, if Select * from Person returnS 20 rows, and startRowIndex is 0, only the first RowCount records will be fetched. This means that the complete result set will be generated on server and will be fetched into an IDataReader, but ADO.NET transmits only RowCount number of records into the DataTable. If the results are too many, that will not be helpful that much, so we have to do something about it.

For doing this, we may define the Fill method virtual and each DAL class may override it to fetch records based on startRowIndex and RowCount from the data base. The 2nd way is to assume that every SelectCommand has two paramerers names startRowIndex and RowCount! This means that we always must supply two int parameters to Fill method. I propose you to have several overloaded Fill methods to use a proper one in different occasions.

Anyway, if we suppose that every SelectCommand executes a stored procedure having two int parameters, we might change the Fill method (or develop an overloaded one) this way:

public void Fill(DataTable table, int startRowIndex, int RowCount)
DbParameter p;

p = ProviderFactory.CreateParameter();
p.ParameterName = “StartIndex”;
p.Value = startRowIndex;

p = ProviderFactory.CreateParameter();
p.ParameterName = “RowCount”;
p.Value = RowCount;
if (table.Rows.Count > 0)

This Fill method adds two parameters to SelectCommand property of the innerAdapter, fills the DataTable by calling the innerAdapter.Fill method. Notice that we must clear up the DataTable because the innerAdapter.Fill method will append new records to the DataTable if any records exists in it.

But how to implement the paging? The answer might differ fro different data bases. However, I will provide a simple method for SQL Server 2005. SQL Server 2005 provides Ranking funtions, i.e. Row_Number() funtion, so that we may easily rank the resulting records of each table. For more information about Row_number function, please check out the SQL Server 2005 Books Online or MSDN.

Anyway, for our sample database, I have written a stored procedure named FetchAllPerson which has two int parameters, called @StartIndex and @RowCount:

create procedure FetchAllPerson
@StartIndex int,
@RowCount int

with NoFilterRecords
Select Row_Number() over (order by Serial) as RIndex , Serial, FirstName, LastName from Person
Select * from NoFilterRecords where RIndex>=@StartIndex and RIndex <@StartIndex+@RowCount
order by 1

This procedure first ranks all records, then fetch only those records positioned in the specific range. Therefore, the innerAdapter.SelectCommand.CommandType must be set to CommandType .StoredProcedure .

innerAdapter.SelectCommand.CommandText = “FetchAllPerson”;
innerAdapter.SelectCommand.CommandType = System.Data.CommandType.StoredProcedure;

Working with identity fields

The value of Identity fields is generated automatically. Therefore, a new row in a DataTable, may not have a value for its Identity fields or their value may not be identical to the mapping record in database after the record is inserted. Thus, we have to reflect the new value of Identity fields to the related row inside the DataTable. For doing this, add a Select statement to the end of Insert statements and seperate them with a semi colon :

innerAdapter.InsertCommand.CommandText = “Insert into Person (Serial, FirstName, LastName) \n” +
“Values (@Serial, @FirstName, @LastName); \n” +
“Select * from Person where Serial = SCOPE_IDENTITY()”;

The Select statement will fetch the new record from database and reflect any changes to the inserted row. To make this method work, we must set the UpdatedRowSource property to FirstReturnedRecord:

innerAdapter.InsertCommand.UpdatedRowSource = System.Data.UpdateRowSource.FirstReturnedRecord;

This may be done in the base DAL class. This setting will fetch one record and will synchronize the inserted row with the data base. The SCOPE_IDENTITY() funtion returns the last identity value assigned to a field. This works in SQL SERVER only and you must perform a similar task for other data bases.

Ok this is all about the Data Access Layer. We will discuss about the business layer on the next post.

Implementing a 3-Tier architecture with C# – Part 2

Hi again,

Let’s keep on going and work on the Data Access Layer. The DAL layer we are going to design can be used with, theoretically, any sort of RDBMS because it will take advantage of ADO.NET generic classes. For this reason, I have written a base class for DAL classes and have placed in into a seperate .dll. The reason is that the base DAL class is used for any database and is not being changed if the underlaying RDBMS is changed. But the DAL class must be replaced in order to change the target database. This will reduce coupling and will let you inherit your own DAL classes and make minor customizations only.

The base DAL class will do nearly everything and the derived DAL classes are used to customize CRUD commands only.

The following class diagram shows the relation between the base and derived DAL classes:

The DalBase class is using System.Data.Common namespace and it’s factory methods in order to create generic objects for different kind of databases. ADO.NET 2 introduces a class named DbProviderFactory, which is an implementation of Factory pattern, to produce a variety kind of database objects, like connection, command, adapter and so on. So far, it can create the following sort of objects:

  1. using System.Data.SqlClient
  2. using System.Data.Odbc
  3. using System.Data.OleDb

Most of the common ADO.NET classes are inherited from their bases classes defined in System.Data.Common namespace. For example. SqlConnection is derived from DbConenction, SqlDataAdapter is derived fom DbDataAdapter and so on.

To create a specific series of objects, we must get an instance of DbProviderFactory class, using DbProviderFactories.GetFactory method. The following code does this to obtain a factory for System.Data.SqlClient provider:

DbProviderFactory _ProviderFactory = DbProviderFactories.GetFactory(“System.Data.SqlClient”);

To make it more flexible, I will put the name of the desired provider in Web.Config file :

<add key=”Provider” value=”System.Data.SqlClient”/>


Access to Web.Config file will be through a helper class, called DalHelper. This class will provide us the connection string, provider name and so on :

public static class DALHelper
public static string ProviderName
return WebConfigurationManager.AppSettings[“Provider”];

Let’s take a look at DalBase class. DalBase has three protected properties:

  1. innerAdapter : a protected and read only DbDataAdapter that the derived classes will use to customize it’s Insert/Udpate/Select/Delete commands.
  2. innerConnection: a protected and read only DbConnection that will be in available to the derived classes and will be used in case a derived class needs to perform a particular CRUD operation.
  3. ProviderFactory: a protected and read only DbProviderFactory that will help derived classes create a specific DbConnection, DbCommand, DbDataAdapter, …

DalBase also has a parameterless constructor which will be discussed shortly. It also provides the following methods:

  1. Fill : it fills a DataSet or DataTable using innerAdapter.
  2. Update : it reflects the updates made to a DataSet/DataTable to the underlaying database using innerAdapter.
  3. Initialize : This method is protected and is used to customze the Select/Update/Insert/Delete command of innerAdapter.
  4. GetTotalRecordCount : for the time being, forget about this method. We will come back and discuss about it later.

The DALBase() parameterless constructor creates Command, Adapter and Connection objects according to the provider name, specified in Web.Config file and finally calls Initialize method:

public DALBase()
ProviderName = DALHelper.ProviderName;
_ProviderFactory = DbProviderFactories.GetFactory(ProviderName);

_innerConnection = _ProviderFactory.CreateConnection();

_innerAdapter = _ProviderFactory.CreateDataAdapter();

_innerAdapter.SelectCommand = _ProviderFactory.CreateCommand();
_innerAdapter.UpdateCommand = _ProviderFactory.CreateCommand();
_innerAdapter.InsertCommand = _ProviderFactory.CreateCommand();
_innerAdapter.DeleteCommand = _ProviderFactory.CreateCommand();

_innerConnection.ConnectionString = DALHelper.ConnectionString;
_innerAdapter.SelectCommand.Connection = _innerConnection;
_innerAdapter.UpdateCommand.Connection = _innerConnection;
_innerAdapter.InsertCommand.Connection = _innerConnection;
_innerAdapter.DeleteCommand.Connection = _innerConnection;


Therefore, by specifying a different provider name in Web.Config file, you will have a different set of ADO.NET objects.

The fill method has three parameters:

  1. a DataTable (or DataSet) to be filled with innerDataAdapter.
  2. an int parameter called startRowIndex. This is used for paging.
  3. ab int parameter called RowCount. This will be used for paging too

If one or two of the two int parameters is -1, the paging will not be performed.

if (startRowIndex == -1 && RowCount == -1)
_innerAdapter.Fill(startRowIndex, RowCount, table);


Implementing a 3-Tier architecture with C# – Part 1

Hi again,

Nearly two years a go I probed Internet to find out a good reference architecture for my ASP.NET applications. What I meant GOOD ARCHITECTURE was an architecture that:


I tried out a variety of architectures I’d come across but I found them somehow faulty. For example, in some cases, typed datasets (datatable) was used with TableAdapters attached to them! This contradicts the rules of a N-Tier application because an Entity would carry out it’s CRUD functions, that must be placed in DAL layer, with itself anywhere (i.e. BIZ layer).

I finally designed a shining architecture (!) that is going to be shared and discussed here. Since a weblog is too small for discussing about all details, I will write about the main parts of the implementation and about main ideas. Therefore we may forget about the details and set our mind to the significant things. I invite you to leave comments and let’s grow this architecture and rectify it’s problems.

The implementation that I am going to discuss is simple and is based on a (somehow) standard 3-tier architecture. In this architecture, four layers play a part:

  1. Common Layer : includes entities and is common between other three layers
  2. Data Access Layer (DAL): includes CRUD operations
  3. Business Layer (BIZ) : does business operations, check business rules, manage concurrencies,…
  4. Presentation Layer (UI)

The presentation layer can be either Web-based or Windows-based. However, since my focus on this article is on the implementation, I will stick to a Web UI.

As the above image shows, each layer is dependent on the common layer and the underneath layer. For example, Biz layer can access the common and DAL layer but can not access UI layer. A layer is not necessarily a physical component. In the other hand, you may define a class for each layer and put all of them inside a single application (i.e. EXE). However, in order to reduce the coupling and increasing the cohesion, we will create a .DLL for each layer and separate them psychically.

The Common Layer

As mentioned before, the Common layer comprises all Entity definitions. An Entity definition is a class describing an entity. An entity is an object that will transmit information between layers and is, nearly, a representation of actual data in DB. Entities can be of any type, however, I strongly recommend to use Typed DataSets (DataTables) because you can work with them visually and ASP.NET UI components are astonishingly match with them.

In order to create the Common layer, first start a solution and name it ThreeTierArch. Afterwards, add a Class Library project to the solution and call it CommonLayer. This will, by default, produce an assembly named CommonLayer.dll and a namespace called CommonLayer. In Solution Explorer window, right-click the CommonLayer project, choose “Add new item” and add a DataSet to the project. An empty DataSet is added and you may drag a DataTable from Toolbox window inside it. DataSet designer let’s you design your DataSet visually and create any relationship needed between tables.

The above image displays a sample DataSet with two DataTables having a Master/Detail relationship.

That’s it. Add as many as needed data tables and datasets to your project. These are our Entities. Something that I usually do, to shorten my entity names, is to inherit a class from their class. For example, I might add a class like the following one to CommonLayer in order to shorten my entity name:

public class PersonEntity : PersonDataSet.PersonEntityDataTable

Needless to say that this is not necessary and it is just to get rid of long names!

Ok, it is enough for the common layer. Tomorrow, I will talk about the DAL layer and we will see how to implement it.

cheers 🙂