Tagged as

Stats

Build a Data Access Layer in less than 15 minutes

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

Editorial Note

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.

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

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:

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:

In Template Explorer click on the Open Folder icon to browse for a folder containing templates.

Go to the folder where .NetTiers was installed. On my computer this is:

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

Once this folder is created as a shortcut in Template Explorer, expand it and select NetTiers.cst

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:

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

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.

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

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:

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.

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.

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:

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.

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.

I compiled project using northwind database from localhost.
When I compile in Codesmith, i use following configuration.
Business layer namespace is BL.
DataAccess layer namespace is DAL.
Namespace is Northwind.
There is no err when i compiled in Codesmith.

And i open project from output directory and compile in VS2003.
There are some namespace errs. err description is following.

" The type or namespace name 'Bases' does not exist in the class or namespace 'NORTHWIND.DAL' (are you missing an assembly reference?) "

First of al, many thnx to the author! Excelent article. My question is: What does ADO.NET bring in to this? As I'm watching various Microsoft Demos they never stop talking about ADO.NET. Does this method use ADO.net?

As far as I see it, there are two ways to retrieve data from the database:
1. This way
2. IDE (drag sqlconnection-component, drop, set properties, finished)

With the second method the IDE creates and handles the data. Also I never seen a Microsoft Demonstration with a DAL.

We used something like this at one of my jobs using .NET 2.0. What we had would overwrite the generated code, but anything that you put into the other part of the class would be left there. That way the base part can change and add fields, but the custom things for the table are not changed. Of course if you remove a field and your custom code relies on it you will have a problem. With .NET 1.1 you can do a similar thing with derived classes.

Each to their own, my team are all impressed with it and we have found that it has saved over two months of work in just one project. It all depends on how you use it. I know this because previously I hand coded all of the stored procedure wrappers, tests, documenation and then ran into all of the bugs on very large databases.

It may have the odd bug, but the developers (open source guys) put the effort in and get things solved fairly quickly (still quicker than MS though who have a raft of VS 2005 bugs).

You'll ever have to put your hands on things, regardless of whether the stuff was generated by yourself or by wizards. I appreciate parts of the work, e.g. the simple creation of dataaccess and business layer code. But never try to use the webservice generated by nettiers from other clients than .net (e.g. Office with WebService ToolKit 2.01) No chance. No go.

What about NHibernate? I always use NHibernate... recently, I found a tool called ORM.NET that does the same thing as this stuff. Still, I'm using (free) NHibernate, and I don't see a reason why change my ORM layer...

Regards,
Fernando.

A polar bear is a bear whose coordinates has been changed in terms of sine and cosine.

The Template want to use the extended properties for the database. I have the OracleSchemaProvider working in other templates, but I have the extended properties turned off for it. Your bigest hurdle with this package would be it expects to write SqlClient Providers.

Invalid section name. The section 'netTiersConfigData' does not exist in the requested configuration file 'C:\.....vshost.exe.config' or the file 'c:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Config\machine.config'. Make sure that the 'enterpriseLibrary.configurationSections' configuration section exists in one of the files and that the section 'netTiersConfigData' is defined.
VS2005 setup, simple database, console app to test the library. The above is the result.

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.