C#基础篇之二 分页

public void BindData(string strClass)
{
int DataCount = 0;
int NowPage = 1;
int AllPage = 0;
int PageSize = Convert.ToInt32(HPageSize.Text);
switch (strClass)
{
case "next":
NowPage = Convert.ToInt32(HNowPage.Text) + 1;
break;
case "up":
NowPage = Convert.ToInt32(HNowPage.Text) - 1;
break;
case "end":
NowPage = Convert.ToInt32(HAllPage.Text);
break;
case "refresh":
NowPage = Convert.ToInt32(HNowPage.Text);
break;
default:
break;
}

DataTable dsLog = BLL.bllClassInfo.GetClassInfo(NowPage, PageSize, out AllPage, out DataCount, HWhere.Text);
if (dsLog.Rows.Count == 0 || AllPage == 1)
{
LBEnd.Enabled = false;
LBHome.Enabled = false;
LBNext.Enabled = false;
LBUp.Enabled = false;
}
else if (NowPage == 1)
{
LBHome.Enabled = false;
LBUp.Enabled = false;
LBNext.Enabled = true;
LBEnd.Enabled = true;
}
else if (NowPage == AllPage)
{
LBHome.Enabled = true;
LBUp.Enabled = true;
LBNext.Enabled = false;
LBEnd.Enabled = false;
}
else
{
LBEnd.Enabled = true;
LBHome.Enabled = true;
LBNext.Enabled = true;
LBUp.Enabled = true;
}
this.dataGridView_ClassInfo.DataSource = dsLog.DefaultView;
PageMes.Text = string.Format("[每页{0}条 第{1}页／共{2}页 共{3}条]", PageSize, NowPage, AllPage, DataCount);
HNowPage.Text = Convert.ToString(NowPage++);
HAllPage.Text = AllPage.ToString();

if (dsLog.Rows.Count > 0)
{
this.Btn_Update.Enabled = true;
this.Btn_Del.Enabled = true;
}
else
{
this.Btn_Update.Enabled = false;
this.Btn_Del.Enabled = false;
}
}

////

/*查询班级信息*/
public static System.Data.DataTable GetClassInfo(int PageIndex, int PageSize, out int PageCount, out int RecordCount, string strWhere)
{
try
{
string strSql = " select ClassInfo.*,SpecialFieldInfo.specialFieldName from ClassInfo,SpecialFieldInfo where 1=1 and ClassInfo.classSpecialFieldNumber=SpecialFieldInfo.specialFieldNumber";
string strShow = "classNumber as 班级编号,className as 班级名称,specialFieldName as 所属专业,convert(char(11),classBirthDate,20) as 成立日期,classTeacherCharge as 班主任,classTelephone as 联系电话";
return DAL.DBHelp.ExecutePagerWhenPrimaryIsString(PageIndex, PageSize, "classNumber", strShow, strSql, strWhere, " classNumber asc ", out PageCount, out RecordCount);
}
catch (Exception ex)
{
throw ex;
}
}

/////

/// <summary>
/// ACCESS高效分页：当表的主键是字符串类型时候
/// </summary>
/// <param name="pageIndex">当前页码</param>
/// <param name="pageSize">分页容量</param>
/// <param name="strKey">主键</param>
/// <param name="showString">显示的字段</param>
/// <param name="queryString">查询字符串，支持联合查询</param>
/// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
/// <param name="orderString">排序规则</param>
/// <param name="pageCount">传出参数：总页数统计</param>
/// <param name="recordCount">传出参数：总记录统计</param>
/// <returns>装载记录的DataTable</returns>
public static DataTable ExecutePagerWhenPrimaryIsString(int pageIndex, int pageSize, string strKey, string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
{
if (pageIndex < 1) pageIndex = 1;
if (pageSize < 1) pageSize = 10;
if (string.IsNullOrEmpty(showString)) showString = "*";
if (string.IsNullOrEmpty(orderString)) orderString = strKey + " asc ";
SqlConnection m_Conn = GetConnection;

try
{
m_Conn.Open();
}catch {}
string myVw = string.Format(" ( {0} ) tempVw ", queryString);
SqlCommand cmdCount = new SqlCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn);

recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

if ((recordCount % pageSize) > 0)
pageCount = recordCount / pageSize + 1;
else
pageCount = recordCount / pageSize;
SqlCommand cmdRecord;
if (pageIndex == 1)//第一页
{
string sql = string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString);
cmdRecord = new SqlCommand(sql, m_Conn);
}
else if (pageIndex > pageCount)//超出总页数
{
string sql= string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString);
cmdRecord = new SqlCommand(sql, m_Conn);
}
else
{
int pageLowerBound = pageSize * pageIndex;
int pageUpperBound = pageLowerBound - pageSize;
string recordIDs = recordIDString(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound);
string queryStringend = string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw, strKey, recordIDs, orderString);
cmdRecord = new SqlCommand(queryStringend, m_Conn);

}
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmdRecord);
DataTable dt = new DataTable();
dataAdapter.Fill(dt);
m_Conn.Close();
m_Conn.Dispose();
return dt;

}

/// <summary>
/// 分页使用
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private static string recordID(string query, int passCount)
{
SqlConnection m_Conn = GetConnection;

try
{
m_Conn.Open();
}
catch { }
SqlCommand cmd = new SqlCommand(query, m_Conn);
string result = string.Empty;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += "," + dr.GetInt32(0);
}
passCount--;
}
}
// m_Conn.Close();
// m_Conn.Dispose();
return result.Substring(1);

}

///

/// <summary>
/// 分页使用:主键是字符串类型时候
/// </summary>
/// <param name="query"></param>
/// <param name="passCount"></param>
/// <returns></returns>
private static string recordIDString(string query, int passCount)
{
SqlConnection m_Conn = GetConnection;

try
{
m_Conn.Open();
}
catch { }
SqlCommand cmd = new SqlCommand(query, m_Conn);
string result = string.Empty;
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (passCount < 1)
{
result += ",'" + dr.GetString(0) + "'";
}
passCount--;
}
}
// m_Conn.Close();
// m_Conn.Dispose();
return result.Substring(1);

}