Populate dropdownlist from database in asp.net MVC in a form

Question

Ok, I found some similar posts, but I don't find a way to solve it without a little help (the other posts have incomplete dates).

I have a page where I need to display a dropdownlist with values from database (display Name, but keeping the key Id as value).... this list, with other types of input for a form.

My Model:

public class AddOfferAnnounceModel
{
    public AnnounceTypeList AnnounceTypeList {get; set; }
    public int Surface { get; set; }
    public int SubTypeId { get; set; }

    [Required]
    public decimal Price { get; set; }

    [StringLength(200, ErrorMessage = "The {0} must be at least {2} and at max {1} characters long.", MinimumLength = 0)]
    public string AnnounceDetails { get; set; }

    public bool Net { get; set; }
}

public class AnnounceTypeList
{
    public int SubTypeId { get; set; }
    public string TypeDesc { get; set; }
}

My Controller [httpGET] Action (where probably must populate the dropdownlist:

// GET: Announce/Add new announce
    public ActionResult AddOffer()
    {
        string sql = "select typeId, typeDesc from announceTypes where parentTypeId = 1";
        using (SqlConnection cn = new SqlConnection(ConnectionString))
        {
            cn.Open();
            DataTable dt = new DataTable();
            SqlCommand cmd = new SqlCommand(sql, cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);

            var apartmentTypeList = new List<AnnounceTypeList>();
            foreach (DataRow dr in dt.Rows)
            {
                var apartmentType = new AnnounceTypeList
                {
                    SubTypeId = Convert.ToInt32(dr["TypeId"]),
                    TypeDesc = dr["TypeDesc"].ToString()
                };
                apartmentTypeList.Add(apartmentType);
            }
            return View();
        }
    }

My Controller [httpPOST] - where are all values returned from View and the query to insert into database

[HttpPost]
    public ActionResult AddOffer(AddOfferAnnounceModel model)
    {
        bool isValid = true; // daca datele introduse in formularul anuntului sunt corecte si respecta regulile

        if (isValid)
        {
            string announceDetailsItem = model.AnnounceDetails;
            decimal priceItem = model.Price;
            int surfaceItem = model.Surface;
            int subTypeId = model.SubTypeId; // GETTING FROM DROPDOWNLIST !!!!
            int netItem = Convert.ToInt32(model.Net);
            DateTime dateItem = DateTime.Now;

            string sql = "insert into announces (typeid, statusId, locationId, finalPrice, date, surface, net, announceDetails) values (1, 1, 1, " + priceItem + ",'" + dateItem + "'," + surfaceItem + "," + netItem + ",'" + announceDetailsItem + "')";
                  sql += " insert into announceOfferApartments (announceId, subTypeId, yPersonTypeId) values ((select max(announceId) from announces), 6, 4)";

            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                cn.Open();
                SqlCommand cmd = new SqlCommand(sql, cn)
                {
                    CommandType = CommandType.Text
                };
                cmd.ExecuteScalar();
                return RedirectToAction("OfferList", "Announce");
                // daca datele au fost introduse in DB, se redirectioneaza catre Lista Anunturilor
            }
        }
        return OfferList();
    }

My View :

@model myApp.com.Models.AddOfferAnnounceModel

...

<div class="form-group">
        @Html.LabelFor(model => model.Price, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.Price, new { htmlAttributes = new { @class = "form-control" } })
            @Html.ValidationMessageFor(model => model.Price, "", new { @class = "text-danger" })
        </div>
    </div>

    <div class="form-group">
        @Html.LabelFor(model => model.Surface, htmlAttributes: new { @class = "control-label col-md-2" })
        <div class="col-md-10">
            @Html.EditorFor(model => model.Surface, new { htmlAttributes = new { @class = "form-control" } })
        </div>
    </div>

....

I know can't have more than one model in a View. I know that probably must create a Combined class which include all parameters, but I don't know how I must to populate a List from DataTable (or maybe a Dictionary with Key and Value ?!?) and display the Value in dropdown, and transfer the key to parameter subTypeId in Controller->ActionResult AddOffer[httpPost].

I need to create 4 5 dropdownlists in same form, but probably the method will be the same .... !

thanks, and hope to not receive too many negative votes :D

Shyju Shyju · Accepted Answer · 2017-11-24T20:55:13

You can add 2 properties to your view model, one of type List<SelectListItem> to pass the options neeeded for your SELECT element and one for the selected value.

public class AddOfferAnnounceModel
{
    public List<SelectListItem> Items { set;get;}  // These 2 properties for the dropdown
    [Required]
    public int SelectedItem { set;get;}

    public int Surface { get; set; }    
    [Required]
    public decimal Price { get; set; }    
    public string AnnounceDetails { get; set; }

    public bool Net { get; set; }
}

Now in your GET action, you can create an object of your AddOfferAnnounceModel, populate the Items property and pass that to the view. There is no need to use DataTable. Use the SqlReader as all you care about is, reading the result once.

public ActionResult AddOffer()
{
   var vm = new AddOfferAnnounceModel();
   vm.Items = GetItems();
   return View(vm);
}
public List<SelectListItem> GetItems()
{
    var list = new List<SelectListItem>();
    var sql = "select TypeId, TypeDesc from announceTypes where parentTypeId = 1";
    var conStr = @"yourConnectionStringGoesHere";
    using (var c = new SqlConnection(conStr))
    {
        using (var cmd = new SqlCommand(sql, c))
        {
            c.Open();
            using (var r = cmd.ExecuteReader())
            {
                if (r.HasRows)
                {
                    while (r.Read())
                    {
                        var t = new SelectListItem()
                        {
                            Value = r.GetInt32(r.GetOrdinal("TypeId")).ToString(),
                            Text = r.GetString(r.GetOrdinal("TypeDesc"))
                        };
                        list.Add(t);
                    }
                }
            }
        }
    }
    return list;
}

In the view, which is strongly typed to the AddOfferAnnounceModel class, you can use the Html.DropDownListFor helper

@model AddOfferAnnounceModel
@using(Html.BeginForm())
{
   @Html.TextBoxFor(a=>a.Price)
   @Html.TextBoxFor(a=>a.Surface)
   @Html.TextBoxFor(a=>a.AnnounceDetails)
   @Html.DropDownListFor(a=>a.SelectedItem,Model.Items)
   <input type="submit" />
}

Now in your HttpPost action, when the form is submitted, the selected option value will be in the SelectedItem property

[HttpPost]
public ActionResult AddOffer(AddOfferAnnounceModel model)
{
     // check model.SelectedItem and use that for saving
}

If you have more dropdowns, you will do the same thing (add 2 properties, one for the list and one for selected value and populate the list property and use the DropDownListFor helper.

I would also suggest to not do string concatenation for your insert query for saving the new record. That approach is prone to SQL Injection attacks. Take a look at SqlParameters and how to use that to pass your different params for your query. This post explains various approach to handle that

Populate dropdownlist from database in asp.net MVC in a form

2 Answers