Jim's Blog Ramblings about novels, comics, programming, and other geek topics


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.


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.


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 Project_id=@appId 
              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: , , ,


Thoughts on Data Abstraction

On my path of becoming a better developer, I've began participating in the development of DotNetKicks. DotNetKicks is an open-source project that is a community driven link submission portal. In other words, it is very similar to major web sites of Digg and Reddit, but its focused into specific areas of technology.

DotNetKicks.com is a community based news site edited by our members. It specializes in Microsoft development techniques, technologies and tools including ASP.NET, C#, VB.NET, C++, Visual Studio, Team System, SQL Server and Microsoft Vista.

Like most open source projects, the project uses Subversion. I've used SVN a few times before for other open source projects. In the past, I've just used the SVN command line. But this time, I downloaded and installed TortoiseSVN and it has worked great so far.

I've been able to address a couple enhancement requests including adding a way to see which stories a user has submitted (DNK issue 24) and see all of the comments submitted by the user (DNK issue 62). These were two pretty simple fixes that were resolved by adding some stored procedures and a little bit of coding. Gavin Joyce, project lead, writes about the new features recently added to DotNetKicks on his blog.

One of the things I've notice from reviewing the code so far, is the use of a data abstraction and business layers. In most of my work so far, I've seen very little implementations of these layers. Most of the projects that I've written or reviewed had the presentation, data, and business layers combined into the web page (either ASP.NET or JSP).

There are advantages and disadvantages to using data abstraction layers and nearly everyone does it or reads about it for academic reasons. The largest benefit is transporting the database to another vendor or platform. You can just edit the DAL instead of going through each page and updating your SQL code for any RDBMS vendor specific code.

But that doesn't happen very often for most internal company web sites. For the most part, I've worked on Oracle databases for the government and SQL Server databases for commercial and in-house projects. I've never been apart of something that need abstraction for portability.

One of the advantages that I found is the use of strongly typing the data. When directly querying a database and just outputting the contents, its impossible to test for the existence of columns at design time. With an automated build process that generates the DAL, you can find errors when referencing database objects that may not exist or be of a different data type that you code in your presentation layer.

The DNK project uses SubSonic to automatically generate a data abstraction layer for its SQL Server 2005 database. SubSonic is an open-source project that can generate data abstraction layers for SQL Server 2000, SQL Server 2005, MySQL, and Oracle databases.

An alternative to SubSonic is to use Strongly Typed TableAdapters. Scott Gu provides a detailed tutorial on how to build a data abstraction layer using Visual Studio.


My Related Posts: , ,