SubSonic Workaround

SubSonic is an Object Relational Mapper (ORM) tool to which I converted a few months back from Strongly-Typed Datasets in Visual Studio.  SubSonic is a free open source program.  It’s brilliant because you point it at your database (supplying the database connection string) and it generates the code that maps your tables into classes.  It automatically generates your Data Access Layer for you.  Anytime you need to alter a table structure, you merely re-run SubSonic, et voila, your classes instantly reflect the change.  You don’t need to muck around with editing 4 stored procedures like I used to do with Strongly Typed Datasets.

As long as the User ID you specified in your connection string has SELECT rights on your table(s) and/or EXEC rights on your Stored Procedures, then SubSonic will pick that up and make only those objects available.  If you want your application to do INSERTS, you also need to grant that right to your User, and SubSonic will auto-generate a method for Insert.

However, I’ve recently discovered a bug, and subsequently the workaround for it.

Problem Description

You can call a stored procedure with the following code:

Public Function GetOutstandingRequests() As DAL.RequestCollection

Dim sp1 As StoredProcedure
Dim ds1 As DataSet
Dim dt1 As DataTable
Dim retVal As New DAL.RequestCollection

sp1 = DAL.SPs.SelectRequestsByFilter()
ds1 = sp1.GetDataSet()
dt1 = ds1.Tables(0)
retVal.Load(dt1)

Return retVal
End Function

This will result in an error: Object does not exist on the line that attempts to GetDataSet().

Explanation

The bug is that SubSonic cannot instantiate a stored procedure that doesn’t have any parameters.

Workaround

The workaround is to alter your Stored Procedure to always accept at least one parameter and use them to instantiate the object, thus:

Public Function GetOutstandingRequests(ByVal createdBy As String, ByVal responseTypeID As Integer?, ByVal seekerID As Integer?) As DAL.RequestCollection
Dim sp1 As StoredProcedure
Dim ds1 As DataSet
Dim dt1 As DataTable
Dim retVal As New DAL.RequestCollection

sp1 = DAL.SPs.SelectRequestsByFilter(createdBy, responseTypeID, seekerID)
ds1 = sp1.GetDataSet()
dt1 = ds1.Tables(0)
retVal.Load(dt1)

Return retVal
End Function

Status

If anyone is aware of whether this issue will be fixed in future versions of SubSonic, I’d be grateful to be kept informed (I’m using version 2.1 [Final] at present).  Please leave a comment or send me a tweet.