I was going to talk about a new feature that I had just added to my database library, but then I realized that I haven’t talked about the library at all yet. I guess that I planned to for so long, I just thought I had!
For my Systems Directorate project, I needed a good database library that would let me support different database servers on the back end. I came up with a pretty extensive library which used resource like strings for each database, much as you would for spoken languages. It was fairly nice, but very time consuming to use because you had to build this large library of SQL commands for every little thing you did.
For another project though, I wanted a much simpler and faster database class so I started over. That one became so easy to use though that I dropped the larger one and went with the new one.
So, what makes my database library so nice to use? I wanted a very easy way to execute a SQL statement with full SQL parameter support and yet, only have a single statement in my code. I achieved this by simply stealing an idea from String.Format(). I figured, that my SQL statement could simply use a params array of parameters much like a formatting strings does.
The first parameter would be the SQL statement, the second would be the parameter definition string, and everything after that was data to fill the parameters with.
[ccle_csharp lines=”-1″ width=”600″] db.Do(“insert into mytable (a,b,c) values (@a,@b,@c)”,”@a:int;@b:varchar;@c:float”, 42, “mystring”, 5.4);[/ccle_csharp]
It’s the second parameter that makes it all happen. I simply specify each SQL parameter and what type it should be. For types, I support the full list of names from SQL Server or PostgreSQL for now. It also supports the .NET names for them like String, Int32, etc. It also includes short versions of them like “v” for varchar, “g” for Guid, etc. In the backend, the string is parsed and all the parameter objects are created along with the command statement.
Once I had that code working, I kind of went crazy in adding new features. Obviously, I needed a Query method as well, so I make about 20 of them returning data in all different ways that I might possibly need.
- DataSet
- DataTable
- List<Dictionary<string,object>> – A list of dictionaries with each column/value pair.
- List<Dictionary<string,string>> – A list of dictionaries with each column/value pair where each object is converted to a string.
- Single row versions of the Dictionary<string,object>
- Single row versions of the Dictionary<string,string>
- Count only
- String only of first column of the first row
- String list of the first column of all rows
- Delegate to a user supplied function
- Enumerators that you can use directly in a foreach loop.
- A template object. Both list and single row versions.
That last one was where it started getting real smart. I didn’t want to have to keep writing code that moved data from the result set into my class objects so I decided to use some reflection and make the library smart enough to do it by itself! If there was a property name that matched a column name in the result set, the value was copied automatically into the property.
It was immediately apparent though that names in C# classes were not going to match what I put in the SQL tables since I used different rules for naming. So I started creating some useful attributes to apply to the class properties. The first being ColumnName(name). This let me define the database column names for each property in the class. I also created attributes to define other information.
- ColumnKey – indicates the column is a key column in the table.
- ColumnName(“my_name”) – forces a particular name for the database column.
- ColumnType(“Int32”) – forces conversion to a particular database type (converts enums to ints for example).
- ColumnReadOnly – tells the library not to write this value to the database (I’ll talk about that soon).
- ColumnWriteOnly – tells the library not to read this value, but it can write it.
- ColumnIgnore – tells the library to completely ignore this property.
- ColumnSerialize – this is the new one I just added. It auto serializes an object to and from XML for storage in a string column type.
So, after all that, I could now do a query like this:
[ccle_csharp lines=”-1″ width=”600″] List<MyClass> result = db.Query<MyClass>(“select * from my_table where name = @n”,”@n:v”,”Trevor”);[/ccle_csharp]
It creates instances of MyClass for each row and puts them into a list after populating all the matching properties to the column names.
I decided not to stop there however. Why not use reflection on classes to also handle insert and update statements! So, that’s what I implemented next. I created a couple methods called DoInsert and DoUpdate that take an object and create the appropriate statements for inserting or updating.
Basically, for insert, it uses reflection to see which properties were in the class, and then using the column attributes, generated the insert string and a parameter format string. Then, when the insert happens, it fills in the parameters from the property values. This sounds like a slow process to build on each call, so I also added in the ability to cache the generated statements so they only have to be done once per application run.
One extra attribute was needed to support the insert and update statements. They need to know what the table name is! So, I created a TableName() attribute that can be put on the class itself.
To help support the conversion even more, I added a bunch more filter attributes for the class to help handle boundary conditions for the property data. These each have a minimum and maximum value and forces the data value to be within the range by adjusting it when writing to the database table.
- ColumnDateFilter
- ColumnDoubleFilter
- ColumnFloatFilter
- ColumnIntFilter
- ColumnLongFilter
- ColumnStringFilter
The new feature that I just added was the ColumnSerialize attribute. If the class has a property that has this attribute and is derived from my XmlBaseType class, the database library can now serialize the property to a string using the .NET XML serialization library. I append the full class type information to the XML document as an XML comment. When read back from the database, I use that information to create the instance of the object again and deserialize the data. Sometimes it is easier to store a structured object directly in a database instead of splitting it all into tables of columns. I could have supported any object instead of ones derived from my XmlBaseType class, but I think this is better. It forces me to think about the object and its data and consider the design of what I am doing.
Transaction support was added as well. Normally each of my Do or Query calls opens the connection, performs the statement, and then closes the connection. Sometimes though, you need to do multiple commands at once. A quick call to BeginTransaction() starts a session with the database where multiple commands can then be used. Calling Commit() or Rollback() finishes the transaction.
Another feature I added was for prepared statements. Instead of having the library generate the SQL command object each time, I added methods like BeginDo which take the statement and parameter string only. Then DoMulti() wihch can be called as many times as needed. It only takes the value parameters. Calling EndDo() finishes the sequence. Using the multi sequence also maintains a single database connection so it isn’t opening and closing the connection on each call.
I extended this multi-call ability to the DoInsert() and DoUpdate() methods so many objects could be updated at once.
Using the database class is also very easy. You just need to create an instance of the Database class. It will automatically use the connection string called “sql” in the application configuration file. You can also pass in a string to the constructor. It checks to see if it is a configuration string name, and if not, it uses it as a connection string.
To support the different database vendors, I added support for a new key in the connection string. The keyword “product” can be set to “mssql”, “pgsql”, “mysql”, “oledb”, or “odbc”. It uses this string to determine which provider to use from the DbProviderFactory.
So, that’s all I have on it for now. I promise to add more as I think of new ideas to expand the library!