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):
- The class inherits from
SQLBase
(to always add an ID).
- The class needs an empty constructor.
- In the constructor, initialize the fields.
- 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);
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:
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.