MVC项目中如何将视图下拉框数据存入SQL数据库？

咱们一步步来搞定这个问题，核心是先补全ViewModel作为控制器和视图的桥梁，然后修正控制器的数据获取与提交逻辑，最后把视图的下拉框正确绑定并放到表单范围内，确保提交时能拿到选中值。

第一步：完善ViewModel #

ViewModel需要包含用户基本信息、下拉框的选中值（接收视图提交的选择）、下拉框的数据源列表（渲染选项），直接上代码：

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Web.Mvc;

public class UserCreateViewModel
{
    // UserProfile表对应的基本信息
    [Required(ErrorMessage = "Username is required")]
    public string UserName { get; set; }

    [Required(ErrorMessage = "Password is required")]
    [DataType(DataType.Password)]
    public string Password { get; set; }

    [Required(ErrorMessage = "Confirm password is required")]
    [DataType(DataType.Password)]
    [Compare("Password", ErrorMessage = "Passwords do not match")]
    public string ConfirmPassword { get; set; }

    [Required(ErrorMessage = "Email is required")]
    [DataType(DataType.EmailAddress)]
    public string Email { get; set; }

    // 下拉框选中值（提交时会自动绑定）
    [Required(ErrorMessage = "Please select a store definition")]
    public int SelectedStoreNumber { get; set; }

    [Required(ErrorMessage = "Please select a store owner")]
    public int SelectedStoreOwnerNumber { get; set; }

    [Required(ErrorMessage = "Please select a store region")]
    public int SelectedStoreRegionNumber { get; set; }

    [Required(ErrorMessage = "Please select a store type")]
    public int SelectedStoreTypeNumber { get; set; }

    // 下拉框数据源
    public List<UserStoreDefinition> StoreDefinitions { get; set; }
    public List<UserStoreDefinition> StoreOwnerDefinitions { get; set; }
    public List<UserStoreDefinition> StoreRegionDefinitions { get; set; }
    public List<UserStoreDefinition> StoreTypeDefinitions { get; set; }
}

// 定义UserStoreDefinition实体类（如果还没有的话）
public class UserStoreDefinition
{
    public int StoreNumber { get; set; }
    public string StoreName { get; set; }
    public int StoreOwnerNumber { get; set; }
    public string StoreOwnerName { get; set; }
    public int StoreRegionNumber { get; set; }
    public string StoreRegionName { get; set; }
    public int StoreTypeNumber { get; set; }
    public string StoreTypeName { get; set; }
    public int UserNumber { get; set; }
}

第二步：修正控制器代码 #

当前控制器存在SQL注入风险、Store Region的SQL未赋值、缺少POST提交逻辑这些问题，咱们逐一修复：

1. GET方法（渲染创建视图） #

using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;

public ActionResult UserCreateNew()
{
    var viewModel = new UserCreateViewModel();
    string constr = "你的数据库连接字符串"; // 替换为实际连接字符串

    // 获取Store Definition列表（参数化查询防注入）
    LoadStoreDefinitions(viewModel, constr);
    // 获取Store Owner列表
    LoadStoreOwnerDefinitions(viewModel, constr);
    // 获取Store Region列表（补全之前缺失的SQL）
    LoadStoreRegionDefinitions(viewModel, constr);
    // 获取Store Type列表
    LoadStoreTypeDefinitions(viewModel, constr);

    return View(viewModel);
}

// 提取下拉框数据加载逻辑，避免重复代码
private void LoadStoreDefinitions(UserCreateViewModel vm, string constr)
{
    string sql = "SELECT StoreNumber, StoreName FROM UserStoreDefinition WHERE UserNumber = @UserNumber";
    using (DataAccess da = new DataAccess(constr))
    {
        var param = new SqlParameter("@UserNumber", vm.UserNumber); // 替换为实际用户编号，比如当前登录用户ID
        DataTable dt = da.GetDataTable(sql, CommandType.Text, param);
        vm.StoreDefinitions = LinqExtensions.ToListof<UserStoreDefinition>(dt);
    }
}

private void LoadStoreOwnerDefinitions(UserCreateViewModel vm, string constr)
{
    string sql = "SELECT DISTINCT StoreOwnerNumber, StoreOwnerName FROM UserStoreDefinition WHERE UserNumber = @UserNumber AND StoreOwnerNumber <> -1";
    using (DataAccess da = new DataAccess(constr))
    {
        var param = new SqlParameter("@UserNumber", vm.UserNumber);
        DataTable dt = da.GetDataTable(sql, CommandType.Text, param);
        vm.StoreOwnerDefinitions = LinqExtensions.ToListof<UserStoreDefinition>(dt);
    }
}

private void LoadStoreRegionDefinitions(UserCreateViewModel vm, string constr)
{
    string sql = "SELECT DISTINCT StoreRegionNumber, StoreRegionName FROM UserStoreDefinition WHERE UserNumber = @UserNumber AND StoreRegionNumber <> -1";
    using (DataAccess da = new DataAccess(constr))
    {
        var param = new SqlParameter("@UserNumber", vm.UserNumber);
        DataTable dt = da.GetDataTable(sql, CommandType.Text, param);
        vm.StoreRegionDefinitions = LinqExtensions.ToListof<UserStoreDefinition>(dt);
    }
}

private void LoadStoreTypeDefinitions(UserCreateViewModel vm, string constr)
{
    string sql = "SELECT DISTINCT StoreTypeNumber, StoreTypeName FROM UserStoreDefinition WHERE UserNumber = @UserNumber AND StoreTypeNumber <> -1";
    using (DataAccess da = new DataAccess(constr))
    {
        var param = new SqlParameter("@UserNumber", vm.UserNumber);
        DataTable dt = da.GetDataTable(sql, CommandType.Text, param);
        vm.StoreTypeDefinitions = LinqExtensions.ToListof<UserStoreDefinition>(dt);
    }
}

2. POST方法（处理表单提交，写入数据库） #

添加POST方法接收视图提交的数据，分别插入UserProfile和关联的Users表：

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult UserCreateNew(UserCreateViewModel viewModel)
{
    if (ModelState.IsValid)
    {
        string constr = "你的数据库连接字符串";
        using (DataAccess da = new DataAccess(constr))
        {
            // 1. 插入UserProfile表，获取生成的UserNumber（假设是自增主键）
            string insertProfileSql = "INSERT INTO UserProfile (Username, Email, Password) VALUES (@Username, @Email, @Password); SELECT SCOPE_IDENTITY();";
            var profileParams = new List<SqlParameter>
            {
                new SqlParameter("@Username", viewModel.UserName),
                new SqlParameter("@Email", viewModel.Email),
                new SqlParameter("@Password", HashPassword(viewModel.Password)) // 一定要加密密码，不要存明文！
            };
            int userNumber = Convert.ToInt32(da.ExecuteScalar(insertProfileSql, CommandType.Text, profileParams.ToArray()));

            // 2. 插入Users表（关联UserNumber和下拉框选中的存储信息）
            string insertUserSql = "INSERT INTO Users (UserNumber, StoreNumber, StoreOwnerNumber, StoreRegionNumber, StoreTypeNumber) VALUES (@UserNumber, @StoreNumber, @StoreOwnerNumber, @StoreRegionNumber, @StoreTypeNumber)";
            var userParams = new List<SqlParameter>
            {
                new SqlParameter("@UserNumber", userNumber),
                new SqlParameter("@StoreNumber", viewModel.SelectedStoreNumber),
                new SqlParameter("@StoreOwnerNumber", viewModel.SelectedStoreOwnerNumber),
                new SqlParameter("@StoreRegionNumber", viewModel.SelectedStoreRegionNumber),
                new SqlParameter("@StoreTypeNumber", viewModel.SelectedStoreTypeNumber)
            };
            da.ExecuteNonQuery(insertUserSql, CommandType.Text, userParams.ToArray());
        }

        // 提交成功后跳转到用户列表页
        return RedirectToAction("UserList");
    }

    // 验证失败时重新加载下拉框数据，返回视图
    LoadStoreDefinitions(viewModel, "你的数据库连接字符串");
    LoadStoreOwnerDefinitions(viewModel, "你的数据库连接字符串");
    LoadStoreRegionDefinitions(viewModel, "你的数据库连接字符串");
    LoadStoreTypeDefinitions(viewModel, "你的数据库连接字符串");
    return View(viewModel);
}

// 密码加密方法（示例，推荐用BCrypt或ASP.NET Identity的加密逻辑）
private string HashPassword(string password)
{
    // 这里替换为实际的密码哈希逻辑，比如：
    // return BCrypt.Net.BCrypt.HashPassword(password);
    return password; // 仅示例，实际项目绝对不能用明文！
}

第三步：修正视图代码 #

当前视图的下拉框在表单外（提交时无法获取），且未正确绑定ViewModel，修复后如下：

<div class="spacerBody">
    <h2 class="admin-home-link orange-titles orange-titles-large">@Html.ActionLink("Create Users", "AdminIndex")</h2>
    <div class="to-link navlinks"> 
        @Html.ActionLink("Users", "UserList") | @Html.ActionLink("Manage User Role", "RoleManageUser")
    </div>
    <p>&nbsp;</p>
    @using (Html.BeginForm()) {
        @Html.AntiForgeryToken()
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <fieldset>
            <legend>Registration Form</legend>
            <ol class="comtrexBlue-text">
                <li> 
                    @Html.LabelFor(m => m.UserName)
                    @Html.TextBoxFor(m => m.UserName, new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.UserName, "", new { @class = "text-danger" })
                </li>
                <li> 
                    @Html.LabelFor(m => m.Password)
                    @Html.PasswordFor(m => m.Password, new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.Password, "", new { @class = "text-danger" })
                </li>
                <li> 
                    @Html.LabelFor(m => m.ConfirmPassword)
                    @Html.PasswordFor(m => m.ConfirmPassword, new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.ConfirmPassword, "", new { @class = "text-danger" })
                </li>
                <li> 
                    @Html.LabelFor(m => m.Email)
                    @Html.TextBoxFor(m => m.Email, new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.Email, "", new { @class = "text-danger" })
                </li>
                <!-- 下拉框放到表单内，绑定ViewModel属性 -->
                <li>
                    @Html.LabelFor(m => m.SelectedStoreNumber, "Store Definition")
                    @Html.DropDownListFor(m => m.SelectedStoreNumber, 
                        new SelectList(Model.StoreDefinitions, "StoreNumber", "StoreName"), 
                        "Select a Store", 
                        new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.SelectedStoreNumber, "", new { @class = "text-danger" })
                </li>
                <li>
                    @Html.LabelFor(m => m.SelectedStoreOwnerNumber, "Store Owner")
                    @Html.DropDownListFor(m => m.SelectedStoreOwnerNumber, 
                        new SelectList(Model.StoreOwnerDefinitions, "StoreOwnerNumber", "StoreOwnerName"), 
                        "Select an Owner", 
                        new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.SelectedStoreOwnerNumber, "", new { @class = "text-danger" })
                </li>
                <li>
                    @Html.LabelFor(m => m.SelectedStoreRegionNumber, "Store Region")
                    @Html.DropDownListFor(m => m.SelectedStoreRegionNumber, 
                        new SelectList(Model.StoreRegionDefinitions, "StoreRegionNumber", "StoreRegionName"), 
                        "Select a Region", 
                        new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.SelectedStoreRegionNumber, "", new { @class = "text-danger" })
                </li>
                <li>
                    @Html.LabelFor(m => m.SelectedStoreTypeNumber, "Store Type")
                    @Html.DropDownListFor(m => m.SelectedStoreTypeNumber, 
                        new SelectList(Model.StoreTypeDefinitions, "StoreTypeNumber", "StoreTypeName"), 
                        "Select a Type", 
                        new { @class = "form-control" })
                    @Html.ValidationMessageFor(m => m.SelectedStoreTypeNumber, "", new { @class = "text-danger" })
                </li>
            </ol>
            <button class="btn larger btn-primary" type="submit">Create</button>
        </fieldset>
    }
</div>

关键提醒 #

• 密码加密：绝对不要存储明文密码，推荐使用BCrypt.Net或ASP.NET Identity的哈希工具处理密码。
• SQL注入防护：所有数据库操作必须用参数化查询，禁止直接拼接SQL字符串。
• 代码复用：把下拉框数据加载逻辑提取到私有方法，避免重复代码，提升维护性。

内容的提问来源于stack exchange，提问作者Megan Meluskey