Return Multiple List from method - C#

Question

Ok. So this has been asked before but I can't get a hint from those questions (maybe due to I'm new to this generic concept).

So I have code something like this :-

if (dbundle.BoardFK != null && dbundle.ClassesFK != null && dbundle.ClassSubjectFK != null && !string.IsNullOrEmpty(Request.Form["MaterialTypeFK"].ToString()))
{
List<ClassMaterial> ClassMaterialList = null;
ClassMaterialList = PagingList(dbundle);
}
else if (dbundle.DegreeFK != null && dbundle.DegreeStudyFieldFK != null && dbundle.DegreeCourseFK != null && dbundle.DegreeUnivFK != null && dbundle.YearFK != null && dbundle.SemesterFK != null && dbundle.DegreeSubjectFK != null && !string.IsNullOrEmpty(Request.Form["MaterialTypeFK"].ToString()))
{
 dbundle.MaterialIndicator = Convert.ToInt32(Request.Form["MaterialTypeFK"]);
 List<DegreeMaterial> DegreeMaterialList = null;
DegreeMaterialList = PagingList(dbundle);
} // This continues like this

And My PagingList method returns a paged list :-

public AllTblListClass PagingList(DataBundle aListBundle)
    {
        AllTblListClass TblList = new AllTblListClass();

        using (SqlConnection conn = new SqlConnection(Constants.Connection))
        {

            if (!string.IsNullOrEmpty(aListBundle.ListType))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("FetchPagedList", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@ListType", SqlDbType.VarChar, 50).Value = aListBundle.ListType;
                if (aListBundle.ListType.Equals(Constants.ClassSubMatRelation))
                {
                    cmd.Parameters.Add("@BoardFK", SqlDbType.Int).Value = aListBundle.BoardFK;
                    cmd.Parameters.Add("@ClassFK", SqlDbType.Int).Value = aListBundle.ClassesFK;
                    cmd.Parameters.Add("@ClassSubjectFK", SqlDbType.Int).Value = aListBundle.ClassSubjectFK;
                }
                else if (aListBundle.ListType.Equals(Constants.DegreeSubMatRel))
                {
                    cmd.Parameters.Add("@DegreeFK", SqlDbType.Int).Value = aListBundle.DegreeFK;
                    cmd.Parameters.Add("@DegreeStudyFieldFK", SqlDbType.Int).Value = aListBundle.DegreeStudyFieldFK;
                    cmd.Parameters.Add("@DegreeCourseFK", SqlDbType.Int).Value = aListBundle.DegreeCourseFK;
                    cmd.Parameters.Add("@DegreeUnivFK", SqlDbType.Int).Value = aListBundle.DegreeUnivFK;
                    cmd.Parameters.Add("@YearFK", SqlDbType.Int).Value = aListBundle.YearFK;
                    cmd.Parameters.Add("@SemesterFK", SqlDbType.Int).Value = aListBundle.SemesterFK;
                    cmd.Parameters.Add("@DegreeSubjectFK", SqlDbType.Int).Value = aListBundle.DegreeSubjectFK;
                }
                else if (aListBundle.ListType.Equals(Constants.DiplomaSubMatRel))
                {
                    cmd.Parameters.Add("@DiplomaFK", SqlDbType.Int).Value = aListBundle.DiplomaFK;
                    cmd.Parameters.Add("@DiplomaStudyFieldFK", SqlDbType.Int).Value = aListBundle.DiplomaStudyFieldFK;
                    cmd.Parameters.Add("@DiplomaCourseFK", SqlDbType.Int).Value = aListBundle.DiplomaCourseFK;
                    cmd.Parameters.Add("@DiplomaUnivFK", SqlDbType.Int).Value = aListBundle.DiplomaUnivFK;
                    cmd.Parameters.Add("@YearFK", SqlDbType.Int).Value = aListBundle.YearFK;
                    cmd.Parameters.Add("@SemesterFK", SqlDbType.Int).Value = aListBundle.SemesterFK;
                    cmd.Parameters.Add("@DiplomaSubjectFK", SqlDbType.Int).Value = aListBundle.DiplomaSubjectFK;
                }
                else if (aListBundle.ListType.Equals(Constants.CertificateRel))
                {
                    cmd.Parameters.Add("@CertificateFK", SqlDbType.Int).Value = aListBundle.CertificateFK;
                    cmd.Parameters.Add("@CertificateTypeFK", SqlDbType.Int).Value = aListBundle.CertificateTypeFK;
                    cmd.Parameters.Add("@CertSubjectFK", SqlDbType.Int).Value = aListBundle.CertificateSubjectFK;
                }

                cmd.Parameters.Add("@MaterialTypeFK", SqlDbType.Int).Value = aListBundle.MaterialIndicator;

                SqlDataReader reader = null;
                reader = cmd.ExecuteReader();
                if (reader.HasRows)
                    while (reader.Read())
                    {
                        //Want to implement reader here
                    }

                reader.Close();
                cmd.Dispose();
                conn.Close();
            }
        }

        if (aListBundle.ListType.Equals(Constants.ClassSubMatRelation))
        {
            List<ClassMaterial> cm = new List<Models.ClassMaterial>();
            TblList.MatrlObj = cm;
            return TblList.MatrlObj; // want to return list from here
        }

        else
            return null;
    }

And my AllTblListClass class I've added all the list in this.

public class AllTblListClass
{
    public List<ClassMaterial> MatrlObj { get; set; }
    public List<CourseTbl> Courses { get; set; }
    public List<CourseDesc> CourseDesc { get; set; }
    public List<CourseSubDesc> CourseSubDesc { get; set; }
    public List<Contact> Contacts { get; set; }

}

My SP FetchPagedList is as:-

Alter Proc FetchPagedList
@PageStart int,
@PageEnd int,
@TblName varchar(200),
@BoardFK int=null, 
@ClassFK int=null, 
@ClassSubjectFK int=null, 
@MaterialTypeFK int=null, 
@ClassMaterialFK int=null,
@DegreeFK int=null, 
@DegreeStudyFieldFK int=null, 
@DegreeCourseFK int=null, 
@DegreeUnivFK int=null, 
@DegreeSubjectFK int=null,
@DiplomaFK int=null, 
@DiplomaStudyFieldFK int=null, 
@DiplomaCourseFK int=null, 
@DiplomaUnivFK int=null, 
@DiplomaSubjectFK int=null,
@YearFK int=null,
@SemesterFK int=null,
@CertificateFK int=null,
@CertificateTypeFK int=null,
@CertSubjectFK int=null
--@YearFK int=null,

As Begin

SET NOCOUNT ON;
SET XACT_ABORT ON;

BEGIN TRY
BEGIN TRANSACTION;

if(@TblName='ClassSubMatRelation')
BEGIN
    WITH PagingItem AS
    (
        SELECT cm.ClassMaterialID, cm.Name, cm.Description, cm.EbookLink,
        ROW_NUMBER() OVER (ORDER BY ClassSubMatRelationID) AS 'RowNumber'
        FROM ClassSubMatRelation csmr join ClassMaterial cm on cm.ClassMaterialID=csmr.ClassMaterialFK
        where BoardFK = @BoardFK and ClassFK = @ClassFK and ClassSubjectFK=@ClassSubjectFK and MaterialTypeFK=@MaterialTypeFK 
    ) 
    SELECT * FROM PagingItem WHERE RowNumber BETWEEN @PageStart AND @PageEnd;
END

else if(@TblName='DegreeSubMatRel')
BEGIN
    WITH PagingItem AS
    (
        SELECT dm.DegreeMaterialID,
        ROW_NUMBER() OVER (ORDER BY DegreeSubMatRelID) AS 'RowNumber'
        FROM DegreeSubMatRel  dsmr join DegreeMaterial dm on dsmr.DegreeMaterialFK = dm.DegreeMaterialID
        where DegreeFK = @DegreeFK and DegreeStudyFieldFK = @DegreeStudyFieldFK and DegreeCourseFK=@DegreeCourseFK and DegreeUnivFK=@DegreeUnivFK and DegreeSubjectFK=@DegreeSubjectFK and YearFK=@YearFK and SemesterFK=@SemesterFK and MaterialTypeFK=@MaterialTypeFK
    ) 
    SELECT * FROM PagingItem WHERE RowNumber BETWEEN @PageStart AND @PageEnd;
END

else if(@TblName='DiplomaSubMatRel')
BEGIN
    WITH PagingItem AS
    (
        SELECT dm.DiplomaMaterialID, dm.Name, dm.Description,
        ROW_NUMBER() OVER (ORDER BY DiplomaSubMatRelID) AS 'RowNumber'
        FROM DiplomaSubMatRel  dsmr join DiplomaMaterial dm on dsmr.DiplomaMaterialFK = dm.DiplomaMaterialID
        where DiplomaFK = @DiplomaFK and DiplomaStudyFieldFK = @DiplomaStudyFieldFK and DiplomaCourseFK=@DiplomaCourseFK and DiplomaUnivFK=@DiplomaUnivFK and DiplomaSubjectFK=@DiplomaSubjectFK and YearFK=@YearFK and SemesterFK=@SemesterFK and MaterialTypeFK=@MaterialTypeFK
    ) 
    SELECT * FROM PagingItem WHERE RowNumber BETWEEN @PageStart AND @PageEnd;
END

else if(@TblName='CertificateRelRel')
BEGIN
    WITH PagingItem AS
    (
        SELECT cm.CertificateMaterialID, cm.Name, cm.Description, 
        ROW_NUMBER() OVER (ORDER BY CertificateMaterialID) AS 'RowNumber'
        FROM CertificateRel  cr join CertificateMaterial cm on cr.CertificateMaterialFK = cm.CertificateMaterialID
        where CertificateFK = @CertificateFK and CertificateTypeFK = @CertificateTypeFK and CertSubjectFK=@CertSubjectFK and MaterialTypeFK=@MaterialTypeFK
    ) 
    SELECT * FROM PagingItem WHERE RowNumber BETWEEN @PageStart AND @PageEnd;
END

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF (XACT_STATE()) = -1  
    BEGIN  
        ROLLBACK TRANSACTION;  
    END;  

    IF (XACT_STATE()) = 1  
    BEGIN  
        COMMIT TRANSACTION;     
    END;  
END CATCH

End

I want to return list based on the conditional value. But I can't figure out how to do this. Any help is appreciated.

Answer 1

On Second Thoughts:

Instead on Returning individual lists - Why not return the container:

Instead of  --> return TblList.MatrlObj
Do --> return TblList;

And while consuming the PagingList method - Use relevant Inner Object:

ClassMaterialList = PagingList(dbundle).MatrlObj;

Your Code is going to be messy & prone to mistakes (below is my Initial Thought).

Since AllTblListClass and the DataBundle are broad Compositions and they try to do everything possible with DB, so its bit difficult to write cleaner code. Here's my attempt:

private void FillClassMaterialsTable(DataBundle dataBundle, ref AllTblListClass dataStore)
{
    // Ado.Net code to call Stored Proc & fetch results
    dataStore.MatrlObj = #List of Data of Relevant Type#
}

private void FillCoursesTable(DataBundle dataBundle, ref AllTblListClass dataStore)
{
    // Ado.Net code to call Stored Proc & fetch results
    dataStore.Courses = #List of Data of Relevant Type#
}

... // So on create simpler 15/20 methods for each Table/Stored Proc call

public AllTblListClass GetDataStore(DataBundle aListBundle)
{
AllTblListClass result = new AllTblListClass();
 switch(aListBundle.ListType)
 {
    case Constants.ClassSubMatRelation:
    {
    FillClassMaterialsTable(aListBundle, ref result);
    }
    break;

    case Constants.ClassCourses:
    {
    FillCoursesTable(aListBundle, ref result);
    }
    break;
 }
 return result;
}

Having Individual smaller methods - help to localize code change and will not break other pieces. This also helps to test individual functionality. The GetDataStore method is cleaner now, in sense that any novice developer can follow the code (simplified switch case).

Ideally - I would recommend if All the types (ClassMaterial, CourseTbl, CourseDesc etc.) They all Inherit/Implement from something common. So that you can use simpler return types. Also, since each table has its own columns in where clause - it will take bit more effort to refactor your code. But I would like to generalize the ADO.NET code into a Command Wrapper class -- where you can pass Table name & params and receive a DataTable/DataSet from it.