Returning Values from Stored Procedures using Linq

Linq is Microsofts new way of accessing databases, for the first time you can write database queries directly within your code instead of within a string.

I use stored procedures a lot, in fact I rarely have even a select statement in my code. Other people far more qualified than me will give the pros and cons of this method. I used to use the Microsoft Enterprise Library and they had a very clean way of accessing the data

For a single value returned from a stored procedure the code would go something like this

Dim db as database = DatabaseFactory.CreateDatabase
Dim RetVal as integer= CType(db.ExecuteScalar("sel_DoesUserNameAlreadyExist", UserName), Integer)

Easy, job done.

I've been trying to do the same thing using Linq

Here's the Stored Procedure, about as basic as can be

Create Procedure sel_DoesUserNameAlreadyExist(@Username nvarchar(50))
as
select count(UserName) as CountOfUser from tblUser where UserName = @UserName


1st Job after you create the stored procedure is you drag and drop the stored procedure onto the DataClassDataContext. This very cleverly maps the StoredProcedure to Linq and sets up a
class for the returned data

_
Public Function sel_DoesUserAlreadyExist( ByVal forename As String, ByVal surname As String) As ISingleResult(Of sel_DoesUserAlreadyExistResult)
     Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType MethodInfo.GetCurrentMethod,MethodInfo), forename, surname)
     Return CType(result.ReturnValue,ISingleResult(Of sel_DoesUserAlreadyExistResult))
End Function

Here's the Class definition

Partial Public Class sel_DoesUserNameAlreadyExistResult
Private _CountOfUser As System.Nullable(Of Integer)
     Public Sub New()
          MyBase.New
     End Sub
     _
     Public Property CountOfUser() As System.Nullable(Of Integer)
          Get
               Return Me._CountOfUser
          End Get
          Set
               If (Me._CountOfUser.Equals(value) = false) Then
                    Me._CountOfUser = value
               End If
          End Set
     End Property
End Class

Note how the CountOfUser field has been mapped to the class

My Linq code is now

Dim db As New My_DataClassDataContext
Dim RetVal as integer
For Each row In db.sel_DoesUserNameAlreadyExist(UserName)
     RetVal = row.CountOfUser
Next


This is the end result of my struggles, on the way I discovered a big gotcha

Here's my original Stored Procedure


Create Procedure sel_DoesUserNameAlreadyExist(@Username nvarchar(50))
as
select count(UserName) from tblUser where UserName = @UserName


Note there is no alias on field count(UserName)

When this was mapped to Linq there is no sensible field name so it gets mapped to Column1 instead of CountOfUser. This causes a problem when you try and run the stored procedure. The mapping fails, Column1 does not really exist and so the returned value does not get mapped, a row gets returned but Column1 has a value of Nothing.

Workaround, always alias your calculated fields when using Stored Procedures.

NSLU2 as a media server

The NSLU2 is a very usefull tool, out of the box it functions as a NAS (Networked Attached Storage) device, simply add a hard drive and connect it to your network. Much of it's popularity has come from the ease at which you can replace the devices firmware.

Why would you want to replace the firmware? The standard firmware runs a version of Linux that is squeezed into the devices ROM. The new firmware allows Linux to be moved over to the harddrive, removing the size contraint. You can then install your own programs, attach new USB devices etc. The device still has limitations, limited processor speed and memory being the two main ones, although for those people who are not frightened of hardware mods and invalidating their warrenty you can overclock and add more memory.

Several different firmwares have been developed, I would recommend dropping over to http://www.nslu2-linux.org/, for more information.

I have two NSLU2s, one I'm using for experimentation, it's running Ruby at the moment. The other is used as a media server, I run a program called TwonkyMedia this is a media server and all my music is on it's attached hard drive. My XBOX 360 recognises it and TwonkyMedia will stream my tunes over the network. It also works with many other devices, I've had some success with Netgear's mp101 although I hear people have found the Pinnacle Soundbridge more reliable. The main attraction for me is it's a lot cheaper to run as a server than a dedicated PC, I believe it only consumes about 10W.