Easier Database Access with Diamond Binding (Part 2)

Introduction

In our first article Easier Database Access with Diamond Binding (Part 1), we made a simple object relational data access layer to load and save recipes. In this example, we're going to extend the Recipe database with a new Ingredients table and a new Units table. Finally we'll explore how Diamond Binding can be used to map one-many relationships.

Contents

• Easier Database Access with Diamond Binding (Part 1)
• Easier Database Access with Diamond Binding (Part 2)

Setup

To follow this article, you will need Microsoft Visual Studio 2005 installed and access to a Microsoft SQL Server (that you can create a test database on), and Dunn & Churchill Diamond Binding.

Creating the Database

First, we need to create the demonstration database:

CREATE TABLE Recipe(
    [Id] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(50)  NOT NULL,
    [Instructions nvarchar](max) NOT NULL,
 CONSTRAINT [PK_Recipe] PRIMARY KEY 
(    [Id] ASC )
) ON [PRIMARY] 

The Unit table contains a list of units, e.g. 'teaspoons', 'cups' and 'grams'.

CREATE TABLE [dbo].[Unit](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) 
 CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([Id] ASC)
)

Table Ingredient contains ingredients for a recipe, in addition to a unit and quantity. For example, representing '3 teaspoons of sugar'.

CREATE TABLE [dbo].[Ingredient](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Quantity] [int] NOT NULL,
    [Unit] [int] NOT NULL REFERENCES [dbo].[Unit] ([Id]),
    [Recipe] [int] NOT NULL REFERENCES [dbo].[Recipe] ([Id]),
 CONSTRAINT [PK_Ingredient] PRIMARY KEY CLUSTERED ([Id] ASC)
)
 

Creating the Business Layer

Once you've finished creating the new database tables, load the Diamond Binding Recipe solution you created in the last tutorial. We're going to tell Diamond Binding about the new tables and relationships, so go to the Diamond Binding menu and select Edit Current then click the Definitions tab again. First ensure all three tables are selected (Unit, Recipe, and Ingredient). Diamond Binding will automatically enable all sensible relationships by default. In this particular scenario, we aren't interested in the list of Ingredients which happen to reference a particular Unit, because the Unit table is just a pre-populated list (such as 'teaspoon'.) So remove this relationship by un-checking the Ingredient.Unit relationship under the Unit table. All the other settings are fine, so just go ahead and press Ok. Diamond Binding will now automatically resynchronise the project definitions, and you should see the new classes appear.

Customising the Business Layer

When we designed the database, we added a foreign key to the Ingredient table, pointing to the Recipe table, because we intended to allow Recipe to have a list of Ingredients. In the Recipe class, Diamond Binding will have created a property Ingredient_Recipe; a strongly typed list of Ingredients. This property is named after the Ingredient table, and that table's foreign key column, Recipe. We'll rename this property to Ingredients, because it's more convenient. Open the user class Recipe.cs and add the following Ingredients property to the class.

public IList<ingredient /> Ingredients
{
get
{
      return Ingredient_Recipe;
}
}

We've successfully renamed the property Ingredient_Recipe to Ingredients by wrapping the Ingredient_Recipe property generated by Diamond Binding. However, the old Ingredient_Recipe property is still publicly accessible. To change this, in the Diamond Binding Project Configuration, edit the definition for the Ingredient.Unit relationship and change the field accessibility from Default to Protected. Note, if you rename a property this way, you still have to refer to the property by its original name when using Hibernate Query Language.

Loading and Saving Objects

Saving a New Recipe

Add the following code to the console application:

//Create a new Recipe – Chicken Fondue
Recipe recipe = new Recipe();
recipe.Name = "Honey Garlic Chicken Fondue";
recipe.Instructions = "Empty";

//Create an new Ingredient - Chicken
Ingredient i = new Ingredient();
i.Name = "Chicken";
i.Quantity = 600;

//Create a new unit - grams
Unit unit = new Unit();
unit.Name = "grams";

i.Unit = unit;

recipe.Ingredients.Add(i);
//Persist to database.
recipe.Save();

Here we've saved a Chicken Fondue recipe in the database containing one ingredient; 600 grams of chicken. All we had to do was set some properties, add a new ingredient to the Ingredients collection and finally call recipe.Save(). We only had to call save on the Recipe class because Diamond Binding knows about the Ingredient.Recipe foreign key relationship, and the cascade setting is set to SaveUpdate.

Summary

In this article, we've seen how Diamond Binding makes the relationship between our business objects very intuitive. Hopefully by now you've got an idea of some of the power Diamond Binding has to offer. In future guides, we'll examine more complex features, such as one-to-many relationships, inheritance, expressions, and cascading updates.