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 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:
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
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
Person, and the
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.
- 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.
Employee employee = SqlDataRepository.EmployeeProvider.GetByEmployeeID(13);
employee.FirstName = "John";
employee.LastName = "Doe";
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:
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 email@example.com.
If you have questions about the .NetTiers templates, please visit the .NetTiers forum at: http://community.codesmithtools.com/forums/16/ShowForum.aspx