Easier Database Access with Diamond Binding (Part 1)

Introduction

Many people at Code Project will be familiar with ORM and the Active Record model. However environment setup, mapping files, and creation of classes are all significant obstacles to the developer. That said, ORM still offers significant benefits over traditional hand-coded data layers, and there are a range of tools available - from simplistic template-based generators to all inclusive packages - that attempt to ease the strain on the developer.

For this article we decided that Diamond Binding's ease of use and Visual Studio integration made it an appropriate tool to demonstrate the benefits of ORM in a real world situation. We noticed that Diamond Binding was able to work within our normal development process without "taking it over", and the configuration was as simple as ticking boxes on tables we we're interested in. Most importantly, a free edition is avaliable!

We're going to be building up a small example application – one to catalogue cooking recipes. The application will be called RecipeDemo, and it will store cooking recipe instructions, ingredients and a list of units in a SQL Server database. We're going to use an iterative approach over several articles to develop the RecipeDemo, as we want to show how using a tool such as Diamond Binding really speeds up the development process.

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 have access to a Microsoft SQL Server (that you can create a test databases 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] 

Our demonstration database has a name, a place for instructions, and a simple auto-incrementing primary key.

Creating the Business Layer

In Visual Studio, create a new class library project with name DiamondGettingStarted.Data. Select the empty project in the Solution Explorer window and then open the Project menu. You should see a new Diamond Binding menu option, if there's not, ensure Diamond Binding has been installed and enabled in the Add-In Manager (in the Tools menu).

Connect to Database

Select your project in the solution explorer. Underneath the Diamond Binding menu, select Edit Current. Use this dialog to connect to our recipe database by selecting an appropriate OLE DB provider, such as Microsoft OLE DB Driver for SQL Server. If you are supplying a username and password, you will need to ensure you check the "Allow Password Saving" box so Diamond Binding can remember the password when it needs to resynchronise definitions.

Configure Definitions

Once you have connected to the RecipeDemo database, the Diamond Binding project configuration form will be displayed. Leave the General tab settings at their defaults and click the Definitions tab. Tick the checkbox next to the Recipe table to indicate you wish to have definition files created for it. Press Ok and after a short while a new Recipe class will be added to the project containing all the fields in the Recipe table.

Creating the Application Project

Next we'll use our new business layer to demonstrate loading and saving recipe records, we'll just use a console application for this purpose. Add a new console project to the solution, and add a reference to our business project. At this stage, you should also add a reference to the following Diamond Binding runtime assemblies;

• DunnChurchill.Data.DiamondBinding
• DunnChurchill.Data.DiamondBinding.ActiveRecord
• DunnChurchill.Data.DiamondBinding.Core
• NHibernate

These assemblies are located in the C:\Program Files\Dunn & Churchill\Diamond Binding\Runtime folder by default.

Configuring the Runtime

The simplest way to configure the Diamond Binding runtime is with a standard App.config file, add one of these to the console project and paste in the following xml:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
    <section name="activerecord" type="DunnChurchill.Data.DiamondBinding.ActiveRecord.Framework.Config.ActiveRecordSectionHandler,
     DunnChurchill.Data.DiamondBinding.ActiveRecord, Version=1.2.1.3, Culture=Neutral, PublicKeyToken=6e7b61d5b7adddc4" />
  </configSections>
  <connectionStrings>
    <add name="SqlServer" connectionString="{your connection string}"/>
  </connectionStrings>
  <activerecord namingstrategytype="DunnChurchill.Data.DiamondBinding.SqlServerNamingStrategy, DunnChurchill.Data.DiamondBinding,
   Version=1.2.1.3, Culture=Neutral, PublicKeyToken=6e7b61d5b7adddc4">
    <config>
      <add key="hibernate.connection.driver_class" value="NHibernate.Driver.SqlClientDriver" />
      <add key="hibernate.dialect" value="NHibernate.Dialect.MsSql2000Dialect" />
      <add key="hibernate.connection.provider" value="NHibernate.Connection.DriverConnectionProvider" />
      <add key="hibernate.connection.connection_string_name" value="SqlServer" />
    </config>
  </activerecord>
</configuration>

Replace connectionString with a valid SQL Native Client connection string to your database. This will likely be the same as the connection string created for you in the Diamond Binding Project Configuration, without the Provider item.
One final step before we are ready to start persisting business objects, is to initialise the Diamond Binding Runtime. This can be done simply by pasting the following line of code at the start of your application (the using directives are shown here for clarity):

using DunnChurchill.Data.DiamondBinding.ActiveRecord;
using DunnChurchill.Data.DiamondBinding.ActiveRecord.Framework.Config;

...

// Initialize the Diamond Binding Runtime
ActiveRecordStarter.Initialize(typeof(Recipe).Assembly,
ActiveRecordSectionHandler.Instance);

Loading and Saving Objects

Now for the easy part, the base ActiveRecord class provides overloads for saving and loading records; the following code demonstrates how to persist a new Recipe record:

Save

The Save function persists any modifications to an existing record. If the record's primary key is also an Identity column, as is the case with our recipe database, Save is smart enough to execute an insert if the record is new, or executes an update if the record already exists.

//Create recipe and persist it to the database
Recipe recipe = new Recipe();
recipe.Name = "Honey Peanut Granola";
recipe.Instructions = "Test";
recipe.Save();

Create

The Create function inserts a new record into the database. If the primary key is not an Identity column this function should be called instead of Save to indicate the record should be inserted.

//Create recipe and persist it to the database
Recipe recipe = new Recipe();
recipe.Name = "Honey Peanut Granola";
recipe.Instructions = "Test";
recipe.Create();

Find

The Find function loads a record by its primary key.

Recipe recipe = Recipe.Find(1);
Console.WriteLine("Recipe: {0}", recipe.Name);

Find All

The FindAll function returns an array of every record in the table.

Recipe[] recipes = Recipe.FindAll<recipe>();
foreach (Recipe recipe in recipes)
{
   Console.WriteLine("Found recipe '{0}'", recipe.Name);
}
</recipe>

Find By Property

The FindByProperty function returns an array matching a property. Go to the Recipe.cs class, and add the following function:

public static Recipe[] FindByName(string name)
  {
      return Recipe.FindAllByProperty("Name", name);
  }

Using this function is simple:

//Find a specific recipe by name
foreach (Recipe recipe in Recipe.FindByName("Honey Peanut Granola"))
  {
     Console.WriteLine("Found recipe '{0}'", recipe.Name);
  }

Note: Diamond Binding is smart enough not to overwrite the Recipe.cs file, so it's appropriate to place your own business logic there. Remember, when dealing with the Find series of functions, you are querying the object model, so field and class names refer to the names as they appear in code.

Summary

So far we've examined how to use Diamond Binding to map a business object to an SQL table, how to persist a record, and how to add a business logic query to one of the managed classes. In later articles we will look at how relationships are handled, class inheritance (yes, in a database application!), and more advanced queries.