title: extracting data from DATASET in view layer of MVC
body: <p>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.</p>
  
  <p>Model:</p>
  
  <pre><code>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;
  
                  }
  </code></pre>
  
  <p>controller</p>
  
  <pre><code> 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);
          }
  </code></pre>
  
  <p>View:</p>
  
  <pre><code>@{
      ViewBag.Title = "Login Authentication";
  
  }
  
  @model System.Data.DataSet
  
  &lt;h4&gt;@ViewBag.Message&lt;/h4&gt;
  
  
  @foreach (var row in Model.Tables["tblRegister"].Rows)
  {    
      @(row["userID"] + " " + row["userName"])
  }  
  </code></pre>
  
  <p>note that i created tableCreation classes etc to pass dataset object so i can create it object in controller.</p>