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>
internaloverridevoid 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>
internalstaticvoid 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]
publicclass Product
: PersistableDatabaseObject<Product>{
privateconstint VAT_MIN = 0;
privateconstint VAT_MAX = 50;
privateconststring INVALID_VAT_PERCENTAGE ="You
supplied an invalid VAT Percentage, it must be between {0} and {1}";
[PersistToDatabase("idProduct", "@ID"),
Attributes.PrimaryKey]
privateint _id =int.MinValue;
[PersistToDatabase("pName","@Name")]
privatestring _name =null;
[PersistToDatabase("pPrice","@Price")]
privatedecimal _price = 0;
[PersistToDatabase("pVatPercentage","@VatPercentage")]
privatedecimal _vatPercentage = 21;
///
<summary>
///
The database id of this product
///
</summary>
publicint ID
{
get {
return _id;
}
}
///
<summary>
///
The name of the product
///
</summary>
publicstring Name
{
get {
return _name;
}
set {
_name = value;
}
}
///
<summary>
///
The current price of the product
///
</summary>
publicdecimal Price
{
get {
return _price;
}
set {
_price = value;
}
}
///
<summary>
///
Return the BTW Percentage of this product
///
</summary>
publicdecimal VatPercentage
{
get {
return _vatPercentage;
}
set {
if(value
< VAT_MIN || value > VAT_MAX) {
thrownew NotSupportedException(string.Format(INVALID_VAT_PERCENTAGE,VAT_MIN,VAT_MAX));
}
_vatPercentage = value;
}
}
///
<summary>
///
Returns the price including Value Added Taxes (BTW)
///
</summary>
publicdecimal 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:
publicstatic 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:
SELECTTOP 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>
publicvirtualvoid 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 oneplace)
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)
