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


SubSonic 3.0

Google AdSense

I've used SubSonic 2.x for a while and I've blogged about how useful it was as a tool to aid my development projects. A few weeks ago, SubSonic 3.0 was released and I hadn't spent much time reviewing the updates and changes to version 3.0 until this week.

At the present, I have several large projects using SubSonic 2.x and I have a new smaller project starting this week. So I decided to download and install SubSonic 3.0 and use the small project to get familiar with the updates prior to updating my other web sites from 2.x to 3.0.


How to serialize SubSonic objects with nullable properties

Google AdSense

Recently, I ran into the following error when trying to serialize some SubSonic generated classes.

Cannot serialize member 'XXX' of type System.Nullable`1[XXX]. XmlAttribute/XmlText cannot be used to encode complex types.

The SubSonic autogenerated classes cannot serialize nullable types such as DateTime? and GUID?. This is really a .NET serialization problem and not directly related to SubSonic, since the SubSonic library just uses the native .NET code for serialization.


FetchXML Builder for Microsoft CRM

Every now and then, I come across a developer utility that's worth mentioning. The utility might not be new or even great, but its a tool that I'm able to put to use somewhere to solve a problem or just assist in the problem solving.

The FetchXML Builder by James Downey is a great tool because it is one of those niche utilities that speeds up development time and helps the developer (me). The FetchXML Builder is a query-builder like tool used for creating FetchXML requests for Microsoft CRM.

So unless, you're a CRM developer, you probably won't find this as cool as I do.


How to create a delimited list of values from a one-to-many relationship

Recently, I needed to denormalize a database by putting child records into a single delimited field. For example, the following tables contain source data and the expected output from our function.

The following code is based on Microsoft SQL Server 2005 and has only been tested using Microsoft SQL Server 2005.


The Failure of Electronic Voting Systems

Rob Conery (of SubSonic and Microsoft MVC projects) wrote a very interesting article a few weeks ago titled, "Hacking Your Vote". He summarizes the various electronic voting systems, their bugs, and their designs.

As a software engineer, I find it utterly amazing that a $50 million dollar Diebold system was designed using a Microsoft Access database -- an unsecured Microsoft database. Rob also shows some screenshots of Diebold's database on his blog entry.


How to use custom audit fields with SubSonic

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

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.


Free Development Related Tools

Here's a few links for free development related software and tools. This is not an exhaustive list, so I'm sure there's lots of things that I have forgotten or left out. This is just a list of things that I've used in the past and/or present.

As you can see, most of the free products rely heavily on Microsoft. That's just because nearly everything that I've done in the last 5 years or so revolves around Microsoft technologies.

In addition, many of these free products may not be used for commercial products, so please consult the individual license agreements prior to use.


User interface design for an ad-hoc database query form

That's a mouth full to say and even harder to explain.

It feels like that as long as I've been doing web development, the most important and complicated item of the web projects has been making an ad-hoc query form that is flexible and user-friendly enough for end-users, but provide them with everything they want to do (or everything think they want to do).

It is easy to design a form that allows users to query specific fields of the database, but it's oftentimes harder to give users more options and keep the complexity low enough that they don't end up writing complicated SQL syntax.

The problem lies in when users want to change the query form from a simple SQL statement into a more complex statement by using logical operators (AND, OR, NOT, etc.).

My examples below will use three (3) database fields: CarModel (varchar), CarColor (varchar), and HasGPS (bit/boolean). These database fields are for readability and are not intended to be implemented as such.


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: , , ,