11 July 2006

.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:

253-DAL001.jpg

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:

253-DAL002.jpg

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:

253-DAL003.jpg

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:

253-DAL004.jpg

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:

253-DAL005.jpg

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:

253-DAL006.jpg

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!

Keep up to date with Simple-Talk

For more articles like this delivered fortnightly, sign up to the Simple-Talk newsletter

Downloads

This post has been viewed 403044 times – thanks for reading.

Tags: , , , ,

  • Rate
    [Total: 842    Average: 3.9/5]
  • Share

Damon Armstrong is a Senior Engineering Team Lead with GimmalSoft in Dallas, Texas, and author of Pro ASP.NET 2.0 Website Programming. He specializes in Microsoft technologies with a focus on SharePoint and ASP.NET. When not staying up all night coding, he can be found playing disc golf, softball, working on something for Carrollton Young Life, or recovering from staying up all night coding.

View all articles by Damon Armstrong

  • Anonymous

    Flexibility vs. Performance
    In general a good article, but orienting a DAL strategy around DataSets in an effort to make your DAL as completely generic as possible just isn’t practical.

    Goes without saying that DataReaders, while very database specific, are superior to DataSets in terms of speed. As such, I prefer to take DataReaders and populate my business objects in order to leverage the aforementioned DR speed plus the type-safety and lightweight nature of the business objects.

  • Damon

    Performance in Practice
    From what I have heard, DataAdapters use DataReaders to populate DataSets (that’s a mouthful). So there is certainly no question that DataReaders are in fact “faster” than DataSets… when used appropriately.

    With DataReaders you have the opportunity to really impact performance by keeping the database connection open for a longer period of time. For example, if you run complex calculation before you close the data reader then the database connection remains open during those calculations, or if you forget to close the underlying database connection altogether. Granted, you can avoid these, but what about everyone else on your development team?

    I was recently wading through the code for a project that used DataSets all throughout the DAL. How many hits a day did the site take? About 5 million. So, although using DataReaders is technically faster, I would have to argue that DataSets are in fact practical.

  • Anonymous

    Choices
    This is a duplicate of a design I have used successfully for years and I agree with the previous comment. However, I give the consumers the choice of both DataSets and DataReaders with custom object collections so I get the best of both worlds.

    The scope of the projects dictate the format of the data.

  • Anonymous

    Standards, please
    Sorry author, this is a shallow article. The concepts are correct but the implementation shows lack of experience. It might work for tiny databases with few tables but would be painful to scale for enterprise-scale databases (1000’s of tables). First, please stick to Microsoft naming standards for your example code (no “_”). Second, use the industry standard names and patterns: entities, DTO’s, domain models, controllers, etc.

  • GQAdonis

    Architecture
    An architecture that takes network infrastructure, deployment configuration, and security requirements into consideration, there is a place for both DataReaders and DataSets in assuring optimal overall system performance.

    I normally lean in the direction of utilizing DataReaders in tight iteration loops that fill collections of business objects for use in the layers above me, so I free connections at the earliest possible instant while immediately giving me the full type safety in the result set that allows for more efficient business rule validation (no boxing of properties inside DataRow instances).

    However, in a highly available system where lots of transactions occur and there is no separation of physical databases (OLTP and OLAP instances), DataSets can be a good tool used in conjunction with caching, since many advanced filter operations are available on in-memory instances without having to use database resources that should be preserved for processing more transactions as opposed to processing multiple queries on single sets of data.

    Using cache dependencies, these in memory caches can be dumped and rebuilt when relevant data changes as a result of a transaction.

    This, I offer as an example of where both constructs (DataReader and DataSet) can be used with optimal performance being the overriding goal.

  • Anonymous

    Still?
    Do people still write DALs by hand? Object-relational mappers (ORMs) were a relatively new idea in 1996, but in 2006 if you aren’t using a ORM to generate DAL code then you are seriously out of touch.

  • GQAdonis

    Not shallow
    By the way, this is not a shallow article. It represents a great introduction to best practices in multi-tier application development, providing something to think about for programmers of various skill levels.

    The gentleman with “all the experience” should have realized that (a) organizations with software systems on multiple platforms sometimes violate typical coding standards for a single platform in favor of having all their code for all platforms conform to a standard of their choosing, and (b) when writing an article like this on the “Internet,” you must consider things like length and audience (made up of newbies and advanced architects alike).

    Whoever that “anonymous” poster was that trashed your article most likely has never written one himself.

  • Anonymous

    Thanks, I was looking for this…
    As a “newbie” who is trying to move beyond placing data access and business logic lumped together in the UI code, I have been stumped as to how to push data back and forth, and this helped. When there are what seems to me a zillion ways to accomplish any coding task at hand, it sure does seem odd that anyone would get nasty over using a dataset.

  • Damon

    RE: Standards, Please
    Microsoft does not, at least in my limited research, seem to offer any standards on naming private fields. I may have missed it, so if there’s a link out there then someone please post it up for all to see.

    Most of the non-Microsoft coding standards documents I HAVE run into, however, say to prefix private fields with an underscore “_” and use camel casing (but not hungarian camel casing). Of course, this is all a style issue so as long as everyone on your development team agrees on a consistent standard, whatever you adopt is probably just fine.

    http://home.comcast.net/~lancehunt/CSharp_Coding_Standards.pdf

    http://www.irritatedvowel.com/Programming/Standards.aspx

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconnamingguidelines.asp

  • Anonymous

    Whats up with people?
    I’ve just found this site for the first time today. I’ve only read two articles and the comments section for both seems to be full of people who just wish to show their “superiority” by bitching and moaning without offering anything remotely constructive.

    I for one appreciate these articles even if I am familiar with the subject at hand. Nobody knows everything about everything and there’s always the opportunity to learn more or see things from a different perspective. If you disagree with something at least do it in a polite and constuctive manner and not just dismiss everything out of hand because you think you know better!

    BTW, I also place “_” in front of my private variables but who cares anyway? 🙂

  • vicneanschi
  • Anonymous

    What link to a ZIP file
    I must be going blind. Where is the link to the ZIP file mentioned at the end of the article?

  • Anonymous

    Link – followup
    Just found it. Now I know I was blind – and illiterate. Blessings.

  • Anonymous

    Better ways
    I have developed Enterprise application portals where this DAL needs to be the important piece. Basically, when you design this DAL, you have to be very careful about making flexibility to work fully. My additional cent for this article is about making this to use stored procedures effectively to reduce a WHOLE new DAL with 50 classes each for some specific functionality. Use DAL for database specific and the methods should take a store proc name and return either xml string or dataset to make things simpler.

  • Anonymous

    Another plus not mentioned for DataSets…
    …is DataSets can be cached locally. While local caching has it’s limits, it is very useful if you have a desktop-based UI that is disconnected from the original datasource (be it a split web service DAL or other local DAL components). Another use is if you have a setup process that gets common infrequently changed data so that you’re not doing a DB read unless you need to (weighing out cache load time vs. db load times, of course).

    Since the focus of the article was web n-Tier, this may not have made sense to mention it, but with those of use using Win Forms (for a varity of reasons, disconnected state as a primary), DataSets are used for more than just DAL abstraction.

  • Anonymous

    Typed Datasets
    Another option which is much stronger with 2.0 is using typed datasets for the Data Layer. Though there are some disadvantages the ability have type-safe referencing to fields, tables/resultsets, and the various CRUD methods at both the data layer and business layer is nice.

  • Anonymous

    Data Readers and early release of DB resources
    (For the record, I don’t like data sets.)
    Early release of DB connections is crucial, and closing / dispose of readers etc are very important.

    For that purpose, one could use the data adapter like .Fill() method in a DAL design.
    Either the DAL calls a .Fill delegate on a business data object, or a business data object can have a Populate(IDateReader reader) .
    In any event, the DAL can be the one responsible for the copy from stream and close right away policy.

  • Anonymous

    Application
    Sir/Mum
    This is gopal giri i want a smale application in asp.net using vb and sqlserver2000 backan.

    the application is based on online shopping from bank tranjesation .
    please sand on my gopal_giri1985@yahoo.co.in

    Thanks

  • Anonymous

    sra
    This is an excellent article.I ahve come across very few on this topic.Howvere I had one thing,Datasets are designed to handle sets of data .SO using dataset to pass single instances can be a performance concern.You do get better performance overhead if you use scalar values to pass data to the DAL ex Person_Save method accepts Name,ID,DOB as input parameters.the problem with this however is that db schema changes would require method signatures to be modified ,which will affect the calling code.What would you suggest be the best way to work around this?

  • Damon

    Single Instance Calls
    If you are more worried about flexibility then I would suggest using a DataSet (or a DataReader if you so choose) to pass the information because it readily accepts changes.

    If you are more worried about performance, then I would suggest going with passing individual items in via method parameters. And, this goes along with another comment, I would recommend that you have a CodeGeneration utility to help you generate the DAL (I use CodeSmith – http://www.CodeSmithTools.com). That way when something changes you can regenerate your DAL instead of re-coding it by hand. That way you have all the speed and you can still retain maintainability.

  • Anonymous

    Dinesh from India
    Great article buddy, Was getting confused on how these N-tier layers are decided (i knew n-tier purposes.. but no layers)… but i have a very clear picture… i think u have let the cat out of the bag… i do not see many similar articles where the content is so practical and to-the-point that anyone can breakdown Functional requirements into Tiers/layers….. i too have been assigned the task fo presenting a plan for architecture and howto go about the development… this has helped me… i would also like ur email-id.. cos i am tense about my work and may stuck when i begin the 3-tier…. could u help me out??? My email id is “thiru.dinesh@gmail.com”

    Thanks.

  • radu

    Forget about DataSet
    Damon, you’re “adicted” to DataSets but be prepared for the OR/M revolution. Finally Microsoft is going to offer something better than DataSets (which are a dead end in my opinion):

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/ADONETEnFrmOvw.asp

  • Anonymous

    Good work
    Hi Damon,
    Good article.

    Sudheer Palyam

  • Anonymous

    WHat about H/w Setup
    It is indeed an excellent article on mutil-tie concepts and using .NET.

    Would appreciate if you could let us know what kind of h/w do we need to typically set up a n-tier application within the company using LAN.

  • Anonymous

    Data Objects
    I wonder if anyone tried the approach of creating the dedicated <name>_Data objects having just Get() / Set() properties. Those objects in turn could call the DAL passing the SQL strings or better yet database procedure name and a data values as a DataTable by reference?

  • Anonymous
  • Anonymous

    Robustness of content.
    Good article. Thank you. It would have been a great article if you had added code examples for those of us who are new to some of the seemingly abstract terminology. (E.g. the code that comprises a business object.)

  • Anonymous

    How does it relate to my system?
    Overall a good article. However I am trying to relate it to an analytical database system that I have developed using Perl and MS SQL Server tables and SPROCS. Let me explain with a few extra programs removed from the equation. I guess that the Perl program that picks up the request in the form of a flat file is a business object. This program calls a second Perl program that is instructed to read the flat file. I am guessing that this flat file that is read is also a business object. The second Perl program that EXECs MS SQL Server SPROCs after reading the flat file is part of the data access layer I presume. The second Perl program returns the output file to a bin directory. Is this output text file a business object as well?

  • Anonymous

    NHibernate
    Best way to build your DAL is using NHibernate instead of using DataSets!

  • Anonymous

    Great article!
    07/24/2006

    Damon, I just want to say “great article!” I’m with you on everything except the datasets. I prefer your explanation of using common referenced business objects (entities).

    Overall, your article is the design approach of most enterprise ASP.NET applications. I gave you the max stars minus one for the datasets. Stop it man! Use a datareader and pump that data into an object. 🙂

    To the guy who scorned you: First, your comments sound condescending. I bet the company you work for keeps you way in the back, far away from customers, if they keep you at all. Second, get your facts straight, Damon’s article is a very nice example of n-tiered architecture and is completely scalable.

    btw- I still us the “_” on private members too!

    Great Job!

  • Anonymous

    nilesh kumar from india
    hi it is great effort taken to make a tutorial which is very usefull.

  • Anonymous

    What about WF?
    While controller, business layer, data transfer objects (typed dataset or entity collections) coupled with a data layer that may or may not encapsulate enterprise library/DAAB will fit the bill, has anyone seen a standard pattern for MSFT workflow foundation in the enterprise world? There are multiple ways you can use workflow components. One way I thought of using it was with the controller using a command pattern to invoke business logic in the form of rule sets (say the name of a ruleset borrowing from business rule engine paradigm). The underlying command pattern (with callback implementation to fetch the results) will abstract the controller from knowing if the usecase is implemented using WF components or regular business components (as using WF for every usecase would be an overkill).

    I’d welcome other ideas/experiences in this regard.
    -Rohit (from India)

  • Anonymous

    Amazing and Concrete
    Concept of Layer espically the DAL haunted me for many days. The article just removed me from fog and made things much more clear.

  • Anonymous

    Error while trying the demo
    WHile trying the demo in VB 2005 Express edition I am geeting the error Make sure the project file (.csporj) is installed. Does this mean this project reuqires C#. Alternativley is there a VB Version of the same that can be down loaded.

    Thanks

    Srini

  • Anonymous

    Comment
    Comment

  • Anonymous

    Where is the ZIP file??
    I cannot find it. I really want to see the example.

  • Tony Davis

    re: ZIP file
    Scroll right to the top of the article and to the right of the article title you’ll see a speech bubble showing the article’s average rating, number of votes etc. The fourth item in this bubble is a link to the ZIP file containing the Demo app and PDF. I’ll try to find a way to make this link more prominent.

    Best,

    Tony (Simple-talk Ed.)

  • Anonymous

    It all depends
    I think the approach Damon suggests is pretty typical in the web world. It’s simple and easy enough even if you’re just one guy working on a small (30 day) project, but still works well for – as he demonstrates – high traffic sites.

    If you’re in a large org/database with 1000’s of tables like one reader is, then most likely you’re on a huge team that will use a different approach. But then, you’re really at the tail of the curve – this article isn’t necc. meant for your.

    As for DataReader vs. DataSet perf, yeah it’s true that one is faster but it can be argued that in most scenarios the difference isn’t significant for the performance of the app.

    My $.02…

  • Damon

    Regarding Person_GetAll
    If you have a million records, then calling Person_GetAll will definately hurt. It’s really just there as an example. You are more than welcome to add a few parameters to the Person_GetAll method and underlying stored procedure to return pages of data instead of the entire set. For example, Person_GetAll(int page, int recordsPerPage).

  • Anonymous

    Wrong word
    “you should have a descent understanding”

    descent = The act or an instance of descending.

    decent = Characterized by conformity to recognized standards of propriety or morality.

    Feel free to delete my comment.

  • Anonymous

    Passing Dataobjects to the DAL
    A design point I thought you missed and your readers might want to be aware of is that you can pass business objects to your DAL if you adhere to a common interface and then use reflection in the DAL to populate the objects. This is a useful technique and is quite powerful. One drawback is that reflection is slow, but it can be alleviated through the use of dynamic proxies which have speed close to that of pure GET/SET of dataobject properties.

  • Anonymous

    Implement DAL with Generics
    Great article. I have a couple of suggestions.

    a) Implement a Database Factory. Put all the SQL vs. Oracle vs. whatever in it. Then you only need one DAL object. You can base it on the Enterprise library data access block.

    b) Implement an Access Layer using Generics and Reflection like the very first comment at the top.

    class BAL<T, K> {
    T Get(int id) {};
    T Update(T) {};
    T Insert(T) {};
    T Delete(T) {};
    List<T> GetAll() {};
    List<K> GetMembers(T parent) {};

    }

    With the proper naming convention tying Tables, Stored Procedures, and public Properties of the business object, you never have to write another line of access code once you get the first one done.

    c) Don’t use a dataset unless you need the dataset capabilities. I personally return List<T> which is a typesafe array of the records retrieved. Avoids the overhead.

    d) Note that this does not preclude “large table” functions to retrieve First/Next or pages of records instead of all. You just need to ad a context structure to keep your access straight.

    Again. Great article!

    Dave

  • Anonymous

    to Authors
    Plz specify the how to create data access layer

    plz send back to my id

    lukuansari@gmail.com

  • Anonymous

    one more comment
    one more comment to add:

    when you’d need to pass data around across machine boundaries, using remoting for example, dataset can be a performance problem. you’ll need to implement custom serializer, with surrogate classes. but with the shared assembly, it’s less of a problem.

  • Anonymous

    Great article
    Great article Damon. You stated that this is the first in a series of DAL related articles, and I for one can’t wait to see the rest. Keep up the good work!

  • Anonymous

    Good Article
    Its a good article.

  • Anonymous

    Congrats
    Rarely i have seen an article getting so much of feedback..and its great that it has propelled so many guys to respond to it.. i appriciate the article writer that he has achived what an article on internet has to, that is start a debate on the article .. congrats 🙂

    junkpraveen@gmail.com

  • Anonymous

    Thanks
    Newbie here – you’ve cleared up a lot of concepts for me related to this topic. Thanks, and keep up the good work.

    P.S. – anyone who can read your article and come away from it discussing naming conventions (“_”) has a bit too much time on their hands.

  • Anonymous

    oops – forgot
    …. I wanted to also mention, because it seems as though it was ignored, that your ‘article’ came with a all the code, and a 34 page .PDF user’s manual!!

    Makes the (“_”) guy/gal even more ridiculous.

  • Anonymous

    At last !
    It’s about time I find a site with valuable articles like this! I can so relate to this when I try to design a good DAL approach.

    I ususally use custom classes and pass them between the BAL and DAL using datareader in the DAL.

  • Anonymous

    Web Services in DAL instead of relational database…
    This is a great article! Thanks for all the info. Howerver, I have a different situation, what if your data layer talks to a web service and not to the database. You cannot use .Net objects to exchange data between DAL and Busniess Layer… In this case, how would you pass data from the DAL to Business layer? Custom classes is option suggested in this article, and thats what I am thinking of… any other suggestion?
    Thx,

  • Anonymous

    Book
    To the author:
    This is a great article and I would like to know more information on web application design using ASP.net 2.0 and C#. I was wondering if you have written any book on web application design (including the topic covered in this article) with more details on DAL and Business layer designs…
    Thanks,

  • Tony Davis

    re: book
    I’m sure Damon will get back to you on this personally, but you might want to check out his book, Pro ASP.NET 2.0 Website Programming. I was Damon’s editor on the book and I can recommend it without reservation.

    Thanks,

    Tony (Simple-Talk editor)

  • gafoor

    how u r closing the database connection
    Hi Damon
    I read ur article waaa its greate man.and i am also going to implement the same architecture.
    but still i have one doubt..i didn’t seen any line of code that u r not closing the database connection using close() method on ur demo project..can pls explain why…
    gafoor

  • Anonymous

    What about non-primitive data types??
    This article has helped me out a great deal, however I still have one road-block that I’m trying to overcome. I have a normalized SQl Server Express db. So, I’m not sure how to handle a table that has a foreign key to another table. Specifically, I don’t know how to implement the foreign key field in my sub-class MapData() override in the Business Layer.

  • Damon

    RE: How are you closing the connection?
    Hey gafoor,

    It’s interesting that you mention that because it actually reveals a problem with the code that I’m suprised nobody has caught until now!

    If you look in the DataServiceBase class, you will see a section of code that looks like this:

    if (_isOwner)
    {
    cnx.Dispose(); //Implicitly calls cnx.Close()
    }

    This is what closes the connection. Unfortunately, it also removes the connection from the connection pool, which hurts performance. Luckily, it takes all of about two seconds to fix. The code should look like this:

    if (_isOwner)
    {
    cnx.Close();
    }

    There are two places in the DataServiceBase class where you will need to fix this issue.

    My appologies!

  • Damon

    RE: What about non-primitive data types??
    Let’s say you have two objects, Person and Car. Car has a foreign key that links it back to a person.

    Make a method called GetCarsByPesonID(int personId) that returns all of the cars that a person own (you’ll have to make it in the DAL, then make a method off CarCollection that calls iet, etc)

    In the Person class, create a Property that looks something like this:

    private CarCollection _cars = null;
    public Cars
    {
    get
    {
    if(_cars == null)
    {
    _cars = CarCollection.GetByPersonId(this.PersonId);
    }
    return _cars;
    }
    }

    What this does is this: it puts off loading the car collection until you actually call the Car property. And since the property automatically loads when you call it (also known as a Lazy Load), you do not have to account for the “Non-Primative” CarCollection type on the Person class when you are loading the Person data from the database.

  • Damon

    RE: Web Services in DAL instead of relational database…
    Actually, you don’t have a different situation 🙂

    You have data. Your application needs it. Your DAL goes out and gets it (in this case from a Web Service instead of a database), and then presents it back to your application using a non-data-source specific set of types and classes.

    The main difference is that you have a lot more work to do to get your data from a Web Service into non-data-source specific set of types and classes. I would go the custom objects route, as you mentioned.

  • Anonymous

    Very …..Very …Good

    Nice article ………evry one should read this….

  • Anonymous

    Best of both worlds
    I think it’s been mentioned that you can offer the best of both worlds, but you can use escape-hatches in your TableAdapters to gain access to the underlying DataReader and thus provide both functionality using typed datasets and standard DataReader.

  • Anonymous

    very Good Article
    ITS REALLY A VERY GOOD ARTICLE…..

  • Anonymous

    Translating to VB.Net
    I’ve been unsuccessfully trying to translate the following line of code from the sample into VB.Net:

    public abstract class DemoBaseCollection<T> : List<T> where T :
    DemoBaseObject, new()

    Can anyone help me?

  • Anonymous

    re: Translating to VB.Net
    Try:

    Public MustInherit Class DemoBaseCollection(Of T As {New, DemoBaseClass})

  • Anonymous

    re: Translating to VB.Net
    Oops, try:

    Public MustInherit Class DemoBaseCollection(Of T As {New, DemoBaseObject})

  • Anonymous

    Multiple Parameters
    Great article. Thanks for taking the time and effort to write this!

    I’m relatively new with this type of architecture and am modifying your demo to connect to our oracle database. It works great when I only need to pass in or out one parameter. How can I modify it to pass more than one parameter to the ExecuteDataSet?

    Thanks!

  • Anonymous

    Multiple Parameters
    Great article. Thanks for taking the time and effort to write this!

    I’m relatively new with this type of architecture and am modifying your demo to connect to our oracle database. It works great when I only need to pass in or out one parameter. How can I modify it to pass more than one parameter to the ExecuteDataSet?

    Thanks!

  • Anonymous

    Multiple Parameters
    Great article. Thanks for taking the time and effort to write this!

    I’m relatively new with this type of architecture and am modifying your demo to connect to our oracle database. It works great when I only need to pass in or out one parameter. How can I modify it to pass more than one parameter to the ExecuteDataSet?

    Thanks!

  • Anonymous

    Multiple Parameters
    Great article. Thanks for taking the time and effort to write this!

    I’m relatively new with this type of architecture and am modifying your demo to connect to our oracle database. It works great when I only need to pass in or out one parameter. How can I modify it to pass more than one parameter to the ExecuteDataSet?

    Thanks!

  • Damon

    RE: Multiple Parameters
    I’m not completely clear on the exact issue you are having with passing multiple parameters back and forth, but here’s a thought: one of the ExecuteDataSet methods signatures has an output parameter named cmd. You can declare a SqlCommand (or in your specific case an Oracle command object) and then pass it into the method. Since it is an output parameter, you do NOT have to initialize it (just set it to null). When the ExecuteDataSet method finishes executing, the command variable you passed into the method will have a reference to the command object executed in the ExecuteDataSet method. You can then access output parameters on that command object just like you normally would. IT would look something like this:

    SqlCommand cmd;
    ExecuteDataSet(out cmd, “SomeProcName”,
    CreateParameter(“@Param1”, SqlDbType.Int,
    param1, ParameterDirection.InputOutput),
    CreateParameter(“@Param2”, SqlDbType.Int,
    param2, ParameterDirection.InputOutput),
    CreateParameter(“@Param3”,
    SqlDbType.NVarChar, param3),
    CreateParameter(“@Param4”,
    SqlDbType.DateTime, param4));

    //Acquire values from the command
    //(you can do as many of these as you want)
    param1 = (int)cmd.Parameters[“@Param1”].Value;
    Param2 = (int)cmd.Parameters[“@Param2”].Value;

    cmd.Dispose();

    One thing to remember is you have to make sure you are setting your parameter directions appropriately so it can return a value. Otherwise it’s only going to send the value to the stored procedure and not get a value back. Hope that helps!

  • kamesh

    More…
    Hi,

    I am novic to this architecutre concepts. Ppl are talking abt PL,BL,DL etc. I got an high level idea but what it exactly doing. Can you explain this with the help of simple db with few tables. It wud be of gr8 help for ppl like me.
    Thanks in advance… You can reach me at get2kamesh@gmail.com

  • Anonymous

    .NET 1.1 Example
    Good day to you. I know this is just being downright greedy but do you happen to have the example project in .NET 1.1? I know, I am way behind the times but I am stuck using what the company allows. I believe I understand the need for separation between the DataAccess and the Business objects but have used a BaseBusinessObject classes in the DataAccess in the past and would like to get away from this tight coupling.

    The problem I am having is getting my mined to stop thinking with the base class idea and move to the communication you wrote about. I know if I can just “see” the code I will be able to grasp it so I can’t tell you how happy I was to find someone who provided a working example and not just talk about why I should not use a base class approach. But alas, I have the lowly 1.1 (and to boot I am a dreaded VB developer too!). I can handle the C# to VB conversion but could really use an example in the 1.1 framework.

    Any help would be wonderful

    Christopher Lauer, lauer_Christopher@hotmail.com (my junk account)

  • Damon

    RE: More…
    At the top of the page you can download a sample application which includes a LOT of documentation on the code as well as a sample database. Granted, there is only one table in the database, but it should give you an idea of how it works.

  • Damon

    Re: .NET 1.1 Example
    Unfortunately, I don’t have a 1.1 version readily available, but for the most part it should be pretty easy to port the code backwards. Nothing is 2.0 specific except the generics in the collections. The only reason I used generics here is because it made creating the collections a LOT faster in terms of my development time, but it’s by no means a requirement.

    You can create your own 1.1 collection by inheriting from CollectionBase and implementing any necessary items to make the collection usable. Then add the MapObjects methods as discussed in the article.

    The only thing you will run into is that you need to create a new object to add to the collection from within the MapObject methods. In 2.0 we can use generics pretty easily to do this. In 1.1 you will need to make an abstract method in the BaseCollection that, when overriden in your real collection, creates a class instance of the appropriate type.

  • Anonymous

    .NET 1.1 Example
    Sounds good, thank you for your quick posting! Now I have something to do this weekend other then laying around watching football (ha).

  • Anonymous

    VB.NET 1.1
    You posted in your last response to me that I would need to create an abstract (mustInherit) method in the BaseCollection that when overriden in my real collection, creates a class instance of the appropriate type. Is there a way you can give me a quick example? I have the rest of your C# 2.0 code ported over to VB.NET 1.1 except this and I just keep hitting the wall.

  • Damon

    RE: VB.NET 1.1
    Here’s a VB.NET 1.1 Example that demonstrates the concept I was talking about. Sorry if the code is ugly, no idea how the article comments section is going to handle it:

    Public MustInherit Class DemoBaseObject

    Public Overridable Function MapData(ByVal row As DataRow) As Boolean
    Return True
    End Function

    End Class

    Public MustInherit Class CollectionBase
    Inherits System.Collections.CollectionBase

    Public MustOverride Function GetInstance() As DemoBaseObject

    Public Function MapObjects(ByVal ds As DataSet) As Boolean
    If Not ds Is Nothing And ds.Tables.Count > 0 Then
    Return MapObjects(ds.Tables(0))
    Else
    Return False
    End If
    End Function

    Public Function MapObjects(ByVal dt As DataTable) As Boolean
    Clear()

    For i As Integer = 0 To dt.Rows.Count – 1 Step 1

    ‘—> BELOW IS THE CRITICAL LINE <—

    Dim obj As DemoBaseObject = GetInstance()

    ‘Notice that GetInstance is declared as MustOverride,
    ‘which means that any Collection class you implement
    ‘HAS to override this method. Inside the method, you
    ‘create an instance of your custom business object.
    ‘That object is then returned and populated in the
    ‘next call, to obj.MapData(dt.Rows(i)). Of course,
    ‘your custom business object has to inherit from
    ‘DemoBaseObject, or else you get a casting error.

    obj.MapData(dt.Rows(i))
    Next

    End Function

    End Class

    Public Class Person
    Inherits DemoBaseObject

    End Class

    Public Class PersonCollection
    Inherits CollectionBase

    ‘This is the overriden GetInstance method that actually instanaties an appropriate object
    Public Overrides Function GetInstance() As DemoBaseObject
    Return New Person()
    End Function

    End Class

  • Damon

    RE: VB.NET 1.1
    And in the VB code sample below, you will probably want to call your base collection DemoBaseCollection. I accidentally named it BaseCollection which could be confusing since it inherits from System.Collections.BaseCollection class.

  • Anonymous

    VB.NET 1.1
    THANK YOU! I have not tried the code yet but will very soon. I can not tell you how much I appreciate you taking the time to answer my posting. I’m not in 2.0 yet but as a thank you, I’m going out to purchase your 2.0 book this afternoon.

  • Anonymous

    Taking it further
    Damon,
    Good article, Congratulations.
    Wondering if you could provide some thoughts on taking your architecture to next level by for example,
    Using Microsoft Enterprise Library with your proposed architecture. Which component you would need to modify.
    Also if I have to separate the layers physically, i.e. put DAL on a server other than the Web Server, how would you do it (invoking remoting and serialization)

  • Anonymous

    Taking it further
    Damon,
    Good article, Congratulations.
    Wondering if you could provide some thoughts on taking your architecture to next level by for example,
    Using Microsoft Enterprise Library with your proposed architecture. Which component you would need to modify.
    Also if I have to separate the layers physically, i.e. put DAL on a server other than the Web Server, how would you do it (invoking remoting and serialization)

  • Anonymous

    Great article
    i would suggest everyone should read this article ,which helps you understand the great facts about the programming.

  • Anonymous

    Great article, but where are the stored procedures?
    I can’t find the stored procedures anywhere and when i try to open the .mdf-files in VS2K5, i get a SQL Server error telling me that remote connections are probably not allowed.

  • Anonymous

    Nice
    Good Article.

    Although Sir Damon, I suggest you write down a table of comparison(advantages and disadvantages (given a specific point of comparison like speed, efficiency, flexibility etc.) among datasets, xml return types and other methods in DAL implementation. This would allow readers to fully understand the differences for each kind of DAL implementation.

    Thanks for the great effort, sir. ^^

  • Anonymous

    Good one
    Its a good article.
    You would like to check out .nettier templates to generate DAL.
    Though i would like to have codesmith templates for the above DAL.If possible please share the same.
    Thanks:-)
    Dharmesh

  • Anonymous

    Transaction support
    Hey, nice article.. but what about transaction support! like if I have to access to utilitize person product and invoice table in a single transcation!
    How should I go for this without any replication?

    For transactions I have two ways.. either I extend another DataService from ServiceBase or mess my queries in any one of existing(i.e, either in product/invoice/person)..
    Any other way that you would recon!

  • Pablo

    Transaction support
    Great Article.

    Just one thing, I understand how you manage the transactions, the first DAL object creates an instance of IDbTransaction and then you can pass the transaction object calling the Txn property, but i can’t understand how to finish the transaction in order to commit the changes.

    Another thing, what happened if the second DAL object (the one that recieves the transaction object) wants to execute a procedure, but the transaction had already finished?. The first DAL object can only make one execution before closing the transaction?

  • Moin Ahmed

    problem in Transaction Handling
    there shud be txn.Connection.Close() called after transaction ends (either rollback/commit) to close connection safely.

  • Tim

    Databinding
    Demon,
    Nice work. I have not used the combination of Objects and DataTables before. Interesting concept.

    For Dameon or anyone who has made these enhancements…
    I am trying to get a grasp on how this will function together with databinding. Say I have a web gridview databound to my collection of say person. If I use the built in edit, cancel, delete functionality of the databinding controls how am I supposed to get the updated data back to the datatable so that when the save method fires the database transaction can be completed?

    Too me this seems like 1 additional data storage that not really needed. Normally I populate the BO and handle updates, deletes, addnew there then when prompted hit the database.

  • tconrad

    Databinding
    Demon,
    Nice work. I have not used the combination of Objects and DataTables before. Interesting concept.

    For Dameon or anyone who has made these enhancements…
    I am trying to get a grasp on how this will function together with databinding. Say I have a web gridview databound to my collection of say person. If I use the built in edit, cancel, delete functionality of the databinding controls how am I supposed to get the updated data back to the datatable so that when the save method fires the database transaction can be completed?

    Too me this seems like 1 additional data storage that not really needed. Normally I populate the BO and handle updates, deletes, addnew there then when prompted hit the database.

  • Saiju

    Really Good
    Thanks, I got a lot abt tiered architecture.I was searching this like an article.thanks

  • Gonzalo

    Transactions
    Hi,

    I think the article is very good. I am dealing with DAL in an application that uses the CSLA framework and it helped a lot.
    However, I don’t like some parts of the example application.
    In my opinion, the UI layer should not reference the DAL. I think you had to use it in order to work with transactions because the needed of an IDbTransantion object which
    makes your implementation “Database agnostic” but not “Data Source agnostic”, and that is one
    of the principles you defend. Correct me if I’m wrong, please.
    I suggest to manage the transactions in the Business Layer instead of in the UI, so that we hide the DAL from the UI.

    I am looking forward your comments,

    Saludos
    Gonzalo Rodriguez
    gonzalo.rodriguez@gmx.net

  • asem

    I think the article is very good.

    i found this article in codeproject

    http://www.codeproject.com/cs/database/usingllblgen.asp

    asem hassan
    eng_asem78@yahoo.com

  • Anonymous

    Awesome article
    Brilliant article! I’ve been looking all over for exactly the thoughts presented in this article. I’ve been struggling with setting up my DAL and BAL in a “clean” way, and have been going back and forth for days, refactoring my projects without actually writing any code, until I stumbled on this article! (Its a personal project so i can afford to dilly dally :p )

    Well authored article, and hits the nail right on the hammer.

  • Jits

    An ORM
    I would highly recommend using ‘SubSonic’, a fairly young but incredible DAL builder.

    Available here:
    http://www.codeplex.com/actionpack

  • Jits

    RE: An ORM
    Just to clarify, SubSonic is based on the ActiveRecord pattern and works a bit differently to the method proposed in this article.

    And to add my opinion: I think this is a very good idea that introduces and clarifies some important architectural issues. I’d like to add one more thing if I may…

    … there is more than one way to skin a cat, so a large part of software/systems archiecture is about communication and discipline.

    🙂

  • Jits

    RE: An ORM
    Sorry… correction to previous comment

    * idea = article

    (Urgh, I need some sleep 😉

  • Anonymous

    Data Access Layer
    I thought the above example of a DAL sounds more like a Business Object/Domain Object. It seems as if the DAL would be losely coupled with any specific query; instead, returning a dataSet, integer, string, etc. for any request. Such as new Person().Save() would use a DAL like DAL.save() and new Order().Save(), new Product().save() would all use the same DAL assembly. This way the DAL is centralized.

  • Anonymous

    Nice job
    Very well written essay. Good work. Much cleaner than a lot of 1.1 code I’ve seen. I’m using your demo code in a new site right now. Will post back with probs.

    thanks for good essay

    http://patf.net/blogs

  • Anonymous

    Good One
    Good work

  • Anonymous

    three tier architecture
    It is really an excellent article

  • Anonymous

    system.outofmemory exception
    i got msg system.outofmemory when i search records from database
    I am using dataset, change the some colomn value in this dataset and bind this dataset with datagrid.
    How i solved this problem system.outofmemory in this case?
    can i use datareader with such case?

  • Anonymous

    Trree Tier arc.
    This article gives exact architacture and what happen actually in background to the programmers. This is Great!!!

  • Anonymous

    Great Article
    Go Head………..

  • Anonymous

    Gud 1!!
    Its gr8….. well documented …. kep up..

  • Anonymous

    Nice one
    This article is really clear, concise and useful in the real world – thanks so much!

  • Anonymous

    Can I include using
    In your BaseDataService, can I use “using” against the IDisposable objects such as “cnx”.

    e.g.
    using (cnx = new SqlConnection(GetConnectionString())) ;
    {
    cmd.Connection = cnx;
    cmd.CommandText = sqlCmdTxt;
    cnx.Open();
    }
    …etc

    Is that mean I need to comment out lines in the finally?

    //if (IsOwner)
    //{
    // cnx.close(); //cnx.Dispose() Implicitly calls cnx.Close()
    //}

    Would this work?

    Thanks for a great article.

  • Anonymous

    Can I include using
    In your BaseDataService, can I use “using” against the IDisposable objects such as “cnx”.

    e.g.
    using (cnx = new SqlConnection(GetConnectionString())) ;
    {
    cmd.Connection = cnx;
    cmd.CommandText = sqlCmdTxt;
    cnx.Open();
    }
    …etc

    Is that mean I need to comment out lines in the finally?

    //if (IsOwner)
    //{
    // cnx.close(); //cnx.Dispose() Implicitly calls cnx.Close()
    //}

    Would this work?

    Thanks for a great article.

  • Anonymous

    Can I include using
    In your BaseDataService, can I use “using” against the IDisposable objects such as “cnx”.

    e.g.
    using (cnx = new SqlConnection(GetConnectionString())) ;
    {
    cmd.Connection = cnx;
    cmd.CommandText = sqlCmdTxt;
    cnx.Open();
    }
    …etc

    Is that mean I need to comment out lines in the finally?

    //if (IsOwner)
    //{
    // cnx.close(); //cnx.Dispose() Implicitly calls cnx.Close()
    //}

    Would this work?

    Thanks for a great article.

  • Anonymous

    Thanks for this article
    I was looking for same kind of article. Thanks It help me a lot.
    Where can i find more abt ur article.
    My id is jetheajit@yahoo.com

  • Anonymous

    Thanks For A Great Article
    I am a graduate developer and new to asp.net.I have a big interest in improving my code structure and using best practises. i have read alot about the 3 tiers but never really understood how they should be implemented usually i kept all my biz code in a biz folder and likewise for data within my asp.net app. Thank you so much for showing the correct way to structure the tiers using library

  • Anonymous

    i need the data layer and business layer
    hi,this really good . i am really happy with the concept.i need the data layer and business layer samples in vb.net . could you please send me the the samples to my mail id. here i mentioned my mail id.

    Balamurugan.s@shloklabs.com

  • Anonymous

    Why not split business layer into methods and data
    Great article Damon.
    You said that instead of passing business objects to DAL we should pass strings and ints. I have an idea to have a seperate layer where the data members of business objects are defined in shape of classes and then to have instances of those classes in actual business rule layer.
    This way we can also send business objects’s data to DAL and thus avoiding circular referincing and also avoiding long lists of parameters to be sent to DAL.

    haris4pk@hotmail.com

  • Anonymous

    Why not split business layer into methods and data
    Great article Damon.
    You said that instead of passing business objects to DAL we should pass strings and ints. I have an idea to have a seperate layer where the data members of business objects are defined in shape of classes and then to have instances of those classes in actual business rule layer.
    This way we can also send business objects’s data to DAL and thus avoiding circular referincing and also avoiding long lists of parameters to be sent to DAL.

    haris4pk@hotmail.com

  • Anonymous

    Great article Damon
    Excellent artical please keep do send such kind of articals..
    THANKS loooot…..

  • Svetlana

    Very good explanation
    Excellent article and project example

  • Anonymous

    so good explanation regarding data acccess layer
    it’s great to have a detailed architecture of 3 tier

  • bravenewgirl78

    good stuff!!
    Thanks for this article + demo application…

    Have you worked with the Composite in Application Block framework? Maybe you can present an article on that…..

  • http://www.codeauthor.org

    Generating the DAL
    I find that in most cases the DAL code is identical between objects. A good idea is to use a code generator to build the data access layer for any business application. In my tool (http://www.codeauthor.org/) the data access layer is built with some use of data sets but mostly with plain .NET objects. works very well. I think a well built plain object is always more intuitive than a dataset.

  • Anonymous

    dbout
    sir/madam

    i need a query for searching exact keyword from database.Please help me.

  • Rowan

    Use in vb.Net 2.0
    I am attempting to convert this code to vb.net and was wondering if anyone knows the syntax to expose the SqlTransaction as an IDbTransaction?

    <snip>
    { get { return (IDbTransaction)_txn; }
    <snip>

    Also the syntax for this:

    protected void ExecuteNonQuery(string procName,
    params IDataParameter[] procParams)
    {
    SqlCommand cmd;
    ExecuteNonQuery(out cmd, procName, procParams);
    }

    I would really appreciate any help with this. It is time consuming but forcing me to understand it all. But I am having trouble with these bits.

  • Rowan

    Use in vb.Net 2.0
    I am attempting to convert this code to vb.net and was wondering if anyone knows the syntax to expose the SqlTransaction as an IDbTransaction?

    <snip>
    { get { return (IDbTransaction)_txn; }
    <snip>

    Also the syntax for this:

    protected void ExecuteNonQuery(string procName,
    params IDataParameter[] procParams)
    {
    SqlCommand cmd;
    ExecuteNonQuery(out cmd, procName, procParams);
    }

    I would really appreciate any help with this. It is time consuming but forcing me to understand it all. But I am having trouble with these bits.

  • Rowan

    Use in vb.Net 2.0
    I am attempting to convert this code to vb.net and was wondering if anyone knows the syntax to expose the SqlTransaction as an IDbTransaction?

    <snip>
    { get { return (IDbTransaction)_txn; }
    <snip>

    Also the syntax for this:

    protected void ExecuteNonQuery(string procName,
    params IDataParameter[] procParams)
    {
    SqlCommand cmd;
    ExecuteNonQuery(out cmd, procName, procParams);
    }

    I would really appreciate any help with this. It is time consuming but forcing me to understand it all. But I am having trouble with these bits.

  • Anonymous

    Hi
    Here i would Display all the department Details in a Gridview Control

    For that in the dataaccess layer i had written a method that returns
    collection of Department objects

    and i thought of assigning the valuecollection of DictionaryObject as data source
    to Gridview Control

    My method goes like this

    My Business Object is Department Class

    public IDictionary<int,Department> GetallDepartments()

    {

    // mycollection to hold all Dept objects

    IDictionary<int,Department> myCollection = new IDictionary<int,Department>();

    // Getting IDataReader object

    IDataReader myReader=DatabaseFactory.CreateDatabase().ExecutReader(“mystoredProcedure”);

    while(myReader.Read())
    {

    // Building up Dept Object

    Department dept=new

    Department(myReader.GetInt32(0),myReader.GetString(1),myReader.GetString(2));

    // adding Key and Object to collection
    myCollection.add(myReader.GetInt32(0),dept);
    }

    }
    But here iam unable to refer to Department object

    Its throwing an Error “Need reference”

    Please Help

    Thanks in Advance
    Regards
    Mallesh

  • Jayesh

    Hi
    Great article!!!
    some suggestions

    instead of dupliacting DAL for each databse type why dont build a datalayerhelper class which deals with connection,command objeects and communicate to real database.DAL only pass sql query,stored proc,parameters to helper and accept IDatareader,dataset so that any chang ein DB doesnt have to change all tghe DAL objetcs

  • Anonymous

    error when inserting or updating buut delete works ok
    i tried to use this as the framework for what i am working on ,but when i use the person save i get this error…….Unable to cast object of type ‘System.Data.SqlClient.SqlInternalConnectionTds’ to type ‘System.Data.SqlClient.SqlInternalConnectionSmi’.
    does anyone know how to fix this

  • Anonymous

    error when inserting or updating buut delete works ok
    i tried to use this as the framework for what i am working on ,but when i use the person save i get this error…….Unable to cast object of type ‘System.Data.SqlClient.SqlInternalConnectionTds’ to type ‘System.Data.SqlClient.SqlInternalConnectionSmi’.
    does anyone know how to fix this

  • Atif Hashmi

    How to create Single Collection Class using Generics???

    I need solutions from any one about the following issue:

    I need to create getAll method of Student class. I used the technique of this article code and this is working well but I need a generic way such that I DO NOT CREATE MULTIPLE COLLECTIONS for all my entity classes (student,teacher,admin etc) respectively. Instead I need one Generics Collection Class which receives object of generic type. So that this class can act as a collection class for any type of object.
    Is it possible through this:

    **********************************************
    Public Class GenericCollection(Of GenericType)
    Inherits Collections.CollectionBase

    Public Function MapObjects(ByVal dt As DataTable) As Boolean

    End Function

    End Class
    **********************************************

    And from Main method I can create instance of this generics class specifying the type that it will hold object.

    Dim objStudentGenerics As GenericCollection(Of Student) = New GenericCollection(Of Student)()

    But I am having problem in mapObjects method to map these database row values to the student type objects

    Sample code in this article shows this method is expecting to make T type of reference to call individual collection object.
    How can I call the method of objects in this generic class collection?

    I tried this but it gives error:

    Public Function MapObjects(ByVal dt As DataTable) As Boolean
    Clear()
    For i As Integer = 0 To dt.Rows.Count – 1
    Dim obj As New GENERICTYPE()
    obj.MapData(dt.Rows(i))
    Me.Add(obj)
    Next
    Return True
    End Function

    If the issue can be resolved other than this logic, pls let me know.

    Regards,
    S.M. Atif Hashmi

  • Anonymous

    excellent
    I have read lot of articles on creation of Dal But like this article i have never read.

  • Venu

    BEST
    One of the BEST article on DAL.

  • Jhankar rayjit

    Remarks
    Hi Damon;
    its really simple and great.i was also thinking about the design parten and this will help me a lot .it will be highly appricateble if you can provide other kind of layers like this.some people uses 7 layer artitecture and some 3.what will you suggest and why??

    Regrads
    Jhankar Rayjit
    j_rayjit@hotmail.com

  • Anonymous

    Re: i need the data layer and business layer
    There are different approaches and the simple one can be found on this link http://www.asp.net.

  • Anonymous

    Good
    Hi Damon;
    Its realy cool stuff. But i wonder why you are giving support to DataSets this much!. Its always better to create entity objects to pass information between layers. ( Shold be lightweight and it will be always better to use CLS specific data types within this entity class).

  • Anonymous

    Remarks
    Hi Damon,
    The article is too good. It gives a clear description of the 3 Tier Architecture and also the usage of shared assembly.

  • Anonymous

    function used in VB.NET with example..
    I want Fuction which use to Calculate Age By giving D.O.B to till date

  • Anonymous

    .NET Architecture
    Hi,
    This article for .NET architecture is good…
    still i need some more information precisely…
    and i need .NET 3tier architecture detaily.
    Thanks

  • Pradeesh

    Do not avoid the Plain Dot Net Object!
    There are a many superior ORM toolkits available that use reflection to move the data in and out of the business objects. This makes the business object serializable and can be used by your web services. Also the strong-typing means you can be sure your code will work, not at all like a lookup in a DataSet.

    Do not be scared of performance, we have a application GIS and it can achieve hundreds of thousands of transactions a second inserting data points with merely the free edition of the “Diamond Binding” data layer.

    Also you should remember that using at tool such as this you will not have to write or maintain the code. I do not think I am lazy, but my time is more productive on the user interface. If I tell my computer once the schema of my data, I should not need to tell it a second time in my code!

    I would like to see you write an article on the best practice of using this Diamond Binding tool, so I can be sure that I am using its functions correctly as the API is quite large.

  • Anonymous

    casting error SqlInternalConnectionTds
    Wow, this is the only place in Google (on 2007-09-12) that mentioned this error. Anyone know what it means?

    Unable to cast object of type ‘System.Data.SqlClient.SqlInternalConnectionTds’ to type ‘System.Data.SqlClient.SqlInternalConnectionSmi’.

  • smita

    Gr8
    Thanks, it’s very usefull for freshers in development.

  • Sanju Baba

    Excellent Article
    Hi Damon!
    This was awesome article written. I had few doubts prior reading this article. But everything is clear now.
    I personally use Domain Objects and prefer it over datasets.

    Thanks anywas for this article and expect more such articles in the near future.

  • David

    Databinding and the difficulties in applying the theory
    Hi Damon,
    The article is clearly written but didnt touch upon Databinding in .Net.
    I have a read many articles on DAL and Business Tiers and I get convinced by them all, but when it comes to the practicality of writing a WinForm app or ASP.Net app, it seems to fall through.
    Allow me to describe the process I go through – and maybe you can tell me where I go wrong. I will use the terminology from your article because it is vert clear.
    1. I design my DB schema. Tables, relations etc.
    2. I write stored procedures (takes years – I never thought that in 2007 I would still be writing so much error-prone code for so little return, but what wont I do to make things perfect?).
    3. Then, I go and write a DAL. In your terminology I am writing a Business Tier that includes the datasource and database dependent code, and exposes custom business objects only.
    4. I even write a console application to test my Business Tier by using it to populating the DB ,test the consistenct of my exception handling etc, etc.
    5. Four hours later, I am ready to build my WinForms app. I want to use databinding because it seems so effortless. And then I find that I can either configure dataadapters to use SQL (Yachh), to call stored procedures (not much better) and all my business objects are orphaned.
    I know that 2.0 now allows defining a DataObjectSource as a DataSource allowing me to bind to objects in my business layer – but I always encounter some kind of a problem. One reason is definitely my lack of experience, but it seems that another is that MS only just introduced this option and there are holes in the framework.
    It is peculiar to me that 1000s of people I respect are writing articles about the theory of tier separation, and MS who are building the platform we are using to implement the theory – have only just cottoned on.
    Moreover, I dont understand why the 1000s of people (and Daman at the top of the list) dont go back and demonstrate the practicalities of the theory using the tools we actually do have.

    Am I way out – or do I have a point?

    Thanks Daman for any help.
    Daman and all are welcome to contact me on my email too at davidsackstein@yahoo.com

    Thanks!

    David

  • Anonymous

    AppSettings in vb.net
    Hi frnds
    I have problem with using AppSettings in VB.net window application. Plz send me the full syntax code
    at my mail Id : neeraj007in@hotmail.com

    Thanks
    Neeraj

  • Anonymous

    DAL
    Its a very good article

  • kalyan

    feedback
    it is very important to every .net student

  • Anonymous

    _connectionString
    _connectionString

  • Anonymous

    bal concept need
    connection string and insert,update,select and delete source code usin bal concept.

  • Aqeel(aq_eel@hotmail.com)

    Need full n-Tier Archetacture for vb dotnet Aplication
    Sir I need to see how u build n-tier application using vb dotnet please help me in this regards,

    Aqeel

  • joedotnot

    Business Layer has to know about Database?
    Hi Damon, thanks for taking the time for a nicely written article (and code!).

    However it appears to me the Business Layer (e.g. Person.cs) has to know about Database field names (e.g. the MapData method in the Person.cs); I would have thought it was the job of the Data Layer to know exact field names ?

    Anyone subject matter expert want to clarify this?

  • smitaanand

    Excellent
    Hello Sir,

    I am smita a’m searching About Architecture, But Not find Good Information Read Your article Very Carefully find all my hungry queries about Architecture, please write some others Articles those helpful for all of me.
    Thanking You

    Smita Dixit
    Asp dot Net web Application Developer

  • jtomney

    Kudos on article
    Hi Damon,

    Wow, what a nicely done article about creating a DAL! It’s rare to find someone who has taken the time to provide not only the discussion but a simple, well-commented code example as well to illustrate the points.

    Thanks – I really got something from your effort!

    Jim

  • biergarten

    Possible error
    Hi Damon,

    I’m quite new to all this. I am trying to adapt your code to my needs. One comment of a possible error: one of the versions from the ExecuteDataSet and ExecuteNonQuery have the command object as output parameter, for the case we “need to acquire return values and output parameters from the command object after it executes”. But at the end of these methods the object command is disposed, passing out always a null object command, making impossible to “acquire return values and output parameters”.

  • orcus

    Datasets
    Recommending the use of datasets in order to decouple the data persistence implementation from a DAL is just plain bad advise.

    Run a run time profiler over an application that uses datasets for a non-trivial CRUD and compare its cycles to an equivalent datareader execnonquery scenario

  • Rob Meade

    Objects contains collections objects containing collection objects? 🙂
    Hi Damon,

    Firstly, thank you for this article, I’ve read it, re-read it and been using it for the basis of my DAL over the last 3 weeks and I’ve learnt a lot – thank you!

    I hope you’ll still see this comment as I’d really appreciate a bit of help, but I see the last one was posted almost a year ago so I’m not sure – here goes anyway…

    Using your example classes, if for example your Person object contained a collection of lets say Addresses, each being an Address object stored in an Addresses collection in Person, how would I go about populating that using your dataSet/dataTable/dataRow routines?

    From what I can see, we only deal with the end dataRow and always assume only one dataTable (as you specify (0) in dataSet.Tables(0) for example).

    Presumably I’d need something that checked the number of tables returned, and then iterated through those, in the same way you iterate through the multiple rows for objects in your collection example.

    Could you shed a little light on this for me please?

    I have a scenario where I have a Resource object, which can contain a ResourceAccessLevel collection, which holds individual ResourceAccessLevel objects, each of which holds a ResourceAccessLevelProperty collection, containing ResourceAccessLevelProperty objects!

    An example on your above “Person” with multiple “Addresses” would at least get me started..

    Thanks again for a brilliant article, I have already recommended it to three of my colleagues.

    Regards

    Rob Meade

  • virender

    Implementing a N-Layer Architecture on Difference Servers
    hi Damon,

    Our Team developed a project with following layer
    1. Presentiaon layer
    2. Business Layer
    3. Data Transfer object
    4. Data Access Layer

    For good performance we need to implement difference layer on different server. It it possible or not ? If possible then how . please help me.

    Presentiaon layer, – Server1

    Business Layer ,
    Data Transfer object(DTO)- Server2
    (Serializable)

    Data Access Layer – Server3

    Sql Server 2008 – Server4
    (Database Server already running on separate server)

  • IalsoRan

    MS DAAB
    Hello Damon,

    The article is good and I certainly agree that the author has put in lot of patience and work to come up with this detailed approach along with a working code example.

    But some how I feel that using the Enterprise Library 4.1 based MS DAAB (Data Access Application block) should serve the purpose of a DAL.

    Aren’t we reinventing the wheel here?…
    Please do enlighten me if I am wrong.

    Regards,
    Mukesh

  • msr1980

    problem use data access to sybase
    hello Damon Armstrong
    i change the data access to work with sybase
    i change the sql object by sybase object

    Protected Overloads Sub ExecuteNonQuery(ByRef cmd As AseCommand, ByRef procName As String, ByVal ParamArray

    procParams() As IDataParameter)
    ‘Method variablesoutDim cmd As SqlCommand
    Dim cnx As AseConnection = Nothing
    cmd = Nothing
    ‘Avoids “Use of unassigned variable” compiler error
    Try
    ‘Setup command object
    cmd = New AseCommand(procName)
    cmd.CommandType = CommandType.StoredProcedure
    Dim index As Integer = 0
    Do While (index < procParams.Length)
    cmd.Parameters.Add(procParams(index))
    index = (index + 1)
    Loop
    ‘Determine the transaction owner and process accordingly
    If _esIniciante Then
    cnx = New AseConnection(GetConnectionString)
    cmd.Connection = cnx
    cnx.Open()
    Else
    cmd.Connection = _txn.Connection
    cmd.Transaction = _txn
    End If
    ‘Execute the command
    cmd.ExecuteNonQuery()
    Catch ex As Exception
    Throw
    Finally
    If _esIniciante Then
    cnx.Dispose()
    End If
    If (Not (cmd) Is Nothing) Then
    cmd.Dispose()
    End If
    End Try
    End Sub

    when call the function ExecuteNonQuery

    Public Function Cliente_obtenerPorCedula(ByRef clienteID As Integer, ByVal cedula As String) As DataSet
    Dim cmd As AseCommand
    ExecuteNonQuery(cmd, “crm_co_clientecedula”, _
    CreateParameter(“@i_cliente_id”, AseDbType.Integer, clienteID, ParameterDirection.Output), _
    CreateParameter(“@i_cedula”, AseDbType.NVarChar, cedula))
    clienteID = CType(cmd.Parameters(“@i_cliente_id”).Value, Integer)
    cmd.Dispose()
    End Function

    everything runs fine until invoke the code cmd.Parameters(“@i_cliente_id”).Value

    ocurr the exception –> Run-time exception thrown : System.ObjectDisposedException – Cannot access a disposed object.
    Object name: ‘AseCommand: ‘.

    the cmd.dispose –>System.ObjectDisposedException – Cannot access a disposed object.
    the cnx.dispose –>Parameters {“Cannot access a disposed object.Object name: ‘AseCommand: ‘.

    while i comment the code

    ‘If _esIniciante Then
    ‘ cnx.Dispose()
    ‘End If
    ‘If (Not (cmd) Is Nothing) Then
    ‘ cmd.Dispose()
    ‘End If

    what can i do?

  • skinner36

    Loop through the collection
    Thanks for the great article, Damon.

    I have been unsuccessfully trying to find a way to loop the the PersonCollection. Would you mind explaining how I could do this?

    Thanks in advance,

    John

  • Ahmish

    Code Generation Template
    Can we have the code generation template of your Data Access Layer?

  • Ahmish

    Code Generation Template
    Can we have the code generation template of your Data Access Layer?

  • andrewbb

    A good implementation of a DAL/business layer
    IPersistable – 6 one line methods to implement
    PersistentList – abstract class interfacing to stored procedures returning lists of objects.
    Database – static class that uses a SqlServer plugin (Access or Oracle could be added).

    For more with a working example: andrewbbrown.com

    It allows direct SQL, however, can be secured and force all access through stored procs.

  • kerollos

    Thanks
    Thank you very much, it’s very useful.

  • kiquenet

    updates in 2015
    Now, in 2015, any updates about it ? state of art ?