Click here to Skip to main content
13,145,068 members (66,426 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 8 Mar 2010

Automatic WPF Mapping to a Database (Access 07 Edition)

, 10 Mar 2010
Rate this:
Please Sign up or sign in to vote.
Automatizing creation of a database, tables, SUID actions, and easy WPF to class mapping.


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.


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


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

public class Address : SQLBase
    public String Street;

    public Int32 Number;

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


public class User : SQLBase
    public String Name;

    public Int32? Age;

    public DateTime Birth;

    public Countries Country;

    public Address Address;

    public Boolean Active;

    public Single Salary;

    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, 

Creating and Deleting the Database

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


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:


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" }

//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" }

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;

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


You can update by a field:


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>();

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.SetfromMapping<t>(T item);

Example of use:

private void B_SAdd_Click(object sender, RoutedEventArgs e)

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>();

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;

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

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:

public class Task : SQLChild
    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);

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)
    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.


  • 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


  • 1.0: First release, March 2010.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Juan Pablo G.C.
Software Developer Expediteapps
Spain Spain
I'm Electronic Engineer, I did my end degree project at Astrophysical Institute and Tech Institute. I'm HP Procurve AIS and ASE ,Microsoft 3.5 MCTS
I live in Canary Islands ,developing customized solutions

Deeply involved in Xamarin Forms LOB (including Azure Cloud with offline support, custom controls, dependencies) projects, WP8.1 & W10 projects, WPF modern styled projects. Portable libraries like portablePDF, portableOneDrive, portableReports and portablePrinting (using Google Printing API).

Web and apps showcase at:

Take a look to my blog

You may also be interested in...

Comments and Discussions

GeneralA point before you go any further. Pin
Pete O'Hanlon9-Mar-10 1:44
mvpPete O'Hanlon9-Mar-10 1:44 
GeneralRe: A point before you go any further. Pin
Juan Pablo G.C.9-Mar-10 4:10
memberJuan Pablo G.C.9-Mar-10 4:10 
GeneralRe: A point before you go any further. Pin
Pete O'Hanlon9-Mar-10 4:38
mvpPete O'Hanlon9-Mar-10 4:38 
GeneralInteresting Pin
Petr Pechovic8-Mar-10 11:09
memberPetr Pechovic8-Mar-10 11:09 
GeneralRe: Interesting Pin
Juan Pablo G.C.9-Mar-10 0:55
memberJuan Pablo G.C.9-Mar-10 0:55 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 10 Mar 2010
Article Copyright 2010 by Juan Pablo G.C.
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid