分頁預存程序 + C#後台代碼

標籤：des   io   os   ar   sp   cti   代碼   log   on   

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 ‘[email protected]+‘ ‘[email protected]+‘) rowNumerId,‘[email protected]+
‘ from ‘[email protected]+‘ where 1=1 ‘[email protected]+‘) 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;
}

分頁預存程序 + C#後台代碼