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.

You mean - In the AllTblListClass - at a time only one of the Member Lists would be initialized? If yes, then this is not right design. — Prateek Shrivastava
– Prateek Shrivastava, Commented Mar 27, 2018 at 2:27
@prateek yes. So what should I do to get the correct result? — Deepak
– Deepak, Commented Mar 27, 2018 at 2:30
@Deepak - With this design (I guess you are trying to do weired Factory pattern) - You will have to check the aListBundle.ListType - a lot of times to find & fill the appropriate List in the AllTblListClass as well as also when trying to find which list contains data from consuming code. — Prateek Shrivastava
– Prateek Shrivastava, Commented Mar 27, 2018 at 2:32

Prateek Shrivastava · Accepted Answer · 2018-03-27 03:04:51Z

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.

edited Mar 27, 2018 at 3:04

answered Mar 27, 2018 at 2:51

Prateek Shrivastava

1,9221 gold badge13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Deepak Over a year ago

That's a great answer. I'll keep in mind the individual smaller method concept. Currently I'm struggling with how to assign datareader value to list. Thanks.

Prateek Shrivastava Over a year ago

@Deepak - DataReader is a Connected structure. That means - you loop through the reader until it reaches end of table/records. Always refer to MSDN for help: msdn.microsoft.com/en-us/library/… -- There is code sample at the end

Collectives™ on Stack Overflow

Return Multiple List from method - C#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related