extracting data from DATASET in view layer of MVC

Question

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;

                }

controller

 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);
        }

View:

@{
    ViewBag.Title = "Login Authentication";

}

@model System.Data.DataSet

<h4>@ViewBag.Message</h4>


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

note that i created tableCreation classes etc to pass dataset object so i can create it object in controller.

Answer 1

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; }
}

you could also have a ConnectionStatus model:

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;
        }
    }
}

and then define a view model:

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>