presentation layer business layer data access layer

Three-tier architecture is a well-established software application architecture that organizes applications into three logical and physical computing tiers: the presentation tier, or user interface; the application tier, where data is processed; and the data tier, where application data is stored and managed.

The chief benefit of three-tier architecture is that because each tier runs on its own infrastructure, each tier can be developed simultaneously by a separate development team. And can be updated or scaled as needed without impacting the other tiers.

For decades three-tier architecture was the prevailing architecture for client-server applications. Today, most three-tier applications are targets for modernization that uses cloud-native technologies such as containers and microservices and for migration to the cloud.

Connect and integrate your systems to prepare your infrastructure for AI.

Register for the guide on app modernization

Presentation tier

The presentation tier is the user interface and communication layer of the application, where the end user interacts with the application. Its main purpose is to display information to and collect information from the user. This top-level tier can run on a web browser, as desktop application, or a graphical user interface (GUI), for example. Web presentation tiers are developed by using HTML, CSS, and JavaScript. Desktop applications can be written in various languages depending on the platform.

Application tier

The application tier, also known as the logic tier or middle tier, is the heart of the application. In this tier, information that is collected in the presentation tier is processed - sometimes against other information in the data tier - using business logic, a specific set of business rules. The application tier can also add, delete, or modify data in the data tier. 

The application tier is typically developed by using Python, Java, Perl, PHP or Ruby, and communicates with the data tier by using  API  calls. 

The data tier, sometimes called database tier, data access tier or back-end, is where the information that is processed by the application is stored and managed. This can be a  relational database management system  such as  PostgreSQL , MySQL, MariaDB, Oracle, Db2, Informix or Microsoft SQL Server, or in a  NoSQL  Database server such as Cassandra,  CouchDB , or  MongoDB . 

In a three-tier application, all communication goes through the application tier. The presentation tier and the data tier cannot communicate directly with one another.

Tier versus layer

In discussions of three-tier architecture,  layer  is often used interchangeably – and mistakenly – for  tier , as in 'presentation layer' or 'business logic layer'. 

They aren't the same. A 'layer' refers to a functional division of the software, but a 'tier' refers to a functional division of the software that runs on infrastructure separate from the other divisions. The Contacts app on your phone, for example, is a  three - layer  application, but a  single-tier  application, because all three layers run on your phone.

The difference is important because layers can't offer the same benefits as tiers.

Again, the chief benefit of three-tier architecture is its logical and physical separation of functionality. Each tier can run on a separate operating system and server platform - for example, web server, application server, database server - that best fits its functional requirements. And each tier runs on at least one dedicated server hardware or virtual server, so the services of each tier can be customized and optimized without impacting the other tiers. 

Other benefits (compared to single- or two-tier architecture) include:

• Faster development : Because each tier can be developed simultaneously by different teams, an organization can bring the application to market faster. And programmers can use the latest and best languages and tools for each tier.
• Improved scalability : Any tier can be scaled independently of the others as needed.
• Improved reliability : An outage in one tier is less likely to impact the availability or performance of the other tiers.
• Improved security : Because the presentation tier and data tier can't communicate directly, a well-designed application tier can function as an internal firewall, preventing SQL injections and other malicious exploits.

In web development, the tiers have different names but perform similar functions:

• The web server  is the presentation tier and provides the user interface. This is usually a web page or website, such as an ecommerce site where the user adds products to the shopping cart, adds payment details or creates an account. The content can be static or dynamic, and is developed using HTML, CSS, and JavaScript.
• The application server  corresponds to the middle tier, housing the business logic that is used to process user inputs. To continue the ecommerce example, this is the tier that queries the inventory database to return product availability, or adds details to a customer's profile. This layer often developed using Python, Ruby, or PHP and runs a framework such as Django, Rails, Symphony, or ASP.NET.
• The database server  is the data or backend tier of a web application. It runs on database management software, such as MySQL, Oracle, DB2, or PostgreSQL.

While three-tier architecture is easily the most widely adopted multitier application architecture, there are others that you might encounter in your work or your research.

Two-tier architecture 

Two-tier architecture is the original client-server architecture, consisting of a presentation tier and a data tier; the business logic lives in the presentation tier, the data tier or both. In two-tier architecture the presentation tier - and therefore the end user - has direct access to the data tier, and the business logic is often limited. A simple contact management application, where users can enter and retrieve contact data, is an example of a two-tier application. 

N-tier architecture

N-tier architecture - also called or multitier architecture - refers to  any  application architecture with more than one tier. But applications with more than three layers are rare because extra layers offer few benefits and can make the application slower, harder to manage and more expensive to run. As a result, n-tier architecture and multitier architecture are usually synonyms for three-tier architecture.

Move to cloud faster with IBM Cloud Pak solutions running on Red Hat OpenShift software—integrated, open, containerized solutions certified by IBM®.

Seamlessly modernize your VMware workloads and applications with IBM Cloud.

Modernize, build new apps, reduce costs, and maximize ROI.

IBM Consulting® application modernization services, which are powered by IBM Consulting Cloud Accelerator, offers skills, methods, tools, and initiatives that help determine the right strategy based on your portfolio. To modernize and containerize legacy system applications and accelerate the time-to-value of hybrid cloud environments. 

Discover what application modernization is, the common benefits and challenges, and how to get started.

Learn about how relational databases work and how they compare to other data storage options.

Explore cloud native applications and how they drive innovation and speed within your enterprise.

Modernize your legacy three-tier applications on your journey to cloud. Whether you need assistance with strategy, processes, or capabilities—or want full-service attention—IBM can help. Start using containerized middleware that can run in any cloud—all bundled in IBM Cloud Paks.

Application Architecture Overview

Systems Analysis and Design Tutorial

An application system consists of three logical layers.

The presentation layer is what a system user sees or interacts with.  It can consist of visual objects such as screens, web pages or reports or non-visual objects such as an interactive voice response interface.

When most people think of application systems, they think mainly of the presentation layer.  Unfortunately, this layer represents a small portion of the effort involved in building application systems.

The business logic layer, on the other hand, implements and enforces the business rules via programming logic (computer instructions).

This business logic layer might on the surface appear to be very straight forward, however, that is rarely so.

The data access layer consists of the definitions of database tables and columns and the computer logic that is needed to navigate the database.

The layer in the application architecture enforces rules regarding the storage and access of information. For example: All date fields must be valid dates.  All numeric fields must never contain alphanumeric characters.

This diagram on this page is a "logical" representation of an application system.  When a system is implemented, application system components can be physically deployed on different computer systems.

For example, the presentation of the web page you are looking at is being handled by your computer or mobile device.  The logic required to consolidate and communicate the visual objects that it needs is occurring on a web server located in Virginia USA.

11 July 2006

518581 views

.NET Application Architecture: the Data Access Layer

Find out how to design a robust data access layer for your .NET applications.

Designing and building a robust data access layer

Building an understanding of architectural concepts is an essential aspect of managing your career. Technical interviews normally contain a battery of questions to gauge your architectural knowledge during the hiring process, and your architectural ability only becomes more important as you ascend through the ranks. So it’s always a good idea to make sure you have a good grasp on the fundamentals. In this article you will explore a key component of application architecture known as the Data Access Layer (DAL), which helps separate data-access logic from your business objects. The article discusses the concepts behind the DAL, and the associated PDF file takes a look at a full-blown DAL implementation. This is the first in a series of articles discussing some of the cool things you can do with a DAL, so the code and concepts in this article form the base for future discussions.

Layered design and the data access layer

Layered application designs are extremely popular because they increase application performance, scalability, flexibility, code reuse, and have a myriad of other benefits that I could rattle off if I had all of the architectural buzzwords memorized. In the classic three tier design, applications break down into three major areas of functionality:

• The data layer manages the physical storage and retrieval of data
• The business layer maintains business rules and logic
• The presentation layer houses the user interface and related presentation code.

Inside each of these tiers there may also exist a series of sub-layers that provide an even more granular break up the functional areas of the application. Figure 1 outlines a basic three tired architecture in ASP.NET along with some of the sub-tiers that you may encounter:

Figure 1 – Three tiered ASP.NET application with sub-tiers

The presentation tier

In the presentation layer, the code-behind mechanism for ASP.NET pages and user controls is a prominent example of a layered design. The markup file defines the look and layout of the web form and the code behind file contains the presentation logic. It’s a clean separation because both the markup and the code-behind layers house specific sets of functionality that benefit from being apart. Designers don’t have to worry about messing up code to make user interface changes, and developers don’t have to worry about sifting through the user-interface to update code.

The data tier

You also see sub-layers in the data tier with database systems. Tables define the physical storage of data in a database, but stored procedures and views allow you to manipulate data as it goes into and out of those tables. Say, for example, you need to denormalize a table and therefore have to change its physical storage structure. If you access tables directly in the business layer, then you are forced to update your business tier to account for the changes to the table. If you use a layer of stored procedures and views to access the data, then you can expose the same logical structure by updating a view or stored procedure to account for the physical change without having to touch any code in your business layer. When used appropriately, a layered design can lessen the overall impact of changes to the application.

The business tier

And of course, this brings us to the topic of business objects and the Data Access Layer (also known as the DAL), two sub-layers within the business tier. A business object is a component that encapsulates the data and business processing logic for a particular business entity. It is not, however, a persistent storage mechanism. Since business objects cannot store data indefinitely, the business tier relies on the data tier for long term data storage and retrieval. Thus, your business tier contains logic for retrieving persistent data from the data-tier and placing it into business objects and, conversely, logic that persists data from business objects into the data tier. This is called data access logic.

Some developers choose to put the data access logic for their business objects directly in the business objects themselves, tightly binding the two together. This may seem like a logical choice at first because from the business object perspective it seems to keep everything nicely packaged. You will begin noticing problems, however, if you ever need to support multiple databases, change databases, or even overhaul your current database significantly. Let’s say, for example, that your boss comes to you and says that you will be moving your application’s database from Oracle to SQL Server and that you have four months to do it. In the meantime, however, you have to continue supporting whatever business logic changes come up. Your only real option is to make a complete copy of the business object code so you can update the data access logic in it to support SQL Server. As business object changes arise, you have to make those changes to both the SQL Server code base and the Oracle code base. Not fun. Figure 2 depicts this scenario:

Figure 2 – Business objects with embedded data access logic

A more flexible option involves removing the data access logic from the business objects and placing it all in a separate assembly known as the DAL. This gives you a clean separation between your business objects and the data access logic used to populate those business objects. Presented with the same challenge of making the switch from Oracle to SQL Server, you can just make a copy of the Oracle DAL and then convert it to work with SQL Server. As new business requirements come in, you no longer need to make changes in multiple locations because you only maintain a single set of business objects. And when you are done writing the SQL Server DAL, your application has two functional data access layers. In other words, your application has the means to support two databases. Figure 3 depicts separating data access logic out into a separate DAL:

Figure 3 – Business objects with separate data access layer

Design principals in the data access layer

The objective of the DAL is to provide data to your business objects without using database specific code. You accomplish this by exposing a series of data access methods from the DAL that operate on data in the data-tier using database specific code but do not expose any database specific method parameters or return types to the business tier. Any time a business object needs to access the data tier, you use the method calls in the DAL instead of calling directly down to the data tier. This pushes database-specific code into the DAL and makes your business object database independent.

Now wait, you say, all you’ve accomplished is making the business objects dependent on the DAL. And since the DAL uses database-specific code, what’s the benefit? The benefit is that the DAL resides in its own assembly and exposes database-independent method signatures. You can easily create another DAL with the same assembly name and an identical set of method signatures that supports a different database. Since the method signatures are the same, your code can interface with either one, effectively giving you two interchangeable assemblies. And since the assembly is a physical file referenced by your application and the assembly names are the same, interchanging the two is simply a matter of placing one or the other into your application’s bin folder.

Note: You can also implement a DAL without placing it in a separate assembly if you build it against a DAL interface definition, but we will leave that to another article.

Exchanging Data with the DAL

Now the question is: how do you exchange data between your business objects, the DAL, and vice versa? All interaction between your business objects and the DAL occurs by calling data access methods in the DAL from code in your business objects. As mentioned previously, the method parameters and return values in the DAL are all database independent to ensure your business objects are not bound to a particular database. This means that you need to exchange data between the two using non-database-specific .NET types and classes. At first glance it may seem like a good idea to pass your business objects directly into the DAL so they can be populated, but it’s just not possible. The business object assembly references the DAL assembly, so the DAL assembly cannot reference the business object assembly or else you would get a circular reference error. As such, you cannot pass business objects down into the DAL because the DAL has no concept of your business objects. Figure 4 diagrams the situation:

Figure 4 – Business objects assembly references the DAL, so the DAL has no concept of business objects

The custom class option

One option is to pass information in custom classes, as long as those custom classes are defined in an assembly that both the business object and DAL assemblies can reference. From an academic standpoint, this approach is probably the truest form of a data abstraction for a DAL because you can make the shared classes completely data-source independent and not just database independent. Figure 5 depicts how the business object assembly and the DAL assembly can both reference a shared assembly:

Figure 5 – The business object assembly and the DAL assembly both reference a shared assembly, so they can exchange information using classes and data structures from the shared assembly.

In practice, I find that building out custom classes solely to exchange data doesn’t give you much return for your effort, especially when there are other acceptable options already built into .NET.

The XML approach

You could opt to use XML since it’s the poster child of flexibility and data-source independence and can easily represent any data imaginable. Of course, it also means that you will be doing a lot of XML parsing work to accommodate the data exchange, and I’m not a fan of extra work.

The database interface approach

You could also use the database interfaces from the System.Data namespace to exchange data between business objects and the DAL. Database specific objects such as SqlDataReader , SqlCommand , and SqlParameter are tied to SQL Server, and exposing them from the DAL would defeat the purpose. However, by exposing an IDataReader , IDBCommand , or IDataParameter object you do not tie yourself to particular database so they are an acceptable option, though not my first choice.

From an academic standpoint, the database interface objects do tie you to using a “database management system” even though they do not tie you to a specific database. Pure academics will tell you that the DAL should be “data-source independent” and not just “database independent” so be prepared for that fight if you have a Harvard or Oxford grad on your development team who majored in theoretical application design. Nobody else on the planet cares because the chances of your application moving away from a database system are fairly slim.

My preferred approach: DataSets

Another option for passing information, and the one that I gravitate towards because of its flexibility, is the DataSet. Microsoft created the DataSet class specifically for storing relational information in a non-database specific data structure, so the DataSet comes highly recommended for returning query information containing multiple records and or tables of data. Your work load shouldn’t suffer too significantly from using the DataSet because DataAdapters, which fill DataSets with information, already exists for most database systems. Furthermore, getting data out of the DataSet is fairly easy because it contains methods for extracting your data as tables, rows, and columns.

Also note that a DataSet is technically data-source independent, not just database independent. You can write custom code to load XML files, CSV files, or any other data source into a DataSet object. Additionally, you can even manipulate and move information around inside the DataSet, something that is not possible with the database interfaces from the System.Data namespace.

Exchanging non-relational data

Of course, you also deal with non-relational information when you pass data back and forth between your business objects and the DAL. For example, if you want to save a single business object to the data-tier, you have to pass that business object’s properties into the DAL. To do so, simply pass business object properties into the DAL via native .NET type method parameters. So a string property on your business object is passed into the DAL as a string parameter, and an int property on your business object is passed into the DAL as an int parameter. If the DAL updates the business object property, then you should mark the parameter with the ref modifier so the new value can be passed back to the business object. You can also use return values to return information as the result of a function when the need arises. Listing 1 contains examples of method signatures that you may need in the DAL if you have a Person business object in your application:

Listing 1 – Data access layer method signature examples

Data service classes

Normally you have one data access method in your DAL for each scenario in which you need to exchange data between a business object and the database. If, for example, you have a Person class then you may need data access methods like Person_GetAll , Person_GetPersonByID , Person_GetByLoginCredentials , Person_Update , Person_Delete , and so on, so you can do everything you need to do with a Person object via the DAL. Since the total number of data access methods in your DAL can get fairly large fairly quickly, it helps to separate those methods out into smaller more manageable Data Service Classes (or partial classes in .NET 2.0) inside your DAL. Aside from being more manageable from a shear number standpoint, breaking down the DAL into multiple data service classes helps reduce check-out bottle necks with your source control if you have multiple developers needing to work on the DAL at the same time. Figure 6 depicts a DAL broken down into three individual data service classes:

Figure 6 – Breaking down the DAL into multiple data service classes

Notice that all of the data service classes depicted in Figure 3 derive from a single base class named DataServiceBase . The DataServiceBase class provides common data access functionality like opening a database connection, managing a transaction, setting up stored procedure parameters, executing commands, and so forth. In other words, the DataServiceBase class contains the general database code and provides you with a set of helper methods for use in the individual data service classes. The derived data service classes use the helper methods in the DataServiceBase for specific purposes, like executing a specific command or running a specific query.

Putting theory into practice: the demo application

At this point you should have a descent understanding of what the data access layer is and how it fits into an application from an architectural point of view. Theory is great, but at some point you have to quit talking and start coding. Of course, going from theory to practice is no trivial step, so I wanted to make sure you had a solid example to use as a foundation both in terms of code and understanding.

At the top of this article is a link to a zip file containing two items: a demo application containing a DAL implementation and a Building a Data Access Layer PDF that explains the code in detail. The application is fairly simple, a two page web app that allows you to view / delete a list of people on one page and to add / edit those people on another. However, it does implement all of the design principles that we’ve covered here. Enjoy!

Standardize team-based development - Prevent rework and conflicts, build consistency and quality into your code, and gain time for development that adds value, with standardized best practices for database development.

Find out more

Subscribe for more articles

Fortnightly newsletters help sharpen your skills and keep you ahead, with articles, ebooks and opinion to keep you informed.

Rate this article

Damon Armstrong

Damon Armstrong is a consultant with SystemwarePS in Dallas, Texas. He is also a blogger and author of Pro ASP.NET 2.0 Website Programming and SharePoint 2013 Essentials for Developers . He specializes in the Microsoft stack with a focus on web technologies like MVC, ASP.NET, JavaScript, and SharePoint. When not staying up all night coding, he can be found watching a bunch of kids, studying Biblical topics, playing golf, or recovering from staying up all night coding.

Follow Damon Armstrong via

View all articles by Damon Armstrong

Load comments

Related articles

Inline PDF Viewer in an Angular App? Now you can

The Zen of Code Reviews: Review As If You Own the Code

Using c# to create powershell cmdlets: the basics.

• What are some good and bad practices writing code comments
• How to fix “vc_runtimeMinimum_x64.msi is missing” error
• My old sticky note or how to be a better software developer

CAP theorem explained

• HTTP for beginner programmers
• Three ways to become a better developer

Strategy pattern. A design pattern we use every day

Design patterns explained. what do we benefit from design patterns, layered architecture. what is layered architecture and when to use it.

Layered architecture is the first architectural pattern that we are going to explore. If you haven’t read my article about software architecture , I suggest you do it before continuing with this one.

What is a layered architecture?

Simply put, the layered architecture pattern ‘s main idea is to group and isolate system concerns while defining strict communication direction between them.

Each group of related modules/classes we call layer. Different layers are encapsulated and depend on each other through abstraction and well-defined interfaces.

Layers in this architecture pattern are stacked. The request direction is from the upper layers to the lower layers. In the best-case scenario, a layer can request a layer that is only directly below it.

In sporadic cases, an upper layer is allowed to request a layer two-level or more levels below, but in general, it is considered bad practice.

Layered architecture is widespread. We can find it the simple client->server pattern or OSI network model . Also, many enterprise applications implement that pattern.

*In the following examples, we do not discuss cross-cutting concerns and their implementation.

What is the difference between layered architecture and N-tier architecture?

There is none significant one. By tier, we consider a physical layer (like SQL server, for example). N describes the number of layers/tiers you have.

In general, both names refer to the principles we discuss in this article.

Enterprise application example

Most of the layered architecture implementations consist of four layers (ok, three layers, and a tier).

Presentation layer

Business layer.

• Data Access layer
• Data Storage tier

Their names are explanatory enough, but let’s discuss them in detail.

In the presentation layer, you can find user interface components.

In a standard ASP.NET Core application, these are the Views. The presentation layer, of course, is not limited to any technology. You can have anything that a user can interact with. CLI, SPA, etc.

The presentation layer should not contain business logic and should not access database data directly.

Its only task is to visualize data and dispatch the user’s input. That’s why the presentation layer requests data and sends commands from/to the business layer.

The business layer is where your core business logic lives. Here you can find modules and services (classes) specific to the business domain operations. Operations like calculating discounts, rearranging schedules, etc.

If data is needed, the business layer requests it from the data access layer.

When returning a result to the presentation layer, the business layer should not apply any data formatting. As we understood from the above paragraphs, this responsibility is entirely for the representation layer.

Data access layer

The data access layer or persistence layer’s main task is to communicate (query and persist data) from/to the data storage (SQL DB, NoSQL, flat files, etc.).

The most common data access layer translates requests to SQL queries and sends them to the SQL Server via data access objects (DAOs). In most cases, these DAOs implement a Repository pattern.

The data access layer also implements transaction capability (often through a Unit of work pattern).  You can also find a Unit of work implemented in the business layer in some cases, but this is wrong.

Data Storage

I intentionally don’t say database because your storage could be anything—even CSV files. Of course, no sane man will do that.

You should not put any logic in this tier. Ideally, you should use it only for storage.

Structure of layered architecture

In a layered application, the structure is quite simple.

The example I am giving you is with MS stack, but technology does not change the principles we should follow.

Also, you will see that we have three logical layers and three tiers.

Consider the example set up at on-premises infrastructure, not in the cloud.

On a separate machine (tier 1) with IIS, we have a standard ASP.NET Core application. This is the presentation layer.

One level below (tier 2), we find an IIS on a separate server. On it, there is deployed a Web API application.

In the Web API application, we can see a very thin layer of REST APIs (implemented through Web API controllers), the business layer, and the data access layer.

The primary responsibility of the APIs controllers is to route a request to the business layer, so we don’t consider them important enough to classify them as a separate layer,

Controllers, business layer, and data access layers are isolated. They live in different projects and reference each other only through interfaces . We use dependency injection for instantiating services.

At the bottom (tier 3), we have a machine with an operational database managed by MS SQL Server.

In the past, most of the applications didn’t split their logical layers over different tiers (except the data storage tier). We do this to increase isolation, flexibility (to some extent), and ease of deployability.

Scale-out benefits are limited due to the monolithic nature of the architecture.

Flow of layered architecture

What happens when, for example, a user searches for a product (if we consider our application is an eCommerce website)?

The ASP.NET Core application UI requests the Web API specific REST API.

The API controller instantiates a service object from the business layer and requests the search through a specific method.

On its behalf, the business layer instantiates a data access object (or several) from the data access layer and calls a specific method/s.

The data access object translates the request to a SQL query and sends it to the SQL Server. 

SQL Server returns the result. The data access object returns the result in the form of data transfer object/s to the business service. 

Suppose any transformation or logic is needed (like calculation discount, as we said earlier). In that case, the business service transforms the data based on the business logic and returns it to the Web API controller.

The Web API controller returns an HTTP response to the ASP.NET Core application.

ASP.NET Core application receives the data formats it and shows it to the user.

When to use layered architecture?

Layered architecture is a great pattern, but it comes with its advantages and limitations like any other tool.

If you need a highly scalable solution, this architecture is not for you. The monolithic nature of the pattern prevents you from scaling out different components or services.

Another drawback of the pattern is its lack of agility. Even with good isolation of horizontal layers, it is a matter of time for the internal components to become “chatty” and form coupled dependencies.

And of course, this affects the deployment process. In most cases, even with different tier separation, it is all or nothing.

Or you don’t need a layered architecture at all. If most of your business layer’s services only redirect requests from the UI to the data access layer and return data, this implementation would be overkill.

Layered architecture is a very developer-friendly pattern. Even if your dev team does not have enough experience , following this pattern won’t be hard.

The pattern is also widely used and recognized among developers, so a new joining member can quickly become productive.

Separation of concerns helps for concurrent work over functionalities. It is common to see front-end developers working on the UI while back-end developers develop business services logic and data access functionalities.

Another great plus is testing. Having layers separated is a lot easier to mock them and test in isolation.

If your application does not need to scale out, and the business logic is not very complex, the layered architecture is an excellent choice for rapid development and delivery.

A long time ago, I read that layered architecture is like lasagna (with its layers), and it is better to have lasagna for lunch instead of spaghetti :).  I can not agree more.

But In my next article, we are going to add the onion. Yes, the following architecture pattern is Clean architecture , also called Port and adapters or Onion architecture.

Good books to read: Software architecture in practice 3rd edition

Overengineering. Predicting the future does not work

Related posts.

Write A Comment Cancel Reply

Save my name, email, and website in this browser for the next time I comment.

Type above and press Enter to search. Press Esc to cancel.

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Creating a Data Access Layer (C#)

• 12 contributors

by Scott Mitchell

Download PDF

In this tutorial we'll start from the very beginning and create the Data Access Layer (DAL), using typed DataSets, to access the information in a database.

Introduction

As web developers, our lives revolve around working with data. We create databases to store the data, code to retrieve and modify it, and web pages to collect and summarize it. This is the first tutorial in a lengthy series that will explore techniques for implementing these common patterns in ASP.NET 2.0. We'll start with creating a software architecture composed of a Data Access Layer (DAL) using Typed DataSets, a Business Logic Layer (BLL) that enforces custom business rules, and a presentation layer composed of ASP.NET pages that share a common page layout. Once this backend groundwork has been laid, we'll move into reporting, showing how to display, summarize, collect, and validate data from a web application. These tutorials are geared to be concise and provide step-by-step instructions with plenty of screen shots to walk you through the process visually. Each tutorial is available in C# and Visual Basic versions and includes a download of the complete code used. (This first tutorial is quite lengthy, but the rest are presented in much more digestible chunks.)

For these tutorials we'll be using a Microsoft SQL Server 2005 Express Edition version of the Northwind database placed in the App_Data directory. In addition to the database file, the App_Data folder also contains the SQL scripts for creating the database, in case you want to use a different database version. If you use a different SQL Server version of the Northwind database, you will need to update the NORTHWNDConnectionString setting in the application's Web.config file. The web application was built using Visual Studio 2005 Professional Edition as a file system-based Web site project. However, all of the tutorials will work equally well with the free version of Visual Studio 2005, Visual Web Developer .

In this tutorial we'll start from the very beginning and create the Data Access Layer (DAL), followed by creating the Business Logic Layer (BLL) in the second tutorial, and working on page layout and navigation in the third. The tutorials after the third one will build upon the foundation laid in the first three. We've got a lot to cover in this first tutorial, so fire up Visual Studio and let's get started!

Step 1: Creating a Web Project and Connecting to the Database

Before we can create our Data Access Layer (DAL), we first need to create a web site and setup our database. Start by creating a new file system-based ASP.NET web site. To accomplish this, go to the File menu and choose New Web Site, displaying the New Web Site dialog box. Choose the ASP.NET Web Site template, set the Location drop-down list to File System, choose a folder to place the web site, and set the language to C#.

Figure 1 : Create a New File System-Based Web Site ( Click to view full-size image )

This will create a new web site with a Default.aspx ASP.NET page and an App_Data folder.

With the web site created, the next step is to add a reference to the database in Visual Studio's Server Explorer. By adding a database to the Server Explorer you can add tables, stored procedures, views, and so on all from within Visual Studio. You can also view table data or create your own queries either by hand or graphically via the Query Builder. Furthermore, when we build the Typed DataSets for the DAL we'll need to point Visual Studio to the database from which the Typed DataSets should be constructed. While we can provide this connection information at that point in time, Visual Studio automatically populates a drop-down list of the databases already registered in the Server Explorer.

The steps for adding the Northwind database to the Server Explorer depend on whether you want to use the SQL Server 2005 Express Edition database in the App_Data folder or if you have a Microsoft SQL Server 2000 or 2005 database server setup that you want to use instead.

Using a Database in the App_Data Folder

If you do not have a SQL Server 2000 or 2005 database server to connect to, or you simply want to avoid having to add the database to a database server, you can use the SQL Server 2005 Express Edition version of the Northwind database that is located in the downloaded website's App_Data folder ( NORTHWND.MDF ).

A database placed in the App_Data folder is automatically added to the Server Explorer. Assuming you have SQL Server 2005 Express Edition installed on your machine you should see a node named NORTHWND.MDF in the Server Explorer, which you can expand and explore its tables, views, stored procedure, and so on (see Figure 2).

The App_Data folder can also hold Microsoft Access .mdb files, which, like their SQL Server counterparts, are automatically added to the Server Explorer. If you don't want to use any of the SQL Server options, you can always install Northwind Traders database and apps and drop into the App_Data directory. Keep in mind, however, that Access databases aren't as feature-rich as SQL Server, and aren't designed to be used in web site scenarios. Furthermore, a couple of the 35+ tutorials will utilize certain database-level features that aren't supported by Access.

Connecting to the Database in a Microsoft SQL Server 2000 or 2005 Database Server

Alternatively, you may connect to a Northwind database installed on a database server. If the database server does not already have the Northwind database installed, you first must add it to database server by running the installation script included in this tutorial's download.

Once you have the database installed, go to the Server Explorer in Visual Studio, right-click on the Data Connections node, and choose Add Connection. If you don't see the Server Explorer go to the View / Server Explorer, or hit Ctrl+Alt+S. This will bring up the Add Connection dialog box, where you can specify the server to connect to, the authentication information, and the database name. Once you have successfully configured the database connection information and clicked the OK button, the database will be added as a node underneath the Data Connections node. You can expand the database node to explore its tables, views, stored procedures, and so on.

Figure 2 : Add a Connection to Your Database Server's Northwind Database

Step 2: Creating the Data Access Layer

When working with data one option is to embed the data-specific logic directly into the presentation layer (in a web application, the ASP.NET pages make up the presentation layer). This may take the form of writing ADO.NET code in the ASP.NET page's code portion or using the SqlDataSource control from the markup portion. In either case, this approach tightly couples the data access logic with the presentation layer. The recommended approach, however, is to separate the data access logic from the presentation layer. This separate layer is referred to as the Data Access Layer, DAL for short, and is typically implemented as a separate Class Library project. The benefits of this layered architecture are well documented (see the "Further Readings" section at the end of this tutorial for information on these advantages) and is the approach we will take in this series.

All code that is specific to the underlying data source such as creating a connection to the database, issuing SELECT , INSERT , UPDATE , and DELETE commands, and so on should be located in the DAL. The presentation layer should not contain any references to such data access code, but should instead make calls into the DAL for any and all data requests. Data Access Layers typically contain methods for accessing the underlying database data. The Northwind database, for example, has Products and Categories tables that record the products for sale and the categories to which they belong. In our DAL we will have methods like:

• GetCategories(), which will return information about all of the categories
• GetProducts() , which will return information about all of the products
• GetProductsByCategoryID( categoryID ) , which will return all products that belong to a specified category
• GetProductByProductID( productID ) , which will return information about a particular product

These methods, when invoked, will connect to the database, issue the appropriate query, and return the results. How we return these results is important. These methods could simply return a DataSet or DataReader populated by the database query, but ideally these results should be returned using strongly-typed objects . A strongly-typed object is one whose schema is rigidly defined at compile time, whereas the opposite, a loosely-typed object, is one whose schema is not known until runtime.

For example, the DataReader and the DataSet (by default) are loosely-typed objects since their schema is defined by the columns returned by the database query used to populate them. To access a particular column from a loosely-typed DataTable we need to use syntax like: DataTable .Rows[ index ][" columnName "] . The DataTable's loose typing in this example is exhibited by the fact that we need to access the column name using a string or ordinal index. A strongly-typed DataTable, on the other hand, will have each of its columns implemented as properties, resulting in code that looks like: DataTable .Rows[ index ]. columnName .

To return strongly-typed objects, developers can either create their own custom business objects or use Typed DataSets. A business object is implemented by the developer as a class whose properties typically reflect the columns of the underlying database table the business object represents. A Typed DataSet is a class generated for you by Visual Studio based on a database schema and whose members are strongly-typed according to this schema. The Typed DataSet itself consists of classes that extend the ADO.NET DataSet, DataTable, and DataRow classes. In addition to strongly-typed DataTables, Typed DataSets now also include TableAdapters, which are classes with methods for populating the DataSet's DataTables and propagating modifications within the DataTables back to the database.

For more information on the advantages and disadvantages of using Typed DataSets versus custom business objects, refer to Designing Data Tier Components and Passing Data Through Tiers .

We'll use strongly-typed DataSets for these tutorials' architecture. Figure 3 illustrates the workflow between the different layers of an application that uses Typed DataSets.

Figure 3 : All Data Access Code is Relegated to the DAL ( Click to view full-size image )

Creating a Typed DataSet and Table Adapter

To begin creating our DAL, we start by adding a Typed DataSet to our project. To accomplish this, right-click on the project node in the Solution Explorer and choose Add a New Item. Select the DataSet option from the list of templates and name it Northwind.xsd .

Figure 4 : Choose to Add a New DataSet to Your Project ( Click to view full-size image )

After clicking Add, when prompted to add the DataSet to the App_Code folder, choose Yes. The Designer for the Typed DataSet will then be displayed, and the TableAdapter Configuration Wizard will start, allowing you to add your first TableAdapter to the Typed DataSet.

A Typed DataSet serves as a strongly-typed collection of data; it is composed of strongly-typed DataTable instances, each of which is in turn composed of strongly-typed DataRow instances. We will create a strongly-typed DataTable for each of the underlying database tables that we need to work with in this tutorials series. Let's start with creating a DataTable for the Products table.

Keep in mind that strongly-typed DataTables do not include any information on how to access data from their underlying database table. In order to retrieve the data to populate the DataTable, we use a TableAdapter class, which functions as our Data Access Layer. For our Products DataTable, the TableAdapter will contain the methods GetProducts() , GetProductByCategoryID( categoryID ) , and so on that we'll invoke from the presentation layer. The DataTable's role is to serve as the strongly-typed objects used to pass data between the layers.

The TableAdapter Configuration Wizard begins by prompting you to select which database to work with. The drop-down list shows those databases in the Server Explorer. If you did not add the Northwind database to the Server Explorer, you can click the New Connection button at this time to do so.

Figure 5 : Choose the Northwind Database from the Drop-Down List ( Click to view full-size image )

After selecting the database and clicking Next, you'll be asked if you want to save the connection string in the Web.config file. By saving the connection string you'll avoid having it hard coded in the TableAdapter classes, which simplifies things if the connection string information changes in the future. If you opt to save the connection string in the configuration file it's placed in the <connectionStrings> section, which can be optionally encrypted for improved security or modified later through the new ASP.NET 2.0 Property Page within the IIS GUI Admin Tool, which is more ideal for administrators.

Figure 6 : Save the Connection String to Web.config ( Click to view full-size image )

Next, we need to define the schema for the first strongly-typed DataTable and provide the first method for our TableAdapter to use when populating the strongly-typed DataSet. These two steps are accomplished simultaneously by creating a query that returns the columns from the table that we want reflected in our DataTable. At the end of the wizard we'll give a method name to this query. Once that's been accomplished, this method can be invoked from our presentation layer. The method will execute the defined query and populate a strongly-typed DataTable.

To get started defining the SQL query we must first indicate how we want the TableAdapter to issue the query. We can use an ad-hoc SQL statement, create a new stored procedure, or use an existing stored procedure. For these tutorials we'll use ad-hoc SQL statements.

Figure 7 : Query the Data Using an Ad-Hoc SQL Statement ( Click to view full-size image )

At this point we can type in the SQL query by hand. When creating the first method in the TableAdapter you typically want to have the query return those columns that need to be expressed in the corresponding DataTable. We can accomplish this by creating a query that returns all columns and all rows from the Products table:

Figure 8 : Enter the SQL Query Into the Textbox ( Click to view full-size image )

Alternatively, use the Query Builder and graphically construct the query, as shown in Figure 9.

Figure 9 : Create the Query Graphically, through the Query Editor ( Click to view full-size image )

After creating the query, but before moving onto the next screen, click the Advanced Options button. In Web Site Projects, "Generate Insert, Update, and Delete statements" is the only advanced option selected by default; if you run this wizard from a Class Library or a Windows Project the "Use optimistic concurrency" option will also be selected. Leave the "Use optimistic concurrency" option unchecked for now. We'll examine optimistic concurrency in future tutorials.

Figure 10 : Select Only the Generate Insert, Update, and Delete statements Option ( Click to view full-size image )

After verifying the advanced options, click Next to proceed to the final screen. Here we are asked to select which methods to add to the TableAdapter. There are two patterns for populating data:

• Fill a DataTable with this approach a method is created that takes in a DataTable as a parameter and populates it based on the results of the query. The ADO.NET DataAdapter class, for example, implements this pattern with its Fill() method.
• Return a DataTable with this approach the method creates and fills the DataTable for you and returns it as the methods return value.

You can have the TableAdapter implement one or both of these patterns. You can also rename the methods provided here. Let's leave both checkboxes checked, even though we'll only be using the latter pattern throughout these tutorials. Also, let's rename the rather generic GetData method to GetProducts .

If checked, the final checkbox, "GenerateDBDirectMethods," creates Insert() , Update() , and Delete() methods for the TableAdapter. If you leave this option unchecked, all updates will need to be done through the TableAdapter's sole Update() method, which takes in the Typed DataSet, a DataTable, a single DataRow, or an array of DataRows. (If you've unchecked the "Generate Insert, Update, and Delete statements" option from the advanced properties in Figure 9 this checkbox's setting will have no effect.) Let's leave this checkbox selected.

Figure 11 : Change the Method Name from GetData to GetProducts ( Click to view full-size image )

Complete the wizard by clicking Finish. After the wizard closes we are returned to the DataSet Designer which shows the DataTable we just created. You can see the list of columns in the Products DataTable ( ProductID , ProductName , and so on), as well as the methods of the ProductsTableAdapter ( Fill() and GetProducts() ).

Figure 12 : The Products DataTable and ProductsTableAdapter have been Added to the Typed DataSet ( Click to view full-size image )

At this point we have a Typed DataSet with a single DataTable ( Northwind.Products ) and a strongly-typed DataAdapter class ( NorthwindTableAdapters.ProductsTableAdapter ) with a GetProducts() method. These objects can be used to access a list of all products from code like:

This code did not require us to write one bit of data access-specific code. We did not have to instantiate any ADO.NET classes, we didn't have to refer to any connection strings, SQL queries, or stored procedures. Instead, the TableAdapter provides the low-level data access code for us.

Each object used in this example is also strongly-typed, allowing Visual Studio to provide IntelliSense and compile-time type checking. And best of all the DataTables returned by the TableAdapter can be bound to ASP.NET data Web controls, such as the GridView, DetailsView, DropDownList, CheckBoxList, and several others. The following example illustrates binding the DataTable returned by the GetProducts() method to a GridView in just a scant three lines of code within the Page_Load event handler.

AllProducts.aspx

AllProducts.aspx.cs

Figure 13 : The List of Products is Displayed in a GridView ( Click to view full-size image )

While this example required that we write three lines of code in our ASP.NET page's Page_Load event handler, in future tutorials we'll examine how to use the ObjectDataSource to declaratively retrieve the data from the DAL. With the ObjectDataSource we'll not have to write any code and will get paging and sorting support as well!

Step 3: Adding Parameterized Methods to the Data Access Layer

At this point our ProductsTableAdapter class has but one method, GetProducts() , which returns all of the products in the database. While being able to work with all products is definitely useful, there are times when we'll want to retrieve information about a specific product, or all products that belong to a particular category. To add such functionality to our Data Access Layer we can add parameterized methods to the TableAdapter.

Let's add the GetProductsByCategoryID( categoryID ) method. To add a new method to the DAL, return to the DataSet Designer, right-click in the ProductsTableAdapter section, and choose Add Query.

Figure 14 : Right-Click on the TableAdapter and Choose Add Query

We are first prompted about whether we want to access the database using an ad-hoc SQL statement or a new or existing stored procedure. Let's choose to use an ad-hoc SQL statement again. Next, we are asked what type of SQL query we'd like to use. Since we want to return all products that belong to a specified category, we want to write a SELECT statement which returns rows.

Figure 15 : Choose to Create a SELECT Statement Which Returns Rows ( Click to view full-size image )

The next step is to define the SQL query used to access the data. Since we want to return only those products that belong to a particular category, I use the same SELECT statement from GetProducts() , but add the following WHERE clause: WHERE CategoryID = @CategoryID . The @CategoryID parameter indicates to the TableAdapter wizard that the method we're creating will require an input parameter of the corresponding type (namely, a nullable integer).

Figure 16 : Enter a Query to Only Return Products in a Specified Category ( Click to view full-size image )

In the final step we can choose which data access patterns to use, as well as customize the names of the methods generated. For the Fill pattern, let's change the name to FillByCategoryID and for the return a DataTable return pattern (the Get X methods), let's use GetProductsByCategoryID .

Figure 17 : Choose the Names for the TableAdapter Methods ( Click to view full-size image )

After completing the wizard, the DataSet Designer includes the new TableAdapter methods.

Figure 18 : The Products Can Now be Queried by Category

Take a moment to add a GetProductByProductID( productID ) method using the same technique.

These parameterized queries can be tested directly from the DataSet Designer. Right-click on the method in the TableAdapter and choose Preview Data. Next, enter the values to use for the parameters and click Preview.

Figure 19 : Those Products Belonging to the Beverages Category are Shown ( Click to view full-size image )

With the GetProductsByCategoryID( categoryID ) method in our DAL, we can now create an ASP.NET page that displays only those products in a specified category. The following example shows all products that are in the Beverages category, which have a CategoryID of 1.

Beverages.asp

Beverages.aspx.cs

Figure 20 : Those Products in the Beverages Category are Displayed ( Click to view full-size image )

Step 4: Inserting, Updating, and Deleting Data

There are two patterns commonly used for inserting, updating, and deleting data. The first pattern, which I'll call the database direct pattern, involves creating methods that, when invoked, issue an INSERT , UPDATE , or DELETE command to the database that operates on a single database record. Such methods are typically passed in a series of scalar values (integers, strings, Booleans, DateTimes, and so on) that correspond to the values to insert, update, or delete. For example, with this pattern for the Products table the delete method would take in an integer parameter, indicating the ProductID of the record to delete, while the insert method would take in a string for the ProductName , a decimal for the UnitPrice , an integer for the UnitsOnStock , and so on.

Figure 21 : Each Insert, Update, and Delete Request is Sent to the Database Immediately ( Click to view full-size image )

The other pattern, which I'll refer to as the batch update pattern, is to update an entire DataSet, DataTable, or collection of DataRows in one method call. With this pattern a developer deletes, inserts, and modifies the DataRows in a DataTable and then passes those DataRows or DataTable into an update method. This method then enumerates the DataRows passed in, determines whether or not they've been modified, added, or deleted (via the DataRow's RowState property value), and issues the appropriate database request for each record.

Figure 22 : All Changes are Synchronized with the Database When the Update Method is Invoked ( Click to view full-size image )

The TableAdapter uses the batch update pattern by default, but also supports the DB direct pattern. Since we selected the "Generate Insert, Update, and Delete statements" option from the Advanced Properties when creating our TableAdapter, the ProductsTableAdapter contains an Update() method, which implements the batch update pattern. Specifically, the TableAdapter contains an Update() method that can be passed the Typed DataSet, a strongly-typed DataTable, or one or more DataRows. If you left the "GenerateDBDirectMethods" checkbox checked when first creating the TableAdapter the DB direct pattern will also be implemented via Insert() , Update() , and Delete() methods.

Both data modification patterns use the TableAdapter's InsertCommand , UpdateCommand , and DeleteCommand properties to issue their INSERT , UPDATE , and DELETE commands to the database. You can inspect and modify the InsertCommand , UpdateCommand , and DeleteCommand properties by clicking on the TableAdapter in the DataSet Designer and then going to the Properties window. (Make sure you have selected the TableAdapter, and that the ProductsTableAdapter object is the one selected in the drop-down list in the Properties window.)

Figure 23 : The TableAdapter has InsertCommand , UpdateCommand , and DeleteCommand Properties ( Click to view full-size image )

To examine or modify any of these database command properties, click on the CommandText subproperty, which will bring up the Query Builder.

Figure 24 : Configure the INSERT , UPDATE , and DELETE Statements in the Query Builder ( Click to view full-size image )

The following code example shows how to use the batch update pattern to double the price of all products that are not discontinued and that have 25 units in stock or less:

The code below illustrates how to use the DB direct pattern to programmatically delete a particular product, then update one, and then add a new one:

Creating Custom Insert, Update, and Delete Methods

The Insert() , Update() , and Delete() methods created by the DB direct method can be a bit cumbersome, especially for tables with many columns. Looking at the previous code example, without IntelliSense's help it's not particularly clear what Products table column maps to each input parameter to the Update() and Insert() methods. There may be times when we only want to update a single column or two, or want a customized Insert() method that will, perhaps, return the value of the newly inserted record's IDENTITY (auto-increment) field.

To create such a custom method, return to the DataSet Designer. Right-click on the TableAdapter and choose Add Query, returning to the TableAdapter wizard. On the second screen we can indicate the type of query to create. Let's create a method that adds a new product and then returns the value of the newly added record's ProductID . Therefore, opt to create an INSERT query.

Figure 25 : Create a Method to Add a New Row to the Products Table ( Click to view full-size image )

On the next screen the InsertCommand 's CommandText appears. Augment this query by adding SELECT SCOPE_IDENTITY() at the end of the query, which will return the last identity value inserted into an IDENTITY column in the same scope. (See the technical documentation for more information about SCOPE_IDENTITY() and why you probably want to use SCOPE_IDENTITY() in lieu of @@IDENTITY .) Make sure that you end the INSERT statement with a semi-colon before adding the SELECT statement.

Figure 26 : Augment the Query to Return the SCOPE_IDENTITY() Value ( Click to view full-size image )

Finally, name the new method InsertProduct .

Figure 27 : Set the New Method Name to InsertProduct ( Click to view full-size image )

When you return to the DataSet Designer you'll see that the ProductsTableAdapter contains a new method, InsertProduct . If this new method doesn't have a parameter for each column in the Products table, chances are you forgot to terminate the INSERT statement with a semi-colon. Configure the InsertProduct method and ensure you have a semi-colon delimiting the INSERT and SELECT statements.

By default, insert methods issue non-query methods, meaning that they return the number of affected rows. However, we want the InsertProduct method to return the value returned by the query, not the number of rows affected. To accomplish this, adjust the InsertProduct method's ExecuteMode property to Scalar .

Figure 28 : Change the ExecuteMode Property to Scalar ( Click to view full-size image )

The following code shows this new InsertProduct method in action:

Step 5: Completing the Data Access Layer

Note that the ProductsTableAdapters class returns the CategoryID and SupplierID values from the Products table, but doesn't include the CategoryName column from the Categories table or the CompanyName column from the Suppliers table, although these are likely the columns we want to display when showing product information. We can augment the TableAdapter's initial method, GetProducts() , to include both the CategoryName and CompanyName column values, which will update the strongly-typed DataTable to include these new columns as well.

This can present a problem, however, as the TableAdapter's methods for inserting, updating, and deleting data are based off of this initial method. Fortunately, the auto-generated methods for inserting, updating, and deleting are not affected by subqueries in the SELECT clause. By taking care to add our queries to Categories and Suppliers as subqueries, rather than JOIN s, we'll avoid having to rework those methods for modifying data. Right-click on the GetProducts() method in the ProductsTableAdapter and choose Configure. Then, adjust the SELECT clause so that it looks like:

Figure 29 : Update the SELECT Statement for the GetProducts() Method ( Click to view full-size image )

After updating the GetProducts() method to use this new query the DataTable will include two new columns: CategoryName and SupplierName .

Figure 30 : The Products DataTable has Two New Columns

Take a moment to update the SELECT clause in the GetProductsByCategoryID( categoryID ) method as well.

If you update the GetProducts() SELECT using JOIN syntax the DataSet Designer won't be able to auto-generate the methods for inserting, updating, and deleting database data using the DB direct pattern. Instead, you'll have to manually create them much like we did with the InsertProduct method earlier in this tutorial. Furthermore, you'll manually have to provide the InsertCommand , UpdateCommand , and DeleteCommand property values if you want to use the batch updating pattern.

Adding the Remaining TableAdapters

Up until now, we've only looked at working with a single TableAdapter for a single database table. However, the Northwind database contains several related tables that we'll need to work with in our web application. A Typed DataSet can contain multiple, related DataTables. Therefore, to complete our DAL we need to add DataTables for the other tables we'll be using in these tutorials. To add a new TableAdapter to a Typed DataSet, open the DataSet Designer, right-click in the Designer, and choose Add / TableAdapter. This will create a new DataTable and TableAdapter and walk you through the wizard we examined earlier in this tutorial.

Take a few minutes to create the following TableAdapters and methods using the following queries. Note that the queries in the ProductsTableAdapter include the subqueries to grab each product's category and supplier names. Additionally, if you've been following along, you've already added the ProductsTableAdapter class's GetProducts() and GetProductsByCategoryID( categoryID ) methods.

ProductsTableAdapter

GetProducts :

GetProductsByCategoryID :

GetProductsBySupplierID :

GetProductByProductID :

CategoriesTableAdapter

GetCategories :

GetCategoryByCategoryID :

SuppliersTableAdapter

GetSuppliers :

GetSuppliersByCountry :

GetSupplierBySupplierID :

EmployeesTableAdapter

GetEmployees :

GetEmployeesByManager :

GetEmployeeByEmployeeID :

Figure 31 : The DataSet Designer After the Four TableAdapters Have Been Added ( Click to view full-size image )

Adding Custom Code to the DAL

The TableAdapters and DataTables added to the Typed DataSet are expressed as an XML Schema Definition file ( Northwind.xsd ). You can view this schema information by right-clicking on the Northwind.xsd file in the Solution Explorer and choosing View Code.

Figure 32 : The XML Schema Definition (XSD) File for the Northwinds Typed DataSet ( Click to view full-size image )

This schema information is translated into C# or Visual Basic code at design time when compiled or at runtime (if needed), at which point you can step through it with the debugger. To view this auto-generated code go to the Class View and drill down to the TableAdapter or Typed DataSet classes. If you don't see the Class View on your screen, go to the View menu and select it from there, or hit Ctrl+Shift+C. From the Class View you can see the properties, methods, and events of the Typed DataSet and TableAdapter classes. To view the code for a particular method, double-click the method name in the Class View or right-click on it and choose Go To Definition.

Figure 33 : Inspect the Auto-Generated Code by Selecting Go To Definition from the Class View

While auto-generated code can be a great time saver, the code is often very generic and needs to be customized to meet the unique needs of an application. The risk of extending auto-generated code, though, is that the tool that generated the code might decide it's time to "regenerate" and overwrite your customizations. With .NET 2.0's new partial class concept, it's easy to split a class across multiple files. This enables us to add our own methods, properties, and events to the auto-generated classes without having to worry about Visual Studio overwriting our customizations.

To demonstrate how to customize the DAL, let's add a GetProducts() method to the SuppliersRow class. The SuppliersRow class represents a single record in the Suppliers table; each supplier can provider zero to many products, so GetProducts() will return those products of the specified supplier. To accomplish this create a new class file in the App_Code folder named SuppliersRow.cs and add the following code:

This partial class instructs the compiler that when building the Northwind.SuppliersRow class to include the GetProducts() method we just defined. If you build your project and then return to the Class View you'll see GetProducts() now listed as a method of Northwind.SuppliersRow .

Figure 34 : The GetProducts() Method is Now Part of the Northwind.SuppliersRow Class

The GetProducts() method can now be used to enumerate the set of products for a particular supplier, as the following code shows:

This data can also be displayed in any of ASP.NET's data Web controls. The following page uses a GridView control with two fields:

• A BoundField that displays the name of each supplier, and
• A TemplateField that contains a BulletedList control that is bound to the results returned by the GetProducts() method for each supplier.

We'll examine how to display such master-detail reports in future tutorials. For now, this example is designed to illustrate using the custom method added to the Northwind.SuppliersRow class.

SuppliersAndProducts.aspx

SuppliersAndProducts.aspx.cs

Figure 35 : The Supplier's Company Name is Listed in the Left Column, Their Products in the Right ( Click to view full-size image )

When building a web application creating the DAL should be one of your first steps, occurring before you start creating your presentation layer. With Visual Studio, creating a DAL based on Typed DataSets is a task that can be accomplished in 10-15 minutes without writing a line of code. The tutorials moving forward will build upon this DAL. In the next tutorial we'll define a number of business rules and see how to implement them in a separate Business Logic Layer.

Happy Programming!

Further Reading

For more information on the topics discussed in this tutorial, refer to the following resources:

• Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
• Designing Data Tier Components and Passing Data Through Tiers
• Encrypting Configuration Information in ASP.NET 2.0 Applications
• TableAdapter Overview
• Working with a Typed DataSet
• Using Strongly-Typed Data Access in Visual Studio 2005 and ASP.NET 2.0
• How to Extend TableAdapter Methods

Video Training on Topics Contained in this Tutorial

• Data Access Layers in ASP.NET Applications
• How to Manually Bind a Dataset to a Datagrid
• How to Work with Datasets and Filters from an ASP Application

About the Author

Scott Mitchell, author of seven ASP/ASP.NET books and founder of 4GuysFromRolla.com , has been working with Microsoft Web technologies since 1998. Scott works as an independent consultant, trainer, and writer. His latest book is Sams Teach Yourself ASP.NET 2.0 in 24 Hours . He can be reached at [email protected]. or via his blog, which can be found at http://ScottOnWriting.NET .

Special Thanks To

This tutorial series was reviewed by many helpful reviewers. Lead reviewers for this tutorial were Ron Green, Hilton Giesenow, Dennis Patterson, Liz Shulok, Abel Gomez, and Carlos Santos. Interested in reviewing my upcoming MSDN articles? If so, drop me a line at [email protected].

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

DEV Community

Posted on Oct 30, 2021 • Updated on Oct 16, 2022

Data Access Layer

Data access layer helps us to isolate our code from presentation layer and business layer. We use data access layer to pull data from different data stores (database server, flat files, web services, whatever else). This way, if you have to bring any changes to your data layer for example we want to replace ORM, Database server or add web service repository to retrieve data. We wont end up rewriting the whole thing.

These days, various ORM frameworks blur the boundaries between different layers. This typically makes development easier and cuts development time, but it can cause a lot of problems as demand on application grows and we end up with spaghetti code. To be honest, changing database servers is very uncommon.

Basically there are two main reasons why we should use Data Access Layer.

It help us abstract the actual database or other data providers from our applications and that makes it easier to switch from using MySQL and moving to MS SQL server. On Business Layer side we can abstract the logical data model from our Business Layer making it loosely coupled and it also makes business layer agnostic about data access. Giving us the freedom to modify the data model without impacting the business layer.

Another reason our business layer should not be aware of the logical data model used by our application, is to allow changes in our business layer without having any effect on our physical data model. Lets take an example at some point in time in our applications life we need to retrieve data from third party web API and use it in our business logic, if our Business Layer is agnostic about data and how and from where it is retrieved, this allows us lot of freedom to improve or enhance our application. Modern tool sets like ORMs and Linq make life a lot easier which, in my opinion, encourages developers to cut corners under pressure to deliver applications quickly and tend to forget (or are not able to implement the separation between different layers that should exist).

Essentially, to get a better understanding the reasons and function of a Data Access Layer , you need to see things from Presentation and Business Layer's side, keeping in mind that presentation layer should be agnostic of business layer and business layer should be agnostic of the data access layer.

A data access layer follows the idea of "separation of concerns" whereby all of the logic required for your business layer to interact with your data layer is isolated to a single layer, as shown in the image below.

The reason behind me blabbering about Data Access Layer is because time and again colleagues and others were very adamant that data access frameworks, such as Entity Framework acts as Data access layer and (EF) which follows the repository pattern and it is acting as a data abstraction layer. But, I hold a different opinion and I think ORMs have its place but they can make your application very inefficient if we fail to use them effectively. Some of the performance related issues are well known, for example:

Majority of the time when I migrated a legacy application it was normally a big fat monolith and the most of the Linq statements were DataContext.TableName.where(Some Condition EqualTo) which basically translates to (select * from table where condition) In reality we may only need two fields from a table that has 100 fields.

In order to do a delete or update, (EF) first select the record and then fire a query to the delete or update.

3) ORMS tie your business layer very closely to the data model, defeating the whole concept of "Separation of concerns" and reason why we should have a data access layer.

I personally always implement a class library in my data access layer where I can write SQL queries which are a lot more efficient as compared to Linq, especially when we need to retrieve a large set of data from several tables. You can call it a hybrid application which make use of EF and SQL.

Top comments (0)

Templates let you quickly answer FAQs or store snippets for re-use.

Are you sure you want to hide this comment? It will become hidden in your post, but will still be visible via the comment's permalink .

Hide child comments as well

For further actions, you may consider blocking this person and/or reporting abuse

Flutter Hive: Mastering Offline Data Storage in Flutter

Gosu Code - Feb 4

Including Polymorphic Association In Rails To Avoid N+1 Queries

Pankaj Gupta - Feb 4

End-to-end Flutter Architecture Guide

yatendra2001 - Feb 3

UUID in PostgreSQL

Franck Pachot - Jan 27

We're a place where coders share, stay up-to-date and grow their careers.

3 Layered Architecture

Introduction.

Here in this article, I would like to cover the typical three layer architecture in C# .NET. It is a very useful approach for coding due to easy code maintenance.

First let me give you a small overview about the topic I would like to cover in this article.

• Tier vs. Layer
• Three Tier/Layer Architecture Design Components
• Demo: Three Layer Windows Application in C#.NET

1. Tier vs. Layer

1.1 Tier: Tier indicates a physical separation of components, which may mean different assemblies such as DLL, EXE, etc. on the same server or multiple servers.

As you can see in the above figure, Data Tier have no direction with Presentation Tier, but there is an intermediate Tier called Business Tier which is mainly responsible to pass the data from Data Tier to Presentation Tier and to add defined business logic to Data.

So, if we separate each Tier by its functionality, then we come to know the below conclusion:

1.2 Layer: Layer indicates logical separation of components, such as having distinct namespaces and classes for the Database Access Layer, Business Logic Layer and User Interface Layer.

2. Three Tier/Layer Architecture Design Components

As we have already seen, tier is the sum of all the physical components. We can separate the three tiers as Data Tier, Business Tier and Presentation Tier.

• Data Tier is basically the server which stores all the application’s data. Data tier contents Database Tables, XML Files and other means of storing Application Data.
• Business Tier is mainly working as the bridge between Data Tier and Presentation Tier. All the Data passes through the Business Tier before passing to the presentation Tier. Business Tier is the sum of Business Logic Layer, Data Access Layer and Value Object and other components used to add business logic.
• Presentation Tier is the tier in which the users interact with an application. Presentation Tier contents Shared UI code, Code Behind and Designers used to represent information to user.

The above figure is a mixture of Three Tier and Three Layer Architecture. Here, we can clearly see a different between Tier and Layer. Since each component is independent of each other, they are easily maintainable without changing the whole code.

This approach is really very important when several developers are working on the same project and some module needs to be re-used in another project. In a way, we can distribute work among developers and also maintain it in the future without much problems.

Testing is also a very important issue for Architecture when we are considering writing a test case for the project. Since it’s like a modular architecture, it’s very handy testing each module and to trace out bugs without going through the entire code.

3 . Demo: 3 Layer Windows Application in C#.NET

Let’s go though from one module to other to have a better understanding of it.

dbConnection

This class is mainly used to do the database activity like Select, Update and Delete query to database. It also checks if the database connection is open or not. If database connection is not open, then it opens the connection and performs the database query. The database results are to be received and being passing in Data Table in this class.

This class takes the database setting from the app.config file so it’s really flexible to manage the database settings.

Database Access Layer

Database Access Layer (DAO) builds the query based on received parameters from the Business Logic Layer and passes it the   dbConnection   class for execution. And simple return results from the   dbConnection   class to Business Logic Layer.

Value Object

Value Object is nothing more but a class with the contents   GET   and   SET   methods. It’s mainly used to pass Data from one class to another. It’s directly connected with Business Logic Layer and Presentation Layer. As you can see in the diagram object values are being SET in Business Logic Layer and GET from Presentation Layer.

Business Logic Layer

Business Logic Layer (BUS) works as a bridge between Presentation Layer and DAO. All the user values received from the presentation layer are being passed to BUS. The results received from the DAO are in row data in Data Table format but in BUS it’s converting into Value Objects (VO). Business Logic Layer (BUS) is the most important class in the whole architecture because it mainly contains all the business logic of the program. Whenever a user wants to update the business logic of the program only need to update this class.

Presentation Layer

Presentation Layer is the only layer which is directly connected with the user. So in this matter, it’s also a really important layer for marketing purposes. Presentation Layer is mainly used for getting user data and then passing it to Business Logic Layer for further procedure, and when data is received in Value Object then it’s responsible to represent value object in the appropriate form which user can understand.

Hope this artilce helps beginners in understanding the three layered architecture.

Leave a reply cancel reply.

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

With Canarys, Let’s Plan. Grow. Strive. Succeed.

Copyright © 2024 Canarys Automations Limited . All Rights Reserved.

Software Architecture Patterns by

Get full access to Software Architecture Patterns and 60K+ other titles, with a free 10-day trial of O'Reilly.

There are also live events, courses curated by job role, and more.

Chapter 1. Layered Architecture

The most common architecture pattern is the layered architecture pattern, otherwise known as the n-tier architecture pattern. This pattern is the de facto standard for most Java EE applications and therefore is widely known by most architects, designers, and developers. The layered architecture pattern closely matches the traditional IT communication and organizational structures found in most companies, making it a natural choice for most business application development efforts. 

Pattern Description

Components within the layered architecture pattern are organized into horizontal layers, each layer performing a specific role within the application (e.g., presentation logic or business logic). Although the layered architecture pattern does not specify the number and types of layers that must exist in the pattern, most layered architectures consist of four standard layers: presentation, business, persistence, and database ( Figure 1-1 ). In some cases, the business layer and persistence layer are combined into a single business layer, particularly when the persistence logic (e.g., SQL or HSQL) is embedded within the business layer components. Thus, smaller applications may have only three layers, whereas larger and more complex business applications may contain five or more layers. 

Each layer of the layered architecture pattern has a specific role and responsibility within the application. For example, a presentation layer would be responsible for handling all user interface and browser communication logic, whereas a business layer would be responsible for executing specific business rules associated with the request. Each layer in the architecture forms an abstraction around the work that needs to be done to satisfy a particular business request. For example, the presentation layer doesn’t need to know or worry about how to get customer data; it only needs to display that information on a screen in particular format. Similarly, the business layer doesn’t need to be concerned about how to format customer data for display on a screen or even where the customer data is coming from; it only needs to get the data from the persistence layer, perform business logic against the data (e.g., calculate values or aggregate data), and pass that information up to the presentation layer.  

Figure 1-1. Layered architecture pattern

One of the powerful features of the layered architecture pattern is the separation of concerns among components. Components within a specific layer deal only with logic that pertains to that layer. For example, components in the presentation layer deal only with presentation logic, whereas components residing in the business layer deal only with business logic. This type of component classification makes it easy to build effective roles and responsibility models into your architecture, and also makes it easy to develop, test, govern, and maintain applications using this architecture pattern due to well-defined component interfaces and limited component scope.

Key Concepts

Notice in Figure 1-2 that each of the layers in the architecture is marked as being  closed . This is a very important concept in the layered architecture pattern. A closed layer means that as a request moves from layer to layer, it must go through the layer right below it to get to the next layer below that one. For example, a request originating from the presentation layer must first go through the business layer and then to the persistence layer before finally hitting the database layer. 

Figure 1-2. Closed layers and request access

So why not allow the presentation layer direct access to either the persistence layer or database layer? After all, direct database access from the presentation layer is much faster than going through a bunch of unnecessary layers just to retrieve or save database information. The answer to this question lies in a key concept known as  layers of isolation . 

The layers of isolation concept means that changes made in one layer of the architecture generally don’t impact or affect components in other layers: the change is isolated to the components within that layer, and possibly another associated layer (such as a persistence layer containing SQL). If you allow the presentation layer direct access to the persistence layer, then changes made to SQL within the persistence layer would impact both the business layer and the presentation layer, thereby producing a very tightly coupled application with lots of interdependencies between components. This type of architecture then becomes very hard and expensive to change.  

The layers of isolation concept also means that each layer is independent of the other layers, thereby having little or no knowledge of the inner workings of other layers in the architecture. To understand the power and importance of this concept, consider a large refactoring effort to convert the presentation framework from JSP (Java Server Pages) to JSF (Java Server Faces). Assuming that the contracts (e.g., model) used between the presentation layer and the business layer remain the same, the business layer is not affected by the refactoring and remains completely independent of the type of user-interface framework used by the presentation layer.  

While closed layers facilitate layers of isolation and therefore help isolate change within the architecture, there are times when it makes sense for certain layers to be open. For example, suppose you want to add a shared-services layer to an architecture containing common service components accessed by components within the business layer (e.g., data and string utility classes or auditing and logging classes). Creating a services layer is usually a good idea in this case because architecturally it restricts access to the shared services to the business layer (and not the presentation layer). Without a separate layer, there is nothing architecturally that restricts the presentation layer from accessing these common services, making it difficult to govern this access restriction.  

In this example, the new services layer would likely reside  below  the business layer to indicate that components in this services layer are not accessible from the presentation layer. However, this presents a problem in that the business layer is now required to go through the services layer to get to the persistence layer, which makes no sense at all. This is an age-old problem with the layered architecture, and is solved by creating open layers within the architecture.  

As illustrated in Figure 1-3 , the services layer in this case is marked as open,  meaning requests are allowed to bypass this open layer and go directly to the layer below it. In the following example, since the services layer is open, the business layer is now allowed to bypass it and go directly to the persistence layer, which makes perfect sense.  

Figure 1-3. Open layers and request flow

Leveraging the concept of open and closed layers helps define the relationship between architecture layers and request flows and also provides designers and developers with the necessary information to understand the various layer access restrictions within the architecture. Failure to document or properly communicate which layers in the architecture are open and closed (and why) usually results in tightly coupled and brittle architectures that are very difficult to test, maintain, and deploy.

Pattern Example

To illustrate how the layered architecture works, consider a request from a business user to retrieve customer information for a particular individual as illustrated in Figure 1-4 . The black arrows show the request flowing down to the database to retrieve the customer data, and the red arrows show the response flowing back up to the screen to display the data. In this example, the customer information consists of both customer data and order data (orders placed by the customer).  

The customer screen is responsible for accepting the request and displaying the customer information. It does not know where the data is, how it is retrieved, or how many database tables must be queries to get the data. Once the customer screen receives a request to get customer information for a particular individual, it then forwards that request onto the customer delegate module. This module is responsible for knowing which modules in the business layer can process that request and also how to get to that module and what data it needs (the contract). The customer object in the business layer is responsible for aggregating all of the information needed by the business request (in this case to get customer information). This module calls out to the  customer dao  (data access object) module in the persistence layer to get customer data, and also the order dao module to get order information. These modules in turn execute SQL statements to retrieve the corresponding data and pass it back up to the customer object in the business layer. Once the customer object receives the data, it aggregates the data and passes that information back up to the customer delegate, which then passes that data to the customer screen to be presented to the user.      

Figure 1-4. Layered architecture example

From a technology perspective, there are literally dozens of ways these modules can be implemented. For example, in the Java platform, the customer screen can be a (JSF) Java Server Faces screen coupled with the customer delegate as the managed bean component. The customer object in the business layer can be a local Spring bean or a remote EJB3 bean. The data access objects illustrated in the previous example can be implemented as simple POJO’s (Plain Old Java Objects), MyBatis XML Mapper files, or even objects encapsulating raw JDBC calls or Hibernate queries. From a Microsoft platform perspective, the customer screen can be an ASP (active server pages) module using the .NET framework to access C# modules in the business layer, with the customer and order data access modules implemented as ADO (ActiveX Data Objects). 

Considerations

The layered architecture pattern is a solid general-purpose pattern, making it a good starting point for most applications, particularly when you are not sure what architecture pattern is best suited for your application. However, there are a couple of things to consider from an architecture standpoint when choosing this pattern.

The first thing to watch out for is what is known as the architecture sinkhole anti-pattern . This anti-pattern describes the situation where requests flow through multiple layers of the architecture as simple pass-through processing with little or no logic performed within each layer. For example, assume the presentation layer responds to a request from the user to retrieve customer data. The presentation layer passes the request to the business layer, which simply passes the request to the persistence layer, which then makes a simple SQL call to the database layer to retrieve the customer data. The data is then passed all the way back up the stack with no additional processing or logic to aggregate, calculate, or transform the data. 

Every layered architecture will have at least some scenarios that fall into the architecture sinkhole anti-pattern. The key, however, is to analyze the percentage of requests that fall into this category. The 80-20 rule is usually a good practice to follow to determine whether or not you are experiencing the architecture sinkhole anti-pattern. It is typical to have around 20 percent of the requests as simple pass-through processing and 80 percent of the requests having some business logic associated with the request. However, if you find that this ratio is reversed and a majority of your requests are simple pass-through processing, you might want to consider making some of the architecture layers open, keeping in mind that it will be more difficult to control change due to the lack of layer isolation. 

Another consideration with the layered architecture pattern is that it tends to lend itself toward monolithic applications, even if you split the presentation layer and business layers into separate deployable units. While this may not be a concern for some applications, it does pose some potential issues in terms of deployment, general robustness and reliability, performance, and scalability.   

Pattern Analysis

The following table contains a rating and analysis of the common architecture characteristics for the layered architecture pattern. The rating for each characteristic is based on the natural tendency for that characteristic as a capability based on a typical implementation of the pattern, as well as what the pattern is generally known for. For a side-by-side comparison of how this pattern relates to other patterns in this report, please refer to  Appendix A  at the end of this report.

Get Software Architecture Patterns now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Don’t leave empty-handed

Get Mark Richards’s Software Architecture Patterns ebook to better understand how to design components—and how they should interact.

It’s yours, free.

Check it out now on O’Reilly

Dive in for free with a 10-day trial of the O’Reilly learning platform—then explore all the other resources our members count on to build skills and solve problems every day.

• Web Application Development Enterprise applications maximize your efficiency
• Flutter App Development Building the next-gen mobile app that rocks
• Software Customization & Enhancement Make your existing software better
• Real Estate
• Construction
• Marketplace
• Data Management
• Procurement
• Recruitment
• Microservices
• Schedule a Call

Diving deep into the ocean of technology

How to build and deploy a three-layer architecture application with C#

• February 27, 2021 • Development

What’s three-layer architecture?

Layer indicates the logical separation of components. Layered architecture concentrates on grouping related functionality within an application into distinct layers that are stacked vertically on top of each other. Each layer has unique namespaces and classes.

An N-layer application may reside on the same physical computer (same tier); each layer's components communicate with another layer’s components by well-defined interfaces.

In C#, each layer is often implemented in a Dynamic Link Libraries (DLL) file.

To help you understand more about the n-layer architecture, I’ve put together all pieces of information about three-layer with a practical example in this article as follows:

• Presentation layer It is the first and topmost layer present in the application where users can interact with the application.
• Business Logic layer This is the middle layer - the heart of the application. It contains all business logic of the application, describes how business objects interact with each other, where the Presentation layer and Data Access layer can indirectly communicate with each other.
• Data Access layer The Data Access layer enforces rules regarding accessing data, providing simplified access to data stored in persistent storage, such as SQL Server. It is noteworthy that this layer only focuses on data access instead of data storage. It may create a bit of confusion with the Data-tier in a three-tier architecture.

Advantages of three-layer architecture

• Explicit code: The code is separated into each layer. Each one is dedicated to a single responsibility such as interface, business processing, and querying instead of bundling all the code in one place.
• Easy to maintain: As its roles separate each layer, it would be easier to change something. The modification can be isolated in a single layer or affected only the nearest layer without affecting the whole program.
• Easy to develop, reuse: When we want to modify a function, we can easily do that as we already have a standard architecture. In case we want to alter a complete layer such as from Winform to Web form, we only need to implement to replace the Presentation layer; other layers can be reused completely.
• Easy to transfer: We could save time on moving the application to others as they have a standard architecture to follow and apply.
• Easy to distribute the workloads: By organizing the code into different layers based on its responsibility, each team/member can write their code on each layer independently, which in turn helps developers control their workload.

How does it work?

In three-layer architecture, the Presentation layer doesn’t communicate directly with the Data Access layer. The Business Logic layer works as a bridge between the Presentation layer and the Data Access layer. The three-layer architecture works as follows:

• The Presentation layer is the only class that is directly interacted with the user. It is mainly used for presenting/collecting data from users, then passes them to the Business Logic layer for further processing.
• After receiving information from the Presentation layer, the Business Logic layer does some business logic on the data. During this process, this layer may retrieve or update some data from the application database. However, this layer doesn’t take responsibility for accessing the database; it sends requests to the next layer, the Data Access layer.
• The Data Access layer receives requests from the Business Logic layer and builds some queries to the database to handle these requests. Once the execution gets done, it sends the result back to the Business Logic layer.
• The Business Logic layer gets responses from the Data Access layer, then completes the process and sends the result to the Presentation Layer.
• The Presentation layer gets the responses and presents them to users via UI components.

The diagram below illustrates how three-layer architecture works.

How to build and deploy a three-layer application in C#?

To guide you build and deploy a three-layer application, I have prepared a demo with the following components:

• Business Objects - Entity(Data Transfer Objects) layer: .NET Core class library
• Data Access layer: .NET Core class library
• Business Logic layer: .NET Core class library
• Presentation Layer: ASP.NET Core 5.0 Razor pages

The Business Objects layer includes objects that are used in the application and common helper functions (without logic) used for all layers. In a three-layer architecture, this layer is optional. However, as we follow the OOP with C#, we should reduce the duplicate codes as much as possible. Therefore, using a layer to keep common codes instead of holding them in each layer is essential.

To easily follow the article, you can download the demo of the three-layer architecture sample . It is built based on Repository + UnitOfWork pattern, which’s a well-designed pattern in C#. Here are the demo and practical examples of the Repository + UnitOfWork pattern .

The diagram below explains how the application was designed.

Firstly, create a database with the design below to store the application’s data. You can execute the ThreeLayerSample.Database.sql, which was placed inside the Data Access layer to create it.

The demo will focus on getting the Work table items before representing them to users.

Business Objects/Entity layer

ThreeLayerSample.Domain class project in this example.

1. Connect to the database with Entity Framework Core

When we have the database, we need to create a mapping between the database and the application. Let’s open your Package Manager console in this layer, run a Scaffolding command, then replace SERVER, DATABASE, USER, and PASSWORD with suitable values based on your SQL Server settings.

Once the command has finished, the database tables should be created into the code and name entities. As the entities could be used in all layers without any database or business logic, we should keep them in this layer.

A DataContext class named “DemoContext” is also created. This class provides database access, so it should be placed in the Data Access layer.

2. Create Generic interfaces for Repository and UnitOfWork

All data entities should have CRUD actions, so let’s create a generic interface named IRepository, and then the repository of each entity should implement this interface.

ThreeLayerDomain/Interfaces/IRepository.cs

We need another interface named IUnitOfWork as follows:

ThreeLayerDomain/Interfaces/IUnitOfWork.cs

They are interfaces without business/database logic here, so I put them in this layer.

Data Access layer

ThreeLayerSample.Infrastructure class project in this example.

1. Implement generic classes

By applying Generic Repository and Unit of Work design patterns, the data access layer classes are implemented as follows:

ThreeLayerSample.Infrastructure/Repository.cs

ThreeLayerSample.Infrastructure/UnitOfWork.cs

And another DbFactory class that will initialize a DbContext when we use it.

ThreeLayerSample.Infrastructure/DbFactory.cs

Business Logic layer

ThreeLayerSample.Service class project in this example.

1. Create an interface for the service

Let’s create the interface for the WorkService that the Presentation layer depends on as we don’t want to tighten the Presentation layer and Business Logic layer with a concrete implementation.

ThreeLayerSample.Domain/Interfaces/Services/IWorkService.cs

2. Implement code for the service

Next, implement business logic processing the Work service in a class named WorkService as follows. This is the business logic processing code, so we put this class in the Business Logic layer. As you can see, the service requests to get all items of the Work table via the Repository instance of the Work entity, which was implemented in the generic repository.

ThreeLayerSample.Service/WorkService.cs

Presentation layer

ThreeLayerSample.Web(Razor) ASP.NET Core Web App in this example.

Follow this tutorial to create an ASP.NET Core Razor page application . Once the application is created, create a ServiceCollectionExtensions class under the Extensions folder.

ThreeLayerSample.Web_Razor_/Extensions/ServiceCollectionExtensions .cs

Add your connection string into the appsettings.json file, and it’s the connection string that the Data Access layer will employ to establish a connection to the database (same value as the connection string in the Scaffold command).

ThreeLayerSample.Web_Razor_/appsettings.json

Add an AppSettings class to the Entity layer.

ThreeLayerSample.Domain/Models/AppSettings.cs

Open the Startup.cs file, then add the following codes.

At its constructor: read data from appsettings.json, then store it in the created AppSettings class.

In the ConfigureServices: register instances for DataContext, its Factory, UnitOfWork, and WorkService to the application (using extension methods in ServiceCollectionExtensions class).

ThreeLayerSample.Web_Razor_/Startup.cs

Open Index.cshtml.cs file, add the following code to inject WorkService, then get data from WorkService and set it to Works property.

ThreeLayerSample.Web_Razor_/Pages/Index.cshtml.cs

In the Index.cshtml file, add the following code to present data to the UI.

ThreeLayerSample.Web_Razor_/Pages/Index.cshtml.

Run the application to check for the result.

Create a folder to store your source code.

Open your IIS, then choose Create Website to host your application. Provide a name, a specific port, and a physical path to the source code folder for the Content Directory section. Make sure “Start website immediately” is ticked.

Open your solution in your Visual Studio, then follow these steps:

Right-click on ThreeLayerSample.Web(Razor), select Publish.

Select “Folder”.

Enter “Folder location” by creating the source code folder path below.

Click to publish the project.

Once you’ve done publishing, open the application on your browser to check for the result.

The layered architecture is well-designed for software development that helps organize your code with high maintainability, reusability, and readability. However, even though codes are well-organized in layers, they are deployed and run on the same physical machine, even on the same process. This may not be the right choice for complex applications, which require high availability and stability. We need to upgrade this architecture to another higher level named three-tier architecture, which I will share in my next article.

Thank you for reading, and happy coding!

• Loc Nguyen, How to implement Repository & Unit of Work design patterns in .NET Core with practical examples [Part 1] , enlabsoftware.com/, 2021.
• Anant Patil, Three Layered Architecture , www.ecanarys.com.
• Mark Richards, Software Architecture Patterns , www.oreilly.com.

About the author

Can we send you our next blog posts? Only the best stuffs.

• Engineering Mathematics
• Discrete Mathematics
• Operating System
• Computer Networks
• Digital Logic and Design
• C Programming
• Data Structures
• Theory of Computation
• Compiler Design
• Computer Org and Architecture
• What is Software RAID?
• What is Report Generator?
• Predicate Locking
• Hardware RAID
• Audit Trail
• Measures of Query Cost in DBMS
• Virtual Private Database (VPD)
• What is EII(Enterprise Information Integration)?
• Business-Logic Layer
• Introduction to NoSQL Cloud Database Services
• Query-Execution Plan in SQL
• Strategies For Migrating From SQL to NoSQL Database
• What is Data Inconsistency in DBMS?
• ODBS Full Form
• Weak Levels of Consistency
• Access Types in DBMS
• Difference Between Hardware RAID vs Software RAID
• Double Buffering
• Difference Between Data Mining and Data Analysis

Data-Access Layer

In this article, we are going to learn about the Business Logic Layer in Database Management systems . A DBMS usually consists of various layers where each one has a specific kind of task it performs. The main purpose of the layered structure is to implement abstraction in Database systems. Change in one schema must not affect the other schema. This architecture allows the Database Administrator to design the schema and implement methods to allow secured access to the stored data.

The Data-Access Layer (DAL) is a component of a software architecture that is responsible for managing the data storage and retrieval of an application. It sits between the business-logic layer and the data storage system and provides an abstraction layer that allows the business-logic layer to interact with the data storage system without being aware of its specific implementation.

The DAL is responsible for performing tasks such as: -Connecting to the data storage system and managing the connection. -Generating and executing SQL queries or other data access commands to retrieve and store data. -Mapping the data from the data storage system to the application’s data objects and vice versa. -Handling errors and exceptions related to data access. -Providing support for transactions and other data access features.

The DAL is designed to be reusable and independent of the business logic and data storage implementation. This allows the application to be easily modified or extended without affecting the underlying data access code.

The DAL can be implemented using different data access technologies, such as ADO.NET, JDBC, or ORM (Object-relational mapping) libraries. The choice of technology will depend on the specific requirements of the application and the data storage system being used.

In summary, The Data-Access Layer (DAL) is a component of a software architecture that is responsible for managing the data storage and retrieval of an application. It sits between the business-logic layer and the data storage system and provides an abstraction layer that allows the business-logic layer to interact with the data storage system without being aware of its specific implementation. The DAL is designed to be reusable and independent of the business logic and data storage implementation. It can be implemented using different data access technologies and it provides support for transactions and other data access features.

Data-Access Layer (DAL)  

Data-Access Layer is a layer in an application that provides easy and simplified access to data stored in persistent storage, such as an entity-relational database or any database for that matter. It is layer that exists between the Business Logic Layer (BLL) and the storage layer . 

Let us understand some terminologies involved: 

1. Data-Access Object (DAO): They are entities that are collectively known as the Data Access Layer (DAL). A DAL might be a single class, or it might be composed of multiple Data Access Objects (DAOs). 

2. Business Logic Layer: The business logic layer consists of all the objects and functionalities which handle the business logic of the application, this is where the main implementation of an application takes place and it is very unique for each application. 

3. Storage Layer: The actual database where data is stored, upon which CRUD operations can be performed. This might be some remote or cloud storage.

Data Access Layer

Whenever a specific data query or update is required by the Application (BLL), the request is sent to the Data Access Layer, which then takes appropriate actions to ensure that the requested data or updation is performed, this results in a level of abstraction wherein the Business Layer will only know which function or methods to call, and it does not have to know the actual architecture or details of the entire Database.  

Purpose/Importance of having a DAL:

• Provides a centralized location from where calls are made onto the database(s).
• Provides a layer of abstraction as a developer working on the Business Logic need not worry about the way in which data is stored or their CRUD operations.
• A Data-Access Layer (DAL) can support multiple databases, so the application is able to make use of any database as per its requirement.
• Because segregating the data access code, enables better maintainability and easy migration of the database.

Example : 

Let us assume an airline ticket reservation system, when a user attempts to view the flight availability from the front-end, the business logic layer will send the query request to the Data-Access Layer, from here its this layer’s job to interact with the storage and obtain the queried results. The Business logic layer is not concerned with the way in which data is represented in the storage, whether it is relational or non-relational types. It is the job of the DAL to retrieve the required data from one or more locations from the storage(s) and send it back to the Business Logic Layer, which is converted to user-understandable format, then sent to the front-end.

Please Login to comment...

Similar reads.

• Computer Subject
• 10 Best Slack Integrations to Enhance Your Team's Productivity
• 10 Best Zendesk Alternatives and Competitors
• 10 Best Trello Power-Ups for Maximizing Project Management
• Google Rolls Out Gemini In Android Studio For Coding Assistance
• 30 OOPs Interview Questions and Answers (2024)

Improve your Coding Skills with Practice

What kind of Experience do you want to share?