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
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 Ingredient
s 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:
Recipe recipe = new Recipe();
recipe.Name = "Honey Garlic Chicken Fondue";
recipe.Instructions = "Empty";
Ingredient i = new Ingredient();
i.Name = "Chicken";
i.Quantity = 600;
Unit unit = new Unit();
unit.Name = "grams";
i.Unit = unit;
recipe.Ingredients.Add(i);
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.