ASP.NET MVC Populate DropDown thru ViewBag

Question

I am a beginner to ASP.NET MVC technology. In my View page, I have a WebGrid that is bind to data that is passed from Controller. I also want to populate a dropdown list with the list of Tables in the database. I retrieve the tables List from a helper method. I am trying to send the List to the ViewBag and want to populate the dropdown; but can't get it. Some syntax error is only coming or the List is not accessible.

My Controller class :

        [Authorize]
    public ActionResult Index(int page = 1, string sort = "FirstName", string sortdir = "asc", string search = "")
    {
        int pageSize = 10;
        int totalRecord = 0;
        if (page < 1)
            page = 1;
        int skip = (page * pageSize) - pageSize;
        var data = GetUsers(search, sort, sortdir, skip, pageSize, out totalRecord);

        // Get Tables List
        ViewBag.TableList = DataFiller.GetTableNames();
        ViewBag.TotalRows = totalRecord;

        return View(data);
    }

View file :

@model List<DataStudio.Models.User>

@{
ViewBag.Title = "Index";
Layout = "~/Views/Shared/_MemberLayout.cshtml";

<!-- data is used for grid -->
var grid = new WebGrid(canPage: true, rowsPerPage: 10);
grid.Bind(source: Model, rowCount: ViewBag.TotalRows, autoSortAndPage: false);   
}

<!-- ERROR LINE   -->
@Html.DropDownList("tableName", @ViewBag.TableList, "Select Table")

ERROR

Error   CS1973  'HtmlHelper<List<User>>' has no applicable method named 'DropDownList' but appears to have an extension method by that name. Extension methods cannot be dynamically dispatched. Consider casting the dynamic arguments or calling the extension method without the extension method syntax.    1_Views_Member_Index.cshtml Index.cshtml    82  Active

The User model doesn't contain any List nor any DropDownList. The drop down is a totally separate combo box, that I want to fill with a List items; it just contains the list of table names from the database.

I am not able to get how do I bind or populate the drop down list with the SelectLiteItem items. I googled a lot, but could not get any example with this scenario. I don't wish to add my List with my model. And, I want to populate the Tables list drop down only once.

Can anyone please help me and guide me how do I achieve the goal. Any help is highly appreciated.

Thanks

Tom

UPDATE: The DataFilter.GetTableNames() method

       public static List<SelectListItem> GetTableNames()
    {
        List<SelectListItem> tablesList = new List<SelectListItem>();

        string sql = "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
        string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["DMSDbConnectionString"].ToString();

        using (SqlConnection con = new SqlConnection(conStr))
        {
            using (SqlCommand com = new SqlCommand(sql, con)) {
                con.Open();
                using(SqlDataReader sdr = com.ExecuteReader() )
                {
                    while (sdr.Read())
                    {
                        tablesList.Add(new SelectListItem
                        {
                            Text = sdr["TABLE_NAME"].ToString(),
                            Value = sdr["TABLE_NAME"].ToString()
                        });
                    }
                }

                /*
                SqlDataAdapter da = new SqlDataAdapter(com);
                DataSet ds = new DataSet();
                da.Fill(ds);

                DataTable dt = ds.Tables[0];

                tablesList = (from DataRow dr in dt.Rows
                              select new TablesInfo()
                              {
                                  TableCatalog = dr["TABLE_CATALOG"].ToString(),
                                  TableName = dr["TABLE_NAME"].ToString(),
                                  TableSchema = dr["TABLE_SCHEMA"].ToString(),
                                  TableType = dr["TABLE_TYPE"].ToString(),
                              }).ToList();

                    */
                con.Close();
            }
        }
        return tablesList;
    }

I had created a class also TablesInfo to populate data in it and use strong typed object. But, as couldn't find a way to populate it, so used SelectListItem. And as it contains only list of tables, so I don't wish it to be populated on every refresh of the page.

Answer 1

As Tetsuya stated, you should be using a strongly typed view model.

You can structure your model like this:

 public class MyUserCollection
    {
        public List<MyUser> Users { get; set; }
        public SelectList TableList { get; set; }
        public int TotalRows { get; set; }

        public MyUserCollection(string search, string sort, string sortdir, int skip, int pageSize)
        {
            TableList = DataFiller.GetTableNames();

            Users = GetUsers(search, sort, sortdir, skip, pageSize, out totalRecord);
        }
    }

Then to create your drop down:

@Html.DropDownListFor(model => model.tableName, Model.TableList as SelectList, "Select Table")

Other wise you need to cast your object:

@Html.DropDownList("tableName", (SelectList)ViewBag.TableList, "Select Table")