/// <summary>
/// 获取某表中的总记录数
/// </summary>
/// <param name="tablename">表名</param>
/// <returns></returns>
public static int GetRecordCount(string tablename)
{
string s = "select count(*) from {0}";
s = string.Format(s, tablename);
return(Convert.ToInt32(SqlEasy.ExecuteScalar(s)));
}
public bool Exists(int RoleId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select count(1) from Sys_Role");
strSql.Append(" where ");
strSql.Append(" RoleID = @RoleId ");
SqlParameter[] parameters =
{
new SqlParameter("@RoleID", SqlDbType.Int, 4)
};
parameters[0].Value = RoleId;
Mod_Sys_Role o = (Mod_Sys_Role)SqlEasy.ExecuteScalar(strSql.ToString(), parameters);
if (o != null)
{
return(false);
}
return(true);
}
public int GetUsersCountByDepartment(int depid)
{
string sql = "select count(*) from Sys_Users_Departments where depid=@depid";
return(PublicMethod.GetInt(SqlEasy.ExecuteScalar(sql, new SqlParameter("@depid", depid))));
}
public static bool GetPageSql(ref string strPageSql, string sql, string orderColumns, Mod_Com_Pager pager)
{
int pageSize = 0;
int getPageNum = 0;
int outTotalPage = 0;
int outTotalRows = 0;
if (pager != null)
{
pageSize = pager.PageSize;
getPageNum = pager.PageNum;
outTotalPage = pager.PageCount;
outTotalRows = pager.RowCount;
}
//先计算总的数量，总记录
string sqlRowCountTmp = string.Empty;
sqlRowCountTmp = $"SELECT COUNT(1) AS Rc FROM ({sql}) PageData";
object o = new object();
o = SqlEasy.ExecuteScalar(sqlRowCountTmp);
if (o != DBNull.Value)
{
outTotalRows = (int)o;
}
//计算页码
if (outTotalRows % pageSize == 0)
{
outTotalPage = outTotalRows / pageSize;
}
else
{
outTotalPage = outTotalRows / pageSize + 1;
}
//校验取的页码
if (getPageNum < 1)//比页面小就去第一页
{
getPageNum = 1;
}
if (outTotalPage < getPageNum)//比页面大就取最大页面
{
getPageNum = outTotalPage;
}
//开始行
int startRow = (getPageNum - 1) * pageSize;
//结束行
int endRow = getPageNum * pageSize;
pager.PageCount = outTotalPage;
pager.RowCount = outTotalRows;
StringBuilder strSql = new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append($" SELECT ROW_NUMBER() OVER ( order by {orderColumns} desc) AS Rw,T.*");
strSql.Append($" FROM ({sql}) T");
strSql.Append(" )TT ");
strSql.AppendFormat(" WHERE TT.Rw > {0} and TT.Rw<={1}", startRow, endRow);
strPageSql = strSql.ToString();
return(false);
}