Click here to Skip to main content
15,353,818 members
Articles / Programming Languages / C#
Posted 15 Feb 2006


68 bookmarked

Build a Data Access Layer in less than 15 minutes

15 Feb 20069 min read
In less than 15 minutes you can have a complete Data Access Layer (DAL) using Microsoft's Enterprise Library

This article is in the Product Showcase section for our sponsors at CodeProject. These articles are intended to provide you with information on products and services that we consider useful and of value to developers.

Download Links


In this article you will learn how to build a Data Access Layer using Microsoft's Enterprise Library in less than 15 minutes using CodeSmith and the .NetTiers Template Library.   

Image 1

The Data Access Layer, from here on out referred to as the DAL, is the layer of application functionality that encapsulates all interactions with the database. Typically this type of code is hand written and requires specialized knowledge, not only of .NET, but of the specific data access routines too. Writing the DAL code for an application is one of the most monotonous, time consuming, repetitive, and likely bug-ridden aspects of building software.

For this article we'll use the sample Northwind database in SQL Server, but these examples will work with any database. And of course, after this article you'll no longer view writing the DAL for your application as monotonous and time consuming, but quick, easy, and simple!

If you follow this article at the end you will be able to create a complete best practices Data Access Layer in just under 1 minute (the other 14 minutes are for downloading the software the first time).

Code Generation, a brief overview

Code generation, or the use of software tools to generate code, is not a new concept. In fact, code generation has been around for quite some time. In this article I'm going to use CodeSmith as the tool for generating both the code and T-SQL scripts for the DAL.

CodeSmith is a developer productivity tool that enables developers to use templates to control the formatting and desired code output. Therein is the beauty of CodeSmith - software developers still retain full control over the code created through templates. Templates provide the opportunity for people to create new and interesting code reuse and generation libraries, such as the .NetTiers templates used in this article.

Step 1 – Setup

The first step is to get the necessary tools (CodeSmith) and templates (.NetTiers). CodeSmith is a commercial developer tool, but there is a 30-day free trial we can use for the purpose of this article:

Download and Install CodeSmith

CodeSmith comes with a lot of built-in templates and the next service pack will include the .NetTiers templates shown in this article.

Now that CodeSmith is installed, download the .NetTiers template library:

Download .NetTiers Template Library for CodeSmith

On the .NetTiers site click on the "Latest Download" link in the left-column; next, click on "available for download" at the top of the page. This will take you to another page where you can download the latest Windows MSI installer.

The last step is to ensure you have a SQL Server database setup and have a connection string you can use to connect to the database.

Step 2 – CodeSmith

After downloading and installing CodeSmith you should be able to open it from All Programs | CodeSmith 3.1 | CodeSmith Studio. Before we use the .NetTiers templates you need to familiarize yourself with CodeSmith.

As mentioned in the introduction, CodeSmith is a template driven code generation tool. There is a window on the right of CodeSmith called the Template Explorer. The Template Explorer provides you with a quick and easy way of accessing the templates you've installed or written:

Image 2

To explain the rest of CodeSmith we need a template. Open the Hashtable.cst template from Template Explorer by double-clicking on it.

Hashtable.cst is one of the great sample templates included with CodeSmith and is used to generate strongly typed collections that use the .NET Hashtable type as the base data type. Before we explain the template, let's quickly look at another window in CodeSmith, the Properties window:

Image 3

The Properties window allows you to set properties for the template. When using the Hashtable.cst template we need to set some common elements such as the generated ClassName, ItemType, and KeyType. For example, if you wanted to create a strongly typed collection of Person objects accessed by an integer with a class name of PersonCollection, you would set the ClassName to PersonCollection, the ItemType to Person, and the KeyType to int. You could generate the source for this strongly typed collection now by simply clicking the Run button in CodeSmith (found on the toolbar).

If you took a moment to examine the Hashtable.cst template file you might say to yourself, "This looks a lot like ASP.NET". You'd be correct. CodeSmith templates are modeled after ASP.NET Pages and share many similar ideas. However, whereas ASP.NET Pages are used to generate HTML, CodeSmith Templates are used to generate source files or other text files.

The most important thing to remember about CodeSmith is that CodeSmith does not limit your creativity by forcing you into what code is generated. Since it is template driven the code output is completely controlled by you.

Step 3 – Generate the DAL

Now that you have a basic understanding of CodeSmith I want to show you the .NetTiers templates.

Remember, the idea behind CodeSmith is that it allows you to write code faster, with fewer defects. The .NetTiers templates take that one step further and create a DAL for you that follows all the recommended Patterns & Practices from the team at Microsoft by the same name.

First we need to add the .NetTiers templates to the CodeSmith Template Explorer:

  1. In Template Explorer click on the Open Folder icon to browse for a folder containing templates.
  2. Go to the folder where .NetTiers was installed. On my computer this is:

    >C:\Program Files\SerialCoder\NetTiers 0.9.2 - Caribert\Templates\

  3. Once this folder is created as a shortcut in Template Explorer, expand it and select NetTiers.cst
  4. Now that the .NetTiers templates are installed you need to configure a data source. With the NetTiers.cst template open, open the Properties window and configure a Source Database by clicking on the ellipses:

    Image 4

  5. This opens the Database Picker, we're going to create a new data source so click on the ellipses in the Database Picker to open the Data Source Manager. In the screen shot below is an example of how this should look (choose a name that let's you quickly know what database you're working with):

    Image 5

  6. Test the connection to ensure it works and then back out of these dialog windows by clicking OK. Make sure the new data source is selected.
  7. Finally, set the EntireDatabase property to True, set the NameSpace property to Demo, and set the OutputDirectory property to the directory where you would like the generated files to be located. You will need to create the output directory if it doesn’t exist.

Once you've completed these steps your property window for the NetTiers.cst template should look similar to the screenshot below (changes highlighted):

Image 6

We are not going to show anywhere near the full capabilities of .NetTiers in this article or CodeSmith, but we've now done enough to generate a DAL. Click the Run button in the CodeSmith toolbar to generate the code.

Step 4 – Review the Generated Code

Open Visual Studio .NET and if you followed the directions above you should find a Demo.sln file in the c:\NetTiers\Northwind_Demo\ directory.

Take a moment and browse the solution and project files in Visual Studio:

Image 7

You should find 3 projects within the solution:

  • Demo – the main library that contains the classes you will use within your application to interact with the database.
  • Demo.DataAccessLayer – the library containing the data access implementation code.
  • Demo.DataAccessLayer.SqlClient – the library containing the Microsoft Patterns & Practices classes for working with the database.

Step 5 – Compile …and we're done!

Next, with Visual Studio still open, compile the project.

Congratulations! You now have a Data Access Layer that implements the recommended best practices from the Microsoft Patterns & Practices group for working with a database.

You can now begin using your new Data Access Layer in other Visual Studio .NET projects. For example, below is code to retrieve, update and then delete an employee record.

using Demo.DataAccessLayer.SqlClient;

// Select by primary key 
Employee employee = SqlDataRepository.EmployeeProvider.GetByEmployeeID(13);
employee.FirstName = "John";
employee.LastName = "Doe";

// Save changes

// Delete record

For more information on how to use the generated DAL, take a look at the .NetTiers manual here:

Remember all of the Data Access Layer code was generated by templates, so later when you change your database structure you can simply rebuild the DAL again from the templates. Furthermore, you can even extend the templates with your own ideas, comments, naming conventions, patterns, and so on.


The possibilities for CodeSmith templates are limited only by your own creativity. As I've demonstrated in this article, CodeSmith is an exceptionally powerful developer productivity tool. Coupled with the .NetTiers templates you can write a best practices Data Access Layer for your application in a matter of minutes.

You can also watch a video of this presentation here:

One of the fun features added into version 3.1 was a simple "value calculator". The value calculator provides a conservative estimate of the time saved by using CodeSmith:

Image 8

As shown in the screenshot above, using the .NetTiers templates with CodeSmith generated 91,559 lines of code. Assuming a developer can write approximately 50 lines of code per-hour (had we written this manually) writing the code we generated would have taken 1,800 hours. Taking this a step further and assuming that a developer's time is approximately $60/hour, CodeSmith and .NetTiers together saved you 1,800 hours of your life and $109,000 for your company!

So get CodeSmith… and then go ask for a raise.

CodeSmith and .NetTiers Resources

If you have questions about CodeSmith, please be sure to visit the CodeSmith community portal at:  The CodeSmith community portal is a great resource where people can share templates, ideas, and help each other.

If you are interested in purchasing CodeSmith, please visit the CodeSmith website at There you will find the CodeSmith store where you can purchase license keys to unlock the 30-day trial version. If you have other questions, just email

If you have questions about the .NetTiers templates, please visit the .NetTiers forum at:


This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


About the Author

Rob Howard / CodeSmith
United States United States
No Biography provided

Comments and Discussions

GeneralGood Architecture But ..... Pin
Neosofty3-Oct-07 19:31
MemberNeosofty3-Oct-07 19:31 
GeneralNamespace errors in each layer Pin
phyopyae13-Oct-06 17:04
Memberphyopyae13-Oct-06 17:04 
GeneralRe: Namespace errors in each layer Pin
dongshan2-Nov-06 20:38
Memberdongshan2-Nov-06 20:38 
GeneralSQL Server does not exist or access denied Pin
anhphuong8325-Aug-06 5:02
Memberanhphuong8325-Aug-06 5:02 
Generalno output [modified] Pin
mohamedhosman5-Aug-06 17:29
Membermohamedhosman5-Aug-06 17:29 
GeneralADO.NET Pin
Jaymz6664-May-06 5:03
MemberJaymz6664-May-06 5:03 
QuestionHow to manage DB changes Pin
demiansc3-May-06 15:06
Memberdemiansc3-May-06 15:06 
AnswerRe: How to manage DB changes Pin
Steve Maier15-Aug-06 4:36
professionalSteve Maier15-Aug-06 4:36 
Generaldata migration Pin
B_P7329-Apr-06 17:15
MemberB_P7329-Apr-06 17:15 
Generalthank u Pin
intersense8-Apr-06 20:34
Memberintersense8-Apr-06 20:34 
GeneralAbsolute overkill Pin
neil young12-Mar-06 10:38
Memberneil young12-Mar-06 10:38 
GeneralRe: Absolute overkill Pin
Ryan O'Neill12-Mar-06 22:35
MemberRyan O'Neill12-Mar-06 22:35 
GeneralRe: Absolute overkill Pin
neil young13-Mar-06 21:03
Memberneil young13-Mar-06 21:03 
GeneralRe: Absolute overkill Pin
Fernando A. Gomez F.1-Apr-06 7:01
MemberFernando A. Gomez F.1-Apr-06 7:01 
GeneralDon`t generate code Pin
Repz6-Mar-06 22:20
MemberRepz6-Mar-06 22:20 
GeneralRe: Don`t generate code Pin
jdjohnson21-Mar-06 11:37
Memberjdjohnson21-Mar-06 11:37 
GeneralMore bang for your buck! Pin
netclectic5-Mar-06 23:15
Membernetclectic5-Mar-06 23:15 
QuestionHow can you say Pin
Naveen Karamchetti3-Mar-06 0:48
professionalNaveen Karamchetti3-Mar-06 0:48 
AnswerRe: How can you say Pin
Eric J. Smith6-Mar-06 9:53
MemberEric J. Smith6-Mar-06 9:53 
GeneralDoesn't work Pin
neil young27-Feb-06 10:46
Memberneil young27-Feb-06 10:46 
GeneralRe: Doesn't work Pin
zirtpirt28-Feb-06 2:07
Memberzirtpirt28-Feb-06 2:07 
GeneralRe: Doesn't work Pin
neil young28-Feb-06 11:03
Memberneil young28-Feb-06 11:03 
GeneralRe: Doesn't work Pin
eslamspider13-May-06 6:21
Membereslamspider13-May-06 6:21 
QuestionWhat about means for "optimistic concurrency"? Pin
neil young22-Feb-06 9:39
Memberneil young22-Feb-06 9:39 
AnswerRe: What about means for "optimistic concurrency"? Pin
neil young28-Feb-06 11:05
Memberneil young28-Feb-06 11:05 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.