title: Sorting and Paging with MVC 5 ADO.NET
body: <p><br></p>
  
  <p>I've the following code:<br></p>
  
  <ol>
  <li><p>Model</p>
  
  <pre><code>public int ManufactureID { get; set; }
  
  public string Manufacture { get; set; }
  
  public string ManufactureDescription { get; set; }
  
  public bool IsActive { get; set; }
  </code></pre></li>
  <li><p>Controller</p>
  
  <pre><code>public ActionResult ManufactureIndex(string manufacture = "")
  {
      ManufactureRepository ManufactureRepo = new ManufactureRepository();
      ModelState.Clear();
      ViewBag.sManufacture = manufacture;
      return View(ManufactureRepo.ManufactureGetAll(manufacture));
  }
  </code></pre></li>
  <li><p>Repository</p>
  
  <pre><code>private SqlConnection con;
  //To Handle connection related activities
  private void Connection()
  {
      con = new SqlConnection(ConfigurationManager.ConnectionStrings["ITPCNMSCon"].ToString());
  }
  
  //To view employee details with generic list
  public List&lt;Manufactures&gt; ManufactureGetAll(string manufacture = "")
  {
      Connection();
      List&lt;Manufactures&gt; EmpList = new List&lt;Manufactures&gt;();
      SqlCommand com = new SqlCommand("_spManufactureGet", con)
      {
          CommandType = CommandType.StoredProcedure
      };
      if (string.IsNullOrEmpty(manufacture))
      {
          com.Parameters.AddWithValue("@Manufacture", DBNull.Value);
      }
      else
      {
          com.Parameters.AddWithValue("@Manufacture", manufacture);
      }
      SqlDataAdapter da = new SqlDataAdapter(com);
      DataTable dt = new DataTable();
      con.Open();
      da.Fill(dt);
      con.Close();
  
      //Bind EmpModel generic list using LINQ 
      EmpList = (from DataRow dr in dt.Rows
  
                 select new Manufactures()
                 {
                     ManufactureID = Convert.ToInt32(dr["ManufactureId"]),
                     Manufacture = Convert.ToString(dr["Manufacture"]),
                     ManufactureDescription = Convert.ToString(dr["ManufactureDescription"]),
                     IsActive = Convert.ToBoolean(dr["IsActive"])
                 }).ToList();
      return EmpList;
  }
  </code></pre></li>
  <li><p>Store Procedure</p>
  
  <pre><code>SELECT      *
  FROM        _Manufacture
  WHERE       Manufacture = ISNULL(@Manufacture,Manufacture)
  ORDER BY    Manufacture
  </code></pre></li>
  </ol>
  
  <p>My question is, I want to provide a sorting and paging on the view using sql connection. How can I do that? Please advise.<br></p>
  
  <p>Thank you.</p>