问

在MVC的视图层中从DATASET提取数据

里枯萎发布于 2023-02-07 09:57

mvc

sql

view

string

我被告知在MVC中使用旧的传统SQL方式,因此创建了登录注册页面,但现在问题是我无法从数据集将数据返回到VIEW.

模型:

public ConnectionStatus Login_db(String email, String pwd, String conStr)
        {
            String hashedpwd_login = FormsAuthentication.HashPasswordForStoringInConfigFile(pwd, "SHA1");
            using (SqlConnection sqlCon = new SqlConnection(conStr))
            {
                using (SqlCommand sqlCom = new SqlCommand())
                {
                    sqlCom.Connection = sqlCon;
                    sqlCom.CommandText = "select Count(*) from tblRegister where userEmail=@email AND userPwd=@pwd";
                    sqlCom.Parameters.AddWithValue("@email", email);
                    sqlCom.Parameters.AddWithValue("@pwd", hashedpwd_login);
                    String select_com = "select * from tblRegister";
                    SqlCommand sqlCom2 = new SqlCommand(select_com, sqlCon);
                    ConnectionStatus connectStatus = new ConnectionStatus();
                    int no_rows_affected;
                    SqlDataAdapter sda = new SqlDataAdapter(select_com, sqlCon);
                    DataSet data_tb = new DataSet();

                    try
                    {
                        sqlCon.Open();
                        no_rows_affected = Convert.ToInt32(sqlCom.ExecuteScalar());
                        if (no_rows_affected == 1)
                        {
                            connectStatus.Message = "User logged in successfully, " + no_rows_affected;
                            sda.Fill(data_tb, "tblRegister");
                            tableCreation tb_creation = new tableCreation();
                            tb_creation.CreateTable = data_tb;
                        }
                        else 
                        {
                            connectStatus.Message = "Invalid email/password combination.";
                        }


                    }
                    catch (Exception ex)
                    {
                        connectStatus.Message = ex.Message;
                    }
                    return connectStatus;

                }

调节器

 public ActionResult loginResult(String command, FormCollection formData) 
        {
            if (command == "Login")
            {
                var email = formData["txtboxEmail"];
                var pwd = formData["txtboxPassword"];
           //     String conStr = "Data Source=HUNAIN-PC;Initial Catalog=registration;User ID=sa;Password=abc123!@#";
                database model_db = new database();
                var db_status = model_db.Login_db(email, pwd, conStr);
                ViewBag.Message = db_status.Message;


            }
            tableCreation retTable = new tableCreation();
            ViewData["DataTable"] = retTable.CreateTable;
            return View(retTable.CreateTable);
        }

视图:

@{
    ViewBag.Title = "Login Authentication";

}

@model System.Data.DataSet

@ViewBag.Message


@foreach (var row in Model.Tables["tblRegister"].Rows)
{    
    @(row["userID"] + " " + row["userName"])
}

请注意,我创建了tableCreation类等来传递数据集对象,这样我就可以在控制器中创建它.

1 个回答

您不应该在ASP.NET MVC中使用DataSet和SqlDataAdapters.你应该使用模型.

所以让我尝试重写你的代码.首先定义代表您实体的模型:

public class User
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string Password { get; set; }
}

你也可以有一个ConnectionStatus模特:

public class ConnectionStatus {public T Result {get; 组; public string Message {get; 设置}}

然后您的数据层可能包含2个方法(一个用于验证凭据,另一个用于获取用户列表):

public static class Db
{
    public static ConnectionStatus<bool> Login(string email, string password, string connectionString)
    {
        string hasedPassword = FormsAuthentication.HashPasswordForStoringInConfigFile(password, "SHA1");
        using (SqlConnection sqlCon = new SqlConnection(connectionString))
        using (SqlCommand sqlCom = sqlCon.CreateCommand())
        {
            sqlConn.Open();
            sqlCom.CommandText = "SELECT count(*) FROM tblRegister WHERE userEmail=@email AND userPwd=@pwd";
            sqlCom.Parameters.AddWithValue("@email", email);
            sqlCom.Parameters.AddWithValue("@pwd", hasedPassword);

            var status = new ConnectionStatus<bool>();
            status.Result = false;
            try
            {
                int rowsFound = Convert.ToInt32(sqlCom.ExecuteScalar());
                if (rowsFound == 1)
                {
                    status.Result = true;
                    status.Message = "User logged in successfully, " + rowsFound;
                }
                else
                {
                    status.Message = "Invalid email/password combination.";
                }
            }
            catch (Exception ex)
            {
                status.Message = ex.Message;
            }

            return status;
        }
    }

    public static ConnectionStatus<IList<User>> GetUsers()
    {
        using (SqlConnection sqlCon = new SqlConnection(connectionString))
        using (SqlCommand sqlCom = sqlCon.CreateCommand())
        {
            sqlConn.Open();
            sqlCom.CommandText = "SELECT userID, userName FROM tblRegister";

            var status = new ConnectionStatus<IList<User>>();
            status.Result = new List<User>();

            try
            {
                using (var reader = sqlCom.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var user = new User();
                        user.Id = reader.GetInt32(reader.GetOrdinal("userID"));
                        user.Email = reader.GetString(reader.GetOrdinal("userName"));
                        status.Result.Add(user);  
                    }
                }
            }
            catch (Exception ex)
            {
                status.Message = ex.Message;
            }

            return status;
        }
    }
}

然后定义一个视图模型:

public class LoginViewModel
{
    public string Command { get; set; }
    public string TxtboxEmail { get; set; }
    public string TxtboxPassword { get; set; }
}

您的控制器操作将作为参数:

public ActionResult LoginResult(LoginViewModel model) 
{
    if (model.Command == "Login")
    {
        string conStr = "Data Source=HUNAIN-PC;Initial Catalog=registration;User ID=sa;Password=abc123!@#";
        var loginStatus = Db.Login(model.TxtboxEmail, model.TxtboxPassword, conStr);
        ViewBag.Message = loginStatus.Message;
    }

    var usersStatus = Db.GetUsers(conStr);
    return View(usersStatus.Result);
}

最后在你的强类型视图中:

@model IList<User>
@{
    ViewBag.Title = "Login Authentication";
}

<h4>@ViewBag.Message</h4>

<table>
    @foreach (var user in Model)
    {
        <tr>
            <td>@user.Id</td>
            <td>@user.Email</td>
        </tr>
    }
</table>

2023-02-07 10:01 回答

mobiledu2502931987

撰写答案

今天，你开发时遇到什么问题呢？

立即提问

热门标签