Can anybody shed some light here please for creating the DAL using multiple tables for a module.

I am developing a module that maintains multiple tables. I have created an info, business class, and data provider class that maps to each of the tables. I used mygeneration to auto generate the basic classes. One question I have is when I create a stored procedure that performs a join on these tables do I have to create a seperate info, biz controller and data access classes for each procedure that uses a JOIN or is there a better way of doing this and still maintain the Dotnetnuke provider model?

Regards

Colm

---

Colm Ryan Director
3rd i IT and Business Services Limited

www.actravia.com
Registration and Booking Solutions for Dotnetnuke
www.3rdi.ie
IMCA Registered Management Consulting Practice. Business and IT Strategy, IT Project Management and Product Development

In the Survey module this stored procedure joins multiple tables:

create procedure {databaseOwner}{objectQualifier}GetSurvey

@SurveyID int,
@ModuleID int

as

select SurveyID,
       ModuleID,
       Question,
       ViewOrder,
       OptionType,
       'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
       CreatedDate,
       'Votes' = ( select sum(Votes) from {objectQualifier}SurveyOptions where {objectQualifier}SurveyOptions.SurveyID = {objectQualifier}Surveys.SurveyID )
from   {objectQualifier}Surveys
left outer join {objectQualifier}Users on {objectQualifier}Surveys.CreatedByUser = {objectQualifier}Users.UserID
where  SurveyID = @SurveyID
and    ModuleID = @ModuleID

GO

and this DAL mathod hydrates the SurveyInfo object:

 Public Shared Function GetSurvey(ByVal SurveyID As Integer, ByVal ModuleId As Integer) As SurveyInfo
            Dim SurveyInfo As SurveyInfo = New SurveyInfo
            Using dr As IDataReader = DataProvider.Instance().GetSurvey(SurveyID, ModuleId)
                While dr.Read
                    SurveyInfo.SurveyId = Convert.ToInt32(dr("SurveyId"))
                    SurveyInfo.ModuleId = Convert.ToInt32(dr("ModuleID"))
                    SurveyInfo.Question = Convert.ToString(dr("Question"))
                    SurveyInfo.OptionType = Convert.ToString(dr("OptionType"))
                    SurveyInfo.ViewOrder = Convert.ToInt32(ConvertNullInteger(dr("ViewOrder")))
                    SurveyInfo.Votes = Convert.ToInt32(ConvertNullInteger(dr("Votes")))
                    SurveyInfo.CreatedByUser = Convert.ToInt32(dr("CreatedByUser"))
                    SurveyInfo.CreatedDate = Convert.ToDateTime(dr("CreatedDate"))
                End While
            End Using
            Return SurveyInfo
        End Function

---

Michael Washington
http://ADefWebserver.com

A Free Open Source DotNetNuke Help Desk Module