/// <summary> /// 根据用户所属的角色ID和每页显示多少条数据返回用户信息 /// 创建:李东峰 日期:2014-03-10 /// </summary> /// <param name="_grpId">用户所属的角色ID</param> /// <param name="sCount">起始条数</param> /// <param name="eCount">结束条数</param> /// <returns>返回查询结果集</returns> public DataTable GetUserByRoleId(int sCount, int eCount) { int t1 = eCount - sCount + 1; int t2 = sCount - 1; string rlDBType = dl.init(); string sqlSQL = "select top " + t1 + " * from (select ID_KEY,T_USERID,T_USERNAME from(select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID)as a where a.T_GRPID='" + RoleId + "')as b where (b.ID_KEY not in ( select top " + t2 + " ID_KEY from(select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID)as a where a.T_GRPID='" + RoleId + "'))"; string sqlDB2 = "select * from ( select a.ID_KEY,a.T_USERID,a.T_USERNAME,rownumber() over(order by ID_KEY asc ) as rowid from (select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID ORDER BY T_SYS_MEMBERINFO.ID_KEY)as a where a.T_GRPID='" + RoleId + "') as b where b.rowid between " + sCount + " and " + eCount + ""; string sqlORC = "select * from(select ID_KEY,T_USERID,T_USERNAME,ROWNUM rn from(select T_SYS_MEMBERINFO.ID_KEY,T_SYS_MEMBERINFO.T_USERID,T_SYS_MEMBERINFO.T_USERNAME,T_SYS_MEMBERGRP.T_GRPID from T_SYS_MEMBERINFO left JOIN T_SYS_MEMBERGRP ON T_SYS_MEMBERGRP.T_USERID=T_SYS_MEMBERINFO.T_USERID ORDER BY T_SYS_MEMBERINFO.ID_KEY) where T_GRPID='" + RoleId + "' and ROWNUM <= " + eCount + ")WHERE rn >= " + sCount + ""; string errMsg; DataTable _dt = null; if (rlDBType == "SQL") { try { _dt = dl.RunDataTable(sqlSQL, out errMsg); } catch (Exception ex) { LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间:" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息:" + ex.Message); } } else if (rlDBType == "DB2") { try { _dt = dl.RunDataTable(sqlDB2, out errMsg); } catch (Exception ex) { LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间:" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息:" + ex.Message); } } else if (rlDBType == "ORACLE") { try { _dt = dl.RunDataTable(sqlORC, out errMsg); } catch (Exception ex) { LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间:" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息:" + ex.Message); } } return(_dt); }
/// <summary> /// 添加人员 /// 创建:李东峰 日期:2014-03-10 /// </summary> /// <param name="_userName">用户真实姓名</param> /// <param name="_passWord">用户密码</param> /// <param name="_attachMent">用户照片</param> /// <param name="_classId">用户班组</param> /// <param name="_posId">用户岗位</param> /// <param name="_posId">用户所属角色ID</param> /// <returns>返回是否添加成功</returns> public bool AddMember(string _userName, string _passWord, byte[] _attachMent, string _classId, string _posId, string _grpId) { string rlDBType = dl.init(); string _errMsg; string sql1 = ""; string sql2 = ""; bool result = false; if (rlDBType == "SQL") { if (_attachMent != null && _attachMent.Length > 0) { sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,B_ATTACHMENT,T_CLASSID,T_POSID) values(@T_USERID,@T_USERNAME,@T_PASSWD,@B_ATTACHMENT,@T_CLASSID,@T_POSID);"; } else { sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,T_CLASSID,T_POSID) values(@T_USERID,@T_USERNAME,@T_PASSWD,@T_CLASSID,@T_POSID);"; } sql2 = "insert into T_SYS_MEMBERGRP(T_USERID,T_GRPID) values('" + UserId + "','" + _grpId + "')"; try { SqlConnection sqlconn = SAC.DBOperations.DBsql.GetConnection(); SqlCommand sqlcmd = new SqlCommand(sql1, sqlconn); if (_attachMent != null && _attachMent.Length > 0) { sqlcmd.Parameters.Add("@T_USERID", UserId); sqlcmd.Parameters.Add("@T_USERNAME", _userName); sqlcmd.Parameters.Add("@T_PASSWD", _passWord); sqlcmd.Parameters.Add("@B_ATTACHMENT", _attachMent); sqlcmd.Parameters.Add("@T_PASSWD", _classId); sqlcmd.Parameters.Add("@T_POSID", _posId); } else { sqlcmd.Parameters.Add("@T_USERID", UserId); sqlcmd.Parameters.Add("@T_USERNAME", _userName); sqlcmd.Parameters.Add("@T_PASSWD", _passWord); sqlcmd.Parameters.Add("@T_PASSWD", _classId); sqlcmd.Parameters.Add("@T_POSID", _posId); } if (sqlcmd.ExecuteNonQuery() > 0) { result = true; } sqlconn.Close(); dl.RunNonQuery(sql2, out _errMsg); } catch (Exception ex) { LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间:" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息:" + ex.Message); result = false; } } else if (rlDBType == "DB2") { if (_attachMent != null && _attachMent.Length > 0) { sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,B_ATTACHMENT,T_CLASSIS,T_POSID) values(?,?,?,?,?,?);"; } else { sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,T_CLASSIS,T_POSID) values(?,?,?,?,?);"; } sql2 = "insert into T_SYS_MEMBERGRP(T_USERID,T_GRPID) values('" + UserId + "','" + _grpId + "')"; try { OleDbConnection con = new OleDbConnection(SAC.DBOperations.DBdb2.SetConString()); con.Open(); OleDbCommand oledbcom = new OleDbCommand(sql1, con); if (_attachMent != null && _attachMent.Length > 0) { oledbcom.Parameters.Add("?", UserId); oledbcom.Parameters.Add("?", _userName); oledbcom.Parameters.Add("?", _passWord); oledbcom.Parameters.Add("?", _attachMent); oledbcom.Parameters.Add("?", _classId); oledbcom.Parameters.Add("?", _posId); } else { oledbcom.Parameters.Add("?", UserId); oledbcom.Parameters.Add("?", _userName); oledbcom.Parameters.Add("?", _passWord); oledbcom.Parameters.Add("?", _classId); oledbcom.Parameters.Add("?", _posId); } if (oledbcom.ExecuteNonQuery() > 0) { result = true; } con.Close(); dl.RunNonQuery(sql2, out _errMsg); } catch (Exception ex) { LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间:" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息:" + ex.Message); result = false; } } else if (rlDBType == "ORACLE") { if (_attachMent != null && _attachMent.Length > 0) { sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,B_ATTACHMENT,T_CLASSIS,T_POSID) values(:blobtodb,:blobtodb,:blobtodb,:blobtodb,:blobtodb,:blobtodb);"; } else { sql1 = "insert into T_SYS_MEMBERINFO(T_USERID,T_USERNAME,T_PASSWD,T_CLASSIS,T_POSID) values(:blobtodb,:blobtodb,:blobtodb,:blobtodb,:blobtodb);"; } sql2 = "insert into T_SYS_MEMBERGRP(T_USERID,T_GRPID) values('" + UserId + "','" + _grpId + "')"; try { OracleConnection con = new OracleConnection(SAC.DBOperations.OracleHelper.retStr()); con.Open(); OracleCommand oledbcom = new OracleCommand(sql1, con); if (_attachMent != null && _attachMent.Length > 0) { oledbcom.Parameters.Add("blobtodb", UserId); oledbcom.Parameters.Add("blobtodb", _userName); oledbcom.Parameters.Add("blobtodb", _passWord); oledbcom.Parameters.Add("blobtodb", _attachMent); oledbcom.Parameters.Add("blobtodb", _classId); oledbcom.Parameters.Add("blobtodb", _posId); } else { oledbcom.Parameters.Add("blobtodb", UserId); oledbcom.Parameters.Add("blobtodb", _userName); oledbcom.Parameters.Add("blobtodb", _passWord); oledbcom.Parameters.Add("blobtodb", _classId); oledbcom.Parameters.Add("blobtodb", _posId); } if (oledbcom.ExecuteNonQuery() > 0) { result = true; } con.Close(); dl.RunNonQuery(sql2, out _errMsg); } catch (Exception ex) { LogHelper.WriteLog(LogHelper.EnLogType.Run, "发生时间:" + DateTime.Now.ToString("yyyy-MM-dd H:mm:ss") + "/n错误信息:" + ex.Message); result = false; } } return(result); }