Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / C#
Article

Build a Data Access Layer in less than 15 minutes

15 Feb 20069 min read 149.2K   68   33
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

Introduction

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

http://www.codesmithtools.com/

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

http://www.nettiers.com

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
SqlDataRepository.EmployeeProvider.Save(employee);

// Delete record
SqlDataRepository.EmployeeProvider.Delete(employee);

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

http://www.nettiers.com/nettiers-manual-v1.aspx

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.

Conclusion

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:

http://community.codesmithtools.com/r.ashx?id=1

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: http://community.codesmithtools.com/.  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 http://www.codesmithtools.com/. 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 sales@codesmithtools.com.

If you have questions about the .NetTiers templates, please visit the .NetTiers forum at: http://community.codesmithtools.com/forums/16/ShowForum.aspx

License

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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
JokeVery great job John Roland !! Pin
Marc Chouteau17-Feb-06 0:17
Marc Chouteau17-Feb-06 0:17 
GeneralRe: Very great job John Roland !! Pin
neil young5-Mar-06 8:47
neil young5-Mar-06 8:47 
QuestionDid You Know? Pin
FZelle16-Feb-06 0:15
FZelle16-Feb-06 0:15 
AnswerRe: Did You Know? Pin
demiansc2-May-06 15:40
demiansc2-May-06 15:40 
GeneralRe: Did You Know? Pin
FZelle3-May-06 8:47
FZelle3-May-06 8:47 
GeneralDifference with ORM NET Pin
Fernando A. Gomez F.15-Feb-06 12:09
Fernando A. Gomez F.15-Feb-06 12:09 
GeneralI'd love to see Pin
Marc Clifton15-Feb-06 10:33
mvaMarc Clifton15-Feb-06 10:33 
GeneralRe: I'd love to see Pin
jroland17-Feb-06 6:08
jroland17-Feb-06 6:08 
hi please take a look a nettiers.com to see the templates features.
about relations, nettiers templates use them for example to create children collection. on the Dal side, they are used (along with the indexes to create Select methods) and deep save, load methods.

for example with Northwind Db, you have a ProductCollection property on the Category object. and you'll find methods like :
DataRepository.ProductProvider.GetByProductId(xxx); // generated from PK index
DataRepository.ProductProvider.GetByCategoryId(xxx); // generated from FK with the category table

the templates can also be generated against Views and custom stored procedures.



John roland - www.nettiers.com

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.