Automatic WPF Mapping to a Database (Access 07 Edition)

• Download source code - 500 KB

Abstract

The idea is to create an automatic DAL and an automatic mapping that creates the storage, tables, SUID (Select, Update ...), and WPF mapping from/to the controls to an instance of the classes.

Prerequisites

• Visual Studio 2008 SP1
• WPF Toolkit Feb. 2010 release, just for the DatePicker control
• MS Access 2007, but not necessary

Introduction

When you want to create an application to storage any kind of information, you sketch the information you want to store, defining a class with its subclasses.

Let's suppose now that you want to create a DAL (Data Access Layer) that can be adapted with multiple data sources (SQL Server, Access, MySQL...). So you have to:

• Create a database.
• Create the tables.
• Create the standard queries.
• Create methods to pass values from the classes to queries.
• Create a mapping from the class values to WPF controls.

Why not try to create all these steps automatically? Hard work, isn't it?

I have created all these steps automatically, and you only have to worry about creating the classes and a list with the mappings. Let's take a look at how it works.

• You create a WPF Form
• Create an instance and configure a mapping class
• The engine converts the Form to the classes
• Create the Business Logic classes for your application
• The instanced classes are managed by the DAL
• The engine manages the DAL

The Classes

To begin, I created a basic class Address and the main class User with an Address inside:

public enum Countries
{
    None,Spain,UK,USA
}

[Description("Addresses")]
public class Address : SQLBase
{
    [Description("Street")]
    public String Street;

    [Description("Number")]
    public Int32 Number;

    public Address() { Street = String.Empty; Number = 0; }

}

[Description("Users")]
public class User : SQLBase
{
    [Description("Name")]
    public String Name;

    [Description("Age")]
    public Int32? Age;

    [Description("Birth")]
    public DateTime Birth;

    [Description("Country")]
    public Countries Country;

    [Description("Address")]
    public Address Address;

    [Description("Active")]
    public Boolean Active;

    [Description("Salary")]
    public Single Salary;

    [Description("Photo")]
    public Bitmap Photo;

    public User()
    {
        Name = String.Empty; Birth = new DateTime(1900, 1, 1); Age = -1;
        Country = Countries.None; Photo = new Bitmap(1, 1); Address = new Address();
    }
}

The important steps in the class creation are (here is the magic):

1. The class inherits from SQLBase (to always add an ID).
2. The class needs an empty constructor.
3. In the constructor, initialize the fields.
4. Add the description that will be used to create tables and field tables.

As you can see, at the moment, I have added many types and nullable values.

First Part: Classes and Database

How to Work

Create a form or a class, then add inside an instance of the ACCESS class, with settings or strings for file and path:

access = new ACCESS(Properties.Settings.Default.FileName, 
                    Properties.Settings.Default.Path);

Creating and Deleting the Database

These steps are easy, I added settings to the form:

access.DeleteDB();
access.CreateDB();

Creating Table Definitions

The methods are generic, so if you define the classes well, you will automatically have the tables. In our case, the class to work is User, so:

access.CreateDefinition<User>();

Adding Items

Now if you instantiate classes, you can easily insert into the database:

User Victoria = new User()
{
    Name = "Victoria",
    Age = 20,
    Country = Countries.Spain,
    Birth = new DateTime(1980, 1, 1),
    Active = true,
    Salary = 1200.24F,
    Address = new Address() { Number = 24, Street = "6th Avenue" }
};
access.Add(Victoria);

//Add a List of Users
List<User> users = new List<User>()
    {
        new User() { Name = "John",  Age = 32,   Country = Countries.UK, 
                     Birth=new DateTime(1965,2,3), Active = true, Salary = 900.31F,
                     Address = new Address() { Number = 5, Street = "Brighton Ave."}
        },
        new User() { Name = "Malcom",   Age = null, Country = Countries.UK,
                     Birth=new DateTime(1979,4,5), Active = false, Salary = 1000.32F,
                     Address = new Address() { Number = 7, Street = "London Ave."}
        },
        new User() { Name = "Victoria", Age = 19, Country = Countries.Spain,
                     Birth=new DateTime(1954,6,7), Active = true, Salary = 1200.2F,
                     Address = new Address() { Number = 24, Street = "Sea Road" }
        }
    };
access.Add(users);

As you can see, you do not have to worry about the IDs, the system manages them.

Retrieving Items

At the moment, I have created:

• Retrieve all

 List<User> allusers = access.SelectAll<User>();

• Retrieve filtered

List<User>  someusers = access.SelectWhere<User> ("Name <>" ,"Victoria");

Updating and Deleting Items

If you have a database retrieved instance, you can update, or delete it:

Victoria = access.SelectWhere<User>("Name =", "Victoria").First();
Victoria.Age = 21; Victoria.Country = Countries.UK; Victoria.Address.Number = 10;
Victoria.Photo = Bitmap.FromFile(Environment.CurrentDirectory + 
                                 @"\images\example.png", true) as Bitmap;
access.Update(Victoria);
access.Delete(Victoria);

Now it has no ID, so you can add it again:

access.Add(Victoria);

You can update by a field:

access.UpdateBy(Victoria,Victoria.Name);

The same is implemented for a list of items.

Second Part: WPF Controls and classes

How to Work

Now you have to create a list with the mapping. Let's suppose a form like:

We can easily write the following:

mapping = new List<ControlBind>();
mapping.CustomAdd(C1_Name,TextBox.TextProperty,"Name");
mapping.CustomAdd(C2_Age,TextBox.TextProperty,"Age");
mapping.CustomAdd(C3_Birth,DatePicker.SelectedDateProperty,"Birth");
mapping.CustomAdd(C4_Country,ComboBox.SelectedItemProperty,"Country");
mapping.CustomAdd(C5_Status,CheckBox.IsCheckedProperty,"Active");
mapping.CustomAdd(C6_Salary,TextBox.TextProperty,"Salary");
mapping.CustomAdd(C7_Photo,System.Windows.Controls.Image.SourceProperty,"Photo");
mapping.CustomAdd(C8_Street,TextBox.TextProperty,"Address.Street");
mapping.CustomAdd(C9_Number,TextBox.TextProperty,"Address.Number");
mapping.CustomAdd(C10_ID,TextBox.TextProperty,"ID");

For enums, I bind them as:

C4_Country.ItemsSource = Enum.GetValues(typeof(WPFBusiness.Countries));
C4_Country.SelectedIndex = 0;

Managing Items

Now we can use the first part with the following methods:

mapping.GetfromMapping<t>()
mapping.SetfromMapping<t>(T item);

Example of use:

private void B_SAdd_Click(object sender, RoutedEventArgs e)
{
  access.Add(mapping.GetfromMapping<User>());
}

And to retrieve the information, for example, getting all:

Int32 idx = 0;
List<Users><user> allusers = null;
private void B_NAll_Click(object sender, RoutedEventArgs e)
{
     allusers = access.SelectAll<user>();
     mapping.SetfromMapping(allusers[idx]);
}

It will fill the controls automatically. To navigate, for instance:

private void B_NNext_Click(object sender, RoutedEventArgs e)
{
    if (allusers != null)
    {
        if (++idx > allusers.Count - 1)
            idx = 0;
        mapping.SetfromMapping(allusers[idx]);
    }
}

private void B_NPrevious_Click(object sender, RoutedEventArgs e)
{
    if (allusers != null)
    {
        if (--idx ;lt& 0)
            idx = allusers.Count-1;
        mapping.SetfromMapping(allusers[idx]);
    }
}

Third Part: Child Classes

How to Work

Let's suppose we want to add many to single classes, for instance, a list of tasks that will be appended to a user.

Defining the Class

Instead of inheriting from SQLBase, it inherits from SQLChild:

[Description("Tasks")]
public class Task : SQLChild
{
    [Description("Description")]
    public String Description;

    public Task() { Description = String.Empty; ID = -1; ParentID = -1; }
}

As you can see, ParentID will be the relationship.

Defining the Mapping

To add records of this class in the database, we define a mapping similar to the user mapping:

taskmapping = new List<controlbind>();
taskmapping.CustomAdd(C11_Task, TextBox.TextProperty, "Description");

To simplify it has only description

Adding Child Objects to the Parent

To add objects to one of the parents:

private void B_TaskAdd_Click(object sender, RoutedEventArgs e)
{
    User actual = mapping.GetfromMapping<user>();
    if (actual.ID > 0)
    {
        Task task = taskmapping.GetfromMapping<task>();
        access.AddChild<Task,User>(task, actual);
    }
   ShowTasks(actual);
}

As you can see, we assign the task to the actual user.

Retrieving Child Objects of the Parent

To retrieve the objects to one of the parents, we simply specify who the parent is.

private void ShowTasks(User user)
{
    TaskList.Items.Clear();
    tasks = access.SelectChilds<Task,User>(user);
    tasks.ForEach(t => TaskList.Items.Add(t.Description));
}

And that's all. Now you can define many to single objects.

Improvements

• Be able to store images bigger than 10 KB
• Be able to make n-level nesting in WPF (the DAL part is done)
• DAO methods: Better conversion of Recordset to DataTable

History

• 1.0: First release, March 2010.