ALTER proc [dbo].[p_page]( @tableName varchar(8000),--必须 @selectFields varchar(8000)='*', @andWhere varchar(4000),--不带where ,只写条件如 and 1=1 @orderByFields varchar(100),--必须 row_number() over(order by)用 @pageIndex int=1, @pageSize int=20, @totalCount int=0 output, @orderType varchar(5)='asc',--desc,asc @sql nvarchar(4000) output--返回当前执行的sql语句 )as begin if @selectFields='' or @selectFields is null set @selectFields='*'; if @pageIndex<=0 set @pageIndex=1; if @pageSize<=1 set @pageSize=1;
set @sql= N'select @totalCount=count(*) from '+ @tableName +' where 1=1 '+ @andWhere; EXEC sp_executesql @sql,N'@totalCount int OUTPUT',@totalCount OUTPUT ;
set @sql=N'select * from (select row_number() over(order by '+@orderByFields+' '+@orderType+') rowNumerId,'+@selectFields+ ' from '+@tableName+' where 1=1 '+@andWhere+') T where rowNumerId>'+convert(nvarchar(10),(@pageIndex-1)*@pageSize)+' and rowNumerId<='+convert(nvarchar(10),@pageIndex*@pageSize); exec (@sql); end
public static DataTable GetPagedData(string tableName, string selectFields, string andWhere, string orderByFields, int pageIndex, int pageSize, out int totalRows, string orderByType, out string getSelectSql) { DataTable dt = new DataTable(); totalRows = 0; getSelectSql = string.Empty; try { if (string.IsNullOrEmpty(tableName) || string.IsNullOrEmpty(orderByFields)) { return dt; } pageIndex = pageIndex > 0 ? pageIndex : 1; pageSize = pageSize > 0 ? pageSize : 1; orderByType = ((orderByType.ToLower().Trim() == "asc") || (orderByType.ToLower().Trim() == "desc")) ? orderByType : "asc"; selectFields = string.IsNullOrEmpty(selectFields) ? "*" : selectFields; using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlDataAdapter da = new SqlDataAdapter("p_page", conn)) { da.SelectCommand.CommandType = CommandType.StoredProcedure; da.SelectCommand.Parameters.AddWithValue("@tableName", tableName); da.SelectCommand.Parameters.AddWithValue("@orderByFields", orderByFields); da.SelectCommand.Parameters.AddWithValue("@selectFields", selectFields); da.SelectCommand.Parameters.AddWithValue("@andWhere", andWhere); da.SelectCommand.Parameters.AddWithValue("@pageIndex", pageIndex); da.SelectCommand.Parameters.AddWithValue("@pageSize", pageSize); da.SelectCommand.Parameters.AddWithValue("@totalCount", totalRows).Direction = ParameterDirection.Output; da.SelectCommand.Parameters.AddWithValue("@orderType", orderByType); //da.SelectCommand.Parameters.AddWithValue("@distinct", useDistinct ? 1 : 0); da.SelectCommand.Parameters.Add("@sql", SqlDbType.NVarChar, 4000).Direction = ParameterDirection.Output; da.Fill(dt); getSelectSql = da.SelectCommand.Parameters["@sql"].Value.ToString(); totalRows = (int)da.SelectCommand.Parameters["@totalCount"].Value; }
} } catch (System.Data.SqlClient.SqlException e) { ESHD.Command.LsgLog.AddError(e, tableName + "selectFields"); } return dt; }
|
请发表评论