Net Core教程
c#分页
本文主要是介绍c#分页，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！
一、分页
方法一：
/// <summary>
/// 获得分页后的数据列表
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="strWhere">筛选条件</param>
/// <param name="strOrder">排序条件</param>
/// <param name="startIndex">开始的索引</param>
/// <param name="endIndex">结束的索引</param>
/// <returns></returns>
public DataSet GetListByPaged(string tableName, string strWhere, string strOrder, int startIndex, int endIndex)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(strOrder.Trim()))
{
strSql.Append("order by T." + strOrder);
}
strSql.Append(")AS num, T.* from " + tableName + " T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.num between {0} and {1}", startIndex, endIndex);
return DbManagerSQL.Query(strSql.ToString());
}
方法二：
/// <summary>
/// 分页方法
/// Created by roc,2009/07/11
/// </summary>
/// <param name="Tables">表名,多表联合查询请使用 tA a inner join tB b On a.AID = b.AID</param>
/// <param name="PK">主键，可以带表头 a.AID</param>
/// <param name="Sort">排序字段</param>
/// <param name="PageNumber">开始页码</param>
/// <param name="PageSize">页大小</param>
/// <param name="Fields">读取字段</param>
/// <param name="Filter">Where条件</param>
/// <param name="Group">分组</param>
/// <param name="IsCount">是否获得总记录数,0表示不获得记录总数,1表示获得分页数据的同时获得记录总数,2表示仅获得记录总数</param>
/// <returns>分页结果集</returns>
public static DataSet GetRecordByPage_1_0(string Tables, string PK, string Sort, int PageNumber, int PageSize, string Fields, string Filter, string Group, int IsCount)
{
//获得命令
//string sqlCommand = "Pg_Paging";
//DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
SqlParameter[] parameters = {
new SqlParameter("@Tables", SqlDbType.VarChar, 1000),
new SqlParameter("@PK", SqlDbType.VarChar, 100),
new SqlParameter("@Sort", SqlDbType.VarChar,100),
new SqlParameter("@PageNumber",SqlDbType.Int),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@Fields", SqlDbType.VarChar,1000),
new SqlParameter("@Filter", SqlDbType.VarChar,1000),
new SqlParameter("@Group", SqlDbType.VarChar,1000),
new SqlParameter("@isCount", SqlDbType.Int)
};
parameters[0].Value = Tables;
parameters[1].Value = PK;
parameters[2].Value = Sort;
parameters[3].Value = PageNumber;
parameters[4].Value = PageSize;
parameters[5].Value = Fields;
parameters[6].Value = Filter;
parameters[7].Value = Group;
parameters[8].Value = IsCount;
return RunProcedure("P_GetRecordByPage_1_0", parameters, "ds");
}
#region 存储过程操作
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters)
{
SqlConnection connection = new SqlConnection(connectionString);
try
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
sqlDA.Fill(dataSet, "DataTable");
connection.Close();
return dataSet;
}
finally
{
connection.Dispose();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="tableName">DataSet结果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter sqlDA = new SqlDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
connection.Close();
return dataSet;
}
}
/// <summary>
/// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
/// </summary>
/// <param name="connection">数据库连接</param>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand</returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName, connection );
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add( parameter );
}
return command;
}
/// <summary>
/// 执行存储过程，返回影响的行数
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="rowsAffected">影响的行数</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected )
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(connection,storedProcName, parameters );
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
return result;
}
}
/// <summary>
/// CDW 扩展可以用外面的数据库对串联接
/// </summary>
/// <param name="storedProcName">存储过程名称</param>
/// <param name="parameters">存储过程参数</param>
/// <param name="command">SQLCommon对像</param>
/// <param name="rowsAffected">返回影响数据条数</param>
/// <returns>返回值</returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters,SqlCommand command, out int rowsAffected)
{
int result;
if (command.Connection != null && command.Connection.State == ConnectionState.Closed)
command.Connection.Open();
CommandType oldcommandtype = command.CommandType;
if (oldcommandtype != CommandType.StoredProcedure)
command.CommandType = CommandType.StoredProcedure;
command.CommandText = storedProcName;
command.Parameters.Add(new SqlParameter("ReturnValue",
SqlDbType.Int, 4, ParameterDirection.ReturnValue,
false, 0, 0, string.Empty, DataRowVersion.Default, null));
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
rowsAffected = command.ExecuteNonQuery();
result = (int)command.Parameters["ReturnValue"].Value;
if (oldcommandtype != CommandType.StoredProcedure)
command.CommandType = oldcommandtype;
return result;
}
public static string RunProcedureScalar(string storedProcName, IDataParameter[] parameters, string retName)
{
SqlConnection connection = new SqlConnection(connectionString);
try
{
connection.Open();
SqlCommand command = BuildIntCommand(connection, storedProcName, parameters);
command.ExecuteNonQuery();
return System.Convert.ToString(command.Parameters[retName].Value);
}
catch (Exception ex)
{
return ex.Message;
}
finally
{
connection.Dispose();
}
}
/// <summary>
/// 创建 SqlCommand 对象实例(用来返回一个整数值)
/// </summary>
/// <param name="storedProcName">存储过程名</param>
/// <param name="parameters">存储过程参数</param>
/// <returns>SqlCommand 对象实例</returns>
private static SqlCommand BuildIntCommand(SqlConnection connection,string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(connection,storedProcName, parameters );
command.Parameters.Add( new SqlParameter ( "ReturnValue",
SqlDbType.Int,4,ParameterDirection.ReturnValue,
false,0,0,string.Empty,DataRowVersion.Default,null ));
return command;
}
#endregion
存储过程：
create PROCEDURE [dbo].[P_GetRecordByPage_1_0]
@Tables varchar(3000), --表名,多表联合查询请使用 tA a inner join tB b On a.AID = b.AID
@PK varchar(100), --主键，可以带表头 a.AID
@Sort varchar(100) = '', --排序字段
@PageNumber int = 1, --开始页码
@PageSize int = 20, --页大小
@Fields varchar(3000) = '*',--读取字段
@Filter varchar(1000) = NULL,--Where条件
@Group varchar(1000) = NULL,--分组
@IsCount int = 0 --是否获得总记录数,0表示不获得记录总数,1表示获得分页数据的同时获得记录总数,2表示仅获得记录总数
AS
declare @strFilter varchar(2000)
declare @sql varchar(8000)
declare @strTotal varchar(6000)
declare @grpCountSql varchar(6000) /*存在分组数据统计总数时的sql处理*/
set @grpCountSql = ''
IF((@Filter IS NOT NULL) AND (@Filter != ''))
BEGIN
SET @strFilter = ' WHERE ' + @Filter + ' '
END
ELSE
BEGIN
SET @strFilter = ''
END
if(@IsCount != 2)
begin
if @Sort = ''
begin
set @Sort = @PK-- + ' DESC '
end
IF @PageNumber < 1
begin
SET @PageNumber = 1
end
-- if @PageNumber = 1 --第一页提高性能
-- begin
-- top 跟 BETWEEN 取出来的数据有可能不一样，导致CWT\行旅 UATP客户设置展示不全
-- set @sql = 'select top ' + str(@PageSize) +' '+@Fields+ ' from ' + @Tables + ' ' + @strFilter + @Group + ' ORDER BY '+ @Sort
-- -- print @sql
---- print 'assss'
---- return
-- end
-- else
-- begin
/**//*Execute dynamic query*/
DECLARE @START_ID varchar(50)
DECLARE @END_ID varchar(50)
SET @START_ID = convert(varchar(50),(@PageNumber - 1) * @PageSize + 1)
SET @END_ID = convert(varchar(50),@PageNumber * @PageSize)
set @sql = ' SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, '
+@Fields+ ' FROM '+@Tables+ @strFilter + @Group + ') AS D where rownum BETWEEN '+@START_ID
+' AND ' +@END_ID
exec(@sql)
end
/*分组时的记录总数*/
set @grpCountSql = 'select count(*) from (' + 'select '+@Fields+ ' from ' + @Tables + ' ' + @strFilter + @Group + ') grpTb'
if((@IsCount = 1) or (@IsCount = 2)) --是否获得记录条数
begin
set @strTotal = 'SELECT Count(*) FROM ' + @Tables + @strFilter
if(len(@Group) > 0)
begin
set @strTotal = @grpCountSql
end
exec(@strTotal)
end
这篇关于c#分页的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！
您可能喜欢
-
沐雪多租宝商城源码从.NetCore3.1升级到.Net6的步骤03-01
-
使用Microsoft.Extensions.AI在.NET中生成嵌入向量12-06
-
微软研究：RAG系统的四个层次提升理解与回答能力11-18
-
C#中怎么从PEM格式的证书中提取公钥？-icode9专业技术文章分享11-15
-
云架构设计——如何用diagrams.net绘制专业的AWS架构图？11-14
-
首个适配Visual Studio平台的国产智能编程助手CodeGeeX正式上线！C#程序员必备效率神器！05-08
-
C#设计模式之十六迭代器模式（Iterator Pattern）【行为型】03-30
-
c# datetime tryparse03-29
-
list find index c#02-21
-
convert toint32 c#01-24
-
Advanced .Net Debugging 1：你必须知道的调试工具01-24
-
.NET集成IdGenerator生成分布式全局唯一ID01-24
-
用CI/CD工具Vela部署Elasticsearch + C# 如何使用01-23
-
.NET开源的简单、快速、强大的前后端分离后台权限管理系统01-23
-
C#对象二进制序列化优化：位域技术实现极限压缩01-23
栏目导航
