Usman ur Rehman Ahmed's blog

 

Linq to SQL Stored Procedure Composite Type


One of the good things I learnt while I was working over Hibernate in my early days with Java was to extensively use wrappers once you have fetched the required custom business objects from database. However on .NET platform, when I was trying to fetch a rather composite (with data from two tables) type from a stored procedure, I came across a problem, and intitutively, the solution that I came up with was far more elegent (atleast for myself) than other recomended on the web.You see, I had this table structure, and the output that I needed from the stored procedure can be well understood from,

ALTER procedure [dbo].[GetAllUserAnswersAgainstCategory]
    @CategoryId  int
    as
    BEGIN
        SET NOCOUNT ON;
        select a.AnswerId,a.FUID,a.PostedDate,c.Answer
        from dbo.UserAnswers as a, dbo.tblCategoryAnswers as c
            where  a.CategoryId = @CategoryId and a.AnswerId = c.AnswerId
                order by a.PostedDate descEND

Now when I compiled my class library containing the dbml file, the output function yielded by Visual Studio was returning an Integer value.

That was quite an expected behavior from Visual Studio.

    <FunctionAttribute(Name:="dbo.GetAllUserAnswersAgainstCategory")>
    _GetAllUserAnswersAgainstCategory(<Parameter(Name:="CategoryId", DbType:="Int")> 
        ByVal categoryId As System.Nullable(Of Integer)) As Integer
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), categoryId)
        Return CType(result.ReturnValue, Integer)
    End Function

So what I did was, I changed the return type of function to this,

    <FunctionAttribute(Name:="dbo.GetAllUserAnswersAgainstCategory")>
    _Public Function GetAllUserAnswersAgainstCategory(<Parameter(Name:="CategoryId", DbType:="Int&amp;amp")>
        ByVal categoryId As System.Nullable(Of Integer)) As ISingleResult(Of GetAllUserAnswersAgainstCategoryResult)
        Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), categoryId)
        Return CType(result.ReturnValue, ISingleResult(Of GetAllUserAnswersAgainstCategoryResult))
    End Function

and declared a class manually inside the .dbml designer file that exhibits the values returned from my stored procedure.

Partial Public Class GetAllUserAnswersAgainstCategoryResult   

    Private _AnswerId As Integer   
    Private _Answer As String   
    Private _FUID As Integer   
    Private _PostedDate As System.Nullable(Of Date)   

    Public Sub New()       
        MyBase.New()   
    End Sub   

    <Column(Storage:="_AnswerId", DbType:="Int NOT NULL")> _   
    Public Property AnswerId() As Integer       
        Get           
            Return Me._AnswerId
            End Get
            Set(ByVal value As Integer)           
            If ((Me._AnswerId = value) _ = False) Then
                        Me._AnswerId = value
                End If
           End Set   
    End Property   

    <Column(Storage:="_Answer", DbType:="VarChar(1024) NOT NULL", CanBeNull:=False)> _   
    Public Property Answer() As String
        Get           
            Return Me._Answer       
        End Get       

        Set(ByVal value As String)           
            If (String.Equals(Me._Answer, value) = False) Then               
                Me._Answer = value           
            End If       
        End Set   
    End Property   

    <Column(Storage:="_FUID", DbType:="Int NOT NULL")> _   
    Public Property FUID() As Integer       
        Get           
            Return Me._FUID       
        End Get       
        Set(ByVal value As Integer)           
            If ((Me._FUID = value) _ = False) Then
                        Me._FUID = value           
            End If           End Set   
    End Property   

    <Column(Storage:="_PostedDate", DbType:="DateTime")> _   
    Public Property PostedDate() As System.Nullable(Of Date)       
        Get
            Return Me._PostedDate       
        End Get

        Set(ByVal value As System.Nullable(Of Date)) 
            If (Me._PostedDate.Equals(value) = False) Then
                Me._PostedDate = value
            End If       
        End Set   
    End Property
End Class

Compiled and I was getting the required values from my composite type or wrapper class upon each iteration.

Filed under: Java Linq Sql Stored Procedure composite hibernate
11
To Posterous, Love Metalab