none
how use multiple if condition in Stored Procedure Using LINQ RRS feed

  • Question

  • Hi All
    I need a Help for LINQ in Store procedure. now i am using LINQ2SQL. in my DB using strore procedure in my SP uisng mutiple "IF Condition" how to get the result. please look into below my SP

    Create PROCEDURE [dbo].[sp_QueryAllValues]
     ( 
      @iMode numeric
     )
    AS

    Begin

     if @iMode=100
     BEGIN
      Select * from IP_StatusMas000 where Priority>=0 order by Priority
     END

     if @iMode=101
     begin
      Select * from IP_StatusMas000 order by Priority
     end

     if @iMode=102
     begin
      Select * from IP_UserMas000
     end

     if @iMode=103
     Begin
      Select * from IP_DMSDocTyMas000
     End

     if @iMode=104
     Begin
      Select * from IP_DMSExReason000
     End

     if @iMode=105
     Begin
      Select * from IP_RegionMas000
     End

     if @iMode=106
     Begin
      Select a.UsrCd,a.UsrNm,a.DomainId,a.SUsrCd,SUserNm=(Select UsrNm from IP_UserMas000 where UsrCd=a.SUsrCd)
      from IP_UserMas000 a where a.SUsrCd in
      (Select UsrCd from IP_UserMas000 where SUser=1 and Admin=0)
      order by a.SUsrCd
     End

    End

     

    Monday, January 23, 2012 7:18 AM

Answers

  • Hi gvbas;

    The example only showed two but you can have more as shown below. Each of the ResultType is a class in your code that defines all the columns being returned for that type from the SP.

    <FunctionAttribute(Name:="dbo.sp_QueryAllValues") _
    <ResultType(GetType(IP_StatusMas000))> _
    <ResultType(GetType(IP_UserMas000))> _
    <ResultType(GetType(IP_DMSDocTyMas000))> _
    <ResultType(GetType(IP_DMSExReason000))> _
    <ResultType(GetType(IP_RegionMas000))> _
    Public Function sp_QueryAllValues(<Parameter(DbType:="Decimal")> ByVal iMode As System.Nullable(Of Decimal)) As IMultipleResults
            Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), iMode)
            Return CType(result.ReturnValue, IMultipleResults)
    End Function
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, January 25, 2012 8:03 PM

All replies

  • Hi gvbas;

    Please see this web post, LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures), the section marked "Handling Multiple Result Shapes from SPROCs" for a solution.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Monday, January 23, 2012 3:21 PM
  • Hi Fernado,

    Thanks for Reponse. i tried to use for above example but my senorio is i have more then 3 if condition so i dont know how to configure column name.

    please help me on this..

      Dim results As IMultipleResults = db.sp_QueryAllValues(105)
                Dim objReader = results.GetResult(Of sp_QueryAllValuesResult)().ToList

     

                DataGridView1.DataSource = objReader

    ------------------------------------------------------------------------------------------

    <FunctionAttribute(Name:="dbo.sp_QueryAllValues"), _
        ResultType(GetType(sp_QueryAllValuesResult))> _
        Public Function sp_QueryAllValues(<Parameter(DbType:="Decimal")> ByVal iMode As System.Nullable(Of Decimal)) As IMultipleResults


            Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), iMode)

            Return CType(result.ReturnValue, IMultipleResults)

        End Function

    ------------------------------------------------------------------------------------------

    Partial Public Class sp_QueryAllValuesResult

        Private _RunId As Integer

        Private _StatusNm As String

        Private _Priority As Integer

        Private _SLA As Integer

        Private _UsrStmp As String

        Private _TimStmp As Date

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

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

        <Column(Storage:="_StatusNm", DbType:="VarChar(75) NOT NULL", CanBeNull:=False)> _
        Public Property StatusNm() As String
            Get
                Return Me._StatusNm
            End Get
            Set(ByVal value As String)
                If (String.Equals(Me._StatusNm, Value) = False) Then
                    Me._StatusNm = Value
                End If
            End Set
        End Property

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

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

        <Column(Storage:="_UsrStmp", DbType:="VarChar(50)")> _
        Public Property UsrStmp() As String
            Get
                Return Me._UsrStmp
            End Get
            Set(ByVal value As String)
                If (String.Equals(Me._UsrStmp, Value) = False) Then
                    Me._UsrStmp = Value
                End If
            End Set
        End Property

        <Column(Storage:="_TimStmp", DbType:="DateTime NOT NULL")> _
        Public Property TimStmp() As Date
            Get
                Return Me._TimStmp
            End Get
            Set(ByVal value As Date)
                If ((Me._TimStmp = Value) _
                   = False) Then
                    Me._TimStmp = Value
                End If
            End Set
        End Property
    End Class

     

     

    Wednesday, January 25, 2012 6:55 AM
  • Hi gvbas;

    The example only showed two but you can have more as shown below. Each of the ResultType is a class in your code that defines all the columns being returned for that type from the SP.

    <FunctionAttribute(Name:="dbo.sp_QueryAllValues") _
    <ResultType(GetType(IP_StatusMas000))> _
    <ResultType(GetType(IP_UserMas000))> _
    <ResultType(GetType(IP_DMSDocTyMas000))> _
    <ResultType(GetType(IP_DMSExReason000))> _
    <ResultType(GetType(IP_RegionMas000))> _
    Public Function sp_QueryAllValues(<Parameter(DbType:="Decimal")> ByVal iMode As System.Nullable(Of Decimal)) As IMultipleResults
            Dim result As IExecuteResult = Me.ExecuteMethodCall(Me, CType(MethodInfo.GetCurrentMethod, MethodInfo), iMode)
            Return CType(result.ReturnValue, IMultipleResults)
    End Function
    

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, January 25, 2012 8:03 PM