//return new DBOperationsInsert<sys_user, DBNull>().Insert(user); #endregion #region 用户信息修改 /// <summary> /// 用户信息修改 /// </summary> /// <param name="user">用户信息</param> /// <returns>修改条数</returns> public int Update(sys_user user, int userid) { try { int res; string sql = "Update sys_user SET username = @p1,name = @p2,password = @p3,gender = @p4,org_id = @p6,job = @p5,phone = @p7,email = @p8,status = @p9,modify_time=getdate() where id = '" + userid + "'"; SqlParameter sqlParameter = new SqlParameter("@p1", user.username); SqlParameter sqlParameter1 = new SqlParameter("@p2", user.name); SqlParameter sqlParameter2 = new SqlParameter("@p3", user.password); SqlParameter sqlParameter3 = new SqlParameter("@p4", user.gender); SqlParameter sqlParameter4 = new SqlParameter("@p5", user.job); SqlParameter sqlParameter5 = new SqlParameter("@p6", user.org_id); SqlParameter sqlParameter6 = new SqlParameter("@p7", user.phone); SqlParameter sqlParameter7 = new SqlParameter("@p8", user.email); SqlParameter sqlParameter8 = new SqlParameter("@p9", user.status); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter, sqlParameter1, sqlParameter2, sqlParameter3, sqlParameter4, sqlParameter5, sqlParameter6, sqlParameter7, sqlParameter8); new LogUserDAL().Add(LogOperations.LogUser("修改用户信息")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("修改用户信息" + e.Message)); return(0); } //return new DBOperationsUpdate<sys_dict>().UpdateById(id, newValues); }
/// <summary> /// 用户添加 /// </summary> /// <param name="user">用户信息</param> /// <returns>添加条数</returns> public int Add(sys_user user) { try { int res; string sql = "insert into sys_user (username,name,password,gender,job,org_id,phone,email,status,create_time,modify_time) values(@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,getdate(),getdate())"; SqlParameter sqlparameter = new SqlParameter("@p1", user.username); SqlParameter sqlparameter1 = new SqlParameter("@p2", user.name); SqlParameter sqlparameter2 = new SqlParameter("@p3", user.password); SqlParameter sqlparameter3 = new SqlParameter("@p4", user.gender); SqlParameter sqlparameter4 = new SqlParameter("@p5", user.job); SqlParameter sqlparameter5 = new SqlParameter("@p6", user.org_id); SqlParameter sqlparameter6 = new SqlParameter("@p7", user.phone); SqlParameter sqlparameter7 = new SqlParameter("@p8", user.email); SqlParameter sqlparameter8 = new SqlParameter("@p9", user.status); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlparameter, sqlparameter1, sqlparameter2, sqlparameter3, sqlparameter4, sqlparameter5, sqlparameter6, sqlparameter7, sqlparameter8); new LogUserDAL().Add(LogOperations.LogUser("添加用户")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("添加用户" + e.Message)); return(0); } }
/// <summary> /// 通过parent_id查询组织机构 /// </summary> /// <param name="pid"></param> /// <returns>List<sys_org></returns> public List <sys_org> SelectByparentid(int pid) { try { DataSet ds = new DataSet(); string sql = "Select * from sys_org where parent_id= '" + pid + "'"; List <sys_org> org = new List <sys_org>(); ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { sys_org org1 = new sys_org(); org1.id = (int)ds.Tables[0].Rows[i][nameof(sys_org.id)]; org1.org_name = (string)ds.Tables[0].Rows[i][nameof(sys_org.org_name)]; org1.create_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_org.create_time)]; org1.modify_time = (DateTime)ds.Tables[0].Rows[i][nameof(sys_org.modify_time)]; org.Add(org1); } new LogUserDAL().Add(LogOperations.LogUser("查询组织机构")); return(org); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("查询组织机构" + e.Message)); return(null); } }
/// <summary> /// 用户组权限管理 /// </summary> /// <param name="groupId">用户组ID</param> /// <param name="menu_id">权限ID列表</param> /// <returns>执行结果</returns> public Result Perm(int groupId, List <int> menu_id) { PermDAL perm = new PermDAL(); Result res = new Result(); int re = 0; try { re += perm.DelG2m(groupId); foreach (var menuId in menu_id) { re += perm.AddG2m(groupId, menuId); } if (re == 0) { res.Code = RES.ERROR; res.Message = "修改失败!"; return(res); } else { res.Code = RES.OK; res.Message = "修改成功!"; new LogUserDAL().Add(LogOperations.LogUser("用户组权限修改")); return(res); } } catch { res.Code = RES.ERROR; res.Message = "修改失败!"; return(res); } }
/// <summary> /// 用户删除 /// </summary> /// <param name="user_id">用户id</param> /// <returns>删除条数</returns> public int Del(int UserId) { string sql1 = "delete from sys_u2g where user_id='" + UserId + "'"; string sql2 = "delete from log_user where user_id='" + UserId + "'"; string sql3 = "delete from sys_user where id= '" + UserId + "'"; List <String> SQLStringList = new List <string>(); SQLStringList.Add(sql1); SQLStringList.Add(sql2); SQLStringList.Add(sql3); using (SqlConnection conn = new SqlConnection(ConStr)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction trans = conn.BeginTransaction(); cmd.Transaction = trans; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } trans.Commit(); new LogUserDAL().Add(LogOperations.LogUser("删除用户")); return(count); } catch (Exception e) { trans.Rollback(); new LogSysDAL().Add(LogOperations.LogSys("删除用户" + e.Message)); return(0); } finally { conn.Close(); trans.Dispose(); conn.Dispose(); } //return new DBOperationsDelete<sys_user, DBNull>().DeleteById(userId); } }
/// <summary> /// 删除用户组中的用户 /// </summary> /// <param name="userId">用户id</param> /// <param name="groupId">用户id</param> /// <returns>删除条数</returns> public int DelG2u(int groupId, int userId) { try { int res; string sql = "delete from sys_u2g where user_id='" + userId + "' and group_id= '" + groupId + "' "; res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql); new LogUserDAL().Add(LogOperations.LogUser("删除用户组中的用户")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("" + e.Message)); return(0); } }
/// <summary> /// 重置用户密码 /// </summary> /// <param name="uesrid">用户id</param> /// <returns>修改条数</returns> public int RePassword(int userid) { try { int res; string sql = "Update sys_user SET password = '******' modify_time=getdate() where id = '" + userid + "'"; res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql); new LogUserDAL().Add(LogOperations.LogUser("重置密码")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("重置密码" + e.Message)); return(0); } }
/// <summary> /// 删除用户组 /// </summary> /// <param name="id">用户组id</param> /// <returns>删除条数</returns> public int Del(int id) { string sql1 = "delete from sys_g2m where group_id='" + id + "'"; string sql2 = "delete from sys_u2g where group_id='" + id + "'"; string sql3 = "delete from sys_group where id='" + id + "'"; List <String> SQLStringList = new List <string>(); SQLStringList.Add(sql1); SQLStringList.Add(sql2); SQLStringList.Add(sql3); using (SqlConnection conn = new SqlConnection(ConStr)) { conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { int count = 0; for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n]; if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; count += cmd.ExecuteNonQuery(); } } tx.Commit(); new LogUserDAL().Add(LogOperations.LogUser("删除用户组")); return(count); } catch (Exception e) { tx.Rollback(); new LogSysDAL().Add(LogOperations.LogSys("删除用户组" + e.Message)); return(0); } finally { conn.Close(); tx.Dispose(); conn.Dispose(); } } }
/// <summary> /// 删除组织机构 /// </summary> /// <param name="orgid"></param> /// <returns>删除条数</returns> public int Del(int orgid) { try { int res = 0; string sql = "delete from sys_org where id=@p1"; SqlParameter sqlparameter1 = new SqlParameter("@p", orgid); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlparameter1); new LogUserDAL().Add(LogOperations.LogUser("删除组织机构")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("删除组织机构" + e.Message)); return(0); } }
/// <summary> ///添加用户组 /// </summary> /// <param name="group">用户组信息</param> /// <returns>添加条数</returns> public int Add(sys_group group) { try { int res; string sql1 = "Insert into sys_group(group_name,remark,create_time,modify_time) values(@p1,@p2,getdate(),getdate())"; SqlParameter sqlparameter1 = new SqlParameter("@p1", group.group_name); SqlParameter sqlparameter2 = new SqlParameter("@p2", group.remark); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql1, sqlparameter1, sqlparameter2); new LogUserDAL().Add(LogOperations.LogUser("添加用户组")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("添加用户组" + e.Message)); return(0); } }
/// <summary> /// 用户组信息修改 /// </summary> /// <param name="group">用户组信息</param> /// <returns>返回修改条数</returns> public int Update(sys_group group) { try { int res; string sql = "update sys_group set group_name = @p2,remark = @p3,modify_time = getdate() where id= @p1"; SqlParameter sqlParameter1 = new SqlParameter("@p1", group.id); SqlParameter sqlParameter2 = new SqlParameter("@p2", group.group_name); SqlParameter sqlParameter3 = new SqlParameter("@p3", group.remark); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2, sqlParameter3); new LogUserDAL().Add(LogOperations.LogUser("修改用户组")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("修改用户组" + e.Message)); return(0); } }
/// <summary> /// 添加用户进用户组 /// </summary> /// <param name="userId">用户id</param> /// <param name="groupId">用户组id</param> /// <returns>返回添加条数</returns> public int AddU2g(int groupId, int userId) { try { int res; string sql = "insert into sys_u2g (user_id,group_id) values(@p1,@p2)"; SqlParameter sqlparameter = new SqlParameter("@p1", userId); SqlParameter sqlparameter1 = new SqlParameter("@p2", groupId); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlparameter, sqlparameter1); new LogUserDAL().Add(LogOperations.LogUser("用户组添加用户")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("用户组添加用户" + e.Message)); return(0); } }
/// <summary> /// 根据id进行数据字典修改 /// </summary> /// <param name="SysDict">数据字典信息</param> /// <returns>修改修改条数</returns> public int Update(sys_dict SysDict) { try { int res = 0; SqlParameter sqlParameter = new SqlParameter("@p1", SysDict.category_name); SqlParameter sqlParameter2 = new SqlParameter("@p2", SysDict.id); SqlParameter sqlParameter3 = new SqlParameter("@p3", DateTime.Now); string sql = "update sys_dict set category_name = @p1,modify_time = @p3 where id = @p2"; res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter, sqlParameter2, sqlParameter3); new LogUserDAL().Add(LogOperations.LogUser("修改数据字典")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys("修改数据字典" + e.Message)); return(0); } }
/// <summary> /// 人员信息修改 /// </summary> /// <param name="info">需要更新的人员信息</param> /// <returns>更新条数</returns> public int Update(person_basic info) { try { // sql语句 string sql = "update person_basic set " + "name = @name, former_name = @former_name, gender = @gender, identity_number = @identity_number, birth_date = @birth_date, native_place = @native_place, marry_status = @marry_status, job_status = @job_status, income = @income, temper = @temper, family = @family, person_type_id = @person_type_id, qq = @qq, address = @address, phone = @phone, belong_place_id = @belong_place_id, nation = @nation, input_time = @input_time, user_id = @user_id, isdel = @isdel " + "where id = @id"; // 参数赋值 SqlParameter name = new SqlParameter("@name", info.name); SqlParameter former_name = new SqlParameter("@former_name", info.former_name); SqlParameter gender = new SqlParameter("@gender", info.gender); SqlParameter identity_number = new SqlParameter("@identity_number", info.identity_number); SqlParameter birth_date = new SqlParameter("@birth_date", info.birth_date); SqlParameter native_place = new SqlParameter("@native_place", info.native_place); SqlParameter marry_status = new SqlParameter("@marry_status", info.marry_status); SqlParameter job_status = new SqlParameter("@job_status", info.job_status); SqlParameter income = new SqlParameter("@income", info.income); SqlParameter temper = new SqlParameter("@temper", info.temper); SqlParameter family = new SqlParameter("@family", info.family); SqlParameter person_type_id = new SqlParameter("@person_type_id", info.person_type_id); SqlParameter qq = new SqlParameter("@qq", info.qq); SqlParameter address = new SqlParameter("@address", info.address); SqlParameter phone = new SqlParameter("@phone", info.phone); SqlParameter belong_place_id = new SqlParameter("@belong_place_id", info.belong_place_id); SqlParameter nation = new SqlParameter("@nation", info.nation); SqlParameter input_time = new SqlParameter("@input_time", info.input_time); SqlParameter user_id = new SqlParameter("@user_id", info.user_id); SqlParameter isdel = new SqlParameter("@isdel", info.isdel); SqlParameter id = new SqlParameter("@id", info.id); // 执行sql语句 res = SqlHelper.ExecuteNonQuery(DALBase.ConStr, CommandType.Text, sql, name, former_name, gender, identity_number, birth_date, native_place, marry_status, job_status, income, temper, family, person_type_id, qq, address, phone, belong_place_id, nation, input_time, user_id, isdel, id); // 添加用户日志 new LogUserDAL().Add(LogOperations.LogUser("人员信息修改")); } catch (Exception e) { // 添加系统日志 new LogSysDAL().Add(LogOperations.LogSys("人员信息修改:" + e.Message)); //return 0; } // 返回执行成功条数 return(res); }
/// <summary> /// 修改组织机构 /// </summary> /// <param name="org"></param> /// <returns>修改条数</returns> public int Update(sys_org org) { try { int res = 0; string sql = "upadate sys_menu SET parent_id = @p1,org_name = @p2,create_time = @p3,modify_time = @p4 where id = '" + org.id + "'"; SqlParameter sqlParameter1 = new SqlParameter("@p1", org.parent_id); SqlParameter sqlParameter2 = new SqlParameter("@p2", org.org_name); SqlParameter sqlParameter3 = new SqlParameter("@p3", org.create_time); SqlParameter sqlParameter4 = new SqlParameter("@p4", org.modify_time); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2, sqlParameter3, sqlParameter4); new LogUserDAL().Add(LogOperations.LogUser("修改组织机构")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys(e.Message)); return(0); } }
/// <summary> /// 添加组织机构 /// </summary> /// <param name="org"></param> /// <returns>添加条数</returns> public int Add(sys_org org) { try { int res = 0; string sql = "insert into sys_menu(parent_id,org_name,creat_time,modify_time) values(@p1,@p2,@p3,@p4) where parent_id = @id"; SqlParameter sqlParameter1 = new SqlParameter("@p1", org.parent_id); SqlParameter sqlParameter2 = new SqlParameter("@p2", org.org_name); SqlParameter sqlParameter3 = new SqlParameter("@p3", org.create_time); SqlParameter sqlParameter4 = new SqlParameter("@p4", org.modify_time); res = SqlHelper.ExecuteNonQuery(ConStr, CommandType.Text, sql, sqlParameter1, sqlParameter2, sqlParameter3, sqlParameter4); new LogUserDAL().Add(LogOperations.LogUser("添加组织机构")); return(res); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys(e.Message)); return(0); } }
/// <summary> /// 人员信息删除 /// </summary> /// <param name="id">人员信息id</param> /// <returns>删除条数</returns> public int Del(int id) { try { // sql语句 string sql = "delete from person_basic where id = @id"; // 参数赋值 SqlParameter sp_id = new SqlParameter("@id", id); // 执行sql语句 res = SqlHelper.ExecuteNonQuery(DALBase.ConStr, CommandType.Text, sql, sp_id); // 添加用户日志 new LogUserDAL().Add(LogOperations.LogUser("人员信息删除")); } catch (Exception e) { // 添加系统日志 new LogSysDAL().Add(LogOperations.LogSys("人员信息删除:" + e.Message)); //return 0; } // 返回成功条数 return(res); }
/// <summary> /// 登陆时,通过用户名查询用户信息 /// </summary> /// <param name="UserName">用户名</param> /// <returns>用户信息</returns> public string SelectLogin(string UserName) { try { sys_user user = new sys_user(); string sql = "select id,username,password from sys_user where username='******'"; DataSet ds = SqlHelper.ExecuteDataset(ConStr, CommandType.Text, sql); user.password = (string)ds.Tables[0].Rows[0][nameof(sys_user.password)]; string username = (string)ds.Tables[0].Rows[0][nameof(sys_user.username)]; int id = (int)ds.Tables[0].Rows[0][nameof(sys_user.id)]; //保存用户信息 UserInfoDAL.UserId = id; UserInfoDAL.UserName = username; new LogUserDAL().Add(LogOperations.LogUser("登录")); return(user.password); } catch (Exception e) { new LogSysDAL().Add(LogOperations.LogSys(e.Message)); return(null); } }
/// <summary> /// 人员信息检索 /// </summary> /// <param name="info">查询条件</param> /// <returns>List类型</returns> public List <person_basic> Query(person_basic info) { // 用于返回的列表 List <person_basic> list = new List <person_basic>(); if (info.Equals(null)) { return(list); } try { // sql语句 string sql = "select * from person_basic where isdel = " + info.isdel + " and user_id = " + info.user_id; // 用于拼接查询 List <SqlParameter> sqlPara = new List <SqlParameter>(); // 判断参数 if (info.id != 0) // id { sql += " and id = " + info.id; } if (!string.IsNullOrEmpty(info.name)) // name { sql += " and name like @name"; sqlPara.Add(new SqlParameter("@name", "%" + info.name + "%")); } if (!string.IsNullOrEmpty(info.identity_number)) // identity_number { sql += " and identity_number like @identity_number"; sqlPara.Add(new SqlParameter("@identity_number", "%" + info.identity_number + "%")); } if (info.person_type_id > 0) // person_type_id { sql += " and person_type_id = " + info.person_type_id; } if (!string.IsNullOrEmpty(info.native_place)) // native_place { sql += " and native_place like @native_place"; sqlPara.Add(new SqlParameter("@native_place", "%" + info.native_place + "%")); } DataSet ds = new DataSet(); // 执行sql语句并返回数据集 ds = SqlHelper.ExecuteDataset(DALBase.ConStr, CommandType.Text, sql, sqlPara.ToArray()); // 遍历表中的行 foreach (DataRow dr in ds.Tables[0].Rows) { // 封装实体类 person_basic pb = new person_basic(); pb.id = int.Parse(dr[0].ToString()); pb.name = dr[1].ToString(); pb.former_name = dr[2].ToString(); pb.gender = dr[3].ToString(); pb.identity_number = dr[4].ToString(); pb.birth_date = DateTime.Parse(dr[5].ToString()); pb.native_place = dr[6].ToString(); pb.marry_status = bool.Parse(dr[7].ToString()); pb.job_status = dr[8].ToString(); if (!(dr[9] is DBNull)) { pb.income = decimal.Parse(dr[9].ToString()); } pb.temper = dr[10].ToString(); pb.family = dr[11].ToString(); pb.person_type_id = int.Parse(dr[12].ToString()); pb.qq = dr[13].ToString(); pb.address = dr[14].ToString(); pb.phone = dr[15].ToString(); pb.belong_place_id = int.Parse(dr[16].ToString()); pb.nation = dr[17].ToString(); pb.input_time = DateTime.Parse(dr[18].ToString()); pb.user_id = int.Parse(dr[19].ToString()); pb.isdel = int.Parse(dr[20].ToString()); list.Add(pb); } // 添加用户日志 new LogUserDAL().Add(LogOperations.LogUser("人员信息检索")); } catch (Exception e) { // 添加系统日志 new LogSysDAL().Add(LogOperations.LogSys("人员信息检索:" + e.Message)); } // 返回列表 return(list); }