ASP.NET MVC dropdown-list from database

Question

Ok, so I'm new to this whole MVC-world, but it seems to be a pretty good way of getting things done and I'm trying to make it work here.

The problem is: I can't get data from my table in my SQL-database to a simple drop-down form on my registration page.

I have just no idea where to put the stuff, where to code to open the table, select the ids, where to put the response.write and how do I send it to the view?

My Model is this:

    public class users
{
    public string name {get; set;}
    public int user_id {get; set;}
}

My Controller is this:

    [HttpGet]
    public ActionResult ListUser()
    {
        return View();
    }

And my View is this:

@model Community.Models.users

I have googled for 2 days now and watched several videos on youtube but of no use, I can't find it. Please, anyone with some knowledge here? And please point me to some good tutorials and/or forums where I can browse for more questions I might have

---

Still no luck on this project..

I'm creating a form and within that form, i want a db-loop (IEnumerable).. But the current model is not a IEnumerable. I'm pretty much stuck, watched a bunch of tutorials and they all just list ONE connection, what if I want two models?

Here is my Controller, I get that you must pass a list to the view, right?

    public ActionResult Registration()
    {
        return View(db.users.ToList());
    }

How do i get hold of that list in my view witout an IEnumerable model?

@neoistheone, your example didnt help me much, my DB opens like this:

private DataBaseContext db = new DataBaseContext();

and i don't know how, but it opens the connection. I've tried for so many hours now, its just silly, haven't slept for soo long!

I'm used to programming ASP-Classic fyi, and this is my first serious try to upgrade my knowledge about programing an up-to-date language and OOP.

Answer 1

I had to put Everything together from about 5 different Stack Overflow entries. I'm a newbie that's not in love with EF. I prefer doing things in SQL. Mike Perrenoud got me started, but I had trouble getting his solution to compile properly in the view.

First, I declared my dropdown Id/Name inside my model and then declared a numeric selector plus a SelectList

        public class BusinessType
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

    public int SelectedBusinessId { get; set; }
    public SelectList BusinessTypeddList { get; set; }

In my [HttpGet] (the one that does not pass in a model), I ALWAYS populate my dropdowns. I'm doing things in SQL Server because I find it easier than the EF abstraction and syntax (which baffles me). This code declares a List of business types and populates it directly from the database. Feel free to use a sproc or whatever. IMPORTANT: Don't forget to return the View(model) at the end or your View will get an object missing reference error.

            var list = new List<MerchantDetail.BusinessType>();

        using (var con = new SqlConnection(Common.DB_CONNECTION_STRING_BOARDING))
        {
            con.Open();
            using (var command = new SqlCommand("SELECT Id, BusinessTypeDesc as Name FROM BusinessType order by Id", con))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        list.Add(new MerchantDetail.BusinessType
                        {
                            Id = reader.GetInt32(0),
                            Name = reader.GetString(1)
                        });
                    }
                }
            }
        }

        var model = new MerchantDetail();
        model.BusinessTypeddList = new SelectList(list, "Id", "Name");
        model.SelectedBusinessId = 0;

        return View(model);

The view is simple. Mine looks like this.

@Html.DropDownListFor(m => m.SelectedBusinessId, Model.BusinessTypeddList, "Select One:", new { @class = "custom-select" });

NOTE: Setting the SelectedBusinessId to 0 only works if there's nothing in the model. It will be set to one of the dropdown values otherwise. That will happen in a different HttpGet that passes the model in.

Answer 2

I find this system works (and avoids using ViewBag):

View Model:

public class YourViewModel
{
    // This could be string, int or Guid depending on what you need as the value
    public int YourDropdownSelectedValue { get; set; }
    public IEnumerable<SelectListItem> YourDropdownList { get; set; }
}

Controller:

// Get database values (by whatever selection method is appropriate)
var dbValues = db.YourEntity.ToList();

// Make Selectlist, which is IEnumerable<SelectListItem>
var yourDropdownList = new SelectList(dbValues.Select(item => new SelectListItem
{
    Text = item.YourSelectedDbText,
    Value = item.YourSelectedDbValue
}).ToList(), "Value", "Text");

// Assign the Selectlist to the View Model   
var viewModel = new YourViewModel(){
    // Optional: if you want a pre-selected value - remove this for no pre-selected value
    YourDropdownSelectedValue = dbValues.FirstOrDefault(),
    // The Dropdownlist values
    YourDropdownList = yourDropdownList
};

// return View with View Model
return View(viewModel);

and in the View:

@Html.DropDownListFor(a => a.YourDropdownSelectedValue, Model.YourDropdownList, "select this text - change this to null to exclude", new { @class = "your-class" })

Answer 3

this is my table in the database

take look it my Action controller

    // GET: Letters
    public ActionResult Index()
    {
        ViewBag.LetterStatus = new SelectList(LetterStatusService.GetAllLettersStatus(), "Id", (CultureHelper.GetCurrentCulture() == "ar") ? "NameArabic" : "Name", Request.QueryString["LetterStatus"]);
        return View();
    }

and in the view

  @Html.DropDownList("LetterStatus")

the constructor I used is

new SelectList(
    list<Objlect> myListFromDatabase,
    string PropertyNameOfValueInHtml,
    string PropertyNameOfDesplayInHtml,
    string SelectedItemValue 
);

this line Request.QueryString["LetterStatus"] because I send the Selected Items within QuerySrting

and based on CurrentCulture I chose what column to display

and the result are

but I think the best way to do this,,,, is to get or create the Items then Iterate throw them to generate the select tag manually. I described this approach well in this answer

hope this helps you

Answer 4

There a great answers already but Here is another approach.

You will use user as a model, ListUserViewModel as view-model and UserController as the contoller. The work of view-model is to carry all info needed to be displayed on the page from the controller without adding unwanted properties into the model class. In your case list of users from database into the drop down list.

Model:

public class User     //By the way use singular when naming a class
{
    public string name {get; set;}
    public int user_id {get; set;}
}

View-model

public class ListUserViewModel
{
    public list<User> Users{get; set;}
}

Controller

public class UserController : Controller
{
    private DataBaseContext db = new DataBaseContext();

    [HttpGet]
    public ActionResult ListUser()
    {
        var users = db.Users.ToList();

        var viewModel = new ListUserViewModel { Users = users };

        return View(viewModel);
    }
}

Now use ListUserViewModel instead of User in your view as a model

@model Community.Models.ListUserViewModel

and the drop down

@Html.DropDownListFor(m => m.Users, new SelectList(Model.Users, "user_id", "name"), " ")

Explanation:

You are creating drop down list for Users with Model.Users as select list data source. "user_id" as a value of the selected user and "name" as display label. the last argument( i put empty string " ") is a default value that the drop down will display before selection.

I hope this will help you or someone else.

Answer 5

If you are really new to ASP.Net MVC, this is a quite good Tutorial that shows you how the MVC-Pattern works.

MVC3: http://www.asp.net/mvc/tutorials/getting-started-with-aspnet-mvc3/cs/intro-to-aspnet-mvc-3
MVC4: http://www.asp.net/mvc/tutorials/mvc-4/getting-started-with-aspnet-mvc4/intro-to-aspnet-mvc-4

Here is the sample-code to download: http://code.msdn.microsoft.com/Introduction-to-MVC-3-10d1b098

this is an helpful video: http://www.asp.net/mvc/videos/mvc-1/conference-presentations/creating-nerddinnercom-with-microsoft-aspnet-model-view-controller-mvc

Answer 6

Try this,

model

public string CoutryID { get; set; }
public List<SelectListItem> CountryList { get; set; }

Controller method which fill the list

public List<Country> getCountryList()
        {
            using (QRMG_VendorPortalDataContext _context = new QRMG_VendorPortalDataContext())
            {
                return (from c in _context.Countries
                        where c.IsDeleted == false
                        select c).ToList();
            }
        }

Drop down list in View

 @Html.DropDownListFor(m => m.CoutryID,
         new SelectList(Model.CountryList,
                        "CoutryID", "Value"))

Answer 7

Add the SelectList to your model:

public SelectList DropDownList { get; set; }

build the class for that collection:

public class MyListTable
{
    public string Key { get; set; }
    public string Display { get; set; }
}

and then in your controller, load the data for the MyListTable class from the database:

var list = new List<MyListTable>();

using (SqlConnection c = new SqlConnection(cString))
using (SqlCommand cmd = new SqlCommand("SELECT KeyField, DisplayField FROM Table", c))
{
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        while (rdr.Read())
        {
            list.Add(new MyListTable
            {
                Key = rdr.GetString(0),
                Display = rdr.GetString(1)
            });
        }
    }
}

var model = new users();
model.DropDownList = new SelectList(list, "Key", "Display");

and then finally, you need to send your model to the view:

return View(model);

Now in the Razor you can display this:

@Html.DropDownListFor(m => Model.DropDownList);

You of course can name these things better names, but you get the idea.