3
\$\begingroup\$

I am populating a drop down list from my SQL database. List should be able to be displayed with active only, inactive only or both at the same time.

OfficeRepository.cs:

    public static List<OfficeRollups> ListOfficeRollups(bool active, bool inactive)
    {
        List<DataRow> listDataRow = null;

        string whereClause = string.Empty;

        if (active && !inactive)
            whereClause += @" And Active = 1";
        else if (!active && inactive)
            whereClause += @" And Active = 0";


        string srtQry = @"
Select OfficeRollupID, OfficeRollupName, Active
From OfficeRollups
Where 1 = 1 " + whereClause + @"
Order By OfficeRollupName
";

        using (SqlConnection conn = new SqlConnection(Settings.ConnectionString))
        {
            using (SqlCommand objCommand = new SqlCommand(srtQry, conn))
            {
                objCommand.CommandType = CommandType.Text;
                DataTable dt = new DataTable();
                SqlDataAdapter adp = new SqlDataAdapter(objCommand);
                conn.Open();
                adp.Fill(dt);
                if (dt != null)
                {
                    listDataRow = dt.AsEnumerable().ToList();
                }
            }
        }

        var listOfficeRollups = (from o in listDataRow
                           select new OfficeRollups
                           {
                               OfficeRollupID = o.Field<int>("OfficeRollupID"),
                               OfficeRollupName = o.Field<string>("OfficeRollupName"),
                               Active = o.Field<bool>("Active")

                           }).ToList();

        return listOfficeRollups;
    }

    public static IEnumerable<SelectListItem> ListOfficeRollupsForDD(bool active, bool     inactive)
    {
        var listOfficeRollups = OfficeRepository.ListOfficeRollups(active, inactive);

        return from o in listOfficeRollups.ToList()
               where o.OfficeRollupName.Length != 0
               orderby o.OfficeRollupName
               select new SelectListItem
               {
                   Text = o.OfficeRollupName,
                   Value = o.OfficeRollupID.ToString()
               };
    }

HomeController.cs:

    public ActionResult Create()
    {
        Office currentOffice = new Office();

        var listOfficeRollups = OfficeRepository.ListOfficeRollupsForDD(true, false);
        currentOffice.OfficeRollups = listOfficeRollups;

        return View(currentOffice);
    }

Create.cshtml:

@model Office
@using (Html.BeginForm())
{
    @(Html.DropDownList("OfficeRollupID", Model.OfficeRollups))
}

Office.cs:

public class Office
{
    [Required]
    [Display(Name = "Office Rollup")]
    public IEnumerable<SelectListItem> OfficeRollups { get; set; }

    public Int32 OfficeRollupID { get; set; }
}
\$\endgroup\$
1
  • 3
    \$\begingroup\$ There's a typo in your Hungarian notation for strQry... ;) \$\endgroup\$ Mar 5, 2014 at 21:29

1 Answer 1

2
\$\begingroup\$

Here's a simplification of the OfficeRepository methods. Note the other IDisposable types in using blocks:

    public static IEnumerable<OfficeRollups> ListOfficeRollups(bool active, bool inactive)
    {
        var whereClause = active && !inactive
            ? @"Where Active = 1"
            : (!active && inactive ? @"Where Active = 0" : string.Empty);
        var srtQry = @"
Select OfficeRollupID, OfficeRollupName, Active
From OfficeRollups
" + whereClause + @"
Order By OfficeRollupName
";

        using (var conn = new SqlConnection(Settings.ConnectionString))
        using (var objCommand = new SqlCommand(srtQry, conn) { CommandType = CommandType.Text })
        using (var dt = new DataTable())
        using (var adp = new SqlDataAdapter(objCommand))
        {
            conn.Open();
            adp.Fill(dt);
            return dt.AsEnumerable().Select(o => new OfficeRollups
            {
                OfficeRollupID = o.Field<int>("OfficeRollupID"),
                OfficeRollupName = o.Field<string>("OfficeRollupName"),
                Active = o.Field<bool>("Active")
            }).ToList();
        }
    }

    public static IEnumerable<SelectListItem> ListOfficeRollupsForDD(bool active, bool inactive)
    {
        var listOfficeRollups = OfficeRepository.ListOfficeRollups(active, inactive);

        return listOfficeRollups
                .Where(o => !string.IsNullOrEmpty(o.OfficeRollupName))
                .OrderBy(o => o.OfficeRollupName)
                .Select(o => new SelectListItem
                {
                    Text = o.OfficeRollupName,
                    Value = o.OfficeRollupID.ToString()
                })
                .ToList();
    }
\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.