Explains Authentication Techniques

Well, as you all are familiar what authentication is and what ASP .NET is, I will directly jump to the different types of ASP .NET Authentication options we have.

The three main types of authentication available in ASP .NET are:

1) Windows Authentication

2) Forms Authentication

3) Passport Authentication

The authentication process can be divided into following steps. Reading this will give you a more clear idea and helps understanding this article.

STEPS

1) The request is first served by IIS server. IIS check for the IP of incoming request and allow or deny access according to the allowable domain access configuration.

2) Next thing is IIS will perform it’s authentication it is configured to do. By default it allows all access but you can always go back and change it per application.

3) Once this is done request is passed to ASP .NET application itself where the authentication of the user is performed according to the setting made in WEB.CONFIG and further the resources are allowed or denied to the user.

Now remember our three authentication techniques I described at start.

1) Windows Authentication: will allow you to authenticate user on his/her windows account and credentials. IIS does this for you and passes and credential to code page if required. This is used when the application is an INTRANET application and uses are fixed.

2) Passport Authentication: uses Microsoft passport services to authenticate user. This is used when you have different application hosed on a server and you want to provide single time authentication to user. What I mean is once he/she is authenticated he/she will be authorized to access other applications without any authentication process which has passport authentication as its authentication method.

3) Forms Authentication: This is the most commonly used method of authentication. Simple THML forms are used to collect user data and they are validated against your database and custom configuration for specific user.

Before we go ahead and see details of these methods go through the diagram below. It will give you more idea about I mentioned above.

Now let’s start with each of the above methodology to perform ASP .NET authentication. Let’s take a look at passport authentication and windows authentication first.

1) Passport Authentication

This lets you integrate your application with Microsoft Passport services to authenticate users and allow access to your application resources. This has a major benefit named single-sign on. It means user has to provide credentials only once to access all the application using passport authorization.

It uses encrypted cookie mechanism. To use and implement this in your application you have to download Passport Software Development kit and it has to be installed on your server where you are going to host your application. Sample implementation is shown as below.

<configuration>

<authenticationmode="Passport">

<passportredirectUrl="login.aspx" />

authentication>

<authorization>

<deny users="?" />

authorization>

configuration>

2) Windows Authentication

As I have mentioned before while creating Intranet application the first choice comes to our mind is windows authentication mode. When you integrate windows authentication you don’t have to create login page and maintain username/password database. But yes if you want to customize application to your get windows authentication and custom mode you need to manage table where you have to define user roles with your with their domain username in database.

This can be implemented as simply as writing following lines in your web.config file.

<authenticationmode="Windows" />

<authorization>

<denyusers="?"/>

authorization>

The benefit of using “Deny users” is that application is only available when username is always available from code on the server. I remember creating an application for one of my project where I had to implement mix-mode authentication along with windows authentication. I had to create different roles according to organization’s requirement and then allow/deny resources to the users according to their domain login name. If you have any question regarding this kind of mix mode authentication please send me an email and I would be more than happy to help you with your custom requirements.

I will try to briefly incorporate this. Simply create a table where the actual user names (Domain) user names are store. Let’s say your login name is “k.smith” in your company domain, store this in your database and store user role against this username. Again user roles can be complex and possibly a combination of 2-3 tables.

Once you store this in your database you can always check at the page load time the user from whom the request is coming from. That can be achieved from code below.

System.Security.Principal.IPrincipal User;

User = System.Web.HttpContext.Current.User;

string username = User.Identity.Name;

Now you have user name so you can check the role associated with this username and show/hide menu items accordingly. That’s it for now. I will explain more about Forms authentication in second part of this article.

javascript

Input Components: Wysiwyg Editor SpreadSheet Editor Tree-View Slider Control Checkbox Control Radio Control Dropdown Control DatePicker Control ColorPicker Control FlipFlop MultiLevelSelector NumberField ExtendedTextField LimitedTextarea EditableListBox Other Components: Tree-View Toolbar Control TabSet DataGrid Scrollable Div ResizeGrip TitleToTooltip ListRegister JSRS (JavaScript Remote Scripting) Miscellaneous

N-Tier Web Applications using ASP.NET 2.0 and SQL Server 2005 - Part 1

When .NET Framework was first introduced, it provided excellent features that made the construction of ASP.NET applications a breezy experience. Now the next version of .NET Framework (version 2.0) along with SQL Server 2005 builds on the foundation of the previous versions and introduces some new features that can greatly aid in the design and development of N-Tier ASP.NET 2.0 applications. In this article, I will show you to how to construct an N-Tier ASP.NET 2.0 Web application by leveraging the new features of ASP.NET 2.0 and SQL Server 2005 such as TableAdapter Configuration Wizard, ObjectDataSource Control, App_Code directory to store reusable components, Master Pages, CLR Stored Procedures, and Caching. Along the way, I will also highlight the best practices of using the new ASP.NET features for building N-Tier enterprise class Web applications. From an implementation standpoint, Part-1 of this article will focus on the creation of CLR stored procedures and the data access components in addition to describing the overall architecture of the sample application. Part-2 will focus on the business logic layer object, ASP.NET user interface pages, caching and so on.

Introduction

Designing N-Tier client/server architecture is no less complex than developing two-tier architecture, however the N-Tier architecture, produces a far more flexible and scalable client/server environment. In two-tier architecture, the client and the server are the only layers. In this model, both the presentation layer and the middle layer are handled by the client. N-Tier architecture has a presentation layer and three separate layers - a business logic layer and a data access logic layer and a database layer. The next section discusses each of these layers in detail.

Different Layers of an N-Tier application

In a typical N-Tier environment, the client implements the presentation logic (thin client). The business logic and data access logic are implemented on an application server(s) and the data resides on database server(s). N-tier architecture is typically thus defined by the following layers:

Presentation Layer: This is a front-end component, which is responsible for providing portable presentation logic. Since the client is freed of application layer tasks, which eliminates the need for powerful client technology. The presentation logic layer consists of standard ASP.NET web forms, ASP pages, documents, and Windows Forms, etc. This layer works with the results/output of the business logic layer and transforms the results into something usable and readable by the end user.

Business Logic Layer: Allows users to share and control business logic by isolating it from the other layers of the application. The business layer functions between the presentation layer and data access logic layers, sending the client's data requests to the database layer through the data access layer.

Data Access Logic Layer: Provides access to the database by executing a set of SQL statements or stored procedures. This is where you will write generic methods to interface with your data. For example, you will write a method for creating and opening a SqlConnection object, create a SqlCommand object for executing a stored procedure, etc. As the name suggests, the data access logic layer contains no business rules or data manipulation/transformation logic. It is merely a reusable interface to the database.

Database Layer: Made up of a RDBMS database component such as SQL Server that provides the mechanism to store and retrieve data.

Now that you have a general understanding of the different layers in a N-Tier application, let us move onto discuss the implementation of a N-Tier Web application.

Implementation

In this article, I will consider an example web site (that displays authors and author titles information) constructed using N-Tier principles and use the example Web site to demonstrate the new features of ASP.NET 2.0 and SQL Server 2005. The sample Web site shown in this example is very simple and straightforward and will consist of only two pages: the first page will show the list of authors from the pubs database and the second page will display the list of titles specific to a selected author.

Please note that this article is not aimed at providing an exhaustive coverage of the individual features of ASP.NET 2.0, instead it only focuses on helping the readers understand the features of ASP.NET 2.0 and SQL Server 2005 that are essential to building a N-Tier web application.

Architecture of the Example Application

The following screenshot shows the different layers in the example application. It also highlights the important characteristics of the example application.

Some of the important characteristics of the sample application are as follows:

1.The stored procedures in the SQL Server 2005 database are created using C#. The ability to create stored procedures in managed code enables complex business logic to be executed close to the database resulting in performance improvements. The compiled nature of the stored procedure also results in increased performance.

2.The data access layer classes are generated using the new TableAdapter Configuration Wizard, which enables you to create data access layer classes without writing a single line of code.

3.ASP.NET Web forms in the user interface layer are generated using master pages, providing a consistent look and feel for the entire application.

4.Web forms utilize ObjectDataSource control to directly bind the output of the middle tier methods to data bound controls such as a GridView control.

5.Web forms also take advantage of caching of database contents to increase the performance and throughput of the web application. This is made possible by the use of the database cache invalidation mechanism that can automatically remove specific items from the cache when the data in the database table changes.

Implementation of the Application

I will discuss the implementation by discussing each of the above layers, starting with the database layer.

Database Objects using Managed Code

One of the neat features of SQL Server 2005 is the integration with the .NET CLR. The integration of CLR with SQL Server extends the capability of SQL Server in several important ways. This integration enables developers to create database objects such as stored procedures, user defined functions, and triggers by using modern object-oriented languages such as VB.NET and C#. In this article, I will demonstrate how to create the stored procedures using C#. Before looking at the code, let us understand the pros and cons of using managed language in the database tier to create server side objects.

T-SQL Vs Managed Code

Although T-SQL, the existing data access and manipulation language, is well suited for set-oriented data access operations, it also has limitations. It was designed more than a decade ago and it is a procedural language rather than an object-oriented language. The integration of the .NET CLR with SQL Server enables the development of stored procedures, user-defined functions, triggers, aggregates, and user-defined types using any of the .NET languages. This is enabled by the fact that the SQL Server engine hosts the CLR in-process. All managed code that executes in the server runs within the confines of the CLR. The managed code accesses the database using ADO.NET in conjunction with the new SQL Server Data Provider. Both Visual Basic .NET and C# are modern programming languages offering full support for arrays, structured exception handling, and collections. Developers can leverage CLR integration to write code that has more complex logic and is more suited for computation tasks using languages such as Visual Basic .NET and C#. Managed code is better suited than Transact-SQL for number crunching and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. T-SQL is a better candidate in situations where the code will mostly perform data access with little or no procedural logic. Even though the example you are going to see in this article is best written using T-SQL, I will take the managed code approach and show you how to leverage that feature.

Creating CLR Based Stored Procedures

For the purposes of this example, create a new SQL Server Project using Visual C# as the language of choice in Visual Studio 2005. Since you are creating a database project, you need to associate a data source with the project. At the time of creating the project, Visual Studio will automatically prompt you to either select an existing database reference or add a new database reference. Choose pubs as the database. Once the project is created, select Add Stored Procedure from the Project menu. In the Add New Item dialog box, enter Authors.cs and click Add button. After the class is created, modify the code in the class to look like the following.

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public class Authors

{

[SqlProcedure]

public static void GetAuthors()

{

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new

SqlConnection("context connection=true"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "Select DatePart(second, GetDate()) " +

" As timestamp,* from authors";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

}

[SqlProcedure]

public static void GetTitlesByAuthor(string authorID)

{

string sql = "select T.title, T.price, T.type, " +

"T.pubdate from authors A" +

" inner join titleauthor TA on A.au_id = TA.au_id " +

" inner join titles T on TA.title_id = T.title_id " +

" where A.au_id = '" + @authorID + "'";

using (SqlConnection conn = new

SqlConnection("context connection=true"))

{

conn.Open();

SqlPipe sp = SqlContext.Pipe;

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = sql;

SqlParameter paramauthorID = new

SqlParameter("@authorID", SqlDbType.VarChar, 11);

paramauthorID.Direction = ParameterDirection.Input;

paramauthorID.Value = authorID;

cmd.Parameters.Add(paramauthorID);

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

}

}

Let us examine the above lines of code. The above code starts by importing the required namespaces and then declares a class named Authors. There are two important classes in the Microsoft.SqlServer.Server namespace that are specific to the in-proc provider:

***SqlContext: This class encapsulates the extensions required to execute in-process code in SQL Server 2005. In addition it provides the transaction and database connection which are part of the environment in which the routine executes.

***SqlPipe: This class enables routines to send tabular results and messages to the client. This class is conceptually similar to the Response class found in ASP.NET in that it can be used to send messages to the callers.

The Authors class contains two static methods named GetAuthors and GetTitlesByAuthor. As the name suggests, the GetAuthors method simply returns all the authors from the authors table in the pubs database and the GetTitlesByAuthor method returns all the titles for a specific author.

Inside the GetAuthors method, you start by getting reference to the SqlPipe object by invoking the Pipe property of the SqlContext class.

SqlPipe sp = SqlContext.Pipe;

Then you open the connection to the database using the SqlConnection object. Note that the connection string passed to the constructor of the SqlConnection object is set to "context connection=true" meaning that you want to use the context of the logged on user to open the connection to the database.

using (SqlConnection conn = new SqlConnection("context connection=true"))

Here open the connection to the database using the Open() method.

conn.Open();

Then you create an instance of the SqlCommand object and set its properties appropriately.

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "Select DatePart(second, GetDate()) " + " As timestamp,* from authors";

Finally you execute the sql query by calling the ExecuteReader method of the SqlCommand object.

SqlDataReader rdr = cmd.ExecuteReader();

Then using the SqlPipe object, you then return tabular results and messages to the client. This is accomplished using the Send method of the SqlPipe class.

sp.Send(rdr);

The Send method provides various overloads that are useful in transmitting data through the pipe to the calling application. Various overloads of the Send method are:

*Send (ISqlDataReader) - Sends the tabular results in the form of a SqlDataReader object.

*Send (ISqlDataRecord) - Sends the results in the form of a SqlDataRecord object.

*Send (ISqlError) - Sends error information in the form of a SqlError object.

*Send (String) - Sends messages in the form of a string value to the calling application.

Both the methods in the Authors class utilize one of the Send methods that allows you to send tabular results to the client application in the form of a SqlDataReader object. Since the

GetTitlesByAuthor method implementation is very similar to the GetAuthors method, I will not be discussing that method in detail.

Now that the stored procedures are created, deploying it is very simple and straightforward. Before deploying it, you need to build the project first. To build the project, select Build->Build from the menu. This will compile all the classes in the project and if there are any compilation errors, they will be displayed in the Error List pane. Once the project is built, you can then deploy it onto the SQL Server by selecting Build->Deploy from the menu. This will not only register the assembly in the SQL Server but also deploy the stored procedures in the SQL Server. Once the stored procedures are deployed to the SQL Server, they can then be invoked from the data access layer, which is the topic of focus in the next section.

Before executing the stored procedure, ensure you execute the following sql script using SQL Server Management Studio to enable managed code execution in the SQL Server.

EXEC sp_configure 'clr enabled', 1;

RECONFIGURE WITH OVERRIDE;

GO

Data Access Layer using TableAdapter Configuration Wizard

Traditionally the process you employ to create data access layer classes is a manual process, meaning that you first create a class and then add the appropriate methods to it. With the introduction of Visual Studio 2005, Microsoft has introduced a new TableAdapter Configuration Wizard that makes creating a data access logic layer class a breezy experience. Using this wizard, you can create a data access logic layer component without having to write a single line of code. This increases the productivity of the developers to a great extent. Once you create those classes, you can consume them exactly the same way you consume built-in objects. Before looking at an example, let us briefly review what a TableAdapter is. A TableAdapter connects to a database, executes queries, or stored procedures against a database, and fills a DataTable with the data returned by the query or stored procedure. In addition to filling existing data tables with data, TableAdapters can return new data tables filled with data. The TableAdapter Configuration Wizard allows you to create and edit TableAdapters in strongly typed datasets. The wizard creates TableAdapters based on SQL statements or existing stored procedures in the database. Through the wizard, you can also create new stored procedures in the database.

This section will discuss the creation of a data access component that will leverage the stored procedures created in the previous step. To start, create a new ASP.NET web site named NTierExample in Visual C# by selecting New Web Site from the File menu as shown below.

To create a data component, begin by right clicking on the web site and selecting Add New Item from the context menu. In the Add New Item dialog box, select DataSet from the list of templates. Change the name of the file to Authors.xsd and click Add.

When you click Add, you will be prompted if you want to place the component inside the App_Code directory. Click OK in the prompt and this will bring up the TableAdapter Configuration Wizard. In the first step of the TableAdapter Configuration Wizard, you need to specify the connection string and in the second step you will be prompted if you want to save the connection string in the web.config file. In this step, save the connection string to the web.config file by checking the check box.

In the next step, you will be asked to choose a command type. Select the Use existing stored procedures option as shown below and click Next.

Clicking Next in the above screen brings up the following screen wherein you select the stored procedure to use.

Click Next in the above dialog box and you will see the Choose Methods to Generate dialog box wherein you can specify the name of the method that will be used to invoke the stored procedure selected in the previous step. Specify the name of the method as GetAuthors as shown below:

Clicking Next in the above screenshot results in the following screen wherein you just hit Finish.

When you click on Finish, Visual Studio will create the required classes for you. After the classes are created, you need to rename the class to Authors. After making all the changes, the final output should look as follows.

That's all there is to creating a data access component using the TableAdapter Configuration Wizard. As you can see, all you have to do is to provide the wizard with certain information and Visual Studio hides all the complexities of creating the underlying code for you.

Now that you have created the data access layer method for the GetAuthors stored procedure, you need to do the same thing for the GetTitlesByAuthor stored procedure. To this end, add another TableAdapter to the Authors.xsd by selecting Data->Add->TableAdapter from the menu and follow through the wizard steps. Remember to specify the stored procedure name as GetTitlesByAuthor this time. Note that at the time of writing this article using Visual Studio 2005 Beta 2, I encountered some problems in getting the wizard to work because of some bugs. If you run into any problem with the wizard, simply exit from the wizard, select the appropriate TableAdapter from the designer and select View->Properties Window from the menu. Through the properties dialog box, you should be able to perform all the configurations related to a TableAdapter.

Storing Utility Classes in App_Code Directory

You might remember that when you created the data component, you placed the data component class inside the App_Code directory, which is a special directory used by ASP.NET. It is very similar to bin directory, but with the following exceptions: While the bin directory is designed for storing pre-compiled assemblies used by your application, the App_Code directory is designed for storing class files to be compiled dynamically at run time. This allows you to store classes for business logic components, data access components, and so on in a single location in your application, and use them from any page. Because the classes are compiled dynamically at run time and automatically referenced by the application containing the App_Code directory, you don't need to build the project before deploying it, nor do you need to explicitly add a reference to the class. ASP.NET monitors the App_Code directory and when new components are added, it dynamically compiles them. This enables you to easily make changes to a component and deploy with a simple XCOPY or with a drag-and-drop operation. In addition to simplifying the deployment and referencing of components, the \App_Code directory also greatly simplifies the creation and accessing of resource files (.resx) used in localization, as well as automatically generating and compiling proxy classes for WSDL files (.wsdl).

With the introduction of this new directory, you might be wondering when to use this directory when compared to the bin directory. If you have an assembly that you want to use in your web site, create a bin subdirectory and then copy the .dll to that subdirectory. If you are creating reusable components that you want to use only from your ASP.NET pages, place them under the App_Code directory. Note that any class you add to the App_Code directory is visible only within that Web site meaning that it will not be visible outside of that Web site. So if you are creating a class that needs to be shared across multiple Web sites, you will be better off creating that class as part of a class library project and share that project among those Web sites.

Conclusion

In this installment, you have understood the new features of SQL Server 2005 utilized to create stored procedures using managed language such as C#. After that, you have also seen the steps involved in creating the data access components using TableAdapter Configuration Wizard that greatly simplified the process of creating a data access component. In Part-2 of this article, we will see how to consume this data access components from a business logic layer. We will also look at the use of master pages in creating the user interface, and caching features in ASP.NET 2.0 and so on.

3-Tier Architecture

As the 3-Tier Architecture is the most commonly used architecture in the world, I will start my blogging on this topic.

I have noticed in the past 6 years of writing software that many developers ignore this software engineering paradigm for many reasons. Some include ...

Return on Investment

Software Lifecycle Turnaround time

Knowledgeable Resources

and the list goes on

I will discuss in more details of these reasons in another blog entry so back to the topic of 3-Tier architecture.

A 3-Tier architecture uses the Divide and Conquer strategy and is broken down into 3 logical layers.

Presentation Layer (PL)

Business Logic Layer (BLL)

Data Access Layer (DAL)

Ideally, each layer specializes in one or a handful of functionalities that service the upper layer. Each of the three layers should be designed so that the layer above it does not need to understand nor know the implementation details of any of the layers below it. This is accomplished by providing well defined interfaces that the above layers use. The advantage of "Programming to the Interface" is that, you can change the implementation details and still have the application work as defined. One caveat is that, if the interfaces change, then it will take more effort and time to update the layer above it. Therefore, when designing an application, its important to define the interfaces properly.

Here is an example:

public interface IDatasource

{

Customer GetCustomer(int id)

}

public TextDataSource : IDataSource

{

public Customer GetCustomer(int id)

{

// reads data from a text file

}

}

public SQLDataSource : IDataSource

{

public Customer GetCustomer(int id)

{

// uses ADO.NET to read data from a SQL Server database

}

}

public class MyProgram

{

public static void Main(string[] args)

{

// the DataSourceFactory will create a data source depending on some settings

// and return the appropriate implementation of the data source

IDataSource ds = DataSourceFactory.GetInstance().GetDataSource();

Console.WriteLine(ds.GetCustomer(15).FirstName)

}

}

As you can see from the example, MyProgram does not need to know which datasource it is querying. All it needs to know is the interface it must use to retrieve a customer record. If we have numerous implementations then by changing only the configurations which are declarative and available outside of the compiled code, we can change the datasource the application should use to retrieve data without changing the application logic itself.

Now lets see how we can use the "Programming to the Interface" paradigm to create a 3-Tier architecture.

The Presentation Layer is responsible for rendering the data retrieved by the BLL (with the help of the DAL). The only logic that is necessary in this layer is how to manipulate the data and display it to the user in an easy to consume manner. Along with rendering the content, it should be responsible for rudimentary data validation such as missing fields, regular expression matching for emails and other content, numeric validation, range validations, etc.

In .NET, there are a slew of UI specific controls that one may use to render the data. Some controls include the DataList, DataGrid, Label, TextBox and of course custom controls for the advanced developers. There are also pre-built validation controls that are bundled with the .NET framework. I'll post some links later with examples of how to use these controls soon.

Depending on the application you are building, the presentation layer may be one or more of the following types of applications: web, windows, windows service, smart client or console. By properly defining the responsibilities of each layer, the only logic that is necessary for developers to write is the presentation layer. Since retrieving a customer record is the same throughout the application (through the BLL) you can abstract out all the details hence simplifying your software.

On the same note, your application may also expose Web services and Remoting services so it is essential to centralize the code. Otherwise the logic for retrieving a customer (which may include security authorization and authentication, data validation, pre-processing and post-processing) will need to be duplicated in many places. Code duplication may seem like a viable solution at the early stages of the software lifecycle, but it is extremely hard to maintain such pieces of software.

The Business Logic Layer is like your kernel for your application. It should be the component that performs all the business logic for your application. This ranges from validations, running business logic, running application processes such as sending emails and retrieving and persisting data using the Data Access Layer.

Although, validations were performed on the presentation layer, it is imperative that you revalidate the data because browsers could have been spoofed or older browsers might have completely ignored some of the validations or the developers working on the presentation layer did not validate the data properly.

Depending on the complexity of your application, businesses logic code may not reside on the same server or in a centralized location so there are advanced means of executing such logic remotely. With .NET this process has been extremely simplified and available to you within a few clicks of your mouse. One of which is .NET Remoting which is an advanced topic that I'll opt out for now. And the other is the buzz word that many have heard; Web Services.

Both writing and using a Web Service is once again simplified by Microsoft. Visual Studio 2003 and 2005 will be able to download the WSDL and generate the proxies for you so you can invoke the functions as you may within business object in your application. If you don't have Visual Studio, then you may use the "wsdl.exe" utility that is bundled with .NET Framework on the command prompt.

If you have business logic on legacy systems that were built with Microsoft Technologies such as COM+ that can not be rewritten for what ever reason, not to worry. You can use COM+ wrappers provided by the .NET Framework to communicate with the legacy systems.

The Data Access Layer is responsible for accessing and manipulating data from data sources such as SQL Server, Microsoft Access, Oracle, MySQL, etc. Many applications on the Internet today rely heavily on data found in many databases and it is important to centralize the access to this data. Some reasons are ...

Security

Code Maintenance and Resue

Scalability

Databases contain confidential information about people and it is not necessary for everyone in your organizational hierarchy to have access to such data. For example, credit card information stored on Amazon.com shouldn't be available for an entry level employee working in a warehouse. By centralizing the access the database, we are able to authenticate and authorize the users requesting data and manipulating the data.

Since our economy is constantly in a flux, it is never safe to assume that once you have created your data model, it will not change for a decade. In fact, that data model may change tomorrow, a week from now or in an year, but it will change and as software architects, it is our responsibility to foresee such events and design systems that will be able to change with time. If the code isn't centralized, a database change as simple as adding a new column may result in days of changes to many systems, regression testing and deployment of many applications. Is this really necessary?

By creating simple reusable components, developers are able to abstract out all of the details of creating connections, handling errors, invoking appropriate stored procedures or executing Transact-SQL or SQL/PL code, retrieving the data, closing the connection away from the Business Logic Layer.

Typical code (using ADO.NET) to retrieve a customer record may look as the following:

SqlConnection connection = null;

try

{

connection = new SqlConnection(mySqlConnection);

SqlCommand cmd = connection.CreateCommand();

cmd.CommandText = "SELECT * FROM Customers WHERE CustomerID = " + cid

return CustomerFactory.GetInstance().GetCustomer(cmd.ExecuteReader());

}

catch (Exception e)

{

Console.WriteLine("Exception :: " + e.Message);

}

finally

{

if (connection != null && connection.State != ConnectionState.Closed)

connection.Close();

}

So what's the big deal about writing a few lines of code? Well, imagine repeating these lines in 5 different places and 2 weeks later, making a change and remembering all the pieces of code to change. Compared to this solution, I suggest the following:

Customer customer = CustomerDAO.GetInstance().GetCustomer(customerID);

Where the ADO.NET code is abstracted away by the GetCustomer(int) function and now making a change to the function will take minutes and the change will affect all pieces of code that depends on retrieving customer records. The above examples use Design Patterns, more specifically the Factory Pattern and the Singleton Pattern and you may further read about them at your leisure.

Scalability is huge to enterprise level applications that require time crucial data. It’s beyond the scope of this article so I will leave it out for the time being.

So there you have it.