From 374ce4ffd0c459bb4067e8d5765f972668aff9b1 Mon Sep 17 00:00:00 2001
From: wzp <2040239371@qq.com>
Date: 星期四, 02 三月 2023 14:56:23 +0800
Subject: [PATCH] 变更oracle访问组件
---
web/Dao/UserDao.cs | 94 +++++++++++++++++++++++++++--------------------
1 files changed, 54 insertions(+), 40 deletions(-)
diff --git a/web/Dao/UserDao.cs b/web/Dao/UserDao.cs
index 8ae5992..5e39672 100644
--- a/web/Dao/UserDao.cs
+++ b/web/Dao/UserDao.cs
@@ -1,7 +1,7 @@
-锘�
+锘�
using Common;
using Model;
-using Oracle.DataAccess.Client;
+using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Configuration;
@@ -14,7 +14,7 @@
//璐﹀彿澶勭悊
public class UserDao : IDisposable
{
- public bool CheckLogin(string account, string password, SysUser user)//鐢ㄦ埛鐧诲綍
+ public bool CheckLogin(string account, string password,SysUser user)//鐢ㄦ埛鐧诲綍
{
string str1 = ConfigurationManager.AppSettings["CopyID"];
string str2 = "my@smgw" + str1.Substring(str1.Length - 6);
@@ -34,12 +34,25 @@
{
List<SysUser> list = new List<SysUser>();
recordCount = 0;
- using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from Sys_User", OracleHelper.Connection))
+ StringBuilder stringBuilder = new StringBuilder();
+ stringBuilder.Append(" FROM ( ");
+ stringBuilder.Append(" SELECT su.*, t.ROLE_NAMES FROM sys_user su ");
+ stringBuilder.Append(" LEFT JOIN ( ");
+ stringBuilder.Append(" select su.user_id, wm_concat( sdd.dict_label) ROLE_NAMES from sys_user su ");
+ stringBuilder.Append(" LEFT JOIN (select DICT_VALUE, DICT_LABEL from sys_dict_data where dict_type='USER_ROLE' ) sdd ");
+ stringBuilder.Append(" on INSTR (user_type, sdd.dict_value) > 0 ");
+ stringBuilder.Append(" GROUP BY su.user_id ");
+ stringBuilder.Append(" ) t on t.user_id = su.user_id ");
+ stringBuilder.Append(" ) ");
+
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count from Sys_User", OracleHelper.Connection))
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select count(*) as count " + stringBuilder.ToString(), OracleHelper.Connection))
{
while (((DbDataReader)reader).Read())
recordCount = this.ReadCount(reader);
}
- using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * from Sys_User").ToString(), OracleHelper.Connection))
+ //using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * from Sys_User").ToString(), OracleHelper.Connection))
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(PubConstant.doOracleSql(pageIndex, pageSize, recordCount, "select * " + stringBuilder.ToString()).ToString(), OracleHelper.Connection))
{
while (((DbDataReader)reader).Read())
{
@@ -68,7 +81,7 @@
return dictionary;
}
- public Dictionary<string, SysUserMenu> LoadUserRoleMenuList(int userID)
+ public Dictionary<string, SysUserMenu> LoadUserRoleMenuList(int userID)
{
StringBuilder stringBuilder = new StringBuilder();
@@ -82,11 +95,11 @@
stringBuilder.Append(" select sm.* from sys_menu sm ");
stringBuilder.Append(" left join ( ");
stringBuilder.Append(" select distinct * from ( ");
- stringBuilder.Append(" select * from sys_role_menu where role_id = " + list[0] + " ");
+ stringBuilder.Append(" select * from sys_role_menu where role_id ='" + list[0] + "' ");
for (int i = 1; i < list.Count; i++)
{
- stringBuilder.Append(" union (select * from sys_role_menu where role_id = " + list[i] + ") ");
+ stringBuilder.Append(" union (select * from sys_role_menu where role_id = '" + list[i] + "') ");
}
stringBuilder.Append(" ) bb ");
@@ -140,40 +153,40 @@
}
//鏂板姞鐧婚檰鏃ュ織
- public List<SysMenu> LoadSysMenuIDList(int menuid, int menulv)
- {
- List<SysMenu> list = new List<SysMenu>();
- using (OracleDataReader reader = OracleHelper.ExecuteReader("select * from SYS_MENU WHERE MENU_LV=1 ORDER BY MENU_ID", OracleHelper.Connection))
- {
- while (reader != null)
+ public List<SysMenu> LoadSysMenuIDList(int menuid, int menulv)
{
- if (((DbDataReader) reader).Read())
- list.Add(this.ReadSysMenu(reader));
- else
- break;
+ List<SysMenu> list = new List<SysMenu>();
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select * from SYS_MENU WHERE MENU_LV=1 ORDER BY MENU_ID", OracleHelper.Connection))
+ {
+ while (reader != null)
+ {
+ if (((DbDataReader)reader).Read())
+ list.Add(this.ReadSysMenu(reader));
+ else
+ break;
+ }
+ }
+ return list;
}
- }
- return list;
- }
- public SysMenu GetMenuID(int refid)
- {
- SysMenu sysMenu = new SysMenu();
- using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_MENU WHERE MENU_ID=:MENU_ID"), OracleHelper.Connection, new OracleParameter(":MENU_ID", (object)refid)))
- {
- if (((DbDataReader)reader).Read())
- {
- sysMenu = this.ReadSysMenu(reader);
- return sysMenu;
- }
- }
- return sysMenu;
- }
+ public SysMenu GetMenuID(int refid)
+ {
+ SysMenu sysMenu = new SysMenu();
+ using (OracleDataReader reader = OracleHelper.ExecuteReader(string.Format("select * from SYS_MENU WHERE MENU_ID=:MENU_ID"), OracleHelper.Connection, new OracleParameter(":MENU_ID", (object)refid)))
+ {
+ if (((DbDataReader)reader).Read())
+ {
+ sysMenu = this.ReadSysMenu(reader);
+ return sysMenu;
+ }
+ }
+ return sysMenu;
+ }
-//
+ //
private SysMenu ReadSysMenu(OracleDataReader reader)
{
@@ -209,6 +222,7 @@
e.IsVerification = oracleReaderWrapper.GetInt("ISVERIFICATION", 0);
e.ClientIp = oracleReaderWrapper.GetString("CLIENTIP", "");
e.Status = oracleReaderWrapper.GetInt("STATUS", 0);
+ e.RoleNames = oracleReaderWrapper.GetString("Role_Names","");
return true;
}
@@ -283,14 +297,14 @@
{
//return Convert.ToInt32(OracleHelper.ExecuteScalar("select COUNT(*) from SYS_USER WHERE USER_NAME=:USER_NAME AND USER_ID!=:USER_ID", OracleHelper.Connection, new OracleParameter(":USER_NAME", (object)userName), new OracleParameter(":USER_ID", (object)userId))) > 0;
- using (OracleDataReader reader = OracleHelper.ExecuteReader("select * from SYS_USER WHERE USER_NAME=:USER_NAME ", OracleHelper.Connection, new OracleParameter(":USER_NAME", (object)userName) ))
+ using (OracleDataReader reader = OracleHelper.ExecuteReader("select * from SYS_USER WHERE USER_NAME=:USER_NAME ", OracleHelper.Connection, new OracleParameter(":USER_NAME", (object)userName)))
{
while (((DbDataReader)reader).Read())
{
SysUser e = new SysUser();
if (this.ReadInfo(reader, e))
{
- if(e.UserID!= userId)
+ if (e.UserID != userId)
{
return true;
}
@@ -303,7 +317,7 @@
public bool Update(SysUser o)//淇敼璐﹀彿
{
- return OracleHelper.ExecuteSql("update SYS_USER SET ACCOUNT=:ACCOUNT, USER_NAME=:USER_NAME,USER_TYPE=:USER_TYPE,EXPIRE_TIME=:EXPIRE_TIME,REMARK=:REMARK,MOBILE=:MOBILE,EMAIL=:EMAIL,MOBILE_FLAG=:MOBILE_FLAG,EMAIL_FLAG=:EMAIL_FLAG,ISENCRYPTION=:ISENCRYPTION,ISVERIFICATION=:ISVERIFICATION,CLIENTIP=:CLIENTIP,CREATE_TIME=:CREATE_TIME WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)o.Account), new OracleParameter(":USER_NAME", (object)o.UserName), new OracleParameter(":USER_TYPE", (object)o.UserType), new OracleParameter(":EXPIRE_TIME", (object)o.ExpireTime), new OracleParameter(":REMARK", (object)o.Remark), new OracleParameter(":USER_ID", (object)o.UserID), new OracleParameter(":MOBILE", (object)o.Mobile), new OracleParameter(":EMAIL", (object)o.Email), new OracleParameter(":MOBILE_FLAG", (object)o.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)o.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)o.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)o.IsVerification), new OracleParameter(":CLIENTIP", (object)o.ClientIp), new OracleParameter(":CREATE_TIME", (object)o.CreateTime) ) > 0;
+ return OracleHelper.ExecuteSql("update SYS_USER SET ACCOUNT=:ACCOUNT, USER_NAME=:USER_NAME,USER_TYPE=:USER_TYPE,EXPIRE_TIME=:EXPIRE_TIME,REMARK=:REMARK,MOBILE=:MOBILE,EMAIL=:EMAIL,MOBILE_FLAG=:MOBILE_FLAG,EMAIL_FLAG=:EMAIL_FLAG,ISENCRYPTION=:ISENCRYPTION,ISVERIFICATION=:ISVERIFICATION,CLIENTIP=:CLIENTIP,CREATE_TIME=:CREATE_TIME WHERE USER_ID=:USER_ID", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)o.Account), new OracleParameter(":USER_NAME", (object)o.UserName), new OracleParameter(":USER_TYPE", (object)o.UserType), new OracleParameter(":EXPIRE_TIME", (object)o.ExpireTime), new OracleParameter(":REMARK", (object)o.Remark), new OracleParameter(":USER_ID", (object)o.UserID), new OracleParameter(":MOBILE", (object)o.Mobile), new OracleParameter(":EMAIL", (object)o.Email), new OracleParameter(":MOBILE_FLAG", (object)o.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)o.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)o.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)o.IsVerification), new OracleParameter(":CLIENTIP", (object)o.ClientIp), new OracleParameter(":CREATE_TIME", (object)o.CreateTime)) > 0;
}
//鏇存柊鐘舵��
@@ -314,7 +328,7 @@
public bool Add(SysUser e)//娣诲姞璐﹀彿
{
- return OracleHelper.ExecuteSql("INSERT INTO SYS_USER(USER_ID,ACCOUNT,PASSWORD, SALT, USER_NAME, USER_TYPE, EXPIRE_TIME,REMARK,MOBILE,EMAIL,MOBILE_FLAG,EMAIL_FLAG,ISENCRYPTION,ISVERIFICATION,CLIENTIP,CREATE_TIME) VALUES(SYS_USER_ID_SEQ.NextVal,:ACCOUNT,:PASSWORD, :SALT, :USER_NAME,:USER_TYPE,:EXPIRE_TIME,:REMARK,:MOBILE,:EMAIL,:MOBILE_FLAG,:EMAIL_FLAG,:ISENCRYPTION,:ISVERIFICATION,:CLIENTIP,:CREATE_TIME)", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)e.Account), new OracleParameter(":PASSWORD", (object)e.Password), new OracleParameter(":SALT", (object)e.Salt), new OracleParameter(":USER_NAME", (object)e.UserName), new OracleParameter(":USER_TYPE", (object)e.UserType), new OracleParameter(":EXPIRE_TIME", (object)e.ExpireTime), new OracleParameter(":REMARK", (object)e.Remark), new OracleParameter(":MOBILE", (object)e.Mobile), new OracleParameter(":EMAIL", (object)e.Email), new OracleParameter(":MOBILE_FLAG", (object)e.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)e.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)e.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)e.IsVerification), new OracleParameter(":CLIENTIP", (object)e.ClientIp), new OracleParameter(":CREATE_TIME", (object)e.CreateTime) ) > 0;
+ return OracleHelper.ExecuteSql("INSERT INTO SYS_USER(USER_ID,ACCOUNT,PASSWORD, SALT, USER_NAME, USER_TYPE, EXPIRE_TIME,REMARK,MOBILE,EMAIL,MOBILE_FLAG,EMAIL_FLAG,ISENCRYPTION,ISVERIFICATION,CLIENTIP,CREATE_TIME) VALUES(SYS_USER_ID_SEQ.NextVal,:ACCOUNT,:PASSWORD, :SALT, :USER_NAME,:USER_TYPE,:EXPIRE_TIME,:REMARK,:MOBILE,:EMAIL,:MOBILE_FLAG,:EMAIL_FLAG,:ISENCRYPTION,:ISVERIFICATION,:CLIENTIP,:CREATE_TIME)", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)e.Account), new OracleParameter(":PASSWORD", (object)e.Password), new OracleParameter(":SALT", (object)e.Salt), new OracleParameter(":USER_NAME", (object)e.UserName), new OracleParameter(":USER_TYPE", (object)e.UserType), new OracleParameter(":EXPIRE_TIME", (object)e.ExpireTime), new OracleParameter(":REMARK", (object)e.Remark), new OracleParameter(":MOBILE", (object)e.Mobile), new OracleParameter(":EMAIL", (object)e.Email), new OracleParameter(":MOBILE_FLAG", (object)e.MobileFlag), new OracleParameter(":EMAIL_FLAG", (object)e.EmailFlag), new OracleParameter(":ISENCRYPTION", (object)e.IsEncryption), new OracleParameter(":ISVERIFICATION", (object)e.IsVerification), new OracleParameter(":CLIENTIP", (object)e.ClientIp), new OracleParameter(":CREATE_TIME", (object)e.CreateTime)) > 0;
}
public bool UpdatePassword(int userID, string oldPassword, string newPassword)
@@ -325,7 +339,7 @@
//鍔犲瘑鐩愪慨鏀瑰瘑鐮�
public bool UpdatePwd(string account, string password)
{
- return OracleHelper.ExecuteSql("UPDATE SYS_USER SET PASSWORD=:PASSWORD WHERE ACCOUNT=:ACCOUNT", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account), new OracleParameter(":PASSWORD", (object)password) ) > 0;
+ return OracleHelper.ExecuteSql("UPDATE SYS_USER SET PASSWORD=:PASSWORD WHERE ACCOUNT=:ACCOUNT", OracleHelper.Connection, new OracleParameter(":ACCOUNT", (object)account), new OracleParameter(":PASSWORD", (object)password)) > 0;
}
public void UpdatePermission(int userid, string[] menuIDArray)
--
Gitblit v1.9.1