1 CREATE PROC usp_OrgPage_SQL
 2 @pageIndex INT,
 3 @pageSize INT,
 4 @totalCount INT OUTPUT
 5 AS
 6     BEGIN
 7         SET @totalCount = (SELECT COUNT(*) FROM dbo.Organization)
 8         SELECT * FROM
 9         (
10         SELECT *,ROW_NUMBER() OVER(ORDER BY OrganizationID DESC)AS row FROM dbo.Organization
11         )AS res
12         WHERE res.row BETWEEN @pageSize * (@pageIndex - 1) + 1 AND @pageSize * @pageIndex
13     END

 1 public List<OrganizationModel> SelectPageList(int pageIndex, int pageSize, out int totalCount)
 2         {
 3             var cmd = DbContext.Connection.CreateCommand();
 4             cmd.CommandText = "kydContext.usp_OrgPage_SQL";
 5             cmd.CommandType = System.Data.CommandType.StoredProcedure;
 6             cmd.Parameters.Add(new EntityParameter
 7             {
 8                 ParameterName = "pageIndex",
 9                 Value =http://www.mamicode.com/ pageIndex
10             });
11             cmd.Parameters.Add(new EntityParameter
12             {
13                 ParameterName = "pageSize",
14                 Value =http://www.mamicode.com/ pageSize
15             });
16             cmd.Parameters.Add(new EntityParameter
17             {
18                 ParameterName = "totalCount",
19                 Direction = System.Data.ParameterDirection.Output
20             });
21 
22             cmd.Connection.Open();
23             DbDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SequentialAccess);
24             List<OrganizationModel> organizationModelList = new List<OrganizationModel>();
25             while (reader.Read())
26             {
27                 OrganizationModel organizationModel = new OrganizationModel();
28                 organizationModel.OrganizationID = reader.GetInt32(reader.GetOrdinal("OrganizationID"));
29                 organizationModel.OrganizationName = reader.GetString(reader.GetOrdinal("OrganizationName"));
30                 organizationModelList.Add(organizationModel);
31             }
32             reader.Close();
33             totalCount = Convert.ToInt32(cmd.Parameters["totalCount"].Value);
34             cmd.Connection.Close();
35             return organizationModelList;
36         }