Build Your Own Data Access Layer: Enabling Bulk Inserts
Note: This post is part of a series. Be sure to read the introduction here.
I know I wrapped up the series already, but I just had to add the ability to do bulk inserts to this data layer so I figured I'd might as well post about it. Ayende already talked about how to enable the ability to batch inserts (or updates and deletes) here and here so I'm going to skip that part. I used the exact same trick and created a PublicSqlCommandSet class which wraps the hidden SqlCommandSet class. Again, if you have no idea what I'm talking about in that last sentence then you need to read Ayende's 2 posts that I just linked to ;)
After that, adding the bulk insert feature to the DAL was as simple as creating this class:
And then adding this to the Session class:
Obviously, the method signature was also added to the ISession interface. The batch-size and commandtimeout parameters are currently hardcoded but they should come from some kind of configuration file.
All in all, pretty easy stuff :)
Written by Davy Brion, published on 10/12/2009 12:29:43 PM
Categories:
build-your-own-dal
Build Your Own Data Access Layer: Conclusions
Note: This post is part of a series. Be sure to read the introduction here.
Building your own DAL is almost never a cost-efficient solution. In this case, I wrote this DAL in 24 working hours, but it is limited in scope, power, flexibility and functionality. Having said that, I do think it's better than every single custom DAL I've come across so far. Taking it to the next step however would take a lot more effort, which truly is never worth it. As you try to provide more functionality, overall complexity of your custom DAL will increase heavily and the effort you'll eventually spend on it will more than outweigh any of the downsides that might come with using something that already exists. Building your own DAL is an undertaking that should always be questioned, and shouldn't be considered unless the alternative of doing so is even worse.
If however, you're in a situation where it does make sense, then I hope this series might have been helpful for you. I've shown that you can come up with something relatively decent without having to resort to code generation, without having to spend an insane amount of effort on it, and without having to write repetitive and error-prone code in your application code. Those were the goals I had in mind when I started working on this DAL, and I think I've succeeded at achieving those goals. The final result is an easy-to-use DAL which is far from as powerful as already existing solutions, but it is pretty good for the scenario's where we intend to use this.
The code itself is clear, easy to maintain and in some cases, very easy to extend as well. In total, this DAL is slightly less than 1100 lines of code, and I think the complexity of the code is relatively low so everyone should be able to understand what's going on, how it works, and where things could be modified in order to fix issues or to add new features.
Also, this series of blog posts helps in figuring out how it works since pretty much every aspect of it is now documented pretty extensively :)
All in all, I had a lot of fun in writing both the DAL and this series of posts (which took another 8 hours in total).
In the introduction of this series, I said that the purpose of this series was to:
- Show you that you really don’t need to resort to code generation to build your own custom DAL
- Show you what kind of complexity is involved with the implementation of a good DAL
- Convince you that you typically are better off with simply using something that is already available as a mature, powerful and proven solution
So tell me, did the series succeed in these listed goals? Would you still go for code generation if you had to create a custom DAL? Would you still prefer to use a custom DAL over something that already exists? How would you react to having to use this particular DAL? What would need to be added or modified before you would find it acceptable? What are your thoughts on this in general?
I'd be very interested in hearing about it, so please do post your opinions in the comments :)
Written by Davy Brion, published on 8/29/2009 3:42:10 PM
Categories:
build-your-own-dal
Build Your Own Data Access Layer: Bringing It All Together
Note: This post is part of a series. Be sure to read the introduction here.
By now we've already covered everything that this DAL has to offer, which admittedly isn't all that much. All of the classes you've seen so far are pretty good at whey they should do, but nobody in their right mind would want to use any of these things directly in application code. Any easy-to-use DAL should offer a simple facade which sits on top of the underlying system and makes it very easy to perform the most typical tasks that you need it to perform for you. You shouldn't need to know about specific classes to be able to use it (that goes for most good frameworks and libraries btw).
So once again, I based my approach on what NHibernate does, and with that the ISession interface was born:
Everything that you need to be able to do with this DAL is provided by this single interface. And the implementation of the Session class is very easy as well, since we can simply delegate pretty much everything to each of the classes we've covered in the other posts in the series:
As you can see, there's nothing special here and it's all very straightforward. Application code can now perform database operations pretty easily once it has a reference to an ISession instance. And obtaining a reference to an ISession instance can be done through the ISessionFactory interface:
And its implementation:
The static Create method takes an assembly and a connection string. The assembly (containing your entity types) will be used to to build up the metadata model as covered in the Mapping Classes To Tables post. You would typically call the Create method in your application's startup code, and then you'd have to store a reference to the ISessionFactory somewhere. Your application code can then simply call the ISessionFactory's CreateSession method and that's all there is to it.
Written by Davy Brion, published on 8/29/2009 3:42:02 PM
Categories:
build-your-own-dal
Build Your Own Data Access Layer: Executing Custom Queries
Note: This post is part of a series. Be sure to read the introduction here.
You've already seen that this DAL offers you the ability to query each entity by primary key, or to retrieve a list of all instances of an entity type. Obviously, this isn't even sufficient for the most trivial applications so we need a way to execute custom queries. So I needed to provide something that would enable you to easily execute custom queries and get a list of entity instances, or a single result without having to muck around with transforming the results on your own.
Due to the extremely simplistic nature of this DAL, I can't support much more than queries which either return a single value (being one entity instance, or a scalar value) or a list of entities. As soon as you need to execute queries that return joined results, this DAL won't be able to deal with the results automatically. I'll get back to this part later on in the post so for now, let's focus on queries whose result can be transformed to an entity automatically.
My first version of this DAL hardly had any support for this, and when using the DAL for custom queries, you basically had to deal with SqlCommands and their results manually. Well, you could pass the SqlCommand to the EntityHydrater, but you still had to deal with a lot of ugly code. I had asked Ayende to do a private review before I started with this series, and he quickly pointed out that I needed something better for executing SQL queries and suggested something like (surprise, surprise) what NHibernate offers for SQL queries. So yes, this solution is once again heavily inspired by NHibernate ;)
Let's go over the details... First of all, we have this simple IQuery interface:
That is what you should be able to do with a query once you've created it. Creating a query is possible through my session API (which will be covered in the next post):
Through the regular CreateQuery method, you can provide the full SQL string and you have full control over the actual SQL. The CreateQuery<TEntity> overload only requires you to provide the WHERE clause because it will generate a SELECT clause for the given entity which automatically retrieves all of the columns for this entity. This also ensures that the result of CreateQuery<TEntity> can always be cleanly transformed to a list of entities or a single entity instance through the EntityHydrater.
This is the implementation of both CreateQuery methods:
And here's the actual implementation of the Query class:
Pretty simple, right? The ability to get a strong typed result is something that I find very important when using any DAL, and the Query class makes this very easy to do. If you want to return a list of single value results, you can do that easily. If you want to return a single scalar result, you can do that easily. A single entity instance? No problem, the EntityHydrater takes care of that for us. Same thing goes for a list of entities. Custom delete or update statements? No problem, the ExecuteUpdate method can be used for that and will return the typical number of affected rows as reported by the database.
And now you can do things like this pretty easily:
or
or
I'm sure you get the idea by now ;)
Note: when using the CreateQuery<TEntity> method, the SELECT clause that is generated automatically prefixes each selected column from the entity with the 'this' prefix. This makes it easier to refer to the entity's properties in other clauses while you still have to ability to join on another table in the from clause, though you obviously can't add any columns to the select clause anymore.
Now, this is all pretty good for queries where you only need to return scalar values or specific entities, but what if you want to return values from multiple tables in one query? This DAL doesn't have any support for that, but what you could do instead, is to create a view for your query and map an 'entity' to that view instead. Then you could still get a typed result while querying the view, though the results wouldn't be able to be transformed into your 'real' entities. But for filling grids or just to write typical overview queries, this might already be sufficient.
As you can tell, there's not a lot of power or flexibility behind this approach. But try to think of the complexity involved with trying to deal with results from multiple tables. It makes everything a whole lot more complex. At that time, you really need to consider if it's still worth writing your own DAL because the effort you'll spend on getting it right will be very significant.
Written by Davy Brion, published on 8/28/2009 4:34:47 PM
Categories:
build-your-own-dal
Build Your Own Data Access Layer: Lazy Loading
Note: This post is part of a series. Be sure to read the introduction here.
In the post about entity hydration, I mentioned the following:
If we can’t find the referenced entity instance in the first level cache, what should we do? We obviously can’t load it automatically because that could in turn cause referenced entities’ references to be loaded automatically when they are hydrated. Which in turn could cause their referenced entities… Well, I'm sure you get the point. But those properties obviously can’t be set to a null reference either because the column actually does have a valid foreign key value in the database. Explicitly loading referenced properties leads to seriously ugly (and error-prone) code so that’s not an option I'm willing to consider either. The correct way to deal with this is to use lazy loading. To do that in an automated fashion, we need proxy classes. I'm not going to get into these proxy classes and the whole lazy loading thing just yet, since that will be covered in depth in a future post ;)
It's time to go over the implementation of the lazy loading of this DAL. I honestly expected that this would be the part that would take the most time to get working. It actually turned out to be the easiest and quickest part of the DAL to develop.
As I mentioned, we are going to use proxy classes to achieve our goal of lazy loading.
Consider the following simple entity type:
If we want to avoid having to put any lazy loading logic within this class, we could inherit from it and add the lazy loading logic in the derived class. First, we would have to make the properties of our entity virtual:
Now we could create a proxy class like this:
This could definitely work. Whenever we need a proxy to avoid loading an entity, we can simply instantiate a proxy class like the one above, pass it a Session object (again, the Session implementation will be covered in a future post, though I will show the InitializeProxy later on in this post) and once we retrieve any of the overridden properties, the proxy instance will use the Session to initialize itself. The Session would then use a DatabaseAction and the hydration process to make sure the proxy's properties are filled in with the values of its corresponding properties. I didn't override the Id property because accessing the primary key of a proxied object should never result in a database call, so there is no reason to override it.
But we really can't expect people to manually create proxy types like this. For one, it's repetitive and thus, it is error prone. Both are issues that we've aimed to avoid with this DAL from the start. So how can we make this work automagically? The answer is simple: we can use Castle's excellent DynamicProxy library to generate the proxy classes at runtime for us.
DynamicProxy uses a concept known as an Interceptor. The Interceptor basically intercepts method calls on proxied objects and allows you to add custom logic before and after the original method calls. For our lazy loading purposes, we simply need the following LazyLoadingInterceptor class:
Ok, so what does this class do? Like I said, an interceptor will intercept all method calls on virtual methods of a proxied object. So basically, if we create a proxy through DynamicProxy, and tell DynamicProxy to add a new instance of this LazyLoadingInterceptor so we can add the behavior of this interceptor to our proxy object, and we then access the properties of our proxy object, it will actually show the same behavior as the manually created CustomerProxy class listed earlier. And we get all of this for free, without having to modify our original Customer class, except for marking the properties as virtual obviously.
Now, when the LazyLoadingInterceptor calls the Session's InitializeProxy method, the session will delegate this call to our new InitializeProxyAction class:
As you can see, this is extremely similar to the GetByIdAction. In fact, I should probably put the common logic in another base DatabaseAction class that sits between DatabaseAction and GetByIdAction and InitializeProxyAction. Anyways, when the entity has been retrieved, we ask the EntityHydrater to update this entity instance through its newly added UpdateEntity method:
The Hydrate method is already shown in the post that covers the process of entity hydration, so there's no need to go over that again.
And now there's only one more missing piece in our lazy loading puzzle, which is the actual creation of the proxy in the EntityHydrater's CreateProxy method:
When using an approach like this, it's best to make everything in your entity classes virtual... NHibernate has a similar restriction and I've tried to explain the reasons behind this in this post.
I'm not sure if I succeeded in explaining this topic in a simple and clear manner, but this technique really is pretty easy. If you have any questions, I'd be glad to anser them in the comments :)
Written by Davy Brion, published on 8/27/2009 4:51:35 PM
Categories:
build-your-own-dal