#region数据库分页通用存储过程
///
///数据库分页通用存储过程
///
///表名
///主键（关键字段）
///每页记录数
///要获取的页码
///序类型,0-升序,1-降序
///查询条件(注意:不要加where)
///总记录数
///
publicstaticDataTableGetRecordFromPage(stringtblName,stringfldName,intpageSize,intpageIndex,intorderType,stringstrWhere,refintoutput)
{
SqlConnectionconn=newSqlConnection(SQLHelper.CONN_STRING);
SqlCommandcmd=newSqlCommand("GetRecordFromPage",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add(newSqlParameter("@tblName",SqlDbType.VarChar,255));
cmd.Parameters.Add(newSqlParameter("@fldName",SqlDbType.VarChar,255));
cmd.Parameters.Add(newSqlParameter("@PageSize",SqlDbType.Int,4));
cmd.Parameters.Add(newSqlParameter("@PageIndex",SqlDbType.Int,4));
cmd.Parameters.Add(newSqlParameter("@OrderType",SqlDbType.Bit));
cmd.Parameters.Add(newSqlParameter("@strWhere",SqlDbType.VarChar,2000));
cmd.Parameters.Add(newSqlParameter("@rowTotal",SqlDbType.Int));
cmd.Parameters[0].Value=tblName;
cmd.Parameters[1].Value=fldName;
cmd.Parameters[2].Value=pageSize;
cmd.Parameters[3].Value=pageIndex;
cmd.Parameters[4].Value=orderType;
cmd.Parameters[5].Value=strWhere;
cmd.Parameters[6].Direction=ParameterDirection.Output;
try
{
conn.Open();
SqlDataAdapterda=newSqlDataAdapter(cmd);
DataSetds=newDataSet();
da.Fill(ds);
if(cmd.Parameters[6].Value!=DBNull.Value&&cmd.Parameters[6].Value.ToString()!=string.Empty)
output=Convert.ToInt32(cmd.Parameters[6].Value);
da.Dispose();
conn.Close();
conn.Dispose();
returnds.Tables[0];
}
catch
{
conn.Close();
returnnull;
}
finally
{
cmd.Dispose();
conn.Dispose();
}
}
#endregion
#regionSqlServer2005通用分页方法
///
///SqlServer2005通用分页方法
///
///表名
///每页记录数
///当前页数
///排序方式：0-正序1-倒序
///排序列名称
///查询条件
///总记录数
///
publicstaticDataSetGetRecordFromPageBySQL(stringp_TblName,intp_PageSize,intp_PageIndex,intp_OrderType,stringp_OrderColumnName,stringp_Condition,refintp_RecordCount)
{
string_SQL="SelectCount()From"+p_TblName+"Where1=1"+p_Condition;
p_RecordCount=int.Parse(ExecuteScalar(_SQL).ToString());
StringBuilder_SB=newStringBuilder();
_SB.Append("SELECT");
_SB.Append("FROM");
_SB.Append("(SELECTROW_NUMBER()OVER(ORDERBY"+p_OrderColumnName+""+(p_OrderType==1?"DESC":"ASC")+")");
_SB.Append("ASOrderRank,FROM"+p_TblName+"Where1=1"+p_Condition+")");
_SB.Append("ASRank");
_SB.Append("WHEREOrderRankBETWEEN"+((p_PageIndex-1)p_PageSize+1)+"AND"+p_PageIndexp_PageSize+"");
_SB.Append("ORDERBY"+p_OrderColumnName+""+(p_OrderType==1?"DESC":"ASC")+"");
SqlConnectioniConn=newSqlConnection(CONN_STRING);
SqlCommandiCommand=newSqlCommand(_SB.ToString(),iConn);
DataSetds=newDataSet();
try
{
iConn.Open();
using(SqlDataAdapteriDa=newSqlDataAdapter(iCommand))
{
iDa.Fill(ds);
returnds;
}
}
catch(Exception_Ex)
{
throw_Ex;
}
finally
{
iConn.Close();
iCommand.Dispose();
iConn.Dispose();
}
}
#endregion |
|
