Populate Drop Down List from SQL Database

Question

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; }
}

Answer 1

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();
    }