How to use custom audit fields with SubSonic

Google AdSense

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.

Continue reading

How to perform an aggregate query using SubSonic 2.1’s SqlQuery

Google AdSense

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.

Continue reading

Saving and truncating values to fit with SubSonic

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.

protected override void BeforeValidate()
   //Truncates any string columns
   //to the maximum length of the field
   foreach (TableSchema.TableColumn col in 
      if (col.DataType == DbType.String &&
            this.GetColumnValue<string>(col.ColumnName)) &&
          this.GetColumnValue<string>(col.ColumnName).Length > 
         string newValue = 
               Substring(0, col.MaxLength);
         this.SetColumnValue(col.ColumnName, newValue);

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.

My Related Posts: , , ,

The Rise and Fall of Database Administrators

Diego Parrilla posted a great story/rant on the changing role of DBAs in an increasingly ORM development environment titled “Rise and fall of DBAs: The tyranny of the ORM“.

There was a time when DBAs dictate how developers should use Their databases. It was early and mid-nineties and Their Word was The Truth…

And then a new trend started: move out of the database as much business logic as possible. The web developers started to code the SQL inside the application to relief the database.

IT Manager: And we can also transfer all these development tasks to the development team. Right?
DBAs: Correct, but…


My Related Posts: , ,

Simplify Your Data Access With ORM Automation

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.

 <asp:SqlDataSource ID="sqlTop10Requests" runat="server" 
      ConnectionString="<%$ ConnectionStrings:SCR_Database %>"
      SelectCommand="select top 10 change_request.* 
              from change_request 
              where [email protected] 
              order by created_on desc">
         <asp:QueryStringParameter Name="appId" 
             QueryStringField="appId" Type="string" />

Now, this code was converted over to use objects generated by the SubSonic project.  Below is how my code looks now:

Query qry = new Query(ChangeRequest.Schema)
     .WHERE(ChangeRequest.Columns.ProjectId, projectId);
qry.OrderBy = OrderBy.Desc(ChangeRequest.Columns.CreatedOn);

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.

Here’s another way of querying using SubSonic:

ChangeRequestCollection crs2 = new ChangeRequestCollection()
     .Where(ChangeRequest.Columns.ProjectId, _projectId)

The only reason that I can’t use the above code is that the TOP function isn’t supported by the fluency chaining (see message board over at subsonic for details).


My Related Posts: , , ,