Creating an ORM in C# - Part 4

If you have yet to read the first three parts of this series, go back and do so. Not because the code in this section isn't readable without it, but because each article builds on the ones before it. Anyway, today I'm showing the stored procedures that the system defines as well as the insert/saving and selecting of an item.

Anyway, in the last article, we used an IStatement class called CreateTable in our building of the SQL to build each table. It has been modified slightly if you go back and look at it but it's generally the same as in the last article. Anyway, along with creating a table, we can define four functions that the system will need to be able to do, insert, update, delete, and select. With the exception of the select statements, these functions really aren't going to change much and as such I've decided that instead of creating the functions each time, I would simply use stored procedures. So let's look at how the stored procedures are created:

The SQL builder is called by our provider that we created last time. The builder then goes off and creates our stored procedures (note that at this time it doesn't check to see if they exist first, that will be added later). The SQLBuilder then creates an Insert (or Update, Select, etc. object), passing it the class definition as well as the ClassManager. This class then parses out the information that it needs for each property of the class and builds up a string containing the stored procedure.

In the code above, it's the insert stored procedure that is created. You'll notice that it deals with lists in a different manner than the rest of the properties (note that we're still not dealing with classes). In order to represent a list effectively in a database, you really need to separate out that information in a separate table. As such, the list needs its own insert procedure (and its own delete and select stored procedures also). The code for this is within the List class:

It's not pretty doing it this way and I'm sure that I'll figure another way to do this, but it is what it is for now. Anyway, we have similar code for update, delete, and select stored procedures (note that the select versions only deal with selecting an item by the ID field and selecting all of the items). These stored procedures and then thrown back to the SQLBuilder class and it runs it against the database.

So at this point we have our tables, our stored procedures, etc. Now how do we actually insert something into our database? Well in order to do this I've created a helper class called Session:

1:publicclass Session

2: {

3:public Session()

4: {

5: }

6:

7:internal Session(IProvider Provider)

8: {

9: _Provider = Provider;

10: }

11:

12:publicbool Save(object Object)

13: {

14:if (_Provider != null)

15: {

16:return _Provider.Save(Object);

17: }

18:returnfalse;

19: }

20:

21:publicvoid Select<T>(object ID, out T Object)

22: {

23:if (_Provider != null)

24: {

25: Object= (T)_Provider.SelectByID<T>(ID);

26:return;

27: }

28: Object = default(T);

29: }

30:

31:publicvoid Delete(object Object)

32: {

33:if (_Provider != null)

34: {

35: _Provider.Delete(Object);

36: }

37: }

38:

39:private IProvider _Provider=null;

40: }

As you can see, all it really does is acts as a provider proxy between us and the provider. The two functions that you will want to take a look at are Save and Select. The Save function is pretty straightforward but the Select statement takes a bit more explaining. In the Save function's case, we can get the Type of the object at any time and discover what we should be selecting since we have our ClassManager already set up. In the case of the Select though, we don't know what we're suppose to be selecting. So in order to help, we use generics here. That way we can pull the Type class appropriately and pass that to our ClassManager to find out what to do.

You will notice that all it does is passes it on to the SQLBuilder class. The SQLBuilder class in turn simply creates an Insert/Select class depending on what is called and runs a function. Note that at this point Save only inserts items, but when Update is implemented it would check the ID and call the appropriate class here.

The Insert class uses the following functions to actually go about and insert the information:

The setup properties function uses some basic reflection to get each property's value and inserts it as a parameter with the exception of lists (once again, these have to be handled separately. We then run the stored procedure and get back the new ID (since we're auto generating these from the database for now) and setting the ID field of the class. We then go through each list and insert it's information. This is a bit more in depth as we have to get the List property, pull out a couple properties of the List (specifically Count and Item), and then set each item in the list up and run it separately...

But when all is said and done, we can simply call Save and our item is saved to the database. Now what about selecting it back out?

This is fairly similar. We first create our class object (using the Activator class) and then proceed to load the main (non list) portions of the class. You'll notice that there is a special case for floats. The reason for this is that they're singles and we're saving all of our floats as doubles within the database. This could be improved a bit by treating the various types as a bit more different instead of grouping them together.

Once that is loaded, we then go and load our lists. Once again we have to use a bit of reflection here to first create the list and then go and invoke the Add method on that list for each item. But that's the only portions that may cause some confusion.

Anyway, that's it really. At this point we send back our newly created object to the user. I would show you the update and delete code but I'm still working on it. So download the code, take a look, leave feedback, and happy coding.

Comments

James CraigJuly 07, 2010 10:39 AMActually the series was finished a while ago and can be found here (although since writing it, I've revamped the actual downloadable library):http://www.gutgames.com/page/HaterAide-ORM.aspxAs far as a "how to" app/section for using it, I'm going to be uploading a new updated version of it to CodePlex in the next week or so at http://hateraide.codeplex.com. I'll be including a test app in the documentation section there.QamarJuly 07, 2010 7:27 AMHi, Sir you have done nice job. When you expect to complete this ORM model. Will you make a test application to show, how to use this ORM model. Thanks