/// <summary>
/// 获得分页数据
/// </summary>
/// <param Name="pager">分页实体类：KingTop.Model.Pager</param>
/// <returns></returns>
public void PageData(KingTop.Model.Pager pager, string strModelId, string strFlowId, string strStateValue)
{
ModelManage sqlModelManage = new ModelManage();
DataTable dtModelManage = sqlModelManage.GetList("ONE", Utils.getOneParams(strModelId));
string strTableName = dtModelManage.Rows[0]["tableName"].ToString();
SqlParameter[] prams = new SqlParameter[] {
new SqlParameter("@Id", "ID"),
new SqlParameter("@Table", strTableName),
new SqlParameter("@Where", GetWhere(pager.DicWhere, strFlowId, strStateValue)),
new SqlParameter("@Cou", "*,(SELECT Name FROM K_ReviewFlowState WHERE K_ReviewFlowState.StateValue=FlowState) AS StateName"),
new SqlParameter("@NewPageIndex", pager.PageIndex),
new SqlParameter("@PageSize", pager.PageSize),
new SqlParameter("@order", "ID DESC"),
new SqlParameter("@isSql", 0),
new SqlParameter("@strSql", "")
};
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_Pager", prams);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
public void PageData(KingTop.Model.Pager pager, Hashtable hsWhereEqual, Hashtable hsWhereLike, string sort)
{
StringBuilder selSQL; // 查询SQL
string selTotalSQL; // 查询所有记录条数SQL
List <SqlParameter> selParam; // 查询参数
DataSet ds;
selSQL = new StringBuilder();
selParam = new List <SqlParameter>();
selSQL.Append("ModelFieldType=@ModelFieldType"); // 一般模型字段
selParam.Add(new SqlParameter("@ModelFieldType", 1));
foreach (string key in hsWhereEqual.Keys) // 添加查询条件
{
if (hsWhereEqual[key] != null && !string.IsNullOrEmpty(hsWhereEqual[key].ToString().Trim()))
{
selSQL.Append(" and " + key + "=@" + key);
}
selParam.Add(new SqlParameter("@" + key, hsWhereEqual[key]));
}
foreach (string key in hsWhereLike.Keys) // 添加搜索条件
{
selSQL.Append(" and " + key + " like '%" + hsWhereLike[key] + "%' ");
}
selTotalSQL = "select count(*) from K_ModelField where " + selSQL.ToString() + ";";
if (!string.IsNullOrEmpty(sort)) // 加入自定义排序
{
selSQL.Insert(0, "select T.* from (select ROW_NUMBER() over(order by " + sort + ") as IndexNum,ID,Name,ModelFieldGroupId,IsRequired,FieldAlias,BasicField,IsSystemFiierd,IsDel,IsEnable,Orders,IsListEnable,IsSearch,IsInputValue from K_ModelField where ");
}
else // 默认排序
{
selSQL.Insert(0, "select T.* from (select ROW_NUMBER() over(order by orders asc) as IndexNum,ID,Name,ModelFieldGroupId,IsRequired,FieldAlias,BasicField,IsSystemFiierd,IsDel,IsEnable,Orders,IsListEnable,IsSearch,IsInputValue from K_ModelField where ");
}
if (!string.IsNullOrEmpty(sort)) // 加入自定义排序
{
selSQL.Append(")T where T.IndexNum Between " + (pager.PageIndex - 1) * pager.PageSize + " and " + pager.PageSize * pager.PageIndex + " order by T." + sort.Trim() + ";");
}
else // 默认排序
{
selSQL.Append(")T where T.IndexNum Between " + (pager.PageIndex - 1) * pager.PageSize + " and " + pager.PageSize * pager.PageIndex + " order by T.orders asc;");
}
selSQL.Insert(0, selTotalSQL);
ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction, CommandType.Text, selSQL.ToString(), selParam.ToArray());
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
/// <summary>
/// 获得分页数据
/// </summary>
/// <param Name="pager">分页实体类：KingTop.Model.Pager</param>
/// <returns></returns>
public void PageData(KingTop.Model.Pager pager)
{
SqlParameter[] prams = new SqlParameter[] {
new SqlParameter("@NewPageIndex", pager.PageIndex),
new SqlParameter("@PageSize", pager.PageSize)
};
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_GetDataTableInfo", prams);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
/// <summary>
/// 获得分页数据
/// </summary>
public void PageData(KingTop.Model.Pager pager, string tableName, string sqlField, string sqlWhere, string sqlOrder, string sqlFieldForignData)
{
string sql;
sql = SplitSQLPacking(pager, tableName, sqlField, sqlWhere, sqlOrder, sqlFieldForignData);
sql = Regex.Replace(sql, @"(?<3>(?<1>[0-9a-zA-Z_]+)\.(?<2>[0-9a-zA-Z_]+))", "${1}.[${2}]"); // 格式化SQL中的字段名成[字段名]格式
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.Text, sql);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
/// <summary>
/// 获得分页数据
/// </summary>
/// <param name="pager">分页实体类：KingTop.Model.Pager</param>
/// <returns></returns>
public void PageData(KingTop.Model.Pager pager)
{
SqlParameter[] prams = new SqlParameter[]{
new SqlParameter("@Id","ID"),
new SqlParameter("@Table","K_TemplateProject"),
new SqlParameter("@Where",GetWhere(pager.DicWhere)),
new SqlParameter("@Cou","*"),
new SqlParameter("@NewPageIndex",pager.PageIndex),
new SqlParameter("@PageSize",pager.PageSize),
new SqlParameter("@order","ID DESC"),
new SqlParameter("@isSql",0),
new SqlParameter("@strSql","")
};
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_Pager", prams);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
/// <summary>
/// 使用SQL语句获得分页数据
/// </summary>
/// <param Name="pager">分页实体类：KingTop.Model.Pager</param>
/// <returns></returns>
public void PageData(KingTop.Model.Pager pager, int StrNumber)
{
SqlParameter[] prams = new SqlParameter[] {
new SqlParameter("@Id", ""),
new SqlParameter("@Table", ""),
new SqlParameter("@Where", ""),
new SqlParameter("@Cou", ""),
new SqlParameter("@NewPageIndex", pager.PageIndex),
new SqlParameter("@PageSize", pager.PageSize),
new SqlParameter("@order", "UserID asc"),
new SqlParameter("@isSql", 1),
new SqlParameter("@strSql", GetStrSqlByNumber(StrNumber))
};
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_Pager", prams);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
/// <summary>
/// 获得分页数据
/// </summary>
/// <param Name="pager">分页实体类：KingTop.Model.Pager</param>
/// <returns></returns>
public void PageData(KingTop.Model.Pager pager)
{
SqlParameter[] prams = new SqlParameter[] {
new SqlParameter("@Id", "ID"),
new SqlParameter("@Table", "K_ModelFieldGroup"),
new SqlParameter("@Where", GetWhere(pager.DicWhere)),
new SqlParameter("@Cou", "*,(SELECT Title FROM K_ModelManage WHERE ID = ModelId) AS ModelName"),
new SqlParameter("@NewPageIndex", pager.PageIndex),
new SqlParameter("@PageSize", pager.PageSize),
new SqlParameter("@order", "Orders asc"),
new SqlParameter("@isSql", 0),
new SqlParameter("@strSql", "")
};
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_Pager", prams);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}
/// <summary>
/// 使用SQL语句获得分页数据
/// </summary>
/// <param Name="pager">分页实体类：KingTop.Model.Pager</param>
/// <returns></returns>
public void PageData(KingTop.Model.Pager pager, int StrNumber)
{
SqlParameter[] prams = new SqlParameter[] {
new SqlParameter("@Id", ""),
new SqlParameter("@Table", ""),
new SqlParameter("@Where", ""),
new SqlParameter("@Cou", ""),
//new SqlParameter("@NewPageIndex",pager.PageIndex),
new SqlParameter("@NewPageIndex", Utils.ParseInt(System.Web.HttpContext.Current.Request.QueryString["page"], 1)),
new SqlParameter("@PageSize", pager.PageSize),
new SqlParameter("@order", "Orders DESC,UserID DESC"),
new SqlParameter("@isSql", 1),
new SqlParameter("@strSql", GetStrSqlByNumber(StrNumber, pager.DicWhere))
};
DataSet ds = SQLHelper.ExecuteDataSet(SQLHelper.ConnectionStringLocalTransaction,
CommandType.StoredProcedure, "proc_Pager", prams);
pager.RecordCount = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
pager.PageData(ds.Tables[1]);
}