Recently I wasn’t allowed to use Subsonic but since I use it all the times I needed to have some kind of similar
functionality so I started building a Mini repository helper.
I wanted to be able to decorate my classes with Meta information telling me what tables they belong to, what fields to map the properties to, stating if it was Primary Keys, Nullables and of course what data types.
So I started creating the Attribute Classes, TableAttribute class and TableFieldAttributes class, the first ones states the Tablename the class belongs to and if Updating,Deleting and Inserting is allowed. This so I can map up Views from the database and make sure no Insert,Updates or Deletes are made.
when decoration a class it can look like this:
1: [TableAttributes(TableName="TestTable")]
2: public class TestTable
and the properties looks like this:
1: [TableFieldAttributes(IsPrimaryKey = true, DataBaseFieldName = "TestID", DatabaseFieldType = "int")]
2: public int TestID { get; set; }
The functionality I wanted in the this Mini Repository was to be able to do the basic CRUD functionality, Insert,Update,Delete,Get and GetAll, additionally I wanted to be able to send a SqlDataReader to methods and get back either an object or an list of objects. And I wanted only one method of each operations so I hard to use templates and Reflection in order to be able to do that.
The two workhorse methods is CreateObject<T>(SqlDataReader reader,PropertyInfo[] properties), this one takes an reader as an input and the reflected properties from the object, if null is inputed then the actual reflection would be mad in the procedure. The reason I did this was becuase in case i am using multiple iterations I do not want to do reflection every time, but instead input the data in advance.
This method is responsible for setting the property values in the classes, so here is where all work is done, I only handled the following data types (int,string,datetime,decimal,long,bool) but it is rather easy to add more handling.
The second workhorse method is: public static List<T> CreateObjectList<T>(SqlDataReader reader), this one does all the List making, but is directly useing CreateObject building the objects. In this method a reflection is made that is passed down to CreateObject method.
The Get,GetAll,Insert,Update,Delete methods uses reflection to get the fieldnames from the CustomAttributes in the classes and then building the SQL strings, plus alotof other checking, if there is a Primary Key etc..
1: _properties = typeToInsert.GetProperties();
2: foreach(PropertyInfo prop in _properties)
3: {
4: TableFieldAttributes attrib = Attribute.GetCustomAttribute(prop, typeof(TableFieldAttributes)) as TableFieldAttributes;
5: if (attrib != null)
6: {
7: if(attrib.IsPrimaryKey)
8: {
9: PKField = attrib.DataBaseFieldName;
10: break;
11: }
12: }
13: }
I also included an ExecuteReader method to execute readers, just to simplify the development in the Repositories,
all my new Repositories will inherit this class and thus they will automatically have basic crud functionality.
So an excerpt from the TestTableRepo class would look like this:
1: public TestTable Get(int PrimaryKey)
2: {
3: return Get<TestTable>(PrimaryKey);
4: }
Pretty nice and elegant code !
In order to get a list all that is need is to create a command, execute it, get a SqlDataReader and then pass it to CreateObjectlist, it could typically look like this.
1: List<ListItem> _lst = null;
2: string sql = "SELECT * FROM ListItems WHERE ListTypeID = @ListID";
3:
4: SqlCommand cmd = new SqlCommand();
5: cmd.CommandText = sql;
6: cmd.Parameters.AddWithValue("@ListID", listID);
7:
8: SqlDataReader reader = null;
9:
10: reader = ExecuteReader(cmd);
11: _lst = CreateObjectList<ListItem>(reader);
12: reader.Close();
Simple and nice, that is what I like !
I will attach a sample project below so you can see what it looks like, and in case you want to dabble with it yourself. I only created the bare requirements in this project, but It is not hard to extend it and to build more generic Methods in the base class.
Have a nice day !
/tommy turkijevic
MiniRepo.rar (34.29 kb)