It is well known that there are many Database Persisting libraries out there on the net. But for some reason I don't like them too much... Why is that? because they are very complex. I like to have my objects lightweight and I like to be able to see what happens in the back.
The way I did this uptill now was by making a class with Properties of which the private fields (variables) where initialized by a method (or constructor) that expects an System.Data.IDataReader (for cross database compatability).
I would create a method that would get a DataReader from sql and pass that on with the constructor or with the method. It would result in classes with code like this:
/// <summary>
/// Constructor to create object from database
/// </summary>
/// <param name="idr"></param>
internal Resource(IDataReader idr) {
ParseDataReader(idr);
}
/// <summary>
/// Parse the datareader with the data from the database
/// </summary>
/// <param name="idr"></param>
internal override void ParseDataReader(IDataReader idr) {
_mime = idr[RESMIME] != DBNull.Value ? idr[RESMIME].ToString():null;
_extention = idr[RESEXTENTION] != DBNull.Value ? idr[RESEXTENTION].ToString():null;
_localResource = Convert.ToBoolean(idr[RESLOCALRESOURCE]);
_blobId = Convert.ToInt64(idr[RESBLOBID]);
}
/// <summary>
/// Save a resource to the database
/// </summary>
/// <returns>The <see cref="IDataReader"/> with the new results</returns>
internal static void Save() {
SqlConnection conn = null;
SqlTransaction trans = null;
try {
conn = Data.Connection.Create();
conn.Open();
SqlCommand command = SqlHelper.CreateCommand(conn, Constants.StoredProcedures.SAVE_RESOURCE);
command.CommandTimeout = int.MaxValue;
command.Parameters.Add(new SqlParameter("@ID", this.IsInDatabase ? resource.ID.ToString():null));
command.Parameters.Add(new SqlParameter("@Name", this.Filename));
command.Parameters.Add(new SqlParameter("@Mime", this.Mime));
command.Parameters.Add(new SqlParameter("@Description", this.Description));
command.Parameters.Add(new SqlParameter("@ParentGuid", this.ParentGuid));
command.Parameters.Add(new SqlParameter("@LocalResource", this.LocalResource));
command.Parameters.Add(new SqlParameter("@Size", this.Size));
command.Parameters.Add(new SqlParameter("@VersionMajor", this.VersionMajor));
command.Parameters.Add(new SqlParameter("@VersionMinor", this.VersionMinor));
command.Parameters.Add(new SqlParameter("@AddBytes", this.HasNewStream));
SqlParameter paramBlobId = new SqlParameter("@BlobID",SqlDbType.BigInt);
paramBlobId.Direction = ParameterDirection.Output;
command.Parameters.Add(paramBlobId);
using(IDataReader result = command.ExecuteReader(CommandBehavior.CloseConnection)) {
if(result.Read()) {
this.ParseDataReader(result);
}
}
} finally {
if(conn != null && conn.State == ConnectionState.Open) {
conn.Close();
}
}
}
(Don't try to use the code, it is just an example, it will not do anything)
As you can see this can be a very repetetive task to make all fields persist to the database. And you can imagine what you have to do to add or remove a column:
- Add/Remove The column
- Modify the Save/Update Stored Procedure
- Add/Remove the field from the class (and corresponding property)
- Add/Remove the Sql Parameter from the Save Method
- Add/Remove the code to read from the IDataReader
And, on top of that the class is still full of useless code.
But... I was thinking, since all fields are just in my class, isn't there a way to just 'say', hey you, go to the database.
Well, there is. Using Attributes I can give some properties to a field and tell it what the column name is for that field (for reading) and what stored procedure parameter should be used (for saving).
That sounds cool, but maybe you want to see an example for real:
using System;
using System.Collections.Generic;
using System.Text;
using BusinessLayer.Attributes;
namespace BusinessLayer {
[BusinessLayer.Attributes.DatabasePersistable("SaveProduct", "GetProductById"), Serializable]
public class Product : PersistableDatabaseObject<Product>{
private const int VAT_MIN = 0;
private const int VAT_MAX = 50;
private const string INVALID_VAT_PERCENTAGE = "You supplied an invalid VAT Percentage, it must be between {0} and {1}";
[PersistToDatabase("idProduct", "@ID"), Attributes.PrimaryKey]
private int _id = int.MinValue;
[PersistToDatabase("pName","@Name")]
private string _name = null;
[PersistToDatabase("pPrice","@Price")]
private decimal _price = 0;
[PersistToDatabase("pVatPercentage","@VatPercentage")]
private decimal _vatPercentage = 21;
/// <summary>
/// The database id of this product
/// </summary>
public int ID {
get {
return _id;
}
}
/// <summary>
/// The name of the product
/// </summary>
public string Name {
get {
return _name;
}
set {
_name = value;
}
}
/// <summary>
/// The current price of the product
/// </summary>
public decimal Price {
get {
return _price;
}
set {
_price = value;
}
}
/// <summary>
/// Return the BTW Percentage of this product
/// </summary>
public decimal VatPercentage {
get {
return _vatPercentage;
}
set {
if(value < VAT_MIN || value > VAT_MAX) {
throw new NotSupportedException(string.Format(INVALID_VAT_PERCENTAGE,VAT_MIN,VAT_MAX));
}
_vatPercentage = value;
}
}
/// <summary>
/// Returns the price including Value Added Taxes (BTW)
/// </summary>
public decimal PriceIncludingVat {
get {
return Math.Round(_price * (_vatPercentage / 100M + 1),2);
}
}
}
}
This is the entire class, nothing more, nothing less. But how about saving to database? There is no Save method. and there is no method to get it from the database. Those are in the generic base class:
PersistableDatabaseObject<Product>
That object iterates trough all (private) fields with the PersistToDatabase attribute. It then gets the parameter name or the column name depending on if you are saving or getting from the database.
The stored procedures to do so are assigned to the class itself with the DatabasePersistable attribute.
By assigning a PrimaryKey attribute to the primary key column field of your class the object can automatically get the object from database By ID. The base class thus offers 3 basic methods:
- Save (or update with primary key)
- GetById (get by primary key)
- Reload (get by primary key, and replace local values with fresh values)
- IsInDatabase (check if primary key is larger then zero)
Now, it may be possible to get a product by for example its name:
public static ProductGetByStructuredComment(string name) {
return PersistableDatabaseObject<Product>.GetFromDatabase("GetProductByName", new System.Data.SqlClient.SqlParameter("@Name", name));
}
As you can see this is not much code to just make it possible to get an object by something else than its primary key.
But how can this class give back the object by its correct type? That is why I used Generics. you have to provide the correct type manually when inheriting, and when calling the GetFromDatabase method, as you can see I used PersistableDatabaseObject<Product> a few times. That is the only requirement to make it work correctly, otherwise the incorrect type may come back. You can make mistakes about this, and I haven't tested the outcome of that yet, but be warned that you must always use the correct type.
Now this works nice for a normal class, but getting by name like this last example is not a good example, since the name may not be unique, and my stored procedure will probably give back more than one result except when using:
SELECT TOP 1 * FROM Products WHERE Name = '@Name'
But most likely you want to retrieve a collection from the database. And even that is possible, but that isn't so difficult at all. You just want it to be a collection of the correct type, the rest is already handled inside the Type Class itself.
You can have a generics collection like this:
PersistableDatabaseCollection<OrderItem> orderItemList = PersistableDatabaseCollection<OrderItem>.GetFromDatabase("GetOrderItemsByOrderId", new System.Data.SqlClient.SqlParameter("@ID", order.ID));
or you can make an OrderItemsCollection that inherits from PersistableDatabaseCollection<OrderItem>
Either way it offers these standard methods:
The SaveAll method just iterates trough all children and calls Save and the GetFromDatabase just fills the collection with its children but this time from a Stored Procedure that returns more than one result.
This is all very nice and easy for now, but if you want to know what happens in the back, hold on.
In my example I use ADO.NET to read and write to the database, but it is very easy to alter it and to make it use the Enterprise Library, for example. To do so it may be very usefull to read on.
The key to solving this is using reflection. With reflection it is possible to iterate trough fields (variables) of a class and to read their attributes. It would go too deep to explain Reflection itself, but there is enough information out there that explains reflection. For now it is just important to know what reflection does for us now. I'll show you the code to fill the class with its content:
/// <summary>
/// read the <see cref="IDataReader"/> into the private variables
/// </summary>
/// <param name="idr">The datareader to start reading</param>
public virtual void ReadDataReader(IDataReader idr) {
FieldInfo[] fields = this.GetType().GetFields(BindingFlags.Instance|BindingFlags.NonPublic);
foreach(FieldInfo field in fields) {
Attribute[] attrs = (Attribute[])field.GetCustomAttributes(typeof(Attributes.PersistToDatabase), true);
if(attrs.Length == 1) {
Attributes.PersistToDatabase persistAttribute = (Attributes.PersistToDatabase)attrs[0];
if(field.FieldType.BaseType == typeof(Enum)) {
if(idr[persistAttribute.ColumnName] != DBNull.Value){
foreach(int value in Enum.GetValues(field.FieldType)){
if(value == Convert.ToInt32(idr[persistAttribute.ColumnName].ToString()[0])){
field.SetValue(this, value);
}
}
}
} else {
if(idr[persistAttribute.ColumnName] == DBNull.Value) {
field.SetValue(this, null);
} else {
field.SetValue(this, idr[persistAttribute.ColumnName]);
}
}
}
}
}
Above you see the code that does the Reading trick, I'l explain the parts in detail below:
FieldInfo[] fields = this.GetType().GetFields(BindingFlags.Instance|BindingFlags.NonPublic);
To get an array of all private (BindingFlags.NonPublic) instance (BindingFlags.Instance) i use this method.
The GetFields method is a method of the Type class, since I call this code from inside an instance, I can just get this.GetType()to get the correct type.
Attribute[] attrs = (Attribute[])field.GetCustomAttributes(typeof(Attributes.PersistToDatabase), true);
if(attrs.Length == 1) {
For each field I want to get all its PersistToDatabase attributes, that can be done by providing that type to the GetCustomAttributes method, after which I can just check if the array that is returned has exactly one (if not you declared something wrong, so it is ignored) attribute of the type, if so the code will fill the field with the value that corresponds with the columnname from the database as shown below:
if(idr[persistAttribute.ColumnName] == DBNull.Value) {
field.SetValue(this, null);
} else {
field.SetValue(this, idr[persistAttribute.ColumnName]);
}
It checks if the field is DBNull.Value, and if so it sets the field to null , otherwise it sets it to the result (without casting, so the types must match)
You may have noticed that there is a check to check if the object is of the type Enum, this is done for a specific purpose where I wanted to have characters as the values of my Enum, and I wanted them to be saed as characters to the database. Hence the enum is looped trough to get the correct value.
I could now explain how the Save method works, but you can look at the source, and I guess you will see what happens.... Every field is looped trough, and for each field a SqlParameter is added with the Parametername from the attribute. Then the Stored Procedure for saving is called with those parameters. Thus saving your data to the database. In the Save method you can also see my code for my 'character enums'.
In my code you can also see that I have a Generics dictionary to cache some reflection information in static methods. This is to make sure that Reflection doesn't slow down the process. And it is a Dictionary for the obvious reason that it is located in the base class that must retain information for all classes that inherit from it.
Maybe you don't like the fact of still having to maintain stored procedures, and maybe you prefer generating stored procedures, or not using them at all and let some system create insert, update and select statements. That choice can be entirely up to you, but I prefer to be able to do everything myself, except for the things that never change from an abstract point of view, and therefore I made this library. If you too like to remain in control of your communication with your sql server by maintaining them yourself feel free to use this code since the 5 steps named in the beginning of this article, are now reduced to 3 (of which only one requires a change to your code in one place) and the first two actually really belong together (but they where also named separate at the beginning of the article)
- Add/Remove column
- Modify Stored procedure
- Add field (and corresponding property)
I included the source of my base classes and attributes and a few of my real classes as an example of how you can use it. Hope you like it, please comment on your experience with this code. You can use this code in non commercial projects for free, for use in commercial projects contact me.
DatbasePersistanceLibrary.zip (12,57 KB)