You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
421 lines
16 KiB
421 lines
16 KiB
using System;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Collections;
|
|
|
|
using MySql.Data.MySqlClient;
|
|
|
|
namespace FangYar.OracleDAL
|
|
{
|
|
/// <summary>
|
|
/// 用户（Oracle数据库实现）
|
|
/// </summary>
|
|
public class BaseUserDAL : FangYar.IDAL.BaseUserIDAL
|
|
{
|
|
#region 私有方法
|
|
|
|
/// <summary>
|
|
/// 把DataRow行转成实体类对象
|
|
/// </summary>
|
|
private void DataRowToModel(FangYar.Model.BaseUserModel model, DataRow dr)
|
|
{
|
|
if (!DBNull.Value.Equals(dr["ID"]))
|
|
model.ID = dr["ID"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_UID"]))
|
|
model.USERS_UID = dr["USERS_UID"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_NAME"]))
|
|
model.USERS_NAME = dr["USERS_NAME"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_PWD"]))
|
|
model.USERS_PWD = dr["USERS_PWD"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_AUTH"]))
|
|
model.USERS_AUTH = dr["USERS_AUTH"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_RULE"]))
|
|
model.USERS_RULE = dr["USERS_RULE"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_TYPE"]))
|
|
model.USERS_TYPE = dr["USERS_TYPE"].ToString();
|
|
|
|
}
|
|
/// <summary>
|
|
/// 把DataRow行转成实体类对象
|
|
/// </summary>
|
|
private FangYar.Model.BaseUserModel DataRowToModel(DataRow dr)
|
|
{
|
|
FangYar.Model.BaseUserModel model = new Model.BaseUserModel();
|
|
if (!DBNull.Value.Equals(dr["ID"]))
|
|
model.ID = dr["ID"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_UID"]))
|
|
model.USERS_UID = dr["USERS_UID"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_NAME"]))
|
|
model.USERS_NAME = dr["USERS_NAME"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_PWD"]))
|
|
model.USERS_PWD = dr["USERS_PWD"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_AUTH"]))
|
|
model.USERS_AUTH = dr["USERS_AUTH"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_RULE"]))
|
|
model.USERS_RULE = dr["USERS_RULE"].ToString();
|
|
if (!DBNull.Value.Equals(dr["USERS_TYPE"]))
|
|
model.USERS_TYPE = dr["USERS_TYPE"].ToString();
|
|
|
|
return model;
|
|
|
|
|
|
}
|
|
/// <summary>
|
|
/// 把DataTable行转成实体类List集合
|
|
/// </summary>
|
|
private List<FangYar.Model.BaseUserModel> DataTableToList(DataTable dt)
|
|
{
|
|
List<FangYar.Model.BaseUserModel> modellist = new List<Model.BaseUserModel>();
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
foreach (DataRow myRow in dt.Rows)
|
|
{
|
|
modellist.Add(DataRowToModel(myRow));
|
|
}
|
|
}
|
|
return modellist;
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 基本方法
|
|
|
|
/// <summary>
|
|
/// 添加用户
|
|
/// </summary>
|
|
public bool Add(Model.BaseUserModel user)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("insert into TBL_BASE_USERS(USERS_UID,USERS_PWD,USERS_NAME,USERS_TYPE)");
|
|
strSql.Append(" values ");
|
|
strSql.Append("(@USERS_UID,@USERS_PWD,@USERS_NAME,@USERS_TYPE)");
|
|
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50),
|
|
new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50),
|
|
new MySqlParameter("@USERS_NAME",MySqlDbType.VarChar,50),
|
|
new MySqlParameter("@USERS_TYPE",MySqlDbType.VarChar,50)
|
|
};
|
|
paras[0].Value = user.USERS_UID;
|
|
paras[1].Value = user.USERS_PWD;
|
|
paras[2].Value = user.USERS_NAME;
|
|
paras[3].Value = user.USERS_TYPE;
|
|
int i = FangYar.Common.MySqlHelper.ExecuteSql(strSql.ToString(), paras);
|
|
if (i > 0)
|
|
{
|
|
return true;
|
|
}
|
|
else
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改用户信息
|
|
/// </summary>
|
|
public bool Edit(Model.BaseUserModel user)
|
|
{
|
|
StringBuilder strSql = new StringBuilder();
|
|
strSql.Append("update TBL_BASE_USERS set ");
|
|
strSql.Append("USERS_UID=@USERS_UID,");
|
|
strSql.Append("USERS_PWD=@USERS_PWD,");
|
|
strSql.Append("USERS_NAME=@USERS_NAME,");
|
|
strSql.Append("USERS_RULE=@USERS_RULE,");
|
|
strSql.Append("USERS_AUTH=@USERS_AUTH,");
|
|
strSql.Append("USERS_TYPE=@USERS_TYPE ");
|
|
strSql.Append(" where ID=@ID");
|
|
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_UID",user.USERS_UID),
|
|
new MySqlParameter("@USERS_PWD",user.USERS_PWD),
|
|
new MySqlParameter("@USERS_NAME",user.USERS_NAME),
|
|
new MySqlParameter("@USERS_RULE",user.USERS_RULE),
|
|
new MySqlParameter("@USERS_AUTH",user.USERS_AUTH),
|
|
new MySqlParameter("@USERS_TYPE",user.USERS_TYPE),
|
|
new MySqlParameter("@ID",user.ID)
|
|
};
|
|
paras[0].Value = user.USERS_UID;
|
|
paras[1].Value = user.USERS_PWD;
|
|
paras[2].Value = user.USERS_NAME;
|
|
paras[3].Value = user.USERS_RULE;
|
|
paras[4].Value = user.USERS_AUTH;
|
|
paras[5].Value = user.USERS_TYPE;
|
|
paras[6].Value = user.ID;
|
|
|
|
int i = FangYar.Common.MySqlHelper.ExecuteSql(strSql.ToString(), paras); ;
|
|
if (i > 0)
|
|
return true;
|
|
else
|
|
return false;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 删除用户（可批量删除，删除用户同时删除对应的权限和所处的部门）
|
|
/// </summary>
|
|
public bool Delete(string IDList)
|
|
{
|
|
string sql = "delete from TBL_BASE_USERS where ID in ('" + IDList + "')";
|
|
try
|
|
{
|
|
FangYar.Common.MySqlHelper.ExecuteSql(sql);
|
|
return true;
|
|
}
|
|
catch
|
|
{
|
|
return false;
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据用户账号获取用户
|
|
/// </summary>
|
|
public Model.BaseUserModel GetModelByUserID(string USERS_UID)
|
|
{
|
|
string sql = "select * from TBL_BASE_USERS where USERS_UID =@USERS_UID";
|
|
FangYar.Model.BaseUserModel user = null;
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50)//
|
|
};
|
|
paras[0].Value = USERS_UID;
|
|
DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);
|
|
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
user = new FangYar.Model.BaseUserModel();
|
|
DataRowToModel(user, dt.Rows[0]);
|
|
return user;
|
|
}
|
|
else
|
|
return null;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据ID获取用户
|
|
/// </summary>
|
|
public Model.BaseUserModel GetModelByID(string ID)
|
|
{
|
|
string sql = "select * from TBL_BASE_USERS where ID =@ID";
|
|
FangYar.Model.BaseUserModel user = null;
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@ID",MySqlDbType.VarChar,50)//
|
|
};
|
|
paras[0].Value = ID;
|
|
DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);
|
|
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
user = new FangYar.Model.BaseUserModel();
|
|
DataRowToModel(user, dt.Rows[0]);
|
|
return user;
|
|
}
|
|
else
|
|
return null;
|
|
}
|
|
|
|
|
|
#endregion
|
|
|
|
#region 分页
|
|
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <returns></returns>
|
|
public string QueryJsonList(int PageIndex, int PageSize, string strwhere)
|
|
{
|
|
|
|
return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, strwhere));
|
|
}
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <returns></returns>
|
|
public string QueryJsonList(int PageIndex, int PageSize, string strwhere, string order)
|
|
{
|
|
|
|
return FangYar.Common.JsonHelper.ToJson(QueryPage(PageIndex, PageSize, strwhere, order));
|
|
}
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <returns></returns>
|
|
public List<FangYar.Model.BaseUserModel> QueryList(int PageIndex, int PageSize, string strwhere)
|
|
{
|
|
|
|
return DataTableToList(QueryPage(PageIndex, PageSize, strwhere));
|
|
}
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <returns></returns>
|
|
public List<FangYar.Model.BaseUserModel> QueryList(int PageIndex, int PageSize, string strwhere, string order)
|
|
{
|
|
|
|
return DataTableToList(QueryPage(PageIndex, PageSize, strwhere, order));
|
|
}
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <returns></returns>
|
|
public DataTable QueryPage(int PageIndex, int PageSize, string strwhere)
|
|
{
|
|
|
|
return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, "TBL_BASE_USERS", strwhere);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 分页无排序
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <param name="strwhere">排序</param>
|
|
/// <returns></returns>
|
|
public DataTable QueryPage(int PageIndex, int PageSize, string strwhere, string order)
|
|
{
|
|
|
|
return FangYar.Common.MySqlHelper.QueryPage(PageIndex, PageSize, "TBL_BASE_USERS", strwhere, order);
|
|
}
|
|
/// <summary>
|
|
/// 存储过程分页
|
|
/// </summary>
|
|
/// <param name="PageIndex">页面索引</param>
|
|
/// <param name="PageSize">页面大小</param>
|
|
/// <param name="strwhere">查询条件</param>
|
|
/// <param name="strwhere">排序</param>
|
|
/// <returns></returns>
|
|
public void QueryProc(int PageIndex, int PageSize, string strwhere, string order)
|
|
{
|
|
int totalcount=0;
|
|
FangYar.Common.SqlPagerHelperOra.Select(PageSize,PageIndex,out totalcount, "TBL_BASE_USERS","*", null,null, null);
|
|
|
|
|
|
}
|
|
#endregion
|
|
|
|
#region 扩展业务方法
|
|
|
|
/// <summary>
|
|
/// 首次登陆强制修改密码
|
|
/// </summary>
|
|
public bool InitUserPwd(Model.BaseUserModel user)
|
|
{
|
|
string sql = "update TBL_BASE_USERS set USERS_PWD =@USERS_PWD where ID =@ID";
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50),
|
|
new MySqlParameter("@ID",MySqlDbType.VarChar,50)
|
|
};
|
|
paras[0].Value = user.USERS_PWD;
|
|
paras[1].Value = user.ID;
|
|
int i= FangYar.Common.MySqlHelper.ExecuteSql(sql, paras);;
|
|
if (i > 0)
|
|
return true;
|
|
else
|
|
return false;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 修改密码
|
|
/// </summary>
|
|
public bool ChangePwd(Model.BaseUserModel user)
|
|
{
|
|
string sql = "update TBL_BASE_USERS set USERS_PWD =@USERS_PWD where ID =@ID";
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50),
|
|
new MySqlParameter("@ID",MySqlDbType.VarChar,50)
|
|
};
|
|
paras[0].Value = user.USERS_PWD;
|
|
paras[1].Value = user.ID;
|
|
int i = FangYar.Common.MySqlHelper.ExecuteSql(sql, paras); ;
|
|
if (i > 0)
|
|
return true;
|
|
else
|
|
return false;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 用户登录
|
|
/// </summary>
|
|
public Model.BaseUserModel UserLogin(string loginID, string loginPwd)
|
|
{
|
|
StringBuilder sbSql = new StringBuilder();
|
|
sbSql.Append("select ID,USERS_UID,USERS_PWD,USERS_NAME,USERS_RULE,USERS_AUTH,USERS_TYPE from TBL_BASE_USERS ");
|
|
sbSql.Append(" where USERS_UID=@USERS_UID and USERS_PWD=@USERS_PWD");
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50),
|
|
new MySqlParameter("@USERS_PWD",MySqlDbType.VarChar,50)
|
|
};
|
|
paras[0].Value = loginID;
|
|
paras[1].Value = loginPwd;
|
|
FangYar.Model.BaseUserModel user = null;
|
|
|
|
DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sbSql.ToString(), paras);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
user = new Model.BaseUserModel();
|
|
//DataRowToModel(user, dt.Rows[0]);
|
|
//只取部分属性写入cookie（防止某些列特别长，例如description，导致cookie过长）：
|
|
if (!DBNull.Value.Equals(dt.Rows[0]["ID"]))
|
|
user.ID = dt.Rows[0]["ID"].ToString();
|
|
if (!DBNull.Value.Equals(dt.Rows[0]["USERS_UID"]))
|
|
user.USERS_UID = dt.Rows[0]["USERS_UID"].ToString();
|
|
if (!DBNull.Value.Equals(dt.Rows[0]["USERS_NAME"]))
|
|
user.USERS_NAME = dt.Rows[0]["USERS_NAME"].ToString();
|
|
if (!DBNull.Value.Equals(dt.Rows[0]["USERS_PWD"]))
|
|
user.USERS_PWD = dt.Rows[0]["USERS_PWD"].ToString();
|
|
if (!DBNull.Value.Equals(dt.Rows[0]["USERS_AUTH"]))
|
|
user.USERS_AUTH = dt.Rows[0]["USERS_AUTH"].ToString();
|
|
if (!DBNull.Value.Equals(dt.Rows[0]["USERS_RULE"]))
|
|
user.USERS_RULE = dt.Rows[0]["USERS_RULE"].ToString();
|
|
return user;
|
|
}
|
|
return user;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 根据用户ID判断用户是否可用
|
|
/// </summary>
|
|
public Model.BaseUserModel CheckLoginByUserID(string USERS_UID)
|
|
{
|
|
string sql = " select ID,USERS_UID,USERS_PWD,USERS_NAME,USERS_RULE,USERS_AUTH,USERS_TYPE from TBL_BASE_USERS where USERS_UID=@USERS_UID";
|
|
FangYar.Model.BaseUserModel user = null;
|
|
MySqlParameter[] paras = {
|
|
new MySqlParameter("@USERS_UID",MySqlDbType.VarChar,50)
|
|
};
|
|
paras[0].Value = USERS_UID;
|
|
DataTable dt = FangYar.Common.MySqlHelper.QueryTable(sql, paras);
|
|
if (dt.Rows.Count > 0)
|
|
{
|
|
user = new FangYar.Model.BaseUserModel();
|
|
DataRowToModel(user, dt.Rows[0]);
|
|
return user;
|
|
}
|
|
else
|
|
{
|
|
return null;
|
|
}
|
|
}
|
|
#endregion
|
|
|
|
|
|
}
|
|
}
|
|
|