November 2009 Entries
Differences between using the ‘Convert’ class vs. casting (Part 1 of 2)
This blog post has moved:

Posted On Monday, November 30, 2009 7:58 PM | Comments (0)
The Future of Television?
This blog post has moved:

Posted On Thursday, November 26, 2009 8:35 PM | Comments (0)
Why does SubSonic’s SimpleRepository ‘Add<T>’ return a decimal instead of an int? (Part 2)

Last time I was taking a look at SubSonic’s SimpleRepository functionality and wondering about the return value of the ‘Add<T>’ method. More specifically, I was wondering why the ‘object’ instance being returned was typed as a decimal rather than an int when the object I was persisting had a primary key field that is typed as an int.

I had discovered that the while Add<T> was returning a decimal, it was also updating the primary key field (PostID on my Post class instance in this case) with the same value; essentially I was getting the correct number back both from the return value and the PostID field but the values were being typed differently.

Since SubSonic is an open source project I have the luxury of pulling down the code and having a look for myself. This was easily accomplished by doing a git clone of the repository at github: git:// to a folder on my local machine. Opening the solution and building the source code only took a second and I was then able to directly reference the newly built SubSonic.Core assembly in the bin folder of my local SubSonic copy from my sample project so that I can easily F11 into the Add<T> method and see what’s going on.

This little block of code appeared to be the origin of the return value for the method:

object result = null;
using(var rdr = item.ToInsertQuery(_provider).ExecuteReader())
    result = rdr[0];


The ‘rdr’ variable above is a simple System.Data.IDataReader, the run-time instance of which is a SqlDataReader in this case. To try and understand what’s happening it’s helpful to know what the SQL that the data reader is reading from looks like. Drilling down a little bit further into the ‘ToInsertQuery’ method can show us the SQL that’s being built up at run time:



Pulling that SQL out and formatting it some gives us this:




So our data reader is reading back the result of ‘SELECT SCOPE_IDENTITY()’. Taking a quick look at the ‘Posts’ table that the SimpleRepository created in my database reveals that the PostID column is indeed set as the primary key/identity and is typed as an int. Some poking around in the MSDN documentation reveals what’s going on:

This article on SCOPE_IDENTITY shows that that it has a return type of ‘NUMERIC(38,0)’ while this article on SQL Server Data Type Mappings in ADO .NET shows us that numeric SQL Data Type gets converted to the ‘decimal’ .NET Framework type automatically. So that mystery is solved, but this still doesn’t explain how the integer typed ‘PostID’ field was correctly updated with the new identity value while the return type remained a decimal. The next code block down in the Add<T> shows us how:

if (result != null && result != DBNull.Value) {
  try {
    var tbl =  _provider.FindOrCreateTable(typeof(T));
    var prop = item.GetType().GetProperty(tbl.PrimaryKey.Name);
    var settable = result.ChangeTypeTo(prop.PropertyType);
    prop.SetValue(item, settable, null);

    } catch(Exception x) {
      //swallow it - I don't like this per se but this is a convenience and we
      //don't want to throw the whole thing just because we can't auto-set the value
return result;

The point of this is to provide convert the ‘result’ variable to the type of the primary key of the object being persisted. I’m not sure that I agree with swallowing the exception here, though that’s easy for me to say as an outside observer who hasn’t poured hours and hours into this code. The main reason I don’t agree with it is that I feel like the calling method really needs to be able to rely getting back the identity of the newly created record and if something blows up I would want to know about it. You can always still rely on the ‘result’ object that gets returned, but as we’ve already seen you can’t simply cast that to an int as you might think you could. I’m kind of curious to know under what circumstances this code throws an exception and see if there’s any way to make it more reliable. I can see that the definition of the ‘ChangeTypeTo’ extension method can explicitly throw an exception when the underlying database is SQLLite, but the exception thrown also hints at the workaround for that issue. I suppose this kind of thing can be the cost of doing business when you’re trying to support multiple database platforms; not everyone has the luxury of being “SQL Server only”.

Oh, and the (Exception x) isn’t needed in this case since we’re not doing anything with the caught exception object; I think a simple ‘catch’ would do just fine, but I digress. :-)

It wouldn’t be very difficult to modify this code to attempt to return a properly typed ‘object’ variable since we’re already trying to convert the ‘result’ object for the purposes of setting the primary key field in the provided ‘Post’ instance by trying to do something like this:

object typedResult = null;
if (result != null && result != DBNull.Value) {
  try {
    var tbl =  _provider.FindOrCreateTable(typeof(T));
    var prop = item.GetType().GetProperty(tbl.PrimaryKey.Name);
    var settable = result.ChangeTypeTo(prop.PropertyType);
    typedResult = settable;
    prop.SetValue(item, settable, null);

    } catch(Exception x) {
         //swallow it - I don't like this per se but this is a convenience and we
                    //don't want to throw the whole thing just because we can't auto-set the value

 return typedResult ?? result;


This would attempt to return a properly typed result if the type conversation was successful but would return the original result if needed. This would let the calling code look closer to what I originally expected would work:

SimpleRepository repo = new SimpleRepository("SampleDB", SimpleRepositoryOptions.RunMigrations);
object returnValue = repo.Add<Post>(newPost);
int newPostID = (int)returnValue;

The problem here is that since the type conversion could swallow an exception there’s no guarantee that our cast to an int would work at runtime. This could pose an issue if you were relying on being able to determine the newly created ID of the object immediately after its edited. For example, you might want to take the user to the ‘view’ screen for the post right after they create it. So what’s the solution? I’m not sure that I have the right answer, especially if you don’t have any other means to uniquely identify your records. I think it’s pretty safe to assume that you’re going to get some kind of return value back. The calling code could ‘ToString()’ the returned object and use Int32.Parse but that kind of smells to me. You could also use ‘Convert.ToInt32’, which I think I like better. I think I would also be in favor of removing the empty catch block to be able to rely on the type conversion when the method returns. I think it partially comes down to whether or not you think these potentially platform-specific quirks should be the burden of the library or the library’s consumer. Given that the consumer is always going to be in a better position to know about the specific needs of context in which the library is going to be used I think I’m leaning toward the latter.

That said this is open source so you can always fork it and modify it for your own purposes which is part of the fun. :-)

Posted On Wednesday, November 18, 2009 8:16 PM | Comments (2)
Why does SubSonic’s SimpleRepository ‘Add<T>’ return a decimal instead of an int? (Part 1)

I’ve been spending some time lately digging into SubSonic 3 and have really enjoyed working with it so far. I love the how “low friction” it is to get up and running. I’ve been particularly impressed with the SimpleRepository in this regard. It definitely lives up to it’s name by providing truly simple data access functionality in a pretty sane and straightforward way. That said I don’t think it’s the best choice for every project, but if you don’t have to care much about the implementation details of your database then I can see it being a really useful tool.

For whatever reason I’ve been choosing a “blog engine” as my domain of choice lately when working up sample code (I guess I finally got tired of endlessly building ‘employee management’ or ‘product inventory’ models) and decided to see how I might be able to leverage SimpleRepository in a scenario like this.

I won’t go too far into the mechanics of using SimpleRepository (great walkthroughs are available at the SubSonic project site) but the basic idea is that you create POCO classes to define the data that you want to be able to persist. For the purposes of this example I started with a very simple ‘Post’ entity that ended up looking like this:

    public class Post
        public int PostID { get; set; }

        public string Title { get; set; }

        public string Body { get; set; }

        public string AuthorName { get; set; }

        public DateTime PublishedOn { get; set; }

        public DateTime CreatedOn { get; set; }

SubSonic can take an object like this and automatically create a corresponding table in SQL Server on-the-fly. Taking a “convention over configuration” philosophy, SubSonic sees an int field on the ‘Post’ class called ‘PostID’ and makes that both the primary key and identity on the corresponding ‘Post’ table. When creating a new post it’ll also help you out by bringing back the newly created ‘PostID’ from the database. Creating a new post might look something like this (assuming a valid connection string present in the config file named ‘SampleDB’):

   1:          public int CreatePost(string title, string body, string authorName, DateTime publishDate)
   2:          {
   3:              Post newPost = new Post
   4:              {
   5:                  Title = title,
   6:                  Body = body,
   7:                  AuthorName = authorName,
   8:                  PublishedOn = publishDate,
   9:                  CreatedOn = DateTime.Now
  10:              };
  12:              SimpleRepository repo = new SimpleRepository("SampleDB", SimpleRepositoryOptions.RunMigrations);
  13:              int newPostID = (int)repo.Add<Post>(newPost);
  14:              return newPostID;
  15:          }

The ‘Add<T>’ method of the SimpleRepository returns an ‘object’ which I presumed would be an integer containing the new PostID. I wanted to be able to cast that to a local variable and then return the new ID to the caller of this method. I was somewhat surprised to see that this code blows up on line 13 with an ‘InvalidCastException’. Apparently the object being returned isn’t directly cast-able to an integer. Making a quick change to the code, setting a breakpoint, and digging in some with the ‘Immediate’ window revealed the following:


The ‘Add<T>’ method was returning a decimal instead of an int. The ‘newPost.PostID’ and ‘returnValue’ were both being set to 7 (which was the correct value after looking in the database) but they were typed differently. Now, if I were smart I’d just resign myself to simply using the ‘PostID’ to determine the new identity of the created Post, but where’s the fun in that? ;-)

I decided instead to dig down into SimpleRepository source code and take a closer look at  the ‘Add<T>’ method definition. More on those findings in the next post…

Posted On Sunday, November 15, 2009 6:47 PM | Comments (1)
Resolving Dropbox hanging when relocating local folder

This blog post has moved:

Posted On Friday, November 13, 2009 7:04 PM | Comments (2)
Tag Cloud