asp.net-mvc - 如何在 MVC 中从数据库中填充 DropDownList

Question

我是新手ASP NET MVC

在检查当前用户是否启用应用程序后，我需要使用 MySql 数据库和视图模型从数据库表中获得的值填充下拉列表，使用ASP NET MVC.

这是教程

我下面的代码返回

Error: returns void, a return keyword must not be followed by an object expression

在这条线上

return items; 

任何帮助都非常感谢。

控制器

public ActionResult Recovery()
{
      try
        {
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString;
    
            using (var connection =
                new MySqlConnection(cs))
            {
                string commandText = " SELECT cCountry FROM `dotable_user` " +
                    " WHERE cName = @Username; ";
    
                using (var command =
                    new MySqlCommand(commandText, connection))
                {
                    if (!String.IsNullOrEmpty(HttpContext.User.Identity.Name.ToString()))
                    {
                        command.Parameters.AddWithValue("@Username", HttpContext.User.Identity.Name.ToString());
                    }
    
                    connection.Open();
    
                    string cCountry = (string)command.ExecuteScalar();
    
                    if (String.IsNullOrEmpty(cCountry))
                    {
                        TempData["Message"] = "No user.";
                        ViewBag.Message = String.Format("No user.");
                    }
    
    
                    List<SelectListItem> items = new List<SelectListItem>();
                    using (MySqlConnection con = new MySqlConnection(cs))
                    {
                        string query = " SELECT cCountry FROM `dotable_countries` " +
                            " WHERE cCountry = '" + cCountry.ToString() + "' ";
    
                        using (MySqlCommand cmd = new MySqlCommand(query))
                        {
                            cmd.Connection = con;
                            con.Open();
                            using (MySqlDataReader sdr = cmd.ExecuteReader())
                            {
                                while (sdr.Read())
                                {
                                    items.Add(new SelectListItem
                                    {
                                        Text = sdr["cCountry"].ToString(),
                                        Value = sdr["cCountry"].ToString()
                                    });
                                }
                            }
                            connection.Close();
                        }
                    }
    
                    return items;                        
                }
            }
        }
        catch (Exception ex)
        {
            TempData["Message"] = "Login failed.Error - " + ex.Message;
        }
}

更新

我已经尝试过使用此代码。

我有错误

Error CS0103 The name 'cCountry' does not exist in the current context

  public ActionResult Recovery()
  {
      try
        {
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString;
    
            using (var connection =
                new MySqlConnection(cs))
            {
                string commandText = " SELECT cCountry FROM `dotable_user` " +
                    " WHERE cName = @Username; ";
    
                using (var command =
                    new MySqlCommand(commandText, connection))
                {
                    if (!String.IsNullOrEmpty(HttpContext.User.Identity.Name.ToString()))
                    {
                        command.Parameters.AddWithValue("@Username", HttpContext.User.Identity.Name.ToString());
                    }
    
                    connection.Open();
    
                    string cCountry = (string)command.ExecuteScalar();
    
                    if (String.IsNullOrEmpty(cCountry))
                    {
                        TempData["Message"] = "No user.";
                        ViewBag.Message = String.Format("No user.");
                    }   

                    TempData["Dates"] = PopulateDates();                 
                }
            }
        }
        catch (Exception ex)
        {
            TempData["Message"] = "Login failed.Error - " + ex.Message;
        }
   }


        private static List<SelectListItem> PopulateDates()
        {
            List<SelectListItem> items = new List<SelectListItem>();
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString; 
            
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string query = " SELECT cCountry FROM `dotable_countries` " +
                    " WHERE cCountry = '" + cCountry.ToString() + "'; ";

                using (MySqlCommand cmd = new MySqlCommand(query))
                {
                    cmd.Connection = con;
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            items.Add(new SelectListItem
                            {
                                Text = sdr["cCountry"].ToString(),
                                Value = sdr["cCountry"].ToString()
                            });
                        }
                    }

                    cmd.Connection.Close();
                }
            }

            return items;
        }

标签： asp.net-mvc

Answer 1

您没有将 cCountry 值传递给 populateDates。这就是您收到此错误的原因。您可以执行以下操作来填充下拉列表。但是，直接在控制器中编写业务逻辑并不是一个好主意。您应该将其移至模型或业务层。

private static List<SelectListItem> PopulateDates(string country)
        {
            List<SelectListItem> items = new List<SelectListItem>();
            string cs = ConfigurationManager.ConnectionStrings["cnj"].ConnectionString; 
            
            using (MySqlConnection con = new MySqlConnection(cs))
            {
                string query = " SELECT cCountry FROM dotable_countries WHERE cCountry = @country";

                using (MySqlCommand cmd = new MySqlCommand(query))
                {
                   cmd.Parameters.AddWithValue("@country",country);
                    cmd.Connection = con;
                    con.Open();
                    using (MySqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            items.Add(new SelectListItem
                            {
                                Text = sdr["cCountry"].ToString(),
                                Value = sdr["cCountry"].ToString()
                            });
                        }
                    }

                    cmd.Connection.Close();
                }
            }

            return items;
        }

并在 Action 中调用此方法时，将国家/地区值传递给它，如下所示

TempData["日期"] = PopulateDates(cCountry);