Bracora

Last updated : 11th October 2007

Background

You spend a lot of your time writing very repetitive code just to grind out standard objects that mirror your database tables and all of the CRUD operations that goes along with this. If only there was a way to take care of this and allow us to dive straight into the business logic and UI functionality. Well this article will present one solution.

Strategy

We will be using the Repository Factory Package from the Microsoft Patterns & Practices Team to create our data access layer. The Repository Factory is a CodePlex project that integrates with Visual Studio and it can be used to generate all our business objects, stored procedures and data access code.

Implementation

To implement the features in this article you require the following.

Pre-requisites:
Visual Studio with SP1 (Need to support web applications projects)
A build of Repository Factory Package
Guidance Automation Tools for Visual Studio (GAT and GAX July 2007 CTP)

Note: - This article cannot be used with any of the Visual Studio Express Versions.

In this example we will create a simple web application that can be used to monitor the power capacity of computer racks based in data centres. The database schema is very straightforward and concurrency is not an issue.

Each Rack can accommodate a number of servers to a maximum power capacity. Each Rack is housed in a Cabinet that can contain up to 20 Racks. Each Cabinet is located on a Floor and in a particular Location. Each Rack can be assigned to a customer and may have one or more power sources.

At this stage we have the database designed. So now we want to generate all the grunt code so we can go straight into coding the business rules. So how doe we do it?

Step 1 - Download and Install the Repository Factory Package

Go to http://www.codeplex.com/RepositoryFactory

Click on the releases Tab and download the latest MSI release.

Close any Visual Studio windows and run the MSI. This will Install the Repository Package on your machine.

Step 2 - Install Guidance Automation Extensions and Guidance Automation Toolkit

Download and install both of these Microsoft products to let you run guidance packages with Visual Studio.
GAT and GAX July 2007 CTP Download.

Step 3 - Create the Visual Studio Project Structure

The Repository Package generates all the user code in C#. However if you are a VB.Net developer then don't panic you can still use the classes without having to write a line of C# code.

VB.Net developers: You need to create 2 web applications. 1 C# (Host project for Repository Factory) and 1 VB.
After you have completed all your code generation tasks all you need from the C# project is the web.config file. Copy this to the VB web application and then you can delete the C# web application. Not ideal, but workable.

Create a visual studio solution with the following 3 projects.

1. User Interface Web Project (C# and VB) - Start-up Project
2. Data Access Project (Must be C#)
3. Business Logic Layer Project (C# or VB)

Figure 2.

Step 4 - Enable the Guidance Package for the solution

Within Visual Studio, select Tools and then click on the Guidance Package Manager.

The Following screen will pop up. Click on the Enable/Disable Packages button.

From here you will be presented with packages that are installed on your machine. There may be more or less displayed depending on which packages you have downloaded. Choose the Repository Factory package.

Step 5 - How to use the Repository Factory

Now that you have enabled the package there are a number of 'Recipes' to choose from. I tend to think of Recipes as glorified macros that execute and perform a series of tasks for you. The Repository package contains the following

Our workflow with this package will be:

1. Specify Project Responsibilities
2. Add a database connection
3. Create CRUD stored procedures
4. Create Business Entities
5. Create Data Repository Classes

Step 6 - Specify Project Responsibilities

For the package to generate our data access code and classes it needs to know into which projects will it place the final code. If you think again of a macro scenario then you are simply supplying the parameters. The package need to know :

The Host Project - This will be our web application

The Data Access Project - This will be our PowerCapacityDal class project

The Business Entities Project -  This will also be our PowerCapacityDal class project

*Note: You could create a new project for your business entities but I prefer just to put them in the DAL

Right click the PowerCapacityUI project. You will see that a new 'Repository Factory' menu option has appeared. Select the specify project responsibility option.

From here, select Host Project and click the Finish button.

Next right click the PowerCapacityDal project. Again, select Repository Factory and then specify the project responsibility option. This time choose both Business Entities Project AND Data Access Project. This tells the package that you want to store all the generated code into this project.

Step 7 - Add a database connection

We need to tell the package which database we would like to connect to. This will be stored in the web.config file of the web application. So, right click the PowerCapacityUI project and select Add database connection.

Type in PowerCapacityConnection in the Connection Name field. And click on the builder button (...) to the right side of the Connection String field.

Step through the wizard to create the connection string to the database.

Step 8 - Create CRUD stored procedures

The package will create a sql file that contains scripts to generate all the necessary stored procedures. This file can be run against the database.

Right click on the PowerCapacityDal project and select create CRUD stored procedures.

This will open a wizard. Select the connection name from the drop down list.

Select all the tables that you wish to generate stored procedures for.

The package will generate all the necessary stored procedures. Simply click next to select the default names and CRUD operations.

Specify a file to save the script produced.

We now have a file in the PowerCapacityDal that can be executed against our database. Use SQL Query Analyser or equivalent to execute this script. (see below for SQL 2000 users)

Note for SQL 2000 Users

The SQL generated is in SQL Server 2005 syntax. If your stuck on SQL 2000 don't panic, you can edit this file to get it to run. Remove the RethrowError SP at the top and just find and replace 2005 specific syntax.

Find: sys.objects
Replace: sysobjects

Find: BEGIN TRY
Replace:

Find: END TRY
Replace:

Find: BEGIN CATCH
Replace:

Find: END CATCH
Replace:

Find: EXEC RethrowError;
Replace:

At this stage you should have all your select, update, insert and delete stored procedures created on the database just by going though a few wizards. Easy or what!

Step 9 - Create Business Entities

The next stage is to get the Repository Factory to generate our Business Entities for us. We can then use these objects within our web application to hold and manipulate the data.

Right click the PowerCapacityDal project and select the create business entities from database recipe.

Again, choose the connection name from the drop down list.

From here you can customise which elements are needed within the application. I have just accepted the default to include all fields in the database. Each field is mapped to a property on the Business Entity with the relevant get and set statements created for you.

The final stage is to customise the objects themselves. You may want to change the names and determine if the properties should only have a get method for the given attribute (read only). I have just accepted the default of all.

Click on the Finish button and the Repository Factory will generate all your business entity classes.

You can have a look the class view to see the properties that have been exposed.

Step 10 - Create Data Repository Classes

By this stage we have our Business Entities and all of our database stored procedures. The next stage is to create classes that we can use to execute commands against our database.

Right click the PowerCapacityDal project and select the Create data repository classes from business entities.

This again starts a wizard to walk you through the process. First step is to choose your connection name from the dropdown list. You should be getting used to this by now ;-)

Then, just confirm where the wizard can find the business entities. We chose to put them in the PowerCapacityDal project but as mentioned before you can create a separate project for these objects.

Next, click on the objects that you want to generate repository classes for. I have clicked on them all.

Next you need to tell the Factory which operations you are interested in. By default there is a GetAll,  Insert, Update and Delete.

I also need a GetOne for each object e.g. Bring me back a Cabinet object based on a Cabinet Id that I pass. So click on the Add... button and select Get One.

Next, specify which stored procedure to use to get the one that you want. For me I want to use GetCabinetByCabinetId.

Next, you need to confirm the mapping between output columns and the business object properties. This has been done for me as the field names in the database are the same as my Cabinet business entity.

Click Finish.

You need to do this for each object that you require a GetOne operation. Perhaps in future releases this behaviour will be defaulted. Now you see all the operations that will be generated. Notice the GetCabinetByCabinetId operation below.

When you click Finish this time the Repository Factory will go and create all the classes needed for your data access layer.

You will notice that the Repository Factory has referenced all the necessary dll's that are required. It also uses the Enterprise Library for that actual database calls so this is copied in for you. Each Business Entity get's its own folder with the necessary classes for each CRUD operation. I won't go into the detail here of the programming patterns used as you can just think of it as a black box for now.

So there you have it. A complete data access layer created without you having to write a single line of code. The only thing left is to use it.

Step 11 - Consuming the Data Access Layer

First build the solution to make sure you don't have any errors.

Next,  right click your PowerCapacityBll project add a reference to the PowerCapacityDal project.

Next, Add a reference to the Repository dll. This should be in the following folder:

C:\Program Files\Microsoft Patterns & Practices\Data Access Guidance Package Setup\

Next, in your PowerCapacityBll project add a new class called CabinetManager

Add the following code to get a list of cabinet objects from the database and save a cabinet object back to the database.

using System;
using System.Collections.Generic;
using System.Text;
using PowerCapacityDal;
using Microsoft.Practices.Repository;

namespace PowerCapacityBll
{
    public static class CabinetManager
    { 
        /// <summary>
        /// Get all cabinet objects
        /// </summary>
        /// <returns>A generic list of cabinet objects</returns>
        public static List<Cabinet> GetAll()
        {
            // The Repository creates the proper object based on the interface provided
            // This mapping is held in the web.config file
            ICabinetRepository cabinetRepository = RepositoryFactory.Create<ICabinetRepository>("PowerCapacityConnection");
            // In real life you may want to check the identity of the caller or perform
            // other operations prior to returning the list
            return cabinetRepository.GetAllFromCabinet();
        }

        /// <summary>
        /// Save a cabinet object to the database
        /// </summary>
        /// <param name="cabinet">the cabinet object to save</param>
         public static void Save(Cabinet cabinet)
        {
            ICabinetRepository cabinetRepository = RepositoryFactory.Create<ICabinetRepository>("PowerCapacityConnection");
            cabinetRepository.Save(cabinet);
        }
    } 
}
            
            
            

The Repository Factory 'works out' which class to create based on mapping information held in the web.config. You don't have to worry about this as the Repository Factory deals with it. I show this below for information only.

<repositoryFactory>
    <repositories>
      <add interfaceType="PowerCapacityDal.ICabinetRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.CabinetRepositoryArtifacts.CabinetRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.ICustomerRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.CustomerRepositoryArtifacts.CustomerRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.IFloorRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.FloorRepositoryArtifacts.FloorRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.ILocationRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.LocationRepositoryArtifacts.LocationRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.IPowerSourceRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.PowerSourceRepositoryArtifacts.PowerSourceRepository, PowerCapacityDal" />
      <add interfaceType="PowerCapacityDal.IRackRepository, PowerCapacityDal" repositoryType="PowerCapacityDal.RackRepositoryArtifacts.RackRepository, PowerCapacityDal" />
    </repositories>
  </repositoryFactory>

This is how you can construct your Business Layer. Now that you see how easy it is to call your Data Access methods all we need to do is link up the user interface.

Step 12 - Implement the User Interface

By this stage we have a fully functioning data access layer created entirely without writing a single line of code. We have added a business logic layer that wraps over this allowing you to implement any business rules that are required.

Next, we will create a web page with a GridView control that will bind to a method in the PowerCapacityBll that will in turn call a data access layer method in our PowerCapacityDal project. A nice n-tier design.

Right click the PowerCapacityUI web project and add a reference to the PowerCapacityBll project so we can call its methods.

In the code behind page of the default.aspx page add the following code to the page load event.

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        GridView1.DataSource = PowerCapacityBll.CabinetManager.GetAll();
        GridView1.DataBind();
    }
}

And thats it. What's more, it's up to you to decide how much you want from the Repository Factory package.

Summary

Hopefully this article will encourage you to download and use the Repository Factory package. This package is still work in progress so I'm sure the team would welcome any feedback you have on it but already it provides many benefits.

As you have seen, it's possible to have Visual Studio generate all of your data access code using this package. Think how much time you can save and how little 'grunt' coding you are required to write. At a bare minimum you can use this to generate all of your database stored procedures. You can make the choice whether you want to implement the business entity objects and data access code yourself or extend and modify the code that is generated.

Further Reading

Dave Hayden has a number of good blog entries and screencasts on the Repository Factory. You can find more details at the following links:

David Hayden - Repository Factory Blog 

David Hayden - Patterns And Practices Guidance