Is this table design ok?

Camillia
Camillia used Ask the Experts™
on
I'm under a tight deadline (and have a moody manager who's not patient ) to remove some hardcoded .Net  code and create a table so we could read the data dynamically.

This is how the code looks like (there are 48 FieldIds...so 48 rows)
  Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });

Open in new window



And this one (used for some boolean fields)
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

Open in new window


This is how I want to do it:


A table with columns that match each property in the code. So,
Identity field (this is the auto increment identity field)
FieldId (ex-developer hasn't used sequential Ids. I want to keep the same Id because we have data saved already with these Ids)

TableName
ColumnName
DisplayName
DBType
Bool_ValueIfTrue
Bool_ValueIfFalse
Bool_TrueText
Bool_FalseText

Should I add the Identity field? Any other ideas given that I have to get this done fast?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
hmm, the problem is: What kind of class is ReportField? E.g. a field id makes not really sense. Without further context, it is hard to guess what's correct. When these fields are part of report generation, then it may be not a good idea to do it in a table.
yeah, not a good design to begin with. This is part of the class
 public ReportModel() : base()
        {
            #region Standard Fields

            Fields = new List<ReportField>();
            Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "2", TableName = _tbSchedule, ColumnName = "DateLeaseStarted", DisplayName = Normal.TermStart, DbType = DbType.Date });
            Fields.Add(new ReportField() { FieldId = "3", TableName = _tbSchedule, ColumnName = "DateLeaseEnds", DisplayName = Normal.TermEnd, DbType = DbType.Date });
            Fields.Add(new ReportField() { FieldId = "4", TableName = _tbUnit, ColumnName = "SerialNo", DisplayName = Normal.SerialNumber, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "5", TableName = _tbUnit, ColumnName = "Manufacturer", DisplayName = Normal.Manufacturer, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "6", TableName = _tbUnit, ColumnName = "Model", DisplayName = Normal.Model, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "7", TableName = _tbUnit, ColumnName = "Vendor", DisplayName = Normal.Vendor, DbType = DbType.String, });
            Fields.Add(new ReportField() { FieldId = "8", TableName = _tbUnit, ColumnName = "Quantity", DisplayName = Normal.Quantity, DbType = DbType.Int32 });
            Fields.Add(new ReportField() { FieldId = "9", TableName = _tbUnit, ColumnName = "SiteName", DisplayName = Normal.SiteName, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "10", TableName = _tbUnit, ColumnName = "SiteAddress", DisplayName = Normal.SiteAddress, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "11", TableName = _tbUnit, ColumnName = "SiteCity", DisplayName = Normal.SiteCity, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "12", TableName = _tbUnit, ColumnName = "SiteState", DisplayName = Normal.SiteState, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "13", TableName = _tbUnit, ColumnName = "SiteZip", DisplayName = Normal.SitePostalCode, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "14", TableName = _tbSchedule, ColumnName = "Rent", DisplayName = Normal.ScheduleRent, DbType = DbType.Decimal });
            Fields.Add(new ReportField() { FieldId = "15", TableName = _tbSchedule, ColumnName = "Tax", DisplayName = Normal.ScheduleTax, DbType = DbType.Decimal });
            Fields.Add(new ReportField() { FieldId = "16", TableName = _tbSchedule, ColumnName = "Sched", DisplayName = Normal.Schedule, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "17", TableName = _tbUnit, ColumnName = "SiteCountryCode", DisplayName = Normal.SiteCountry, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "18", TableName = _tbSchedule, ColumnName = "CurrCode", DisplayName = Normal.Currency, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "19", TableName = _tbUnit, ColumnName = "Rent", DisplayName = Normal.UnitRent, DbType = DbType.Decimal });
            Fields.Add(new ReportField() { FieldId = "20", TableName = _tbMasterLease, ColumnName = "CustomerName", DisplayName = Normal.CustomerName, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "21", TableName = _tbUnit, ColumnName = "Tax", DisplayName = Normal.UnitTax, DbType = DbType.Decimal });
            Fields.Add(new ReportField() { FieldId = "22", TableName = _tbUnit, ColumnName = "AssetInfo", DisplayName = Normal.AssetInfo, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "23", TableName = _tbUnit, ColumnName = "SiteAddress2", DisplayName = Normal.SiteAddress + "2", DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "24", TableName = _tbUnit, ColumnName = "BillName", DisplayName = Normal.BillingName, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "25", TableName = _tbUnit, ColumnName = "BillToAttn", DisplayName = Normal.BillingAttentionTo, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "26", TableName = _tbUnit, ColumnName = "BillAddress", DisplayName = Normal.BillingAddress, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "27", TableName = _tbUnit, ColumnName = "BillAddress2", DisplayName = Normal.BillingAddress + "2", DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "28", TableName = _tbUnit, ColumnName = "BillCity", DisplayName = Normal.BillingCity, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "29", TableName = _tbUnit, ColumnName = "BillState", DisplayName = Normal.BillingState, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "30", TableName = _tbUnit, ColumnName = "BillZip", DisplayName = Normal.BillingPostalCode, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "31", TableName = _tbUnit, ColumnName = "BillCountryCode", DisplayName = Normal.BillingCountry, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "32", TableName = _tbSchedule, ColumnName = "Insurance", DisplayName = Normal.Insurance, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "33", TableName = _tbSchedule, ColumnName = "Term", DisplayName = Normal.Term, DbType = DbType.Int32 });
            Fields.Add(new ReportField() { FieldId = "34", TableName = _tbUnit, ColumnName = "CustomerRef", DisplayName = Normal.CustomerReference, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "46", TableName = _tbSchedule, ColumnName = "Billing", DisplayName = Normal.Billed, DbType = DbType.String });
            Fields.Add(new ReportField() { FieldId = "47", TableName = _tbUnit, ColumnName = "DateAccepted", DisplayName = Normal.Accepted, DbType = DbType.Date });
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

            #endregion Standard Fields

            // External users will see their own UDF labels (based off the Master Lease)
            if (!CurrentUser.User.IsCSIInternalUser)
            {
                var dummySchedule = new CSI.MyC.Data.DataAccess.ScheduleAccess().GetAllSchedules().FirstOrDefault();
                if (dummySchedule != null)
                {
                    var ml = dummySchedule.MasterLease;
                    if (ml != null)
                    {
                        var udfData = new
                        {
                            UdfFilter = ml.UdFilterLabel,
                            Udf1 = ml.UdfLabel1,
                            Udf2 = ml.UdfLabel2,
                            Udf3 = ml.UdfLabel3,
                            Udf4 = ml.UdfLabel4,
                            Udf5 = ml.UdfLabel5,
                            Udf6 = ml.UdfLabel6,
                            Udf7 = ml.UdfLabel7,
                            Udf8 = ml.UdfLabel8,
                            Udf9 = ml.UdfLabel9,
                            Udf10 = ml.UdfLabel10
                        };

                        if (!string.IsNullOrWhiteSpace(udfData.UdfFilter)) Fields.Add(new ReportField() { FieldId = "35", TableName = _tbUnit, ColumnName = "UdFilterValue", DisplayName = udfData.UdfFilter, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf1)) Fields.Add(new ReportField() { FieldId = "36", TableName = _tbUnit, ColumnName = "Udf1", DisplayName = udfData.Udf1, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf2)) Fields.Add(new ReportField() { FieldId = "37", TableName = _tbUnit, ColumnName = "Udf2", DisplayName = udfData.Udf2, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf3)) Fields.Add(new ReportField() { FieldId = "38", TableName = _tbUnit, ColumnName = "Udf3", DisplayName = udfData.Udf3, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf4)) Fields.Add(new ReportField() { FieldId = "39", TableName = _tbUnit, ColumnName = "Udf4", DisplayName = udfData.Udf4, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf5)) Fields.Add(new ReportField() { FieldId = "40", TableName = _tbUnit, ColumnName = "Udf5", DisplayName = udfData.Udf5, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf6)) Fields.Add(new ReportField() { FieldId = "41", TableName = _tbUnit, ColumnName = "Udf6", DisplayName = udfData.Udf6, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf7)) Fields.Add(new ReportField() { FieldId = "42", TableName = _tbUnit, ColumnName = "Udf7", DisplayName = udfData.Udf7, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf8)) Fields.Add(new ReportField() { FieldId = "43", TableName = _tbUnit, ColumnName = "Udf8", DisplayName = udfData.Udf8, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf9)) Fields.Add(new ReportField() { FieldId = "44", TableName = _tbUnit, ColumnName = "Udf9", DisplayName = udfData.Udf9, DbType = DbType.String });
                        if (!string.IsNullOrWhiteSpace(udfData.Udf10)) Fields.Add(new ReportField() { FieldId = "45", TableName = _tbUnit, ColumnName = "Udf10", DisplayName = udfData.Udf10, DbType = DbType.String });
                    }
                }
            }
            else
            {
                Fields.Add(new ReportField() { FieldId = "35", TableName = _tbUnit, ColumnName = "UdFilterValue", DisplayName = Normal.UDFilterLabel, DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "36", TableName = _tbUnit, ColumnName = "Udf1", DisplayName = Normal.UserDefinedLabel + " 1", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "37", TableName = _tbUnit, ColumnName = "Udf2", DisplayName = Normal.UserDefinedLabel + " 2", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "38", TableName = _tbUnit, ColumnName = "Udf3", DisplayName = Normal.UserDefinedLabel + " 3", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "39", TableName = _tbUnit, ColumnName = "Udf4", DisplayName = Normal.UserDefinedLabel + " 4", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "40", TableName = _tbUnit, ColumnName = "Udf5", DisplayName = Normal.UserDefinedLabel + " 5", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "41", TableName = _tbUnit, ColumnName = "Udf6", DisplayName = Normal.UserDefinedLabel + " 6", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "42", TableName = _tbUnit, ColumnName = "Udf7", DisplayName = Normal.UserDefinedLabel + " 7", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "43", TableName = _tbUnit, ColumnName = "Udf8", DisplayName = Normal.UserDefinedLabel + " 8", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "44", TableName = _tbUnit, ColumnName = "Udf9", DisplayName = Normal.UserDefinedLabel + " 9", DbType = DbType.String });
                Fields.Add(new ReportField() { FieldId = "45", TableName = _tbUnit, ColumnName = "Udf10", DisplayName = Normal.UserDefinedLabel + " 10", DbType = DbType.String });
            }

            #region Remove international specific fields if the user is a customer and has only domestic Master Leases

            if (CurrentUser.User.IsExternal && CurrentUser.User.AuthMasterLeases != null && CurrentUser.User.AuthMasterLeases.Count > 0)
            {
                if (CurrentUser.User.AuthMasterLeases.Any(x => x.PortfolioID == 1 || x.PortfolioID == 2))
                {
                    Fields = Fields.Where(x => x.FieldId != "32").ToList(); // Remove the Insurance column
                }
            }

            #endregion Remove international specific fields if the user is a customer and has only domestic Master Leases

            

            // Sort by DisplayName so the data appears alphabetically on the page
            Fields = Fields.OrderBy(x => x.DisplayName.ToLower()).ToList();
        }

        #region Private Var

        private const string _tbMasterLease = "MasterLe";
        private const string _tbSchedule = "Sched";
        private const string _tbUnit = "Unit";

        #endregion Private Var

Open in new window

Senior Developer
Commented:
I would start by refactoring it. Cause any region indicates that this is necessary. Also it allows to remove unnecessary comments. E.g.

public ReportModel() : base()
{
    CreateFields();
    if (!CurrentUser.User.IsCSIInternalUser)
    {
        AddFieldsUserdefinedLabels();
    }
    else
    {
        AddFieldsNormalLabels();
    }

    HandleFieldInsurance();
    Fields = Fields.OrderBy(x => x.DisplayName.ToLower()).ToList();
}

private void CreateFields()
{
    Fields = new List<ReportField>();
    Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "2", TableName = _tbSchedule, ColumnName = "DateLeaseStarted", DisplayName = Normal.TermStart, DbType = DbType.Date });
    Fields.Add(new ReportField() { FieldId = "3", TableName = _tbSchedule, ColumnName = "DateLeaseEnds", DisplayName = Normal.TermEnd, DbType = DbType.Date });
    Fields.Add(new ReportField() { FieldId = "4", TableName = _tbUnit, ColumnName = "SerialNo", DisplayName = Normal.SerialNumber, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "5", TableName = _tbUnit, ColumnName = "Manufacturer", DisplayName = Normal.Manufacturer, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "6", TableName = _tbUnit, ColumnName = "Model", DisplayName = Normal.Model, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "7", TableName = _tbUnit, ColumnName = "Vendor", DisplayName = Normal.Vendor, DbType = DbType.String, });
    Fields.Add(new ReportField() { FieldId = "8", TableName = _tbUnit, ColumnName = "Quantity", DisplayName = Normal.Quantity, DbType = DbType.Int32 });
    Fields.Add(new ReportField() { FieldId = "9", TableName = _tbUnit, ColumnName = "SiteName", DisplayName = Normal.SiteName, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "10", TableName = _tbUnit, ColumnName = "SiteAddress", DisplayName = Normal.SiteAddress, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "11", TableName = _tbUnit, ColumnName = "SiteCity", DisplayName = Normal.SiteCity, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "12", TableName = _tbUnit, ColumnName = "SiteState", DisplayName = Normal.SiteState, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "13", TableName = _tbUnit, ColumnName = "SiteZip", DisplayName = Normal.SitePostalCode, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "14", TableName = _tbSchedule, ColumnName = "Rent", DisplayName = Normal.ScheduleRent, DbType = DbType.Decimal });
    Fields.Add(new ReportField() { FieldId = "15", TableName = _tbSchedule, ColumnName = "Tax", DisplayName = Normal.ScheduleTax, DbType = DbType.Decimal });
    Fields.Add(new ReportField() { FieldId = "16", TableName = _tbSchedule, ColumnName = "Sched", DisplayName = Normal.Schedule, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "17", TableName = _tbUnit, ColumnName = "SiteCountryCode", DisplayName = Normal.SiteCountry, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "18", TableName = _tbSchedule, ColumnName = "CurrCode", DisplayName = Normal.Currency, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "19", TableName = _tbUnit, ColumnName = "Rent", DisplayName = Normal.UnitRent, DbType = DbType.Decimal });
    Fields.Add(new ReportField() { FieldId = "20", TableName = _tbMasterLease, ColumnName = "CustomerName", DisplayName = Normal.CustomerName, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "21", TableName = _tbUnit, ColumnName = "Tax", DisplayName = Normal.UnitTax, DbType = DbType.Decimal });
    Fields.Add(new ReportField() { FieldId = "22", TableName = _tbUnit, ColumnName = "AssetInfo", DisplayName = Normal.AssetInfo, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "23", TableName = _tbUnit, ColumnName = "SiteAddress2", DisplayName = Normal.SiteAddress + "2", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "24", TableName = _tbUnit, ColumnName = "BillName", DisplayName = Normal.BillingName, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "25", TableName = _tbUnit, ColumnName = "BillToAttn", DisplayName = Normal.BillingAttentionTo, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "26", TableName = _tbUnit, ColumnName = "BillAddress", DisplayName = Normal.BillingAddress, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "27", TableName = _tbUnit, ColumnName = "BillAddress2", DisplayName = Normal.BillingAddress + "2", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "28", TableName = _tbUnit, ColumnName = "BillCity", DisplayName = Normal.BillingCity, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "29", TableName = _tbUnit, ColumnName = "BillState", DisplayName = Normal.BillingState, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "30", TableName = _tbUnit, ColumnName = "BillZip", DisplayName = Normal.BillingPostalCode, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "31", TableName = _tbUnit, ColumnName = "BillCountryCode", DisplayName = Normal.BillingCountry, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "32", TableName = _tbSchedule, ColumnName = "Insurance", DisplayName = Normal.Insurance, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "33", TableName = _tbSchedule, ColumnName = "Term", DisplayName = Normal.Term, DbType = DbType.Int32 });
    Fields.Add(new ReportField() { FieldId = "34", TableName = _tbUnit, ColumnName = "CustomerRef", DisplayName = Normal.CustomerReference, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "46", TableName = _tbSchedule, ColumnName = "Billing", DisplayName = Normal.Billed, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "47", TableName = _tbUnit, ColumnName = "DateAccepted", DisplayName = Normal.Accepted, DbType = DbType.Date });
    Fields.Add(new ReportField() { FieldId = "48", TableName = _tbUnit, ColumnName = "Returned", DisplayName = Normal.Returned, DbType = DbType.Boolean, Bool_ValueIfTrue = Normal.Yes, Bool_ValueIfFalse = string.Empty, Bool_TrueText = Normal.Yes, Bool_FalseText = Normal.No });
}

private void AddFieldsNormalLabels()
{
    Fields.Add(new ReportField() { FieldId = "35", TableName = _tbUnit, ColumnName = "UdFilterValue", DisplayName = Normal.UDFilterLabel, DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "36", TableName = _tbUnit, ColumnName = "Udf1", DisplayName = Normal.UserDefinedLabel + " 1", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "37", TableName = _tbUnit, ColumnName = "Udf2", DisplayName = Normal.UserDefinedLabel + " 2", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "38", TableName = _tbUnit, ColumnName = "Udf3", DisplayName = Normal.UserDefinedLabel + " 3", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "39", TableName = _tbUnit, ColumnName = "Udf4", DisplayName = Normal.UserDefinedLabel + " 4", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "40", TableName = _tbUnit, ColumnName = "Udf5", DisplayName = Normal.UserDefinedLabel + " 5", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "41", TableName = _tbUnit, ColumnName = "Udf6", DisplayName = Normal.UserDefinedLabel + " 6", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "42", TableName = _tbUnit, ColumnName = "Udf7", DisplayName = Normal.UserDefinedLabel + " 7", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "43", TableName = _tbUnit, ColumnName = "Udf8", DisplayName = Normal.UserDefinedLabel + " 8", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "44", TableName = _tbUnit, ColumnName = "Udf9", DisplayName = Normal.UserDefinedLabel + " 9", DbType = DbType.String });
    Fields.Add(new ReportField() { FieldId = "45", TableName = _tbUnit, ColumnName = "Udf10", DisplayName = Normal.UserDefinedLabel + " 10", DbType = DbType.String });
}

private void AddFieldsUserdefinedLabels()
{
    var dummySchedule = new CSI.MyC.Data.DataAccess.ScheduleAccess().GetAllSchedules().FirstOrDefault();
    if (dummySchedule != null)
    {
        var ml = dummySchedule.MasterLease;
        if (ml != null)
        {
            if (!string.IsNullOrWhiteSpace(ml.UdFilterLabel)) Fields.Add(new ReportField() { FieldId = "35", TableName = _tbUnit, ColumnName = "UdFilterValue", DisplayName = ml.UdFilterLabel, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel1)) Fields.Add(new ReportField() { FieldId = "36", TableName = _tbUnit, ColumnName = "Udf1", DisplayName = ml.UdfLabel1, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel2)) Fields.Add(new ReportField() { FieldId = "37", TableName = _tbUnit, ColumnName = "Udf2", DisplayName = ml.UdfLabel2, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel3)) Fields.Add(new ReportField() { FieldId = "38", TableName = _tbUnit, ColumnName = "Udf3", DisplayName = ml.UdfLabel3, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel4)) Fields.Add(new ReportField() { FieldId = "39", TableName = _tbUnit, ColumnName = "Udf4", DisplayName = ml.UdfLabel4, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel5)) Fields.Add(new ReportField() { FieldId = "40", TableName = _tbUnit, ColumnName = "Udf5", DisplayName = ml.UdfLabel5, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel6)) Fields.Add(new ReportField() { FieldId = "41", TableName = _tbUnit, ColumnName = "Udf6", DisplayName = ml.UdfLabel6, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel7)) Fields.Add(new ReportField() { FieldId = "42", TableName = _tbUnit, ColumnName = "Udf7", DisplayName = ml.UdfLabel7, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel8)) Fields.Add(new ReportField() { FieldId = "43", TableName = _tbUnit, ColumnName = "Udf8", DisplayName = ml.UdfLabel8, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel9)) Fields.Add(new ReportField() { FieldId = "44", TableName = _tbUnit, ColumnName = "Udf9", DisplayName = ml.UdfLabel9, DbType = DbType.String });
            if (!string.IsNullOrWhiteSpace(ml.UdfLabel10)) Fields.Add(new ReportField() { FieldId = "45", TableName = _tbUnit, ColumnName = "Udf10", DisplayName = ml.UdfLabel10, DbType = DbType.String });
        }
    }
}

private void HandleFieldInsurance()
{
    if (CurrentUser.User.IsExternal && CurrentUser.User.AuthMasterLeases != null && CurrentUser.User.AuthMasterLeases.Count > 0)
    {
        if (CurrentUser.User.AuthMasterLeases.Any(x => x.PortfolioID == 1 || x.PortfolioID == 2))
        {
            Fields = Fields.Where(x => x.FieldId != "32").ToList();
        }
    }
}

Open in new window

btw, you shouldn't use globals/singletons. Cause this makes unit testing much harder.

Now I would change the logic:

public ReportModel() : base()
{
    CreateFields();
    AddFieldsNormalLabels();
    if (!CurrentUser.User.IsCSIInternalUser)
    {
        HandleFieldsUserdefinedLabels();
    }

    HandleFieldInsurance();
    Fields = Fields.OrderBy(x => x.DisplayName.ToLower()).ToList();
}

private void CreateFields()
{
    // same as before.
}

private void AddFieldsNormalLabels()
{
    // same as before.
}

private void HandleFieldsUserdefinedLabels()
{
    var dummySchedule = new CSI.MyC.Data.DataAccess.ScheduleAccess().GetAllSchedules().FirstOrDefault();
    if (dummySchedule != null)
    {
        var ml = dummySchedule.MasterLease;
        if (ml != null)
        {
            HandleFieldUserdefinedLabel("35", ml.UdFilterLabel);
            HandleFieldUserdefinedLabel("36", ml.UdfLabel1);
            HandleFieldUserdefinedLabel("37", ml.UdfLabel2);
            HandleFieldUserdefinedLabel("38", ml.UdfLabel3);
            HandleFieldUserdefinedLabel("39", ml.UdfLabel4);
            HandleFieldUserdefinedLabel("40", ml.UdfLabel5);
            HandleFieldUserdefinedLabel("41", ml.UdfLabel6);
            HandleFieldUserdefinedLabel("42", ml.UdfLabel7);
            HandleFieldUserdefinedLabel("43", ml.UdfLabel8);
            HandleFieldUserdefinedLabel("44", ml.UdfLabel9);
            HandleFieldUserdefinedLabel("45", ml.UdfLabel10);
        }
    }
}

private void HandleFieldUserdefinedLabel(string displayName, string fieldID)
{
    if (string.IsNullOrWhiteSpace(displayName))
    {
        Fields.Remove(f => f.FieldId == fieldID);
    }
    else
    {
        Fields.Where(f => f.FieldId == fieldID).First().DisplayName = displayName;
    }
}

private void HandleFieldInsurance()
{
    // same as before.
}

Open in new window


Cause now we have a single point, where we can serialize/deserialize the fields as XML using the built-in .NET serializer.

public ReportModel() : base()
{
    CreateFields();
    AddFieldsNormalLabels();
    SerializeFields(@"C:\Temp\fields.xml");
    if (!CurrentUser.User.IsCSIInternalUser)
    {
        HandleFieldsUserdefinedLabels();
    }

    HandleFieldInsurance();
    Fields = Fields.OrderBy(x => x.DisplayName.ToLower()).ToList();
}

private void SerializeFields(string fileName)
{
    XmlSerializer serializer = new XmlSerializer(typeof(List<ReportFields>));
    using (TextWriter writer = new StreamWriter(fileName))
    {
        serializer.Serialize(writer, Fields);
        writer.Close();
    }
}

private void CreateFields()
{
    // same as before.
}

private void AddFieldsNormalLabels()
{
    // same as before.
}

private void HandleFieldsUserdefinedLabels()
{
    // same as before.
}

private void HandleFieldUserdefinedLabel(string displayName, string fieldID)
{
    // same as before.}

private void HandleFieldInsurance()
{
    // same as before.
}

Open in new window


Now you can either use this XML as a template for creating your table. Or you may use it directly as "report control file", e.g. as an embedded resource.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Thank you so much! Let me look at it in detail now. This was done by the developer who's not here anymore and my moody manager is in a rush.
Thanks again. Let me take a look.
thank you so much for taking the time and helping me out.
ste5anSenior Developer

Commented:
Your welcome.
I closed this but I hope you're checking this post...

I don't think I need to have this in a table anymore. The reason I wanted to do it is because the FieldIds in the code above are for "assets". Then, we will have another set of FieldIds for "invoice".

My manager wanted all this in a table with a "type" column to tell us if it's "asset" or "invoice".

This is used to populate a list on the screen.

I think it's ok to keep adding these FieldIds to the class. We don't need a table necessarily. I don't know what a table would buy us. I think this class is as good as a table.

Any thoughts on that?
ste5anSenior Developer

Commented:
As I already wrote, without out context I cannot tell, but I doubt that it is the way to go.

Cause in the cases of report generation I know, you can often derive this information from the underlying database system (e.g. SET FMTONLY in T-SQL) or by retrieving an empty result set to enumerate the columns collection.
thanks. I doubt I should move it to the database either.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial