Modern Database Access Client

This idea uses these main steps:

  • Per each row I call to an action with Dictionary<string, object> as parameter
  • This Dictionary is processed by reflection to fill the inner List
  • Finally I call the ICollectionView refresh.

So let’s begin:

1.- The model

In order to use reflection and have elegant code, we have to define the classes like:


public class Client: BaseModel
{
private string _name;
[Access(Name = "NAME")]
public String Name
{
get { return _name; }
set
{
_name = value;
Notify(() => Name);
}
}

...
}

Where the Access attribute is a custom defined attribute that matches with the access column (like the SQLCompact)


public class AccessAttribute : Attribute
{
public string Name { get; set; }
}

In this case we do not have any server so the model inherits from BaseModel that is simply for Notifypropertychanged.

2.- The Data Access Layer

The Select Method calls to the Action ‘Process’ in order to process the row externally and when it’s finished calls the Action ‘End’


public void Select(string query, Action<Dictionary<string, object>> Process, Action End)
{
OpenConnection();
cmd = new OleDbCommand(query, con);
reader = cmd.ExecuteReader();

Int32 columns, i;
Dictionary<string, object> row;
columns = reader.FieldCount;
while (reader.Read())
{
i = -1; row = new Dictionary<string, object>();
while (++i < columns)
{
row.Add(reader.GetName(i), reader[i]);
}
Process(row);
}
reader.Close();
CloseConnection();
End();
}

3.- The ViewModel

Now we have the information from Access, let’s show an example:


public void SelectBills(Client client)
{
if (client == null) return;
BillsList.Clear();
App.AccessClient.Select(String.Format(AccessQueries.BillsByClient, client.Code),
(b) => BillsList.Add(Reflection.FillProperties<Bill>(b)),
() => Bills.Refresh());
}

where Reflection.FillProperties makes the filling in of the class:


public class Reflection
{
public static Dictionary<string, PropertyInfo> GetPropertiesDict(object input)
{
Dictionary<string, PropertyInfo> ret = new Dictionary<string, PropertyInfo>();
object[] atts;
input.GetType().GetProperties().ToList().ForEach((p) =>
{
atts = p.GetCustomAttributes(typeof(AccessAttribute),false);
if (atts.Length > 0)
{
ret.Add((atts[0] as AccessAttribute).Name, p);
}

});
return ret;
}

public static T FillProperties<T>(Dictionary<string, object> input) where T: class
{
T ret = Activator.CreateInstance<T>();
Dictionary<string, PropertyInfo> Properties = GetPropertiesDict(ret);

input.Keys.ToList().ForEach( k =>
{
if(input[k] != DBNull.Value)
Properties[k].SetValue(ret, input[k], null);
});
return ret;
}

As you see I think is the most next to SQLCompact format, in order to be MVVM compatible. I hope you find this useful because avoids a lot of dirty work.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s