How do I fix an empty dropdownlist when binding ASP.NET MVC using Dapper?-CodePudding

I am attempting to bind a dropdownlist to values in an SQL table using dapper, but the dropdown is empty. Is shows the correct number of rows, but they are all empty. In the debug values from the dapper call I see that it is returning the correct number of rows (3) and that the Id's are correct. - So I think that the data is retrieved correctly but not shown. I am new to MVC and ASP, so any help is very much appreciated.

Controller.cs

public ActionResult AddPilot()
    {
        ViewBag.Message = "Add new pilot.";
        PilotModel program = new PilotModel();
        program.ProgramList = new SelectList(LoadDropDown("SELECT [Id], [Programs] FROM tbl_ProgOverview"), "Id", "Program");            
        return View(program);
    }

PilotModel.cs

    public class PilotModel
{
    public int Id { get; set; }
    public string WingID { get; set; }
    public string Pilot { get; set; }
    public bool Ship4 { get; set; }
    public bool Ship2 { get; set; }
    public bool Wing { get; set; }
    public bool IP { get; set; }
    public bool ME { get; set; }
    public bool Supervisor { get; set; }
    public string Program { get; set; }
    public string Program2 { get; set; }
    public DateTime? PrgmStart { get; set; }
    public DateTime? PrgmEnd { get; set; }
    public DateTime? Prgm2Start { get; set; }
    public DateTime? Prgm2End { get; set; }
    public bool ExtSync { get; set; }
    public SelectList ProgramList { get; set; }
}

}

View.cthtml

<div >
    @Html.LabelFor(model => model.Program, htmlAttributes: new { @class = "control-label col-md-2" })
    <div >            
        @Html.DropDownListFor(model => model.Program, Model.ProgramList, "Select")
        @Html.ValidationMessageFor(model => model.Program, "", new { @class = "text-danger" })
    </div>
</div>

Helper.cs

public static IEnumerable<PilotModel> LoadDropDown(string sql)
    {
        return SqlDataAccess.GetDropDown<PilotModel>(sql);
    }

DataAccess.cs

public static IEnumerable<PilotModel> GetDropDown<PilotModel>(string sql)
    {
        using (IDbConnection cnn = new SqlConnection(GetConnectionString()))
        {
            return cnn.Query<PilotModel>(sql);
        }
    }

The table "tbl_ProgOverview" has only 2 columns ("Id", "Programs"), and all I am trying to achieve is to have the dropdown in model.Program, show a list of the values in the "Programs" column of this table. Thank you!

CodePudding user response：

Dapper: cares about names.

You have the SQL:

SELECT [Id], [Programs] FROM tbl_ProgOverview

but your c# property is Program (or at least, I presume that's what you intend to map it to). Dapper is going to ignore the Programs column and not set Program, hence why it is null in the screen-shot.

Try:

SELECT [Id], [Programs] as Program FROM tbl_ProgOverview

This re-maps the projected column name in the SQL to Program, to match what Dapper is looking for. Alternatively, you could renaming the C# property to Programs.

As a side note: when you use literals like "Id" or "Program" in your C# code, it makes it hard to rename those properties later if you need to. You might want to try nameof(PilotModel.Id) / nameof(PilotModel.Program), which avoids hard-coding those literals. If you rename them: it will fix your binding code automatically (or at least tell you that no such member exists).