/// <summary>
/// 分頁方法
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="IdName">主鍵名</param>
/// <param name="columns">選取的列,默認為*,表示全部</param>
/// <param name="whereText">查詢條件,可以為空,默認為1=1</param>
/// <param name="orderText">排序條件,可以為空,默認為空</param>
/// <param name="startIndex">開始值</param>
/// <param name="endIndex">結束值</param>
/// <returns></returns>
public static DataTable GetPager(string tablename,string IdName,string columns,string whereText,string orderText,int startIndex,int endIndex)
{
if (string.IsNullOrEmpty(columns))
{
columns="*";
}
if (string.IsNullOrEmpty(whereText))
{
whereText = " 1=1 ";
}
if (string.IsNullOrEmpty(orderText))
{
orderText = " id ";
}
SqlParameter[] par ={
new SqlParameter("@Tname",tablename),
new SqlParameter("@kname",IdName),
new SqlParameter("@columns",columns),
new SqlParameter("@whereText",whereText),
new SqlParameter("@orderText",orderText),
new SqlParameter("@startIndex", startIndex),
new SqlParameter("@endIndex", endIndex)
};
return GetPagerBase(GlobalConnectionString.ToString(), CommandType.StoredProcedure, "sp_paging", par).Tables[0];
}
/// <summary>
/// 分頁基礎方法
/// </summary>
/// <param name="m_connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
private static DataSet GetPagerBase(string m_connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(m_connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return ds;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 獲取查詢到的數據總數,用于分頁
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="sqlwhere">查詢條件,不帶where</param>
/// <returns></returns>
public static int GetPagingNum(string tablename, string sqlwhere)
{
string sql = "select count(id) from {0} where {1}";
return (int)ExecuteScalar(string.Format(sql,tablename,sqlwhere));
}
/// 分頁方法
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="IdName">主鍵名</param>
/// <param name="columns">選取的列,默認為*,表示全部</param>
/// <param name="whereText">查詢條件,可以為空,默認為1=1</param>
/// <param name="orderText">排序條件,可以為空,默認為空</param>
/// <param name="startIndex">開始值</param>
/// <param name="endIndex">結束值</param>
/// <returns></returns>
public static DataTable GetPager(string tablename,string IdName,string columns,string whereText,string orderText,int startIndex,int endIndex)
{
if (string.IsNullOrEmpty(columns))
{
columns="*";
}
if (string.IsNullOrEmpty(whereText))
{
whereText = " 1=1 ";
}
if (string.IsNullOrEmpty(orderText))
{
orderText = " id ";
}
SqlParameter[] par ={
new SqlParameter("@Tname",tablename),
new SqlParameter("@kname",IdName),
new SqlParameter("@columns",columns),
new SqlParameter("@whereText",whereText),
new SqlParameter("@orderText",orderText),
new SqlParameter("@startIndex", startIndex),
new SqlParameter("@endIndex", endIndex)
};
return GetPagerBase(GlobalConnectionString.ToString(), CommandType.StoredProcedure, "sp_paging", par).Tables[0];
}
/// <summary>
/// 分頁基礎方法
/// </summary>
/// <param name="m_connectionString"></param>
/// <param name="cmdType"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
/// <returns></returns>
private static DataSet GetPagerBase(string m_connectionString, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(m_connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
// detach the SqlParameters from the command object, so they can be used again.
cmd.Parameters.Clear();
return ds;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 獲取查詢到的數據總數,用于分頁
/// </summary>
/// <param name="tablename">表名</param>
/// <param name="sqlwhere">查詢條件,不帶where</param>
/// <returns></returns>
public static int GetPagingNum(string tablename, string sqlwhere)
{
string sql = "select count(id) from {0} where {1}";
return (int)ExecuteScalar(string.Format(sql,tablename,sqlwhere));
}
浙公網安備 33010602011771號