Implementing a Generic Data Access Layer in ADO.NET Part 2

In the first part of this series of articles on Data Access Layer, we have had a look at what the strategies are, for designing and implementing a Generic Data Access Layer. We have had a look at the enumerators and the factory classes that we will be using. In this part of this article of three part series, we will discuss how we can implement the DatabaseHelper class, one that would be responsible for performing the actual database operations.

The DatabaseHelper class encapsulates the various calls to the database to perform the CRUD operations. The DBManager class that we will discuss later acts as a wrapper on top of this class. You have various methods in the DatabaseHelper class to add parameters, to execute queries, stored procedures, etc.

Here is the code that illustrates how the connection to the database is established based on the provider type chosen and the command object created.

 public DatabaseHelper(string connectionstring, ProviderType provider)
this.strConnectionString = connectionstring;
objFactory = DBFactory.GetProvider(provider);
objConnection = objFactory.CreateConnection();
objCommand = objFactory.CreateCommand();
objConnection.ConnectionString = this.strConnectionString;
objCommand.Connection = objConnection;

In ADO.NET, you have the following data providers.

  • SQL Server Data Provider
  • Oracle Data Provider
  • Odbc Data Provider
  • OleDB Data Provider

Note: Depending on the data provider used, you need to use the command object that is specific to that provider. Your data reader should also be specific to the data provider used. The use of the DBFactory class as shown in the code snippet above. Note that you use the command objects to execute the database commands that contain the SQL statements. Added to this, we will have overloaded versions of AddParameter method to add parameters to the command objects so that we can pass parameters to the database stored procedures or SQL statements. Here is the simplest version of the AddParameter method.

internal int AddParameter(string name, object value)

DbParameter dbParameter = objFactory.CreateParameter();
dbParameter.ParameterName = name;
dbParameter.Value = value;
return objCommand.Parameters.Add(dbParameter);

While the ParameterName identifies the unique name of the parameter to be passed, the Value implies the value of the parameter passed. Hence, if the ParameterName comprises of “@EmpName”, the Parameter’s value might be “Joydip Kanjilal”.

Implementing a Generic Data Access Layer in ADO.NET Part 1

A Data Access Layer (DAL) is an integral part in the design of any application. There are plenty of articles that discuss how we an implement a DAL using ADO.NET. Most of these have constraints in the sense that they are not generic in nature. In other words, they are not provider independent. This series of articles will discuss the implementation of a generic, i.e., a provider independent Data Access Layer in ADO.NET. The basic prerequisite to learning this article is a proper understanding of ADO.NET and good coding skills in C#. I will present the code examples in this article in C#. However with little effort, you can twist it over to VB.NET as well.

The Strategies Involved in Creating a Data Access Layer

Let us first understand what the necessities are for building such a layer. I would rather start by discussing how an application designed using ADO.NET actually connects to the database and performs the CRUD (Create, Read, Update and Delete) operations.

First, you need to open the connection using a database provider. Fine, but what is a provider anyway? A provider is responsible for connecting to a specific database. Why specific? The reason is that a provider for an Oracle database cannot be used to connect to a SQL Server database and vice-versa. Next, you need a command object that can be used to execute the database commands of your choice. This is followed by the usage of a DataReader or a DataSet or a DataTable instance to retrieve data (if you are performing a Read operation) from the database table. When you use a DataSet, you need a DataAdapter as a bridge between the actual database and the DataSet instance.

Implementing the DAL Framework

With this in mind, let us design a provider independent Data Access Layer.

Let us first understand the ADO.NET Library. The major classes that constitute the ADO.NET library are:


  • Connection
  • Command
  • Data Reader
  • Data Adapter

The corresponding interfaces that the above classes implement are stated below.


  • IDBConnection
  • IDataReader
  • IDBCommand
  • IDBDataAdapter

The Data Providers that make up the library are specific to a particular database that they would connect to. These are the Data Providers that are available in ADO.NET.


  • SQL Server Data Provider
  • Oracle Data Provider
  • ODBC Data Provider
  • OleDB Data Provider

Now we are all set to implement our DAL. The major components that constitute our DAL block are:


  • ProviderType (Enum)
  • DatabaseConnectionState (Enum)
  • StoredProcedureParameterDirection (Enum)
  • DBManager (Class)
  • DBHelper (Class)

We will start our discussion with the enum data type that would contain the data provider types in it. These provider types relate to the databases that we will be connecting to, depending our requirements. The following code snippet illustrates the ProviderType enum that contains four values that correspond to a specific data provider.Continues…

Understanding The ASP.NET Page Life Cycle

Understanding the Page Life Cycle in ASP.NET is essential knowledge for developing ASP.NET apps, without a firm understanding of the Page Life Cycle developing apps will be an uphill battle.

When a web page is sent to the Web Server for processing, it goes through a sequence of steps before it finally gets displayed in the Web Browser. This article discusses these series of steps and events that occur in a page life cycle in ASP.NET.

From The Web Browser to IIS

When a POST request is initiated from the client side, the Web Server traps the request and it is usually routed to an .aspx web page. The request is actually routed to the HTTP Pipeline, a chain of managed objects.

After the HTTP Page handler class is identified, the ProcessRequest () method is called which eventually fires the different page events in the life cycle of a web page. The sequence of events that takes place in the life cycle of a web page in ASP.NET is:

  1. Page_Init
  2. LoadViewState
  3. LoadPostData
  4. Page_Load
  5. RaisePostDataChangedEvent
  6. RaisePostBackEvent
  7. Page_PreRender
  8. SaveViewState
  9. Page_Render
  10. Page_UnLoad

All these events are associated with their respective handlers and you can even override them to customize their default behaviour. The following section discusses each of these events in detail.

The Page Life Cycle Events Explained

Once the request for the web page arrives at the web server, the ASP.NET runtime determines whether the page needs to be parsed or whether a cached version of the page needs to be rendered to the requestor. Then the Request and the Response objects for the page are set and the page life cycle starts.

The Page_Init event is the first event to be triggered in the page life cycle. It is responsible for the initialization activities that are essential to create a page instance. In this phase of the page life cycle, all the server controls of the web page are initialized to their default values. However, it should be noted that the View State for a page is not available at this stage of the page life cycle and a server control of the page cannot access other server controls of the page at this phase.

You can use the Page_Init event to create or re-create the controls that need to be created or re-created dynamically. The following example illustrates how you can override the OnInit() method.

protected override void OnInit(EventArgs e)
if (Page != null)
Page.Trace.Write ("The OnInit method has been called");

Next, the LoadViewState method is called. “The load view state stage only happens when the page has been posted back.  During this stage the view state data saved from the previous page visit is loaded and recursively populated into the Page’s control hierarchy”. This method restores the View State information of a web page that was last saved using the SaveViewState method. You can override the LoadViewState() method to get an idea on how the viewstate is actually restored.

ASP.NET Security Best Practices

Security is one of the most important concerns in application software development. Building a robust security model is one of the most important factors that drive the success of application software. As far as security in ASP.NET is concerned, three terms come into my mind, i.e., Authentication, Authorization and Impersonation. Put simply, authentication authenticates the user’s credentials and authorization relates to the resources that an authenticated user has access to. This article is the first in a series of articles on ASP.NET security and discusses these concepts and their applicability.

Let us start our discussion with a brief outline on the sequence of events are as far as authentication and authorization are concerned when a new request comes in. When a new request arrives at IIS, it first checks the validity of the incoming request. If the authentication mode is anonymous (default) then the request is authenticated automatically. But if the authentication mode is overridden in the web.config file settings, IIS performs the specified authentication check before the request is passed on to ASP.NET.

ASP.NET then checks whether Impersonation is enabled or not. We will discuss impersonation later in this article. If impersonation is enabled, ASP.NET executes with the identity of the entity on behalf of which it is performing the task; otherwise, the application executes with the identity of the IIS local machine and the privileges of the ASP.NET user account. Finally, the ASP.NET engine performs an authorization check on the resources requested by the authenticated user and if the user is authorized, it returns the request through IIS pipeline.

The following section discusses Authentication, Authorization and Impersonation and how we can implement them in ASP.NET applications.

Authentication determines whether a user is valid or not based on the user’s credentials. Note that a user can be authorized to access the resources provided the user is an authenticated user. The application’s web.config file contains all of the configuration settings for an ASP.NET application. An authentication provider is used to prove the identity of the users in a system. There are three ways to authenticate a user in ASP.NET:

  • Forms authentication
  • Windows authentication
  • Passport authentication

Forms Authentication

This is based on cookies where the user name and the password are stored either in a text file or a database. It supports both session and persistent cookies.

After a user is authenticated, the user’s credentials are stored in a cookie for use in that session. When the user has not logged in and requests for a page that is secured, he or she is redirected to the login page of the application. The following code snippet illustrates how this can be implemented in ASP.NET.

<authentication mode="Forms"/>
<forms name="LoginForm" loginUrl="LoginForm.aspx" />
<deny users="?"/>

Note that the symbol “?” indicates all Non Authenticated and Anonymous users. Typically, the user enters the username and the password, clicks the login button and the form validates the values against values from that stored in a persistent store, usually a database. The following code snippet illustrates how this can be validated.

String username = txtUserName.Text;
String password = txtPassword.Text;
bool isUserValid = false;
//Code to validate the user name and password
FormsAuthentication.RedirectFromLoginPage(txtUserName.Text, False);
else // User is not valid
lblMessage.Text = “Invalid login…”;

The RedirectFromLoginPage method creates an authentication ticket and is used to redirect an authenticated user back to the originally requested URL or the default URL. The following code snippet illustrates how we can specify the user’s credentials in the application’s web.config file.

<authentication mode="Forms">
<forms loginUrl="LoginForm.aspx">
<user name="JoydipK" password="JudeK" />

However you choose the above technique of authentication you should provide a means of encrypting the configuration file for security reasons. I will discuss these and other issues in the forthcoming articles in this series of articles on ASP.NET Security.


ASP.NET MVC Routing – StopRoutingHandler and IgnoreRoute

There are two methods to exclude a URL from being handled by Routing – StopRoutingHandler and IgnoreRoute.


The below listing shows two routes created manually, the first of which has a StopRoutingHandler which blocks Routing for handling the matching URL:

public static void RegisterRoutes(RouteCollection routes)
routes.Add(new Route
"{appresource}.axd/{*urlInfo}", new StopRoutingHandler() ));

routes.Add(new Route
"articles/{cat}/{id}", new SomeRouteHandler()  ));

Therefore a URL such as resource1.axd will be matched to the first route and since that route returns a StopRoutingHandler  Routing system will pass this request on for normal ASP.NET processing.


A simpler way to circumvent Routing is to use IgnoreRoute .

This is declared in a similar manner to the familiar MapRoute method and is normally used alongside it as shown below:

public static void RegisterRoutes(RouteCollection routes)
routes.MapRoute("norm", “articles/{cat}/{id}“, new MvcRouteHandler());

This is a lot tidier than the method using StopRoutingHandler and functions exactly the same.

ASP.NET MVC Routing – WildCard Parameters

A wildcard or catch-all parameter in MVC Routing allows for a route to match a URL with an arbitrary number of parameters. This feature is especially useful when building CMSs, blogs, wikis and other content driven applications.

Typically, URLs for such content driven apps should be as descriptive as possible, for example reviews/hotels/germany/berlin/fourseasons. But what if the CMS also handles airline reviews, and want to use URLs like reviews/airlines/northwest . In this circumstance reviews would be the controller and airlines or hotels the action, but the id is either germany/berlin/fourseasons or northwest. Normally the parameters are inferred from the “/” character, but with wildcard parameters a chunk of a URL with multiple / characters can be read as a single parameter. In this case the below route definition could be used:

routes.MapRoute("reviews", "{controller}/{action}/{*id}");

The WildCard * character allows everything beyond the action parameter to be placed in the id parameter.

ASP.NET 4.0 URL Routing Tutorial

It is a common mis-perception that URL Routing is  exclusive to MVC, with .NET 3.5 SP1 and above URL Routing can be also used with ASP.NET Web Forms. In ASP.NET 4.0 URL Routing is fully integrated and so straightforward and powerful to use it should be preferred over URL Rewriting whenever possible.

Routing allows for SEO friendly URLs such as to be used instead of a URL such as .

First you will need to register the Routes in a subroutine and then add them in the Application_Start() event:

void RegisterRoutes(RouteCollection routes)
"articles-category",  //Name of the Route (can be anyuthting)
"articles/{category}", // URL with parameters
"~/category.aspx"); //Page which will handles and process the request

void Application_Start()

In the above code, the Route named articles-category will handle incoming requests for URLs which match the articles/{category} route definition. This route definition uses “articles/” as a literal string in the URL and then takes the subsequent URL text for the category parameter.

For example, the URL articles/routing will direct to the page category.aspx and pass “routing” as the value for the category parameter. There is a lot of flexibility in mapping parameters to the URL, please refer to Default Values, Constraints, Multiple Parameters for more details.

The parameters from the URL are passed to the aspx page in key-value dictionary pairs which can be accessed from Page.RouteData.Values. So in the above example the category value can be read into a variable using the below code:

string category = Page.RouteData.Values["category"] as string;

This code would typically be placed in the Page_Load() event of the page.

In addition to accessing the parameters programatically, they can also be read by the SQLDataSource control using the <asp:routeparameter> control:

<asp:sqldatasource id="ds1" runat="server"
connectionstring="<%$ ConnectionStrings:Main %>"
selectcommand="select title, text from Articles where category=@cat">
<asp:routeparameter name="category" RouteKey="cat" />

Generate URLs Using ASP.NET Routing

A major difference between Routing and URL rewriting is that Routing also allows for the generation of outgoing URLs served on the site. In the above example if we wanted to show users a URL for the ado category of articles, then we could generate a URL by using the below code:

string url = Page.GetRouteUrl("articles-category",
  new { category = "ado" });

This code uses the articles-category Route to generate a url string, this could be used on the page or passed to the Response.Redirect method to redirect to another page. Whilst the last use is valid, if the user needs to be redirected to another page using Routing it is more efficient to use the Response.RedirectToRoute method:

  new { category = "ado" });

ASP.NET MVC Routing – Constraints

MVC Routing Constraints allow for the application of a regular expression to a URL segment to determine if the route will match the request.

Previously in the introductory MVC Routing Tutorial we looked at defining Routes based on the number of segments in a URL. So the Route definition:

routes.MapRoute(“simple”, “{controller}/{action}/{id}“);

would match the URL /articles/category/12 . But suppose the content management system we are building also had a blog and we wanted to use URLs such as /2010/04/03. Such a URL would map to the simple Route we defined above and so in our example it would be processed as if it were referring to an article category.

Therefore we can see that just mapping URL segments to Route definitions is extremely limiting. Constraints allow for regex to be used to read the contents of the URL parameters to determine which Route definition to use. For example:

routes.MapRoute(“blogpost”, “{year}/{month}/{day}“
, new {controller=”blogpost”, action=”index”}
, new {year=@“\d{4}“, month=@“\d{2}“, day=@“\d{2}“});

routes.MapRoute(“simple”, “{controller}/{action}/{id}“);

Here there are two route definitions both of which have three parameters. Note the highlighted part of the blogpost route definition which is the definition of the constraint. The constraint in this case matches a URL of  4 digit number/2 digit number/2 digit number format. If this test is met the blogpost route definition will be applied, for all other 3 segment URLs the simple route definition will be applied.

Note the order of the definitions. ASP.NET MVC Routing attempts to match route definitions in sequence and once a match is made it is processed. Therefore, routes which contain constraints should always be placed before the simple non-constrained route definitions.

ASP.NET MVC Routing – Multiple Parameters

In MVC Routing, multiple parameters can be used in a URL segment. For example the route definition:

routes.MapRoute(“multiple”, “{controller}/{action}/{author}-{tag}");

contains multiple parameters (ie author and id) in the final segment. This will match the URL blog/technology/johnspencer-aspnet . Any literal string can separate the multiple parameters, so the route definitions {author}.{tag} , {author}on{tag}, author-{author}on{tag} are all be valid. The parameters must be separated by a literal string, so the definition {author}{tag} would not be valid.

When working with multiple parameters it is important to understand the concept of greedy matching. Under this concept the first parameter will take the maximum amount of the url fragment that is possible. For example, how would the URL fragment /john.francis.spencer.sqlserver/ be mapped the the route definition {author}.{tag} since the are three “.” characters and only one as a literal in the route definition? Under greedy matching, the first parameter (author) will take john.francis.spencer but leave sqlserver for the tag parameter since the tag parameter must take at least some text beyond a “.” character.