下面是SQL命令在企业管理器里面直接创建一个存储过程然后输入下面内容保存为GetCurrentPageData(这个名字可以自己更改)
CREATE PROCEDURE [dbo].[GetCurrentPageData]
@TableName varchar(1000), --table name 换成SQL语句
@KeyFildName varchar(255), --key filed name
@PageIndex int, --page index
@PageSize int, --page size
@TotalRecord int output --return recordNum
AS
IF lower(left(ltrim(@TableName),6))='select'
BEGIN
SET @TableName='SELECT TOP 999999999 '+ substring(@TableName,7,8000)
END
declare @strSQL nvarchar(4000)
DECLARE @TopNum int
SET @TopNum=(@PageIndex-1)*@PageSize
SET @strSQL=N'select @TotalRecord=count(*) from ('+@TableName+') AS T '
print(@strSQL)
EXEC SP_EXECUTESQL @strSQL,N'@TotalRecord int OUT',@TotalRecord OUT
IF @TotalRecord is Null
begin
set @TotalRecord=1
end
PRINT(@TotalRecord)
PRINT(@TableName)
SET @strSQL='SELECT TOP '+str(@PageSize)+' *
FROM ('+@TableName+') AS T1
WHERE '+@KeyFildName+ ' NOT IN (SELECT TOP '+str(@TopNum)+' '+@KeyFildName+'
FROM ('+@TableName+') AS T2)'
print (@strSQL)
exec(@strSQL)
GO
以上存储过程建立完成后就开始在C#项目下面建立一个类库,在类库新建一个类,然后在类里面建一个共用方法如下:
- /// <summary>
- /// 获取当前页的信息
- /// </summary>
- /// <param name="sql">SQL语句或是视图</param>
- /// <param name="keyFildName">关键字段名(注意:一定要是唯一性的字段)</param>
- /// <param name="pageIndex">当前页数</param>
- /// <param name="pageSize">每页大小</param>
- /// <param name="totalRecord">记录总数</param>
- /// <returns></returns>
- public DataTable GetCurrentPageDataForSql(string sql, string keyFildName,int pageIndex, int pageSize, out int totalRecord)
- {
- totalRecord = 0;
- SqlParameter[] sqlPar = new SqlParameter[6];
- sqlPar[0] = new SqlParameter("@TableName", SqlDbType.VarChar);
- sqlPar[0].Direction = ParameterDirection.Input;
- sqlPar[0].Value = sql;
- sqlPar[1] = new SqlParameter("@KeyFildName", SqlDbType.VarChar);
- sqlPar[1].Direction = ParameterDirection.Input;
- sqlPar[1].Value = keyFildName;
- sqlPar[2] = new SqlParameter("@PageIndex", SqlDbType.Int);
- sqlPar[2].Direction = ParameterDirection.Input;
- sqlPar[2].Value = pageIndex;
- sqlPar[3] = new SqlParameter("@PageSize", SqlDbType.Int);
- sqlPar[3].Direction = ParameterDirection.Input;
- sqlPar[3].Value = pageSize;
- sqlPar[4] = new SqlParameter("@TotalRecord", SqlDbType.Int);
- sqlPar[4].Direction = ParameterDirection.Output;
- DataTable dt = DataAccess.GetDataTable_Proc("GetCurrentPageDatal", sqlPar); //这方法要自己去写一个,
- //只是一个调用存储过程的方法
- if (sqlPar[4].Value != DBNull.Value && sqlPar[4].Value != null)
- {
- totalRecord = Convert.ToInt32(sqlPar[4].Value);
- }
- return dt;
- }
- 所有代码就如上了