Ramblings about novels, comics, programming, and other geek topics

Menu

ORM

SubSonic uses a few audit fields by default – you don’t need to write any code. However, the field names that are used are coded into the source code. You can modify the source code and generate a new assembly, but if you’re like me and would rather not edit the source code (so you don’t need to update the code at every release), then you’ll be looking for an alternative approach.

Just recently, I upgraded from SubSonic 2.0 to SubSonic 2.1 RC1. One of the new features is the addition of the SqlQuery class and it’s ability to perform aggregate queries (along with many other features).

If you’re not familiar with SubSonic, then it is a tool that builds your DAL (Data Abstraction Layer). Each of your database, tables, and rows become classes that you can insert, update, delete, and select from.

By using an ORM instead of embedding SQL statements in your applications, it makes your code easier to test and catch mistakes (such as typos in field or table names). There’s pros and cons to ORM versus SQL statements, but this entry won’t touch on those issues.

This is an example of how to perform a query to get a count of unique records while grouping by a column.

For one of my applications that I’m working on, I have two different data sources where one needs to push data to the other. However, since both data sources were not designed for this, there’s a few destination fields that are shorter than the source fields.

To resolve this, I needed to truncate any values to fit within the maximum size of the destination fields. If there were only a handful, then it would be easy enough to code. However, there’s several dozen fields and just to make sure I don’t miss a field, I wrote the below code to loop through the destination fields and automatically truncate any values that exceed the maximum length.

After asking if there was a SubSonic way to do this, I learned that there wasn’t any code already written. Montyguy recommended that I override the BeforeValidate method of my Data Access Layer (DAL) related class instead of calling a separate function in my code.

The above code loops through each column/field in the table/class and checks to see if the value is not null or empty and if the length exceeds the maximum length defined by the database. If so, then it takes a Substring of the value and sets the column to the truncated version of the original value.

Every now and then, I stumble upon something worth saving. The SubSonic project is an open-source project that generates a Data Access Layer (or Data Abstraction Layer) for your .NET projects.

At first, I wasn’t very familiar with ORM (Object Relational Mapping). I’ve been writing SQL statements for many, many years. Mostly, just because I’ve been surrounded by data-centric personnel such as DBAs or I’ve had to take on the role of a DBA.

Here is an example of how to query the “Change_Request” table to retrieve the top 10 records ordered by the created_on column. For the most part, many of my ASP.NET web sites look like this. It is very easy to generate the SQL and this example doesn’t even show a select command with several join statements and complex where clauses.

Although, you may be saying that it is still rather complicated. I see it that my biggest benefit is that everything is now strongly-typed. If the field named “Created_On” is renamed to “Created_On_Date”, I can just refactor my code to make the change and if it compiles, then I know I found every instance and fixed it.

If a column was renamed when I am using the inline SQL (the first example), I’ll have to rely on text searching and looking at each individual page and query statement to determine if the field is owned by the table that was modified.