.NET Application Architecture: the Data Access Layer

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:

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:

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!

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.

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.

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.

Subject:

Choices

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, July 15, 2006 at 11:49 AM

Message:

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.

Subject:

Standards, please

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, July 15, 2006 at 4:11 PM

Message:

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.

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.

Subject:

Still?

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, July 15, 2006 at 4:20 PM

Message:

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.

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.

Subject:

Thanks, I was looking for this...

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, July 15, 2006 at 6:58 PM

Message:

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.

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.

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? :)

Person_GetByPersonID is not a private method.See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpgenref/html/cpconnamingguidelines.asp

Subject:

What link to a ZIP file

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 17, 2006 at 9:54 AM

Message:

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

Subject:

Link - followup

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 17, 2006 at 9:55 AM

Message:

Just found it. Now I know I was blind - and illiterate. Blessings.

Subject:

Better ways

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 17, 2006 at 11:57 AM

Message:

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.

Subject:

Another plus not mentioned for DataSets...

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 17, 2006 at 1:11 PM

Message:

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

Subject:

Typed Datasets

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 17, 2006 at 1:41 PM

Message:

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.

Subject:

Data Readers and early release of DB resources

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 17, 2006 at 9:53 PM

Message:

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

Subject:

Application

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, July 19, 2006 at 4:03 AM

Message:

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

Subject:

sra

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, July 19, 2006 at 12:08 PM

Message:

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?

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

Subject:

Dinesh from India

Posted by:

Anonymous (not signed in)

Posted on:

Friday, July 21, 2006 at 8:50 AM

Message:

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"

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.

Subject:

Data Objects

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 24, 2006 at 7:59 AM

Message:

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?

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

Subject:

How does it relate to my system?

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 24, 2006 at 10:44 AM

Message:

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?

Subject:

NHibernate

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 24, 2006 at 4:01 PM

Message:

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

Subject:

Great article!

Posted by:

Anonymous (not signed in)

Posted on:

Monday, July 24, 2006 at 5:31 PM

Message:

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!

Subject:

nilesh kumar from india

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, July 25, 2006 at 12:45 AM

Message:

hi it is great effort taken to make a tutorial which is very usefull.

Subject:

What about WF?

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, July 26, 2006 at 3:06 PM

Message:

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)

Subject:

Amazing and Concrete

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, July 27, 2006 at 3:56 AM

Message:

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

Subject:

Error while trying the demo

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, August 2, 2006 at 9:34 AM

Message:

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.

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

Subject:

It all depends

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, August 10, 2006 at 5:36 PM

Message:

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.

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

Subject:

Wrong word

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, August 17, 2006 at 3:02 AM

Message:

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

Subject:

Passing Dataobjects to the DAL

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, August 17, 2006 at 7:37 AM

Message:

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.

Subject:

Implement DAL with Generics

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, August 17, 2006 at 9:37 AM

Message:

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.

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

Subject:

to Authors

Posted by:

Anonymous (not signed in)

Posted on:

Monday, August 21, 2006 at 2:04 AM

Message:

Plz specify the how to create data access layer

plz send back to my id

lukuansari@gmail.com

Subject:

one more comment

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, August 23, 2006 at 2:02 PM

Message:

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.

Subject:

Great article

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, August 23, 2006 at 5:38 PM

Message:

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!

Subject:

Good Article

Posted by:

Anonymous (not signed in)

Posted on:

Friday, August 25, 2006 at 3:59 AM

Message:

Its a good article.

Subject:

Congrats

Posted by:

Anonymous (not signed in)

Posted on:

Sunday, August 27, 2006 at 7:33 AM

Message:

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

Subject:

Thanks

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, August 29, 2006 at 10:15 PM

Message:

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.

Subject:

oops - forgot

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, August 29, 2006 at 10:18 PM

Message:

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

Subject:

At last !

Posted by:

Anonymous (not signed in)

Posted on:

Friday, September 1, 2006 at 7:42 AM

Message:

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.

Subject:

Web Services in DAL instead of relational database...

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, September 5, 2006 at 12:41 PM

Message:

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,

Subject:

Book

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, September 5, 2006 at 12:52 PM

Message:

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,

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.

Hi DamonI 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

Subject:

What about non-primitive data types??

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, September 19, 2006 at 1:38 PM

Message:

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.

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.

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.

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.

Subject:

Very .....Very ...Good

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, September 27, 2006 at 4:35 AM

Message:

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

Subject:

Best of both worlds

Posted by:

Anonymous (not signed in)

Posted on:

Monday, October 2, 2006 at 4:52 PM

Message:

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.

Subject:

very Good Article

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, October 10, 2006 at 1:35 AM

Message:

ITS REALLY A VERY GOOD ARTICLE.....

Subject:

Translating to VB.Net

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, October 24, 2006 at 2:18 PM

Message:

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

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!

Subject:

Multiple Parameters

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, October 25, 2006 at 7:11 AM

Message:

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!

Subject:

Multiple Parameters

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, October 25, 2006 at 8:08 AM

Message:

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!

Subject:

Multiple Parameters

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, October 25, 2006 at 8:46 AM

Message:

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?

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:

//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!

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

Subject:

.NET 1.1 Example

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, November 2, 2006 at 12:44 PM

Message:

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.

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.

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.

Subject:

.NET 1.1 Example

Posted by:

Anonymous (not signed in)

Posted on:

Friday, November 3, 2006 at 10:20 AM

Message:

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

Subject:

VB.NET 1.1

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, November 9, 2006 at 7:35 AM

Message:

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.

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

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.

Subject:

VB.NET 1.1

Posted by:

Anonymous (not signed in)

Posted on:

Monday, November 13, 2006 at 8:42 AM

Message:

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.

Subject:

Taking it further

Posted by:

Anonymous (not signed in)

Posted on:

Monday, November 13, 2006 at 6:08 PM

Message:

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)

Subject:

Taking it further

Posted by:

Anonymous (not signed in)

Posted on:

Monday, November 13, 2006 at 6:10 PM

Message:

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)

Subject:

Great article

Posted by:

Anonymous (not signed in)

Posted on:

Friday, November 17, 2006 at 6:46 AM

Message:

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

Subject:

Great article, but where are the stored procedures?

Posted by:

Anonymous (not signed in)

Posted on:

Sunday, November 26, 2006 at 3:33 PM

Message:

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.

Subject:

Nice

Posted by:

Anonymous (not signed in)

Posted on:

Sunday, November 26, 2006 at 9:07 PM

Message:

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

Subject:

Good one

Posted by:

Anonymous (not signed in)

Posted on:

Sunday, December 10, 2006 at 5:25 AM

Message:

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

Subject:

Transaction support

Posted by:

Anonymous (not signed in)

Posted on:

Friday, December 15, 2006 at 10:21 AM

Message:

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!

Subject:

Transaction support

Posted by:

Pablo (not signed in)

Posted on:

Saturday, December 23, 2006 at 11:53 PM

Message:

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?

Subject:

problem in Transaction Handling

Posted by:

Moin Ahmed (not signed in)

Posted on:

Wednesday, January 3, 2007 at 8:13 AM

Message:

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

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.

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.

Subject:

Really Good

Posted by:

Saiju (not signed in)

Posted on:

Thursday, January 4, 2007 at 5:22 AM

Message:

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

Subject:

Transactions

Posted by:

Gonzalo (not signed in)

Posted on:

Saturday, January 6, 2007 at 11:51 AM

Message:

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 whichmakes 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,

SaludosGonzalo Rodriguezgonzalo.rodriguez@gmx.net

Subject:

I think the article is very good.

Posted by:

asem (not signed in)

Posted on:

Wednesday, January 10, 2007 at 1:22 AM

Message:

i found this article in codeproject

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

asem hassaneng_asem78@yahoo.com

Subject:

Awesome article

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, January 16, 2007 at 6:55 PM

Message:

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 )

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.

Subject:

Nice job

Posted by:

Anonymous (not signed in)

Posted on:

Monday, February 19, 2007 at 11:06 AM

Message:

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

Subject:

Good One

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, February 24, 2007 at 4:14 AM

Message:

Good work

Subject:

three tier architecture

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, February 28, 2007 at 11:53 PM

Message:

It is really an excellent article

Subject:

system.outofmemory exception

Posted by:

Anonymous (not signed in)

Posted on:

Monday, March 12, 2007 at 5:24 AM

Message:

i got msg system.outofmemory when i search records from databaseI 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?

Subject:

Trree Tier arc.

Posted by:

Anonymous (not signed in)

Posted on:

Monday, March 26, 2007 at 5:36 AM

Message:

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

Subject:

Great Article

Posted by:

Anonymous (not signed in)

Posted on:

Friday, March 30, 2007 at 7:27 AM

Message:

Go Head...........

Subject:

Gud 1!!

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, April 3, 2007 at 12:43 AM

Message:

Its gr8..... well documented .... kep up..

Subject:

Nice one

Posted by:

Anonymous (not signed in)

Posted on:

Sunday, April 15, 2007 at 3:38 PM

Message:

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

Subject:

Can I include using

Posted by:

Anonymous (not signed in)

Posted on:

Monday, April 23, 2007 at 9:34 AM

Message:

In your BaseDataService, can I use "using" against the IDisposable objects such as "cnx".

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

Subject:

Thanks For A Great Article

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, May 2, 2007 at 9:34 AM

Message:

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

Subject:

i need the data layer and business layer

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, May 3, 2007 at 7:23 AM

Message:

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

Subject:

Why not split business layer into methods and data

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, May 5, 2007 at 1:02 PM

Message:

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

Subject:

Why not split business layer into methods and data

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, May 5, 2007 at 1:12 PM

Message:

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.

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

Subject:

Generating the DAL

Posted by:

http://www.codeauthor.org (not signed in)

Posted on:

Thursday, June 14, 2007 at 11:32 PM

Message:

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.

Subject:

dbout

Posted by:

Anonymous (not signed in)

Posted on:

Monday, June 18, 2007 at 3:39 AM

Message:

sir/madam

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

Subject:

Use in vb.Net 2.0

Posted by:

Rowan (not signed in)

Posted on:

Friday, June 22, 2007 at 6:52 PM

Message:

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?

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

Subject:

Posted by:

(not signed in)

Posted on:

Thursday, June 28, 2007 at 12:41 PM

Message:

Subject:

error when inserting or updating buut delete works ok

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, June 28, 2007 at 2:45 PM

Message:

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

Subject:

error when inserting or updating buut delete works ok

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, June 28, 2007 at 2:46 PM

Message:

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

Subject:

How to create Single Collection Class using Generics???

Posted by:

Atif Hashmi (not signed in)

Posted on:

Monday, July 2, 2007 at 5:19 AM

Message:

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:

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

Subject:

excellent

Posted by:

Anonymous (not signed in)

Posted on:

Thursday, July 5, 2007 at 2:39 AM

Message:

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

Subject:

BEST

Posted by:

Venu (not signed in)

Posted on:

Friday, July 20, 2007 at 11:45 AM

Message:

One of the BEST article on DAL.

Subject:

Remarks

Posted by:

Jhankar rayjit (not signed in)

Posted on:

Monday, July 23, 2007 at 4:49 AM

Message:

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??

RegradsJhankar Rayjitj_rayjit@hotmail.com

Subject:

Re: i need the data layer and business layer

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, August 11, 2007 at 6:13 AM

Message:

There are different approaches and the simple one can be found on this link www.asp.net.

Subject:

Good

Posted by:

Anonymous (not signed in)

Posted on:

Friday, August 17, 2007 at 5:20 AM

Message:

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

Subject:

Remarks

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, August 28, 2007 at 5:03 AM

Message:

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

Subject:

function used in VB.NET with example..

Posted by:

Anonymous (not signed in)

Posted on:

Saturday, September 8, 2007 at 8:59 PM

Message:

I want Fuction which use to Calculate Age By giving D.O.B to till date

Subject:

.NET Architecture

Posted by:

Anonymous (not signed in)

Posted on:

Tuesday, September 11, 2007 at 5:50 AM

Message:

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

Subject:

Do not avoid the Plain Dot Net Object!

Posted by:

Pradeesh (not signed in)

Posted on:

Wednesday, September 12, 2007 at 2:02 AM

Message:

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.

Subject:

casting error SqlInternalConnectionTds

Posted by:

Anonymous (not signed in)

Posted on:

Wednesday, September 12, 2007 at 2:51 PM

Message:

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

Subject:

Gr8

Posted by:

smita (not signed in)

Posted on:

Friday, October 26, 2007 at 12:33 AM

Message:

Thanks, it's very usefull for freshers in development.

Subject:

Excellent Article

Posted by:

Sanju Baba (not signed in)

Posted on:

Friday, October 26, 2007 at 6:41 AM

Message:

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.

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

Sir I need to see how u build n-tier application using vb dotnet please help me in this regards,

Aqeel

Subject:

Business Layer has to know about Database?

Posted by:

joedotnot (not signed in)

Posted on:

Sunday, December 30, 2007 at 10:41 PM

Message:

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 ?

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

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.

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

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.