Hand Crafted Database Access

I know I promised a while back that I would be talking more about business stuff but that is simply not ready. Since its been a while since I published anything at all, I have a little technical piece to share.

My current project is a Browser MMO that has an ASP.Net based webservice running things on the server. My day job has me writing a lot of code for windows, and since I do a lot of work from my home computer, it must have windows. However, should I ever find success building my game I don't want to get stuck buying expensive windows server licenses so I am attempting to develop my code to be cross platform. I'd like to be able to run my servers from a Linux OS with Apache and Mod_Mono. With some carefull planning its really not all that hard to do, but there are some pitfalls.

One trouble that you may run into if like me you develop first on Windows and Visual Studio, and test later on Mono is Mono's support for what  Microsoft calls Strongly Typed Datasets. Don't get me wrong I like strongly typed data sets becuase they allow you to put together your database code very quickly, and prevent simple mistakes like typos in your code. They also work reasonably well with many Data components that are distrubuted with .Net like grids and list views. However they do have a price. 

One of the first things you might notice when you try to run an ASP.Net  website that uses typed datasets is that the XSP webserver which can run ASP.Net code in Mono on Windows is that it can't directly compile the .XSD file that contains the dataset description. Microsoft .Net code doesn't directly compile them either. There is a tool xsd.exe that ships with .Net that converts the .xsd files into C# (or VB.Net) code, which is then compiled into the binary code that the webserver will run. The nice thing about Microsofts Tools and Servers is that it will do this automagically for you. The simplest way to get around this is to put all your Typed Dataset .xsd files into a  library project inside Visual Studio, and reference that project from your ASP.Net Website. This makes Visual Studio Pre-compile the .xsd files into a DLL that Mono and XSP can load. Simple right?

Well not so fast.  This is all well and good for running ASP.Net code on Mono based services, but what if you, like me, would also like to develop your ASP.Net code using free software? Mono Develop, an IDE for Mono, does infact support compiling .xsd files, the problem is that It doesn't really support editing them. Well, you can edit the XML code inside of a .xsd file directly, but that pretty much ruins the point in creating the .xsd files in the first place. You don't have a GUI helping you so its likely not that fas to edit the XML anymore. Also you have to learn what syntax is expected and appropriate for the .xsd file. All the advantages except for data type checking are lost as son as you start editing .xsd files by hand. 

Now, what is the alternative? Well if you are stuck coding something by hand because there is no GI editor for .xsd files in Monodevelop, you might as well write come C# code by hand since you probably already know how to do that. To be honest, its not that hard. There are only a few key components you need to leand such as Connection, Command, Adapter, Parameter and Table objects. I'm not going to talk about the code directly here since there are only about 6 million pages on the subject according to google,but suffice to say if you don't know how to create a DB connection and do some simple queries by hand, its probably worth learning. 

There are some other benefits aside from better cross platform development to creating data access code by hand. For me, it seems to be faster at run time since the SQL queries that I write are a bit mor optimized than the ones that  get created for me. For example the generic update code that is created automatically updates every field in the table, and often I just want to update one field. This also means less code has to execute in the DB client code converting parameters into SQL statements so not only does the database service have less work to do, your ASP.Net code has less work to do. 

Compiled size seems to be improved as well. This usually isn't a worry for server code but if you are developing code that will run on a desktop computer or a mobile device this may be a concern. In general your hand crafted database code will be much shorter then the generated database code, and as a rule less source code often means less machine code creating a smaller installation and might reduce the memory footprints of the running code.

Now I know optimization is in general a good thing, but I know I am guilty of letting Moore's law save me from having to do it. It's pretty easy to say computers will be faster in the future so why worry. I know the dataset editor in Visual Studio is great for throwing things together in a hurry. So if you are short for time or just prototyping, use typed datasets.

Personally, I really would like to commit to the switch to free software as I have been putting it off for years, So that has influenced my decision heavily. Clearly you should evaluate your own needs befor commiting to do the extra work of creating your own Database code rather than letting a tool do some of that work for you.