代码之家 › 专栏 › 技术社区 › Haminteu

使用MVC 5 ADO进行排序和分页。净额

ado.net asp.net-mvc c#

Haminteu · 技术社区 · 8 年前

我有以下代码:

模型

public int ManufactureID { get; set; }

public string Manufacture { get; set; }

public string ManufactureDescription { get; set; }

public bool IsActive { get; set; }

控制器

public ActionResult ManufactureIndex(string manufacture = "")
{
    ManufactureRepository ManufactureRepo = new ManufactureRepository();
    ModelState.Clear();
    ViewBag.sManufacture = manufacture;
    return View(ManufactureRepo.ManufactureGetAll(manufacture));
}

存储库

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<Manufactures> ManufactureGetAll(string manufacture = "")
{
    Connection();
    List<Manufactures> EmpList = new List<Manufactures>();
    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;
}

存储过程

SELECT      *
FROM        _Manufacture
WHERE       Manufacture = ISNULL(@Manufacture,Manufacture)
ORDER BY    Manufacture

我的问题是,我想使用sql连接对视图进行排序和分页。我该怎么做?请告知。

非常感谢。

3 回复 | 直到 8 年前

Mannan Bahelim 8 年前

 int pageSize = 10;
 int skip = 0;
 int recordsTotal = 0;

哪里 页大小 是要在网格中显示的记录数, 跳过是挑选记录的起点, recordsTotal记录 网格中的记录总数。

  recordsTotal = EmpList.Count();
  EmpList= EmpList.Skip(skip).Take(pageSize).ToList();

Bhuban Shrestha 8 年前

您需要传递页码和每个请求要获取的记录数。

@PageNumber int
@Records int


Declare @StartingPoint int, @EndingPoint int
Set @StartingPoint = ((@PageNumber -1 ) * @Records)+1
Set @EndingPoint = @PageNumber  * @Records
Select * from (
  Select Dense_Rank() over(order by <PK>) Order,* from <Table>
)TempRecord where [Order]>= @StartingPoint  and [Order]<=@EndingPoint

现在,当您调用这个存储过程时,传递PageNumber并记录参数值

当PageNumber为1,Records为10时,StartingPoint为1,EndingPoint为10,因此得到10条记录。同样,第2页的StartingPoint为11,EndingPoint为20,因此得到10条记录。

Haminteu 8 年前

谢谢你的回答。我有自己的答案。我按照下面的教程创建排序和分页。

Sorting, Filtering, and Paging with the Entity Framework in an ASP.NET MVC Application

非常感谢。
非常感谢。