public List <int> GetDepIDs(int userid)
{
List <int> list = new List <int>();
var dr = SqlEasy.ExecuteDataReader("select depid from Sys_Users_Departments where userid=@UserID", new SqlParameter("@UserID", userid));
while (dr.Read())
{
list.Add(PublicMethod.GetInt(dr[0]));
}
dr.Close();//放置数据库连接过多的问题
return(list);
}
/// <summary>
/// 获取指定表的数据，并转换为jqgrid 的JSON格式。适用于sql2000 以上版本
/// </summary>
/// <param name="fields">要选取的列，以逗号隔开</param>
/// <param name="pageindex">当前第几页</param>
/// <param name="pagesize">每页记录条数</param>
/// <param name="orderfield">排序</param>
/// <param name="key">关键字</param>
/// <param name="where">条件</param>
/// <param name="tbname">表名或视图名</param>
/// <returns></returns>
public static string GetJsonforjQgrid(string fields, int pageindex, int pagesize, string orderfield, string key, string where, string tbname)
{
int recordcount = 0;
DataTable dt = SqlEasy.GetDataByPager2000(fields, tbname, where, orderfield, key, pageindex, pagesize, out recordcount);
int pagecount = SqlEasy.GetDataPages(pagesize, recordcount);
string json = FormatJSONForJQgrid(pagecount, pageindex, recordcount, dt);
return(json);
}
public DataTable GetNavBtnsBy(params string[] roleid)
{
var roleids = roleid.Aggregate("", (current, i) => current + (i.ToString() + ",")).TrimEnd(',');
string[] array = roleids.Split(',');
List <string> ids = new List <string>();
foreach (string id in array)
{
ids.Add("'" + id + "'");
}
string sql = "select a.*,b.ButtonTag from sys_roleNavBtns a join sys_buttons b on a.btnid=b.FID where a.roleid in (" + string.Join(",", ids.ToArray()) + ")";
return(SqlEasy.ExecuteDataTable(sql));
}
/// <summary>
/// 获得数据列表
/// </summary>
public List <Mod_Sys_Author> GetList(Hashtable ht)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select * ");
strSql.Append(" FROM Sys_Author ");
strSql.Append(" Where 1=1 ");
if (ht != null)
{
foreach (DictionaryEntry de in ht)
{
strSql.AppendLine(de.Value.ToString());
}
}
return(ConvertToListData(SqlEasy.ExecuteDataSet(strSql.ToString())));
}
/// <summary>
/// 为指定的用户分配角色
/// </summary>
/// <param name="userId">用户ID</param>
/// <param name="roleIds">角色ID</param>
/// <returns></returns>
public int AddUserTo(string userId, params string[] roleIds)
{
string sql = "insert into Sys_UserRoles (FID,userid,roleid) values('{0}','{1}','{2}')";
StringBuilder sb = new StringBuilder();
foreach (var rid in roleIds)
{
sb.AppendFormat(sql, Guid.NewGuid().ToString(), userId, rid);
sb.AppendLine();
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
return(SqlEasy.ExecuteNonQuery(sb.ToString()));
}
return(0);
}
/// <summary>
/// 为指定的用户分配角色
/// </summary>
/// <param name="userId">用户ID</param>
/// <param name="roleIds">角色ID</param>
/// <returns></returns>
public int AddUserTo(int userId, params int[] roleIds)
{
string sql = "insert into Sys_UserRoles (userid,roleid) values({0},{1})";
StringBuilder sb = new StringBuilder();
foreach (var rid in roleIds)
{
sb.AppendFormat(sql, userId, rid);
sb.AppendLine();
}
if (!string.IsNullOrEmpty(sb.ToString()))
{
return(SqlEasy.ExecuteNonQuery(sb.ToString()));
}
return(0);
}
/// <summary>
/// 批量删除一批数据
/// </summary>
public bool DeleteList(string MenuIdlist)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Sys_Menu ");
strSql.Append(" where ID in (" + MenuIdlist + ") ");
int rows = SqlEasy.ExecuteNonQuery(strSql.ToString());
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 获得前几行数据
/// </summary>
public DataSet GetList(int Top, string strWhere, string filedOrder)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select ");
if (Top > 0)
{
strSql.Append(" top " + Top.ToString());
}
strSql.Append(" * ");
strSql.Append(" FROM Sys_Btn ");
if (strWhere.Trim() != "")
{
strSql.Append(" where " + strWhere);
}
strSql.Append(" order by " + filedOrder);
return(SqlEasy.ExecuteDataSet(strSql.ToString()));
}
public string Delete(int roleid)
{
string msg = "删除失败。";
var r = RoleDal.Instance.Get(roleid);
//先删除角色中分配的权限
SqlEasy.ExecuteNonQuery("delete Sys_RoleNavBtns where roleid=@roleid", new SqlParameter("@roleid", roleid));
int k = RoleDal.Instance.Delete(roleid);
if (k > 0)
{
msg = "删除成功。";
LogBll <Role> log = new LogBll <Role>();
log.DeleteLog(r);
}
return(new JsonMessage {
Success = true, Data = k.ToString(), Message = msg
}.ToString());
}
/// <summary>
/// 得到一个对象实体
/// </summary>
public Mod_Sys_Menu GetModel(int MenuId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select MenuId, Pid, MenuName, Menu_Url, Icon, Sort ");
strSql.Append(" from Sys_Menu ");
strSql.Append(" where MenuId=@MenuId");
SqlParameter[] parameters =
{
new SqlParameter("@MenuId", SqlDbType.Int, 4)
};
parameters[0].Value = MenuId;
Mod_Sys_Menu model = new Mod_Sys_Menu();
DataSet ds = SqlEasy.ExecuteDataSet(strSql.ToString(), parameters);
if (ds.Tables[0].Rows.Count > 0)
{
if (ds.Tables[0].Rows[0]["MenuId"].ToString() != "")
{
model.MenuId = int.Parse(ds.Tables[0].Rows[0]["MenuId"].ToString());
}
if (ds.Tables[0].Rows[0]["Pid"].ToString() != "")
{
model.Pid = int.Parse(ds.Tables[0].Rows[0]["Pid"].ToString());
}
model.MenuName = ds.Tables[0].Rows[0]["MenuName"].ToString();
model.Menu_Url = ds.Tables[0].Rows[0]["Menu_Url"].ToString();
model.Icon = ds.Tables[0].Rows[0]["Icon"].ToString();
if (ds.Tables[0].Rows[0]["Sort"].ToString() != "")
{
model.Sort = int.Parse(ds.Tables[0].Rows[0]["Sort"].ToString());
}
return(model);
}
else
{
return(null);
}
}
public int SetDepartments(int roleid, string deps)
{
if (string.IsNullOrEmpty(deps))
{
return(0);
}
string[] arrDep = deps.Split(',');
string sql = "insert into Sys_Roles_Departments (roleid,depid) values({0},{1}) ";
StringBuilder sb = new StringBuilder();
foreach (string depid in arrDep)
{
sb.AppendFormat(sql, roleid, depid);
sb.AppendLine();
}
return(sb.Length > 0 ? SqlEasy.ExecuteNonQuery(sb.ToString()) : 0);
}
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);
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Mod_Sys_Menu model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Sys_Menu set ");
strSql.Append(" Pid = @Pid , ");
strSql.Append(" MenuName = @MenuName , ");
strSql.Append(" Menu_Url = @Menu_Url , ");
strSql.Append(" Icon = @Icon , ");
strSql.Append(" Sort = @Sort ");
strSql.Append(" where MenuId=@MenuId ");
SqlParameter[] parameters =
{
new SqlParameter("@MenuId", SqlDbType.Int, 4),
new SqlParameter("@Pid", SqlDbType.Int, 4),
new SqlParameter("@MenuName", SqlDbType.NVarChar, 100),
new SqlParameter("@Menu_Url", SqlDbType.NVarChar, 500),
new SqlParameter("@Icon", SqlDbType.VarChar, 50),
new SqlParameter("@Sort", SqlDbType.Int, 4)
};
parameters[0].Value = model.MenuId;
parameters[1].Value = model.Pid;
parameters[2].Value = model.MenuName;
parameters[3].Value = model.Menu_Url;
parameters[4].Value = model.Icon;
parameters[5].Value = model.Sort;
int rows = SqlEasy.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int RoleID)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Sys_Role ");
strSql.Append(" where RoleID=@RoleID");
SqlParameter[] parameters =
{
new SqlParameter("@RoleID", SqlDbType.Int, 4)
};
parameters[0].Value = RoleID;
int rows = SqlEasy.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public int Update(Mod_Sys_User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" Update Sys_User SET ");
strSql.Append(" UserName=@UserName, ");
strSql.Append(" PassWord=@PassWord, ");
strSql.Append(" RoleId=@RoleId, ");
strSql.Append(" PassSalt=@PassSalt, ");
strSql.Append(" IsDisabled=@IsDisabled ");
strSql.Append(" where 1=1 and UserId=" + model.UserId);
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 100),
new SqlParameter("@PassWord", SqlDbType.NVarChar, 100),
new SqlParameter("@RoleId", SqlDbType.Int, 8),
new SqlParameter("@PassSalt", SqlDbType.VarChar, 50),
new SqlParameter("@IsDisabled", SqlDbType.Bit, 2)
};
parameters[0].Value = model.UserName;
parameters[1].Value = model.PassWord;
parameters[2].Value = model.RoleId;
parameters[3].Value = model.PassSalt;
parameters[4].Value = model.IsDisabled;
object obj = SqlEasy.ExecuteNonQuery(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int MenuId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Sys_Menu ");
strSql.Append(" where MenuId=@MenuId");
SqlParameter[] parameters =
{
new SqlParameter("@MenuId", SqlDbType.Int, 4)
};
parameters[0].Value = MenuId;
int rows = SqlEasy.ExecuteNonQuery(strSql.ToString(), parameters);
if (rows > 0)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Mod_Sys_User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Sys_User(");
strSql.Append("UserName,PassWord,RoleId,PassSalt,IsDisabled");
strSql.Append(") values (");
strSql.Append("@UserName,@PassWord, @RoleId, @PassSalt, @IsDisabled");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@UserName", SqlDbType.NVarChar, 100),
new SqlParameter("@PassWord", SqlDbType.NVarChar, 100),
new SqlParameter("@RoleId", SqlDbType.Int, 8),
new SqlParameter("@PassSalt", SqlDbType.VarChar, 50),
new SqlParameter("@IsDisabled", SqlDbType.Bit, 2)
};
parameters[0].Value = model.UserName;
parameters[1].Value = model.PassWord;
parameters[2].Value = model.RoleId;
parameters[3].Value = model.PassSalt;
parameters[4].Value = model.IsDisabled;
object obj = SqlEasy.ExecuteNonQuery(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(Mod_Sys_Menu model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Sys_Menu(");
strSql.Append("Pid,MenuName,Menu_Url,Icon,Sort");
strSql.Append(") values (");
strSql.Append("@Pid,@MenuName, @Menu_Url, @Icon, @Sort");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
SqlParameter[] parameters =
{
new SqlParameter("@Pid", SqlDbType.Int, 4),
new SqlParameter("@MenuName", SqlDbType.NVarChar, 100),
new SqlParameter("@Menu_Url", SqlDbType.NVarChar, 500),
new SqlParameter("@Icon", SqlDbType.VarChar, 50),
new SqlParameter("@Sort", SqlDbType.Int, 4)
};
parameters[0].Value = model.Pid;
parameters[1].Value = model.MenuName;
parameters[2].Value = model.Menu_Url;
parameters[3].Value = model.Icon;
parameters[4].Value = model.Sort;
object obj = SqlEasy.ExecuteNonQuery(strSql.ToString(), parameters);
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
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);
}
