问题描述
我告诉我使用旧的传统方式的SQL在MVC所以创建登录注册页面，但现在的问题是，我不能从数据集返回数据到VIEW。
i am told t use old traditional way of SQL in MVC so created login register page but now problem is that i can't return data to VIEW from dataset.
Model：
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
<h4>@ViewBag.Message</h4>
@foreach (var row in Model.Tables["tblRegister"].Rows)
{
@(row["userID"] + " " + row["userName"])
}
注意，我创建了tableCreation类等，
note that i created tableCreation classes etc to pass dataset object so i can create it object in controller.
推荐答案
您不应该在ASP.NET MVC中使用DataSets和SqlDataAdapters。你应该使用模型。
You should not use DataSets and SqlDataAdapters in ASP.NET MVC. You should use models instead.
所以让我尝试重写你的代码。首先定义将代表您的实体的模型：
So let me try to rewrite your code. Start by defining the model that will represent your entity:
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; set}
}
public class ConnectionStatus{ public T Result { get; set; } public string Message { get; set }}
，然后您的数据层可能包含两个方法（一个用于验证凭据，一个用于获取用户列表）：
and then your data layer might contain 2 methods (one for verifying the credentials and one for getting the list of users):
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; }
}
您的控制器操作将作为参数：
that your controller action will take as parameter:
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);
}
，最后在强类型视图中：
and finally in your strongly typed view:
@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>
这篇关于从MVC的视图层中的DATASET提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！