/// <summary>
/// 生成拼接sql参数列表
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List <SqlParameter> ParseToSqlParameters(SysRoleSearchEntity entity)
{
List <SqlParameter> paraList = new List <SqlParameter>();
//table
paraList.Add(CPTable(entity));
//fields
paraList.Add(CPFields(entity));
//filter_SqlWhere
paraList.Add(CPWhere(entity));
//order
paraList.Add(CPOrder(entity));
//pagesize
paraList.Add(new SqlParameter("@pageSize", entity.PageSize));
//pageindex
paraList.Add(new SqlParameter("@pageIndex", entity.PageIndex));
paraList.Add(new SqlParameter()
{
ParameterName = "@Records", Value = 0, Direction = ParameterDirection.Output
});
return(paraList);
}
/// <summary>
/// 设置表关联
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPTable(SysRoleSearchEntity entity)
{
StringBuilder sbtable = new StringBuilder();
//基本表
sbtable.Append(" M_System_Role ");
return(new SqlParameter("@TableName", sbtable.ToString()));
}
/// <summary>
/// 设置显示字段
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPFields(SysRoleSearchEntity entity)
{
StringBuilder sbfileds = new StringBuilder();
if (entity.UseDBPagination)
{
sbfileds.Append(@" Role_ID, Role_Name, CONVERT(NVARCHAR(19),CreateTime,121) AS CreateTime ");
}
else
{
throw new NotImplementedException();
}
return(new SqlParameter("@Fields", sbfileds.ToString()));
}
/// <summary>
/// 设置排序
/// </summary>
/// <param name="enity"></param>
/// <returns></returns>
private SqlParameter CPOrder(SysRoleSearchEntity enity)
{
StringBuilder sborder = new StringBuilder();
if (enity.OrderfieldType == OrderFieldType.Desc)
{
sborder.Append(" Role_ID DESC ");
}
else
{
sborder.Append(" Role_ID ASC ");
}
return(new SqlParameter("@OrderField", sborder.ToString()));
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPWhere(SysRoleSearchEntity entity)
{
StringBuilder sbwhere = new StringBuilder(" 1=1 ");
if (!string.IsNullOrEmpty(entity.Role_Name))
{
sbwhere.Append(" AND Role_Name like '" + entity.Role_Name + "%'");
}
if (entity.Role_ID > 0)
{
sbwhere.Append(" AND Role_ID = " + entity.Role_ID + "");
}
return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
}
private DataTable GetQueryData(bool isDownload)
{
BCtrl_SysRole bll = new BCtrl_SysRole();
int totalcnt = 0;
SysRoleSearchEntity entity = new SysRoleSearchEntity();
entity.Role_Name = _strRolename;
entity.PageSize = base.PageSize;
entity.PageIndex = base.PageIndex;
entity.UseDBPagination = !isDownload;
DataTable table = bll.QueryRoleTableByPage(entity, out totalcnt);
base.TotalRecords = totalcnt;
return(table);
}
public DataTable QueryRoleTableByPage(SysRoleSearchEntity entity, out int totalcnt)
{
totalcnt = 0;
DataTable table = null;
try
{
if (entity.UseDBPagination)
{
SqlParameter[] prms = ParseToSqlParameters(entity).ToArray();
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
}
else
{
List <SqlParameter> prmsList = new List <SqlParameter>();
string sql = "SELECT Role_ID,Role_Name,Convert(nvarchar(19), CreateTime,121) AS CreateTime FROM M_System_Role ";
string sqlWhere = " WHERE 1=1 ";
string sqlOrder = " ORDER BY Role_ID Asc";
if (!string.IsNullOrEmpty(entity.Role_Name))
{
prmsList.Add(new SqlParameter("@Role_Name", entity.Role_Name));
sqlWhere += " AND Role_Name like @Role_Name + '%' ";
}
if (entity.OrderfieldType == OrderFieldType.Desc)
{
sqlOrder = " ORDER BY Role_ID Desc";
}
sql = sql + sqlWhere + sqlOrder;
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.Text, sql, prmsList.ToArray()).Tables[0];
totalcnt = table != null ? table.Rows.Count : 0;
}
}
catch (Exception ex)
{
totalcnt = -1;
LogUtil.WriteLog(ex);
}
return(table);
}
public DataTable QueryRoleTableByPage(SysRoleSearchEntity entity, out int totalcnt)
{
return(dao.QueryRoleTableByPage(entity, out totalcnt));
}
