StoredProcedure from Multiple Tables

[Garry]

I've seen a few posts on here about stored procedures and not being able to handle no table classes. My question however is how would I handle the situation where I have manually defined a class that contains the return fields of a stored procedure that pulls data from multiple tables but I want to use OpenAccess as my DAL and call the SProc and fill my class so I can bind grids and what not to it?

I thought I had this all setup correctly, but when OpenAccess hits this line of code:

static public IObjectScope GetNewObjectScope()
        {
            Database db = Database();

            IObjectScope newScope = db.GetObjectScope();
            return newScope;
        }

It throws an error stating it could not find any persistent classes which is correct I am not persisting any classes I simply doing Selects to fill a GenericList of a certain class from different SProcs.

If it is not clear what I am asking please let me know and I will try and think of a better way to word my question.

Here is the stored procedure

/****** Object:  StoredProcedure [dbo].[rpts8HAPRegister]    Script Date: 08/04/2009 11:25:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO


CREATE  Procedure [dbo].[rpts8HAPRegister] @HAPPayeePKs varchar(255) = null,
                                      @ParticipantPKs varchar(255) = null,
                                      @CaseWorkerPKs varchar(255) = null,
                                      @IsActiveParticipant varchar (4), --  07/15/2003
                                      @AgencyPKs varchar(255) = null,
                                      @IncrementPKs varchar(255) = null,
                                      @StartDate DateTime ,
                                      @EndDate DateTime
AS
SET NOCOUNT ON
/* 06/18/2002 Lmontero (Copied and modified to incorporate new input parameters)
*
*
* Called From:  Program
*
* Input:  List of Resident Participant PKs (can be null)
*         List of CaseWorker PKs (can be null)
*         List of Agency PKs (can be null)
*         Start Date
*         End Date
*
* Output:  Fields for report
*
* Revisions:
*   07/03/2002 lmontero - Event 201518 to include reimbursements
*                         This procedure will be used for three different HAP
*                          registers depending on the input parameters.
*                          Only one List of PKs can be used at a time.
*   10/22/2002 VMcBain  - Event 202294.  Added Check Cleared Date field.
*   11/11/2002 lmontero - Event 208860. Added Left Joins to all agency tables
*   07/15/2003 TKendall - Event 219913. Added @IsActiveParticipant, and reversed logic of osParticipant.IsActive
*                                       Added additional output field : PayeeFileNameAs
*   08/19/2004 YSh      - Event 232813. Fixed inaccurate usage @joinSQL
*   12/13/2005 rdm      - CR# 1-1756 - added company module to company worker join
*   01/12/2006 AComstock- Ticket 1-24996.  Changed date constraints to verify against
*                                          bbRegister.ItemDate rather than TnTran.ItemDate,
*                                          because the latter is a pre-payment generation date.
*   08/22/2006 AComstock - Ticket 1-32552 - adjusted caseworker constraints so that
*               a resident with an assigned caseworker but no primary
*               caseworker will still have line item payments show up.
*   03/02/2007 Vlad. CR# 1-6169 Modified procedure to disguise Tax ID.
*   03/28/2008 DHernandez -- CR# 1-52480 Get Increment code.
*/

-- 03/02/2007 Vlad. CR# 1-6169.
Declare @UseDisguisedTaxID Varchar(3)
Set @UseDisguisedTaxID = 'No'
If (Select Top 1 isActive
    From osCompanyModuleCAuthorization
    Where Description = 'Use Disguised Tax ID') = 'Yes'
     Set @UseDisguisedTaxID = 'Yes'
Else Set @UseDisguisedTaxID = 'No'

Declare @SQL varchar(5000)
Declare @joinSQL varchar(1000)

/* ysh 8/18/04 Assign blank string to the variable
set @JoinSQL = null */
set @JoinSQL = ''

/* ysh 8/18/04 Moved this statement to the end and fixed it.
--Return "Active" Participants when @IsActiveParticipant = 'No'-- 07/15/2003
If @IsActiveParticipant = 'No'
 Set @JoinSQL =  ' And Resident.IsActive like ''Yes'''
*/

If @AgencyPKs is not null and @AgencyPKs <> '' and @AgencyPKs <> '0'
  Begin
    Set @JoinSQL = ' And s8Agency.PK in (' + @AgencyPKs + ')'
    If @ParticipantPKs is not null and @ParticipantPKs <> '' and @ParticipantPKs <> '0'
      Set @JoinSQL = @JoinSQL + ' And Resident.PK in (' + @ParticipantPKs + ')'
    Else If @CaseWorkerPKs is not null and @CaseWorkerPKs <> '' and @CaseWorkerPKs <> '0'
      Set @JoinSQL = @JoinSQL + ' And osCompanyWorker.PK in (' + @CaseWorkerPKs + ')'
    Else If @HapPayeePKs is not null and @HapPayeePKs <> '' and @HapPayeePKs <> '0'
      Set @JoinSQL = @JoinSQL + ' And osParticipantHAPPayee.PK in (' + @HapPayeePKs + ')'
    Else If @IncrementPKs is not null and @IncrementPKs <> '' and @IncrementPKs <> '0'
      Set @JoinSQL = @JoinSQL + ' And s8Increment.pk in (' + @IncrementPKs + ')' -- 03/28/2008 DHernandez @IncrementPKs
  End
Else If @ParticipantPKs is not null and @ParticipantPKs <> '' and @ParticipantPKs <> '0'
  Set @JoinSQL = ' And Resident.PK in (' + @ParticipantPKs + ')'
Else If @CaseWorkerPKs is not null and @CaseWorkerPKs <> '' and @CaseWorkerPKs <> '0'
  Set @JoinSQL = ' And osCompanyWorker.PK in (' + @CaseWorkerPKs + ')'
Else If @HapPayeePKs is not null and @HapPayeePKs <> '' and @HapPayeePKs <> '0'
  Set @JoinSQL = ' And osParticipantHAPPayee.PK in (' + @HapPayeePKs + ')'
Else If @IncrementPKs is not null and @IncrementPKs <> '' and @IncrementPKs <> '0'
  Set @JoinSQL = @JoinSQL + ' And s8Increment.pk in (' + @IncrementPKs + ')' -- 03/28/2008 DHernandez @IncrementPKs

If @IsActiveParticipant = 'No'
 Set @JoinSQL =  @JoinSQL + ' And Resident.IsActive like ''Yes'''


Set @SQL =
'  Select osEntityHAPPayee.FullName As PayeeFullName,'
+ ' osEntityHAPPayee.FileNameAs As PayeeFileNameAs,' -- 07.15.03

-- 03/02/2007 Vlad. CR# 1-6169.
--+ ' osEntityHAPPayee.TaxID As PayeeTaxId,'
If @UseDisguisedTaxID = 'Yes'
     Set @SQL = @SQL + ' osEntityHAPPayee.DisguisedTaxID As PayeeTaxId,'
Else Set @SQL = @SQL + ' osEntityHAPPayee.TaxID As PayeeTaxId,'

Set @SQL = @SQL
+ ' "ItemDate" = '
+ '  Case '
+ '    when bbRegister.ItemDate is not null then bbRegister.ItemDate'
+ '    Else ReimbursementStatus.LastStatusChangeDate'
+ '  end, '
+ '  bbRegister.VoidDate,bbRegister.ClearDate,'
+ '  "ItemType" = '
+ '  Case '
+ '    when bbRegister.ItemType is not null then S8TranType.Description'
+ '    Else Null'
+ '  end,'
+ ' bbRegister.ItemAmount * - 1 As CheckAmount,'
+ '  "CheckNumber" ='
+ '  Case '
+ '    when bbRegister.ItemType is not null then bbRegister.CheckNumber'
+ '    Else Null'
+ '  end,'
+ ' bbRegister.DirectDepositNumber,'
+ ' bbPaymentHeader.PayeeName, '
+ ' ResidentEntity.FullName As ResidentFullName,'
-- 03/02/2007 Vlad. CR# 1-6169.
--+ ' ResidentEntity.TaxID As ResidentTaxId,'
If @UseDisguisedTaxID = 'Yes'
     Set @SQL = @SQL + ' ResidentEntity.DisguisedTaxID As ResidentTaxId,'
Else Set @SQL = @SQL + ' ResidentEntity.TaxID As ResidentTaxId,'

Set @SQL = @SQL
+ ' ResidentEntity.EntityID As ResidentId,'
+ ' tnTranPayments.TranAmount As PaymentAmount,'
+ ' MLStnTranType.Description As PaymentType,'
+ ' osCompanyWorker.FullName AS CaseWorkerFullName,'
+ ' tnTranPayments.TranDate,'
+ ' S8TranType.Description,'
+ ' ptApprovedCertification.UnitPrimaryStreet,'
+ ' ptApprovedCertification.UnitCity,'
+ ' ptApprovedCertification.UnitState,'
+ ' ptApprovedCertification.UnitZip,'
+ ' ptApprovedCertification.UnitSuite,'
+ ' bbRegister.PK As bbRegisterPK,'
+ ' osParticipantHAPPayee.PK As PayeeParticipantPK,'
+ ' Resident.PK As ResidentParticipantPK,'
+ ' Resident.IsActive As ResidentIsActive,'
+ ' osCompanyWorker.PK As osCompanyWorkerPK,'
+ ' tnTran.PK As tnTranPK,'
+ ' ResidentEntity.FileNameAs As ResidentFileNameAs, '
+ ' bbBank.Description As Bank,'
+ ' s8PaymentAdjustment.AdjustmentReason,'
+ ' s8Agency.PK as AgencyPK,'
+ ' osEntityAgency.FullName as AgencyFullName,'
+ ' Increment' -- 03/28/2008 DHernandez -- CR# 1-52480 Get Increment code.
+ ' from s8Tran '
+ ' join tnTran On s8Tran.fktnTran = tnTran.PK'
+ ' Join tnSummaryTran On tnTran.fktnSummaryTran = tnSummaryTran.PK '
+ ' Join TnTran TnTranPayments on TnTranPayments.fkTnSummaryTran = TnSummaryTran.PK '
+ ' Join MlsTnTranType S8TranType On S8TranType.Pk = S8Tran.fkMlsTnTranType'
+ ' Join MlsTnTranType On MlsTnTranType.Pk = TnTranPayments.fkMlsTnTranType'
+ '       and MlsTnTranType.FunctionCode = ''Payment'' '
+ ' Join s8IssueTran On s8Tran.fks8IssueTran = s8IssueTran.PK '
-- 03/28/2008 DHernandez -- CR# 1-52480 Get Increment code.
+ '   Left Join s8Increment on s8Increment.pk = s8IssueTran.fks8Increment'
+ ' Left Join s8AgencyIncrementLink On s8IssueTran.fks8Increment = s8AgencyIncrementLink.fks8Increment'
+ ' Left Join s8Agency On s8AgencyIncrementLink.fks8Agency = s8Agency.PK'
+ ' Left Join osParticipant osParticipantAgency On fkosParticipantAgency = osParticipantAgency.Pk'
+ ' Left Join osEntity osEntityAgency On osParticipantAgency.fkosEntity = osEntityAgency.PK'
+ ' Join osParticipant osParticipantHAPPayee On tnTran.fkosParticipant = osParticipantHAPPayee.PK '
+ ' Join osEntity osEntityHAPPayee On osParticipantHAPPayee.fkosEntity = osEntityHAPPayee.PK'
+ ' Join osParticipant Resident On s8IssueTran.fkosParticipant = Resident.PK'
+ ' Join osEntity ResidentEntity On Resident.fkosEntity = ResidentEntity.PK '
+ ' Join ptApprovedCertification On s8Tran.fkptApprovedCertification = ptApprovedCertification.PK'
+ ' Left join tnHeader On tnSummaryTran.fktnHeader = tnHeader.PK'
+ ' left join bbPaymentLineItemTranLink on bbPaymentLineItemTranLink.fktnTranPaymentRecord = TnTranPayments.Pk'
+ ' left Join bbPaymentLineItem on bbPaymentLineItem.Pk =  bbPaymentLineItemTranLink.fkbbPaymentLineItem'
+ ' left Join bbPaymentHeader on bbPaymentHeader.Pk = bbPaymentLineItem.fkbbPaymentHeader'
+ ' left Join bbRegister on bbRegister.Pk = bbPaymentHeader.fkbbRegister'
--added for reimbrusements
+ ' left join bbReimbursementTnTranLink on bbReimbursementTnTranLink.fktnTranPaymentAdjustment = TnTranPayments.pk'
+ ' left join bbReimbursement on bbReimbursement.pk = fkbbReimbursement'
+ ' left join bbregister bbregisterReimbursement on bbregisterReimbursement.pk =  bbReimbursement.fkbbregister'
+ ' Left Join osControlNumberStatus ReimbursementStatus on '
+ '     ReimbursementStatus.Pk = bbregisterReimbursement.fkosControlNumberPostStatus'
+ ' Left join bbBank On bbRegister.fkbbBank = bbBank.PK'
+ ' Left join s8PaymentAdjustment On s8Tran.fks8PaymentAdjustment = s8PaymentAdjustment.PK'
+ ' Left join osParticipantCompanyWorker On osParticipantCompanyWorker.fkosParticipant = Resident.PK '
+ '             and osParticipantCompanyWorker.fkosCompanyModule = Resident.fkosCompanyModule'      -- 12/13/2005 limit by company module
+ '             and osParticipantCompanyWorker.IsPrimaryWorker = ''Yes'''           --added 8/22/2006 AComstock
+ ' Left join osCompanyWorker On osParticipantCompanyWorker.fkosCompanyWorker = osCompanyWorker.PK'
+ ' Where'
--      + ' (osParticipantCompanyWorker.IsPrimaryWorker = ''Yes'' '     --removed 8/22/06 AComstock
--      + ' OR osParticipantCompanyWorker.IsPrimaryWorker IS NULL)'         --removed 8/22/06 AComstock
--      + ' And tnTran.TranDate >=  ''' + cast(@startdate as varchar) + ''' '   --removed 8/22/06 AComstock
--      + ' And bbRegister.ItemDate >=  ''' + cast(@startdate as varchar) + ''' '   --1/12/06 1-3831 AComstock --removed 8/22/06 AComstock
+ ' bbRegister.ItemDate >=  ''' + cast(@startdate as varchar) + ''' '   --added 8/22/2006 AComstock
+ ' And bbRegister.ItemDate <= ''' + cast(@Enddate as varchar) + ''' '   --1/12/06 1-3831 AComstock
--Updated 10/6/02 TKendall Event 208860
If @joinSQL is not null
  Set @SQL = @SQL + @joinSQL

Exec (@SQL)


GO

My Class that contains the SP's fields

using System;
using System.Collections.Generic;

namespace GambitCreations.Sitefinity.LandLordModule.Objects
{
    // Generated by Telerik OpenAccess
    // Used template: c:\program files (x86)\telerik\openaccess orm\sdk\IDEIntegrations\templates\PCClassGeneration\cs\templates\classgen\class\partialuserdefault.vm
    // NOTE: Field declarations and 'Object ID' class implementation are added to the 'designer' file.
    //       Changes made to the 'designer' file will be overwritten by the wizard.
    [Serializable]
    public partial class LandLordHAP
    {
        //The 'no-args' constructor required by OpenAccess.
        public LandLordHAP()
        {
        }

        public string PayeeFullName
        {
            get { return payeeFullName; }
            set { this.payeeFullName = value; }
        }

        public string PayeeFileNameAs
        {
            get { return payeeFileNameAs; }
            set { this.payeeFileNameAs = value; }
        }

        public string PayeeTaxID
        {
            get { return payeeTaxID; }
            set { this.payeeTaxID = value; }
        }

        public DateTime? ItemDate
        {
            get { return itemDate; }
            set { this.itemDate = value; }
        }

        public DateTime? VoidDate
        {
            get { return voidDate; }
            set { this.voidDate = value; }
        }

        public DateTime? ClearDate
        {
            get { return clearDate; }
            set { this.clearDate = value; }
        }

        public string ItemType
        {
            get { return itemType; }
            set { this.itemType = value; }
        }

        public Decimal? CheckAmount
        {
            get { return checkAmount; }
            set { this.checkAmount = value; }
        }

        public string CheckNumber
        {
            get { return checkNumber; }
            set { this.checkNumber = value; }
        }

        public string DirectDepositNumber
        {
            get { return directDepositNumber; }
            set { this.directDepositNumber = value; }
        }

        public string PayeeName
        {
            get { return payeeName; }
            set { this.payeeName = value; }
        }

        public string ResidentFullName
        {
            get { return residentFullName; }
            set { this.residentFullName = value; }
        }

        public string ResidentFileNameAs
        {
            get { return residentFileNameAs; }
            set { this.residentFileNameAs = value; }
        }

        public string ResidentTaxID
        {
            get { return residentTaxID; }
            set { this.residentTaxID = value; }
        }

        public int ResidentID
        {
            get { return residentID; }
            set { this.residentID = value; }
        }

        public Decimal? PaymentAmount
        {
            get { return paymentAmount; }
            set { this.paymentAmount = value; }
        }

        public string PaymentType
        {
            get { return paymentType; }
            set { this.paymentType = value; }
        }

        public string CaseWorkerFullName
        {
            get { return caseWorkerFullName; }
            set { this.caseWorkerFullName = value; }
        }

        public DateTime? TranDate
        {
            get { return tranDate; }
            set { this.tranDate = value; }
        }

        public string Description
        {
            get { return description; }
            set { this.description = value; }
        }

        public string UnitPrimaryStreet
        {
            get { return unitPrimaryStreet; }
            set { this.unitPrimaryStreet = value; }
        }

        public string UnitCity
        {
            get { return unitCity; }
            set { this.unitCity = value; }
        }

        public string UnitState
        {
            get { return unitState; }
            set { this.unitState = value; }
        }

        public string UnitZip
        {
            get { return unitZip; }
            set { this.unitZip = value; }
        }

        public string UnitSuite
        {
            get { return unitSuite; }
            set { this.unitSuite = value; }
        }

        public int BBRegisterPK
        {
            get { return bbRegisterPK; }
            set { this.bbRegisterPK = value; }
        }

        public int PayeeParticipantPK
        {
            get { return payeeParticipantPK; }
            set { this.payeeParticipantPK = value; }
        }

        public int ResidentParticipantPK
        {
            get { return residentParticipanPK; }
            set { this.residentParticipanPK = value; }
        }

        public bool ResidentIsActive
        {
            get { return residentIsActive; }
            set { this.residentIsActive = value; }
        }

        public int OSCompanyWorkerPK
        {
            get { return osCompanyWorkerPK; }
            set { this.osCompanyWorkerPK = value; }
        }

        public int TNTranPK
        {
            get { return tnTranPK; }
            set { this.tnTranPK = value; }
        }

        public string Bank
        {
            get { return bank; }
            set { this.bank = value; }
        }

        public string AdjustmentReason
        {
            get { return adjustmentReason; }
            set { this.adjustmentReason = value; }
        }

        public int AgencyPK
        {
            get { return agencyPK; }
            set { this.agencyPK = value; }
        }

        public string AgencyFullName
        {
            get { return agencyFullName; }
            set { this.agencyFullName = value; }
        }

        public string Increment
        {
            get { return increment; }
            set { this.increment = value; }
        }
    }
}

[PetarP]

Hello Garry Clark,

The problem comes from that Telerik OpenAccess ORM needs persistent classes to operate. Currently having no persistent classes in an enabled project is not supported. The workaround would be to create a dummy class and mark it as persistent. Remember to set the Update database property of your project to false as you do not want this dummy class to end up in your database.

Sincerely yours,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.