public static int Of_GetPageSQL(ref string as_SQL, string as_keyName, int ai_PageSize, int ai_PageIndex, bool ab_needTotalPage = true)
{
string ls_find_key_SQL;
ls_find_key_SQL = as_SQL;
n_findby_dw lnv_SQL = new n_findby_dw();
lnv_SQL.of_setSQL(ls_find_key_SQL);
lnv_SQL.of_SetDbtype(SqlHelper.Dbtype.ToString().ToLower());
//只取主键列的数据 ，目前假设主键列，只有一个字段
lnv_SQL.of_SetSelectPart(as_keyName);
//取到前面多少条记录
int li_top = 0;
li_top = ai_PageSize * ai_PageIndex;
lnv_SQL.of_SetTop(li_top);
DataTable ldt_Tb = new DataTable();
ls_find_key_SQL = lnv_SQL.of_GetSQLselect();
ldt_Tb = SqlHelper.ExecuteDataTable(ls_find_key_SQL);
//最终返回的SQL语句
n_findby_dw lnv_rc = new n_findby_dw();
lnv_rc.of_setSQL(as_SQL);
string ls_InWhere = "";
if (ldt_Tb.Rows.Count == 0) //没有记录
{
lnv_rc.of_add_ons("find", "0=1");
as_SQL = lnv_rc.of_GetSQLselect();
return 0;
}
string ls_ColType = null;
if (ldt_Tb.Columns.Count == 1)
{
ls_ColType = ldt_Tb.Columns[0].DataType.Name.ToLower();
}
else
{
ls_ColType = "string"; //有多列
}
string ls_One = null;
string ls_In = null;
ls_In = "";
int li_row = 0;
//取到这一页的主键值
int li_Total = 0;
li_Total = ai_PageSize * ai_PageIndex;
if (ldt_Tb.Rows.Count < li_Total)
li_Total = ldt_Tb.Rows.Count;
for (li_row = ai_PageSize * (ai_PageIndex - 1); li_row <= li_Total - 1; li_row++)
{
ls_One = ldt_Tb.Rows[li_row][0].ToString().Trim();
//目前最多支持二列
if (ldt_Tb.Columns.Count > 1)
{
ls_One += "#" + ldt_Tb.Rows[li_row][1].ToString().Trim();
}
if (ls_ColType == "string")
{
ls_One = "'" + ls_One + "'";
}
GYstring.gf_addstring_list(ref ls_In, ls_One, ",");
//每5个值，换一行，好看点
if (li_row % 5 == 0)
{
ls_In += "\r\n";
}
}
if (ls_In.Trim().Length > 0)
{
if (ldt_Tb.Columns.Count > 1)
{
//多列用#联结起来
string ls_oneCol, ls_TwoCol;
int li_pos;
li_pos = as_keyName.IndexOf(",");
ls_oneCol = as_keyName.Substring(0, li_pos);
ls_TwoCol = as_keyName.Substring(li_pos + 1);
if (SqlHelper.Dbtype.ToString().ToLower() == "mssql")
{
as_keyName = "TRIM(" + ls_oneCol + ")+TRIM(" + ls_TwoCol + ")";
}
else //mysql版
{
as_keyName = "CONCAT(TRIM(" + ls_oneCol + "), CONCAT('#',TRIM(" + ls_TwoCol + "))) ";
}
ls_InWhere = as_keyName + " in (" + ls_In + ")";
}
ls_InWhere = as_keyName + " in (" + ls_In + ")";
}
else
{
ls_InWhere = "0=1";
}
//本次需要显示的记录对应的主键 id
lnv_rc.of_add_ons("find", ls_InWhere);
as_SQL = lnv_rc.of_GetSQLselect();
//第一次计算一下，一共有多少条记录
if (ai_PageIndex == 1)
{
if (!ab_needTotalPage)
{
return 0;
}
lnv_SQL.of_SetTop(0);
//不要显示top
lnv_SQL.of_DelOrderBy(true);
string ls_SelectCount = null;
ls_SelectCount = lnv_SQL.of_GetSQLselect();
//ls_SelectCount 统计里面的SQL出来的记录数量
ls_SelectCount = " Select count(*) from (" + ls_SelectCount + ") as tt ";
int ll_Total = 0;
ll_Total = SqlHelper.ExecuteScalarNum(ls_SelectCount);
return ll_Total;
//返回总共多少条记录
}
else
{
return 1;
}
}
/// <summary>
/// 取到分页的数据
/// </summary>
/// <param name="as_sql"></param>
/// <param name="as_keyName"></param>
/// <param name="ai_PageSize"></param>
/// <param name="ai_PageIndex"></param>
/// <param name="ai_totalrow"></param>
/// <param name="ap_list"></param>
/// <returns></returns>
public static DataTable Of_GetPageDataTable(string as_sql, string as_keyName, int ai_PageSize, int ai_PageIndex, ref int ai_totalrow, GysoftParameter[] ap_list)
{
ErrStr = "";
n_findby_dw lnv_find = new n_findby_dw();
lnv_find.of_setSQL(as_sql);
DataTable ldt_tb = new DataTable();
ldt_tb= lnv_find.Of_GetPageDataTable(as_keyName, ai_PageSize, ai_PageIndex, ref ai_totalrow, ap_list);
ErrStr = lnv_find.ErrorStr;
return ldt_tb;
}