I promised to blog about the lack of DAO support in Business Object Generator command.
As you might have noticed by now I have created a new menu under Smarties 2008 called “Data Commands” to list all the relevant commands there. One of the first commands that I added for “Data Commands” was the BO/VBO Generators. Basically my goal was to add the capability for the developers to create flat BO or VBO from database tables.
Early on into the work I mentioned about DAO and how simple it would be. After working for a few days to see how I could do that I gave it up because I could only see myself doing what I did in the past with my actual OR/M project.
I have also concluded touching DAO would side track me from Smarties 2008 project all together. Believe me when I say this I have a weakness when it comes to DAL because I’m so passionate about making this process easier.
There are three main approaches that I think of for creating DAL. Before I talk about them let me explain or sell using Business Objects to you instead of the conventional way of using DataSets.
There are millions (exaggerating of course) of Software Development principals that one need to follow to reduce risk of failure. The more you know the more you realise how painful software development actually is. One of the SD principals is to have an object template that represents an entity within a domain i.e. Employee. My biggest problem with DataSet class has always been it cannot be defined as an entity.
Picture this if you will, you have a method called ProcessEmployees(DataSet emp) that accept a parameter of DataSet type. If you are going to expose such method to consumers then each method that accept DataSets needs to check if the DataSet contains all the correct attributes for an Employee. This is a nightmare for sure.
With strong BO types you never face such fundamental issues. It would be ten times more difficult to create DAL with BOs than using DataSets but all is worth it. If you use the same approach all the time and more or less use the same database engine then you can always spend a quality time designing something that you can re-use for the next projects as well. This is another software development principal. The extra time you spend always pays back ten times more at later time.
DataSets can come to rescue when the structure of data is not known until runtime (late-binding). A good example of that would be the GetSchema() methods of Connection providers.
As I mentioned before there are three main approaches for creating DAL for application systems. These are:
Direct Interaction with ADO.NET
In this approach you would code against ADO.NET directly which you would end up writing the same kind of routines all the time. The hardest part would be populating data from DataReader to BO and writing that boring insert routine. I believe I can add supports to Smarties 2008 for this. Writing this article has already given me some fresh ideas that I would conclude them at the end of this article. I’m thinking loudly now.
ADO.NET > DAO Helper > BO
In this approach you would use a DAO Helper i.e. Microsoft Enterprise Library to avoid writing the same routine to interact with the backend database. In this approach still populating BO on data fetch events are a pain.
OR/M
It does all you need from generating the BO, BO Collection Types and DAOs. OR/M doesn’t exactly reduces the number of methods you need to write for data operations since you still need to write the routines to instruct the underlying OR/M framework what to do but the routines you write are far more easier than the ADO.NET > BO. There are disadvantages in OR/M such as synchronisation, dependencies i.e. mapping, and losing over 60% of its power in web environment.
You can be assured that all above approaches have pros and cons therefore no one can say for certain which one is the best. Considering the size of database, deployment environment, and what you want to achieve makes one of the above approaches to stand out but for others it might be the worst approach.
I know of many developers that are totally arrogant towards OR/M and I know of I.T. Managers that would use DataSet in an Enterprise System for the sake of DTO.
I know one thing for sure I never use DataSet in place of BO and I have refused to work on projects that the man in charge wanted to use DataSets in the past. I’m not an advocate of Linq either and I doubt it very much Linq has brought any true advantage for the developers except for the fancy lambda expression which will be limiting where as in my opinion OQL (Object Query Language) can handle more object queries. When Linq is part of framework itself then you have to wait until the next release to see more lambda expressions support. Then once you use the new expressions you cannot re-use your code (investment) in the previous version of .Net Framework. It might not sound a big issue to you but in business world it is.
What I have achieved with my OR/M project was this… You could run virtually all SQL aggregate functions and mathematical functions in memory (Client-Side) to avoid hitting the database server for the simplest operations. If I’m not mistaken Linq supports some of the SQL aggregate functions in memory too.
When you fetch a record set from database the data will resides in the client machine. If you wanted to get an average figure from one of the columns then you would have to write a routine and run it against the database that has the same where clause and joins to get the result back. It made a perfect sense to me that the lack of support in the client-side causes system to hit the database far more. We developers are not going to be immune to the “green” campaign and one day we are required to write ‘greener’ software. Hitting the server for a small operation is not ‘green’ at all. You might laugh at this but one day Microsoft will issue guidelines on how to write greener software, remember where you read about this first.
I have predicted long time ago Microsoft’s attempt to OR/M would be the same with DataSet. I had a chance to look at vNext ADO.NET long time ago and it really scared me how much mapping I had to do to get it to work and also how many layers are in place to handle simple operations.
I really think Microsoft should stop adding certain things as part of .Net Framework because they can’t simply do a good job, not because of technical incompetence but because of the Framework nature which has its draw backs too. The ADO.NET 2.0 provider model is great isn’t it? Well 99% of it but it can fail just because of the 1% implication involved with different data types in database engines.
The nightmare (it is really) is the Parameter’s type. Each provider knows how to handle its own Data Type enumerator when is used but universally the DbType is not truly compatible with all the providers. To make this to work you need to add a new layer in between to do the mapping when writing DAL that meant to support multi-platforms. The OR/M developers write their own provides on top of ADO.NET to handle this one issue. The .Net Framework did not server its purpose very well with data providers.
What is DAL?
Data Access Layer is made up different objects that each server a purpose. In general there are Entity (BO) that stores a single row of database table, Entity Collection Type that stores a collection of table rows, and DAO (Data Access Objects).
DAO’s responsibility is to have all the data operations either specific to an Entity or to act as a helper class when the entities in DAL have been given the responsibility to handle their own data operations. I recall numerous discussions about DAL models which are Domain, Entity, and Direct Table a couple of years go. No one came out as a winner because simply one solution doesn’t fit all problems.
Personally, I don’t like entities to have any knowledge of how to interact with the backend database for two reasons, security and loosely-coupled. When you place all data operations with DAO then you can introduce security. The Proxy pattern works very well with DAOs. A proxy class is like an object but is not the object itself. You can also think of a Proxy class as a wrapper around an object.
The best way to understand something is to use a real world example. You are responsible to write a DAL within your organisation and distribute it to different departments which in return the developers within each department write their own UI on top of your DAL API. Within your organisation each department have different access rights to database tables. For instance HR can modify an Employee record but Accounting Dept. can only view or make very limited updates to the Employee table.
The first thing you’d notice is that if the Entity had the knowledge how to interact with the backend database then the developers in each department can do all sort of things with the Employee table. This is where Proxy classes shine. You have two choices now. First choice is to create one proxy class for each department and limit the members you expose in the proxy class. The second choice would be to implement a security check against the user login credentials in the proxy class to see if he/she can execute methods.
How Smarties 2008 can help?
If an OR/M is not a solution and BO is a must then there isn’t much option available to ease up the process. Smarties 2008 version 1.3.0 supports nine database engines to create BOs from database tables. The supported databases are MS SQL Server, MS SQL CE, MS Access, Oracle, Firebird, Sqilte, PostgreSql, MySql and VistaDB.
Smarties 2008 can help ease up the first two approaches by creating rich BOs and BO Collection types. While I’ve been writing this article I had some refresh idea that could ease up the first two approaches even further.
After all I might be able to work on DAO that I know would save developer a lot of time. My mistake was thinking OR/M and multi-platform database support in DAO that I originally meant to design. I can now narrow this down to the database engine that the developer is using to create the Business Objects. With this approach I can write all the routines for populating BO on data fetch events and write that boring insert routine.