/// <summary> /// 获取全部角色(绑定下拉框) /// </summary> public DataTable GetAllRoles(string userAccount) { try { if (string.IsNullOrWhiteSpace(userAccount)) { return(GetList("")); } var strSql = "select A.F_ROLECODE, A.F_NAME, DECODE(B.F_ID, NULL, 0, 1) AS \"ISUSERHAS\"" + " from ROLEINFO A" + " LEFT JOIN (SELECT * FROM USERROLE WHERE F_ACCOUNT = :F_ACCOUNT) B" + " ON A.F_ROLECODE = B.F_ROLECODE"; OracleParameter[] parms = { new OracleParameter("F_ACCOUNT", userAccount) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(strSql, parms); dbapi.CloseConn(); return(rst); } catch (Exception ex) { LogBN.WriteLog(typeof(MonitorLog), "获取全部角色(绑定下拉框)方法GetAllRoles 程序段的异常" + ex); return(null); } }
/// <summary> /// 新增角色 /// </summary> /// <param name="roleinfo">角色实体类</param> /// <returns>是否成功</returns> public bool AddRoleMain(ROLEINFO roleinfo) { var strSql = new StringBuilder(); var dbHelper = new DbAPI(); strSql.Append("INSERT INTO ROLEINFO(F_ROLECODE, F_NAME, F_DESCRIPTION)VALUES(SYS_GUID(),:F_NAME,:F_DESCRIPTION)"); OracleParameter[] parms = { new OracleParameter("F_NAME", roleinfo.F_NAME), new OracleParameter("F_DESCRIPTION", roleinfo.F_DESCRIPTION ?? "") }; try { dbHelper.OpenConn(""); var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms); dbHelper.CloseConn(); return(rstInt > 0); } catch (Exception ex) { LogBN.WriteLog(typeof(ROLEINFO_BN), "新增角色AddRoleMain 程序段的异常" + ex); return(false); } }
/// <summary> /// 修改角色 /// </summary> /// <param name="roleinfo">角色实体类</param> /// <returns>是否成功</returns> public bool EditRoleMain(ROLEINFO roleinfo) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("UPDATE ROLEINFO SET F_NAME=:F_NAME,F_DESCRIPTION=:F_DESCRIPTION WHERE F_ROLECODE=:F_ROLECODE"); OracleParameter[] parms = { new OracleParameter("F_NAME", roleinfo.F_NAME), new OracleParameter("F_DESCRIPTION", roleinfo.F_DESCRIPTION), new OracleParameter("F_ROLECODE", roleinfo.F_ROLECODE) }; try { dbHelper.OpenConn(""); var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms); dbHelper.CloseConn(); return(rstInt > 0); } catch (Exception ex) { LogBN.WriteLog(typeof(ROLEINFO_BN), "修改角色EditRoleMain 程序段的异常" + ex); return(false); } }
/// <summary> /// 得到一个对象实体 /// </summary> public Entity.ROLEINFO GetModel(string F_ROLECODE) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("select F_ROLECODE, F_NAME, F_DESCRIPTION "); strSql.Append(" from ROLEINFO "); strSql.Append(" where F_ROLECODE=:F_ROLECODE "); OracleParameter[] parameters = { new OracleParameter(":F_ROLECODE", OracleType.VarChar, 36) }; parameters[0].Value = F_ROLECODE; Entity.ROLEINFO model = new Entity.ROLEINFO(); dbHelper.OpenConn(""); DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); if (ds.Rows.Count > 0) { model.F_ROLECODE = ds.Rows[0]["F_ROLECODE"].ToString(); model.F_NAME = ds.Rows[0]["F_NAME"].ToString(); model.F_DESCRIPTION = ds.Rows[0]["F_DESCRIPTION"].ToString(); return(model); } else { return(null); } }
public string GetMapEqipment(string strWhere) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); OracleParameter[] parameters = null; strSql.Append("select * "); strSql.Append(" FROM DEVICEFULL "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } try { dbHelper.OpenConn(""); DataTable dt = new DataTable(); dt = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); string result = JsonConvert.SerializeObject(dt, new DataTableConverter()); return(result); } catch (Exception ex) { LogBN.WriteLog(typeof(DEVICEATTACH_BN), "GetMapEqipment 程序段的异常" + ex); return(""); } }
/// <summary> /// 根据部门ID获取所有的用户名 /// </summary> /// <param name="code"></param> /// <returns></returns> public DataTable GetNameList(string code) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); OracleParameter[] parameters = null; strSql.Append(" SELECT U.F_ACCOUNT,U.F_NAME FROM DEPARTMENTUSER D "); strSql.Append(" LEFT JOIN USERINFO U ON D.F_ACCOUNT=U.F_ACCOUNT "); strSql.Append(" WHERE 1=1 "); if (!string.IsNullOrEmpty(code)) { strSql.Append(" AND F_DEPARTMENTCODE= " + code); } try { dbHelper.OpenConn(""); DataTable dt = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); return(dt); } catch (Exception ex) { LogBN.WriteLog(typeof(DEPARTMENTUSER_BN), "GetNameList 程序段的异常" + ex); return(null); } }
/// <summary> /// (分页查询)从数据库中获取某个时间段的监控历史记录列表 /// </summary> /// <param name="tableName">表名</param> /// <param name="beginTime">开始时间</param> /// <param name="endTime">结束时间</param> /// <param name="strWhere">其他查询条件</param> /// <param name="pageNumber">页码</param> /// <param name="pageSize">每页记录数</param> /// <returns></returns> private DataTable GetList(string tableName, string beginTime, string endTime, string strWhere, int pageNumber, int pageSize) { var strSql = new StringBuilder(); strSql.Append("select d.devicename, t.* "); strSql.AppendFormat("from {0} t ", tableName); strSql.Append("left join deviceinfo d "); strSql.Append("on t.devicecode = d.devicecode "); strSql.Append("where t.DATETIME between "); strSql.Append("to_date(:beginTime, 'YYYY-MM-DD HH24:MI:SS') and "); strSql.Append("to_date(:endTime, 'YYYY-MM-DD HH24:MI:SS') "); if (!string.IsNullOrWhiteSpace(strWhere)) { strSql.AppendFormat("and {0} ", strWhere); } strSql.Append("order by t.DATETIME desc"); OracleParameter[] parameters = { new OracleParameter(":beginTime", beginTime), new OracleParameter(":endTime", endTime) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters); dbapi.CloseConn(); return(rst); }
/// <summary> /// 修改部门 /// </summary> /// <param name="deptName">部门名称</param> /// <param name="id">部门ID</param> /// <returns>是否成功的JSON结果</returns> public bool UpdateDepartment(string deptName, string id) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("update departmentinfo set f_name=:deptName where f_departmentcode=:id"); OracleParameter[] parms = { new OracleParameter("deptName", deptName), new OracleParameter("id", id) }; try { dbHelper.OpenConn(""); var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms); dbHelper.CloseConn(); return(rstInt > 0); } catch (Exception ex) { LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "修改部门AddDepartment 程序段的异常" + ex); return(false); } }
/// <summary> /// 新增部门 /// </summary> /// <param name="deptName">部门名称</param> /// <param name="pid">父节点ID</param> public bool AddDepartment(string deptName, string pid) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("insert into departmentinfo(f_departmentcode, f_name, f_description, f_parent)values((SELECT TO_CHAR(MAX(TO_NUMBER(f_departmentcode))+1) FROM departmentinfo),:deptName,'',:pid)"); OracleParameter[] parms = { new OracleParameter("deptName", deptName), new OracleParameter("pid", pid) }; try { dbHelper.OpenConn(""); var rstInt = dbHelper.ExecuteNonQuery(strSql.ToString(), parms); dbHelper.CloseConn(); return(rstInt > 0); } catch (Exception ex) { LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "新增部门AddDepartment 程序段的异常" + ex); return(false); } }
/// <summary> /// (分页查询)从数据库中获取角色列表 /// </summary> /// <param name="queryModel">查询条件</param> /// <returns>返回角色列表</returns> public DataTable GetRoleList(Entity.System.view.QueryModel queryModel) { try { var roleName = queryModel.roleName ?? ""; var pageNumber = queryModel.offset / queryModel.limit + 1; var pageSize = queryModel.limit; var strSql = new StringBuilder(); strSql.Append("select t.*,COUNT(*) OVER () RESULT_COUNT from ROLEINFO t where t.f_name like '%'||:roleName||'%'"); OracleParameter[] parameters = { new OracleParameter(":roleName", roleName) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), parameters); dbapi.CloseConn(); return(rst); } catch (Exception ex) { LogBN.WriteLog(typeof(USERROLE_BN), "(分页查询)从数据库中获取角色列表GetRoleList 程序段的异常" + ex); return(null); } }
/// <summary> /// 验证用户名是否存在 /// </summary> /// <param name="userName">用户名</param> /// <returns>存在返回false,不存在返回true</returns> public bool ValidateUsername(string userName) { var strSql = new StringBuilder(); strSql.Append("SELECT COUNT(1) FROM USERINFO WHERE UPPER(F_NAME) = UPPER(:F_NAME)"); OracleParameter[] parms = { new OracleParameter("F_NAME", userName) }; var dbHelper = new DbAPI(); try { dbHelper.OpenConn(""); var rstInt = dbHelper.ExecuteOracleScalar(strSql.ToString(), parms); dbHelper.CloseConn(); return(int.Parse(rstInt.ToString()) <= 0); } catch (Exception ex) { LogBN.WriteLog(typeof(ROLEINFO_BN), "验证用户名是否存在ValidateUsername 程序段的异常" + ex); return(false); } }
/// <summary> /// 获取实时数据列表 /// </summary> /// <param name="device"></param> /// <returns></returns> public int GetDeviceInfoCount(DEVICEINFO device) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT COUNT(1) "); strSql.Append(" FROM DEVICEINFO DI "); strSql.Append(" LEFT JOIN DEVICEATTACH DA ON DI.DEVICECODE=DA.DEVICECODE "); strSql.Append(" WHERE 1=1 "); List <OracleParameter> list = new List <OracleParameter>(); // 设备类型(岸基站、浮标) if (device.DEVICETYPE > 0) { strSql.Append(" AND DEVICETYPE=:DEVICETYPE "); list.Add(new OracleParameter(":DEVICETYPE", device.DEVICETYPE)); } if (!string.IsNullOrEmpty(device.SEAAREA)) { strSql.Append(" AND SEAAREA=:SEAAREA "); list.Add(new OracleParameter(":SEAAREA", device.SEAAREA)); } if (!string.IsNullOrEmpty(device.PROVINCE)) { strSql.Append(" AND PROVINCE=:PROVINCE "); list.Add(new OracleParameter(":PROVINCE", device.PROVINCE)); } if (!string.IsNullOrEmpty(device.BAY)) { strSql.Append(" AND BAY=:BAY "); list.Add(new OracleParameter(":BAY", device.BAY)); } if (!string.IsNullOrEmpty(device.BUREAUDEVICE)) { strSql.Append(" AND BUREAUDEVICE=:BUREAUDEVICE "); list.Add(new OracleParameter(":BUREAUDEVICE", device.BUREAUDEVICE)); } if (!string.IsNullOrEmpty(device.LOCALDEVICE)) { strSql.Append(" AND LOCALDEVICE=:LOCALDEVICE "); list.Add(new OracleParameter(":LOCALDEVICE", device.LOCALDEVICE)); } if (!string.IsNullOrEmpty(device.SERVICE)) { strSql.Append(" AND SERVICE=:SERVICE "); list.Add(new OracleParameter(":SERVICE", device.SERVICE)); } DbAPI dbHelper = new DbAPI(); dbHelper.OpenConn(""); object result = dbHelper.ExecuteOracleScalar(strSql.ToString(), list.ToArray()); dbHelper.CloseConn(); return(Convert.ToInt32(result.ToString())); }
/// <summary> /// 删除一条数据 /// </summary> public ReturnResult DeleteRole(string F_ROLECODE) { ReturnResult result = new ReturnResult(); StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); try { dbHelper.OpenConn(""); dbHelper.OpenTrans(); strSql.Append("delete from ROLEINFO "); strSql.Append(" where F_ROLECODE=:F_ROLECODE "); OracleParameter[] parameters = { new OracleParameter(":F_ROLECODE", dbHelper.CheckDBNull(F_ROLECODE)) }; int rows = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters); if (rows > 0) { strSql.Clear(); strSql.Append("delete from ROLEFUNCTION "); strSql.Append(" where F_ROLECODE=:F_ROLECODE "); OracleParameter[] parameters1 = { new OracleParameter(":F_ROLECODE", dbHelper.CheckDBNull(F_ROLECODE)) }; int rows1 = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters1); if (rows1 <= 0) { goto Over; } result.success = true; result.Message = "操作成功"; dbHelper.CommitTrans(); dbHelper.CloseConn(); return(result); } else { goto Over; } } catch (Exception ex) { LogBN.WriteLog(typeof(ROLEINFO_BN), "DeleteRole 程序段的异常" + ex); goto Over; } Over: result.success = false; result.Message = "操作失败"; dbHelper.RollBack(); dbHelper.CloseConn(); return(result); }
/// <summary> /// 根据user account得到一个对象实体 /// </summary> public Entity.USERINFO GetUserinforModel(string account) { var strSql = new StringBuilder(); var dbHelper = new DbAPI(); strSql.Append("select a.F_ACCOUNT,a.F_NAME,a.F_PASSWORD,a.F_EMAIL,a.F_PHONE,a.F_TEL,a.F_DESCRIPTION,a.F_ADDRESS,a.F_REALNAME,d.f_name as F_DEPARTMENTCODE,C.Roleinfos"); strSql.Append(" from USERINFO a"); strSql.Append(" left join departmentuser b"); strSql.Append(" on a.f_account = b.f_account"); strSql.Append(" left join departmentinfo d on b.f_departmentcode = d.f_departmentcode"); strSql.Append(" LEFT JOIN (SELECT A.F_ACCOUNT,"); strSql.Append(" LISTAGG(TO_CHAR(B.F_NAME), ',') WITHIN GROUP(ORDER BY A.F_ACCOUNT) AS Roleinfos"); strSql.Append(" FROM USERROLE A"); strSql.Append(" LEFT JOIN ROLEINFO B"); strSql.Append(" ON A.F_ROLECODE = B.F_ROLECODE"); strSql.Append(" GROUP BY A.F_ACCOUNT) C"); strSql.Append(" ON A.F_ACCOUNT = C.F_ACCOUNT"); strSql.Append(" where a.F_ACCOUNT = :F_ACCOUNT and rownum = 1"); OracleParameter[] parameters = { new OracleParameter(":F_ACCOUNT", account) }; dbHelper.OpenConn(""); var ds = dbHelper.GetDataTable(strSql.ToString(), parameters); if (ds == null || ds.Rows.Count <= 0) { return(null); } var row0 = ds.Rows[0]; var model = new Entity.USERINFO { F_ACCOUNT = row0["F_ACCOUNT"].ToString(), F_NAME = row0["F_NAME"].ToString(), F_PASSWORD = row0["F_PASSWORD"].ToString(), F_EMAIL = row0["F_EMAIL"].ToString(), F_PHONE = row0["F_PHONE"].ToString(), F_TEL = row0["F_TEL"].ToString(), F_DESCRIPTION = row0["F_DESCRIPTION"].ToString(), F_ADDRESS = row0["F_ADDRESS"].ToString(), F_REALNAME = row0["F_REALNAME"].ToString(), DepartmentNames = row0["F_DEPARTMENTCODE"].ToString(), RoleNames = row0["Roleinfos"].ToString() }; dbHelper.CloseConn(); return(model); }
/// <summary> /// 增加一条数据 /// </summary> public bool Add(Entity.SYSTEMLOG model) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("insert into SYSTEMLOG("); strSql.Append("LOG_ID,LOGNAME,TYPE,CONTENT,F_ACCOUNT,REMARK,DATETIME"); strSql.Append(") values ("); strSql.Append(":LOG_ID,:LOGNAME,:TYPE,:CONTENT,:F_ACCOUNT,:REMARK,:DATETIME"); strSql.Append(") "); OracleParameter[] parameters = { new OracleParameter(":LOG_ID", OracleType.VarChar, 36), new OracleParameter(":LOGNAME", OracleType.VarChar, 256), new OracleParameter(":TYPE", OracleType.VarChar, 36), new OracleParameter(":CONTENT", OracleType.VarChar, 1000), new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36), new OracleParameter(":REMARK", OracleType.VarChar, 128), new OracleParameter(":DATETIME", OracleType.DateTime) }; parameters[0].Value = dbHelper.GUID(); parameters[1].Value = model.LOGNAME; parameters[2].Value = model.TYPE; parameters[3].Value = model.CONTENT; parameters[4].Value = model.F_ACCOUNT; parameters[5].Value = model.REMARK; parameters[6].Value = model.DATETIME; try { dbHelper.OpenConn(""); int result = dbHelper.ExecuteNonQuery(strSql.ToString(), parameters); dbHelper.CloseConn(); if (result > 0) { return(true); } else { return(false); } } catch (Exception ex) { LogBN.WriteLog(typeof(SYSTEMLOG_BN), "Add 程序段的异常" + ex); return(false); } }
/// <summary> /// 获取所有的数据 /// </summary> /// <returns></returns> public DataTable GetAll() { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("select * "); strSql.Append(" FROM DEPARTMENTINFO"); dbHelper.OpenConn(""); DataTable bt = dbHelper.GetDataTable(strSql.ToString(), null); dbHelper.CloseConn(); return(bt); }
public string SearchEqipment(DEVICEATTACH model) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); OracleParameter[] parameters = null; strSql.Append("select * "); strSql.Append(" FROM DEVICEFULL WHERE 1=1 "); if (model.SEAAREA != "全部") { strSql.Append(" AND SEAAREA = '" + model.SEAAREA + "'"); } if (model.PROVINCE != "全部") { strSql.Append(" AND PROVINCE LIKE '%" + dbHelper.CheckDBNull(model.PROVINCE).ToString() + "%'"); } if (model.BAY != "全部") { strSql.Append(" AND BAY = '" + model.BAY + "'"); } if (model.BUREAUDEVICE != "全部") { strSql.Append(" AND BUREAUDEVICE ='" + model.BUREAUDEVICE + "'"); } if (model.LOCALDEVICE != "全部") { strSql.Append(" AND LOCALDEVICE = '" + model.LOCALDEVICE + "'"); } if (model.SERVICE != "全部") { strSql.Append(" AND SERVICE = '" + model.SERVICE + "'"); } try { dbHelper.OpenConn(""); DataTable dt = new DataTable(); dt = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); string result = JsonConvert.SerializeObject(dt, new DataTableConverter()); return(result); } catch (Exception ex) { LogBN.WriteLog(typeof(DEVICEATTACH_BN), "SearchEqipment 程序段的异常" + ex); return(""); } }
/// <summary> /// 获得数据列表 /// </summary> public DataTablesResult GetList(string logType, string startDate, string endDate, int pageNumber, int pageSize) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); List <OracleParameter> listParams = new List <OracleParameter>(); strSql.Append(" SELECT LOG_ID,LOGNAME,\"TYPE\",\"CONTENT\",REMARK,DATETIME,US.F_NAME FROM SYSTEMLOG S "); strSql.Append(" LEFT JOIN USERINFO US "); strSql.Append(" ON S.F_ACCOUNT=US.F_ACCOUNT "); strSql.Append(" WHERE 1=1 "); if (!string.IsNullOrEmpty(logType)) { strSql.Append(" AND \"TYPE\" LIKE '%").Append(logType).Append("%' "); //listParams.Add(new OracleParameter(":logType", logType)); } if (!string.IsNullOrEmpty(startDate) && !string.IsNullOrEmpty(endDate)) { strSql.Append(" AND DATETIME BETWEEN TO_DATE(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE(:endDate, 'YYYY-MM-DD HH24:MI:SS') "); listParams.Add(new OracleParameter("startDate", startDate)); listParams.Add(new OracleParameter("endDate", endDate + " 23:59:59")); } strSql.Append(" ORDER BY DATETIME DESC"); try { dbHelper.OpenConn(""); DataTable dt = dbHelper.GetDataTable(DbAPI.GeneratePagingSql(strSql.ToString(), pageNumber, pageSize), listParams.ToArray()); dbHelper.CloseConn(); DataTablesResult result = new DataTablesResult(); result.aaData = dt; result.sEcho = Convert.ToInt16(pageSize); result.iTotalDisplayRecords = GetCount(logType, startDate, endDate, pageNumber, pageSize); result.iTotalRecords = GetCount(logType, startDate, endDate, pageNumber, pageSize).ToString(); return(result); } catch (Exception ex) { LogBN.WriteLog(typeof(SYSTEMLOG_BN), "GetList 程序段的异常" + ex); return(null); } }
/// <summary> /// 获取监测要素列表 /// </summary> /// <returns></returns> public DataTable GetJcysList() { try { var strSql = new StringBuilder(); strSql.Append("select * from TABSTD where std_type is not null order by std_id asc"); var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(strSql.ToString(), null); dbapi.CloseConn(); return(rst); } catch (Exception ex) { LogBN.WriteLog(typeof(MonitorLog), "获取监测要素列表GetJcysList 程序段的异常" + ex); return(null); } }
/// <summary> /// 获得数据列表 /// </summary> public DataTable GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); OracleParameter[] parameters = null; strSql.Append("select * "); strSql.Append(" FROM ROLEINFO "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(strSql.ToString(), parameters); dbapi.CloseConn(); return(rst); }
/// <summary> /// 根据用户名、密码查询用户是否存在 /// </summary> /// <param name="user"></param> /// <returns></returns> public DataTable GetUserList(Entity.USERINFO user) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT F_ACCOUNT,F_NAME,F_REALNAME FROM USERINFO WHERE 1=1 AND F_NAME=:F_NAME AND F_PASSWORD=:F_PASSWORD "); DbAPI dbHelper = new DbAPI(); List <OracleParameter> list = new List <OracleParameter>(); list.Add(new OracleParameter(":F_NAME", user.F_NAME)); list.Add(new OracleParameter(":F_PASSWORD", user.F_PASSWORD)); dbHelper.OpenConn(""); DataTable dt = dbHelper.GetDataTable(strSql.ToString(), list.ToArray()); dbHelper.CloseConn(); return(dt); }
/// <summary> /// 得到一个对象实体 /// </summary> public Entity.USERINFO GetModel(string F_ACCOUNT) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("select F_ACCOUNT, F_NAME, F_PASSWORD, F_EMAIL, F_PHONE, F_TEL, F_DESCRIPTION, F_PHOTO, F_ADDRESS, F_REALNAME "); strSql.Append(" from USERINFO "); strSql.Append(" where F_ACCOUNT=:F_ACCOUNT "); OracleParameter[] parameters = { new OracleParameter(":F_ACCOUNT", OracleType.VarChar, 36) }; parameters[0].Value = F_ACCOUNT; dbHelper.OpenConn(""); Entity.USERINFO model = new Entity.USERINFO(); DataTable ds = dbHelper.GetDataTable(strSql.ToString(), parameters); if (ds.Rows.Count > 0) { model.F_ACCOUNT = ds.Rows[0]["F_ACCOUNT"].ToString(); model.F_NAME = ds.Rows[0]["F_NAME"].ToString(); model.F_PASSWORD = ds.Rows[0]["F_PASSWORD"].ToString(); model.F_EMAIL = ds.Rows[0]["F_EMAIL"].ToString(); model.F_PHONE = ds.Rows[0]["F_PHONE"].ToString(); model.F_TEL = ds.Rows[0]["F_TEL"].ToString(); model.F_DESCRIPTION = ds.Rows[0]["F_DESCRIPTION"].ToString(); if (ds.Rows[0]["F_PHOTO"].ToString() != "") { model.F_PHOTO = (byte[])ds.Rows[0]["F_PHOTO"]; } model.F_ADDRESS = ds.Rows[0]["F_ADDRESS"].ToString(); model.F_REALNAME = ds.Rows[0]["F_REALNAME"].ToString(); dbHelper.CloseConn(); return(model); } else { return(null); } }
/// <summary> /// 获得数据列表 /// </summary> public DataTable GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); OracleParameter[] parameters = null; strSql.Append("select t.*,0 AS \"ISUSERIN\" "); strSql.Append(" FROM DEPARTMENTINFO t"); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } strSql.Append(" ORDER BY t.f_departmentcode ASC"); dbHelper.OpenConn(""); var rst = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); return(rst); }
/// <summary> /// 获取所有的数据 /// </summary> /// <returns></returns> public DataTable GetAll() { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); strSql.Append("select * "); strSql.Append(" FROM DEPARTMENTINFO"); dbHelper.OpenConn(""); try { DataTable bt = dbHelper.GetDataTable(strSql.ToString(), null); dbHelper.CloseConn(); return(bt); } catch (Exception ex) { LogBN.WriteLog(typeof(DEPARTMENTINFO_BN), "GetAll 程序段的异常" + ex); return(null); } }
/// <summary> /// 获取当前设备信息 /// </summary> /// <param name="device"></param> /// <returns></returns> public DataTablesResult getDeviceRow(string deviceCode) { StringBuilder strSql = new StringBuilder(); strSql.Append(" SELECT * "); strSql.Append(" FROM DEVICEINFO "); strSql.Append(" WHERE 1=1 "); List <OracleParameter> list = new List <OracleParameter>(); // 编号 if (!string.IsNullOrEmpty(deviceCode)) { strSql.Append(" AND DEVICECODE=:DEVICECODE "); list.Add(new OracleParameter(":DEVICECODE", deviceCode)); } // strSql.Append(" )INFO "); try { DbAPI dbHelper = new DbAPI(); DataTablesResult result = new DataTablesResult(); dbHelper.OpenConn(""); DataTable bt = dbHelper.GetDataTable(strSql.ToString(), list.ToArray()); result.aaData = bt; dbHelper.CloseConn(); return(result); } catch (Exception ex) { LogBN.WriteLog(typeof(DEVICEINFO_BN), "GetDeviceInfo 程序段的异常" + ex); return(null); } }
/// <summary> /// 删除角色 /// </summary> /// <param name="id">角色ID</param> /// <returns>是否成功的JSON结果</returns> public bool DeleteRole(string id) { try { OracleParameter[] parameters = { new OracleParameter("p_roleCode", id) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.ExecuteNonQueryByProcedure("DeleteRole", parameters); dbapi.CloseConn(); return(rst > 0); } catch (Exception ex) { LogBN.WriteLog(typeof(MonitorLog), "删除部门AddDepartment 程序段的异常" + ex); return(false); } }
/// <summary> /// 获得数据列表(with user) /// </summary> public DataTable GetAllDeptsByUser(string userAccount) { var strSql = new StringBuilder(); var dbHelper = new DbAPI(); OracleParameter[] parms = { new OracleParameter("F_ACCOUNT", userAccount) }; strSql.Append("SELECT A.*, DECODE(B.F_ID, NULL, 0, 1) AS \"ISUSERIN\""); strSql.Append(" FROM DEPARTMENTINFO A"); strSql.Append(" LEFT JOIN (SELECT * FROM DEPARTMENTUSER WHERE F_ACCOUNT = :F_ACCOUNT) B"); strSql.Append(" ON A.F_DEPARTMENTCODE = B.F_DEPARTMENTCODE"); strSql.Append(" ORDER BY A.f_departmentcode ASC"); dbHelper.OpenConn(""); var rst = dbHelper.GetDataTable(strSql.ToString(), parms); dbHelper.CloseConn(); return(rst); }
/// <summary> /// 获得数据列表 /// </summary> public DataTable GetList() { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); OracleParameter[] parameters = null; strSql.Append(" SELECT * FROM TABDICTIONARY WHERE DICTIONARYID LIKE '9%' "); try { dbHelper.OpenConn(""); DataTable dt = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); return(dt); } catch (Exception ex) { LogBN.WriteLog(typeof(DICTIONARY_BN), "GetList 程序段的异常" + ex); return(null); } }
/// <summary> /// 导出角色列表到Excel /// </summary> /// <param name="queryModel">查询参数</param> public DataTable GetExcelList(Entity.System.view.QueryModel queryModel) { try { var roleName = string.IsNullOrWhiteSpace(queryModel.roleName) ? "" : queryModel.roleName; var strSql = "select f_rolecode \"编号\",f_name \"名称\",f_description \"备注\" from ROLEINFO where f_name like '%' || :roleName || '%'"; OracleParameter[] parameters = { new OracleParameter(":roleName", roleName) }; var dbapi = new DbAPI(); dbapi.OpenConn(""); var rst = dbapi.GetDataTable(strSql, parameters); dbapi.CloseConn(); return(rst); } catch (Exception ex) { LogBN.WriteLog(typeof(MonitorLog), "导出角色列表到Excel方法GetExcelList 程序段的异常" + ex); return(null); } }
/// <summary> /// 获得数据列表 /// </summary> public DataTable GetList(string strWhere) { StringBuilder strSql = new StringBuilder(); DbAPI dbHelper = new DbAPI(); OracleParameter[] parameters = null; strSql.Append("select * "); strSql.Append(" FROM FUNCTIONINFO "); if (strWhere.Trim() != "") { strSql.Append(" where " + strWhere); } try { dbHelper.OpenConn(""); DataTable dt = new DataTable(); dt = dbHelper.GetDataTable(strSql.ToString(), parameters); dbHelper.CloseConn(); return(dt); }catch (Exception ex) { LogBN.WriteLog(typeof(FUNCTIONINFO_BN), "GetDataTable 程序段的异常" + ex); return(null); } }