New Zealand's new Copyright Law presumes 'Guilt Upon Accusation' and will Cut Off Internet Connections without a trial. CreativeFreedom.org.nz is against this unjust law - help us


A Space for All Things .NET Related


SubSonic Workaround

By James Hippolite, in , posted: 11-Sep-2008 07:59

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.

Other related posts:
Geek Post Monthly Newsletter Volume 2 Issue 6
Exam 70-300 Objective 3
COALESCE T-SQL Function




Permalink to SubSonic Workaround | Add a comment (2 comments) | Main Index




Comment by Jason, on 12-Nov-2008 06:38

I found this issue too. Here is my suggestion for a fix. http://www.jasonstiefer.com/blog/post/2008/11/SubSonic-with-Stored-Procs.aspx


Author's note by JamesHip, on 14-Nov-2008 08:04

Awesome!  Thanks heaps Jason.


Add a comment

Please note: comments that are inappropriate or promotional in nature will be deleted. E-mail addresses are not displayed, but you must enter a valid e-mail address to confirm your comments.

Are you a registered Geekzone user? Login to have the fields below automatically filled in for you and to enable links in comments. If you have (or qualify to have) a Geekzone Blog then your comment will be automatically confirmed and shown in this blog post.

Your name:

Your e-mail:

Your webpage:

JamesHip's profile

James Hippolite
Wellington
New Zealand


Welcome to my technical blog. 

Here, I attempt to distill the Microsoft Certified Professional Developer knowledge I have accumulated since first qualifying MCP in 1996.  This blog started on 13 September 2007 as an off-shoot from my mixed up personal blog.  But it took a shot in the arm from Scott Hanselman's talk at TechEd New Zealand 08 "32 Ways To Make Your Blog Suck Less".