public List <RoleDto> ListRole()
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = "select id, roleName from tbRole ";
return(DBAgent.SQLExecuteReturnList <RoleDto>(param));
}
public bool AddAchievement(AchievementDto dto)
{
SQLExecuteParam param = new SQLExecuteParam();
param.obj = dto;
param.sql = @"
INSERT INTO tb_achievement (
title,
content,
`status`,
is_deleted,
creator,
create_time
)
VALUES
(
@title,
@content,
1,
0,
@creator,
now()
)
";
if (DBAgent.SQLExecuteReturnRows(param) == 1)
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// SQL执行方法返回List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dto"></param>
/// <returns></returns>
public List <T> SQLExecuteReturnList <T>(SQLExecuteParam dto) where T : new()
{
SqlConnection connection = new SqlConnection(this.connectString);
List <T> list = new List <T>();
try
{
connection.Open();
if (dto.obj != null)
{
list = connection.Query <T>(dto.sql, dto.obj).ToList();
}
else if (dto.dict.Count > 0)
{
list = connection.Query <T>(dto.sql, dto.dict).ToList();
}
else
{
list = connection.Query <T>(dto.sql, null).ToList();
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
Log.WriteLog(ex.Message);
return(null);
}
return(list);
}
public List <SalaryDto> ListSalary()
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = XMLHelper.GetNodeString("Report", "SQL/ListSalary");
return(DBAgent.SQLExecuteReturnList <SalaryDto>(param));
}
/// <summary>
/// SQL执行方法返回影响行数
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public int SQLExecuteReturnRows(SQLExecuteParam dto)
{
SqlConnection connection = new SqlConnection(this.connectString);
var rows = -1;
try
{
connection.Open();
if (dto.obj != null)
{
rows = connection.Execute(dto.sql, dto.obj);
}
else if (dto.dict.Count > 0)
{
rows = connection.Execute(dto.sql, dto.dict);
}
else
{
rows = connection.Execute(dto.sql, null);
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
Log.WriteLog(ex.Message);
return(-1);
}
return(rows);
}
/// <summary>
/// SQL执行方法返回单一数据对象
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public T SQLExecuteSingleData <T>(SQLExecuteParam dto) where T : new()
{
T t = default(T);
SqlConnection connection = new SqlConnection(this.connectString);
try
{
connection.Open();
if (dto.obj != null)
{
t = connection.Query <T>(dto.sql, dto.obj).SingleOrDefault();
}
else if (dto.dict.Count > 0)
{
t = connection.Query <T>(dto.sql, dto.dict).SingleOrDefault();
}
else
{
t = connection.Query <T>(dto.sql, null).SingleOrDefault();
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
Log.WriteLog(ex.Message);
return(default(T));
}
return(t);
}
/// <summary>
/// SQL执行方法返回影响行数
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public int SQLExecuteReturnRows(SQLExecuteParam dto)
{
var rows = -1;
try
{
connection.Open();
if (dto.obj != null)
{
rows = connection.Execute(dto.sql, dto.obj);
}
else if (dto.dict.Count > 0)
{
rows = connection.Execute(dto.sql, dto.dict);
}
else
{
rows = connection.Execute(dto.sql, null);
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
utils.GetLogUtil().WriteLog(ex.Message);
return(-1);
}
return(rows);
}
/// <summary>
/// 执行分页返回List
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dto"></param>
/// <returns></returns>
public PaginationResult <T> SQLExecuteReturnPaginationList <T>(SQLExecuteParam dto) where T : new()
{
const int defaultLimit = 10;
PaginationResult <T> pResult = new PaginationResult <T>();
SqlConnection connection = new SqlConnection(this.connectString);
try
{
if (dto.pagination == null)
{
throw new Exception("未初始化分页条件");
}
pResult.limit = dto.pagination.limit == 0 ? defaultLimit : dto.pagination.limit;
pResult.offset = dto.pagination.offset;
connection.Open();
string countSql = "SELECT COUNT(*) FROM (" + dto.sql + ") AS countTable";
if (dto.obj != null)
{
pResult.total = connection.Query <int>(countSql, dto.obj).SingleOrDefault();
}
else if (dto.dict.Count > 0)
{
pResult.total = connection.Query <int>(countSql, dto.dict).SingleOrDefault();
}
else
{
pResult.total = connection.Query <int>(countSql, null).SingleOrDefault();
}
dto.sql = "SELECT * FROM (" + dto.sql + ") AS Pagination WHERE Pagination.rowNo > @offset AND Pagination.rowNo <= @limit + @offset";
if (dto.obj != null)
{
Dictionary <string, object> objDict = ObjToDict(dto.obj);
objDict.Add("limit", dto.pagination.limit);
objDict.Add("offset", dto.pagination.offset);
pResult.rows = connection.Query <T>(dto.sql, objDict).ToList();
}
else if (dto.dict.Count > 0)
{
dto.dict.Add("limit", dto.pagination.limit);
dto.dict.Add("offset", dto.pagination.offset);
pResult.rows = connection.Query <T>(dto.sql, dto.dict).ToList();
}
else
{
pResult.rows = connection.Query <T>(dto.sql, new { limit = dto.pagination.limit, offset = dto.pagination.offset }).ToList();
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
Log.WriteLog(ex.Message);
return(null);
}
return(pResult);
}
public DataTable ListIndex_Custom(Dictionary <string, object> dict)
{
SQLExecuteParam param = new SQLExecuteParam();
param.dict = dict;
param.sql = XMLHelper.GetNodeString("Report", "SQL/" + dict["sql"].ToString());
return(DBAgent.SQLExecuteReturnTable(param));
}
/// <summary>
/// 根据Id获取单条数据信息
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <param name="sql"></param>
/// <returns></returns>
protected T GetInfoById <T>(int id, string paramName, string sql) where T : new()
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = sql;
param.dict.Add(paramName, id);
return(DBAgent.SQLExecuteSingleData <T>(param));
}
/// <summary>
/// 存储过程执行
/// </summary>
/// <param name="dto"></param>
public void SQLExecuteProcedure(SQLExecuteParam dto)
{
SqlConnection connection = new SqlConnection(this.connectString);
connection.Open();
connection.Execute(dto.sql, dto.procedureParam, null, null, CommandType.StoredProcedure);
connection.Close();
}
/// <summary>
/// 获取用户列表
/// </summary>
/// <returns></returns>
public List <UserDto> GetUserList()
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = @"
select * from tb_user order by id desc
";
return(DBAgent.SQLExecuteReturnList <UserDto>(param));
}
/// <summary>
/// 初始化SQL参数
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="pagInfo"></param>
/// <param name="t"></param>
/// <param name="sql"></param>
/// <returns></returns>
protected SQLExecuteParam InitSQLParam(PaginationInfo pagInfo, Object t, string sql)
{
SQLExecuteParam param = new SQLExecuteParam();
param.pagination = pagInfo;
param.obj = t;
param.sql = sql;
return(param);
}
public PaginationDataTable PaginationResult(Dictionary <string, object> dict)
{
DBUtil dbUtil = new DBUtil();
SQLExecuteParam param = new SQLExecuteParam();
param.dict = dict;
param.sql = XMLHelper.GetNodeString("CustomAPI", "SQL/" + dict["sql"].ToString());
return(dbUtil.SQLExecuteReturnPaginationTable(param));
}
public SessionDto GetUserInfo(UserDto user)
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = XMLHelper.GetNodeString("User", "SQL/GetUserInfo");
user.password = MD5Helper.MD5Encrypt(user.password);
param.obj = user;
return(DBAgent.SQLExecuteSingleData <SessionDto>(param));
}
/// <summary>
/// 修改用户昵称
/// </summary>
/// <param name="user"></param>
/// <returns></returns>
public bool ModifyUserInfo(UserDto user)
{
SQLExecuteParam param = new SQLExecuteParam();
param.obj = user;
param.sql = "update tb_user set nick_name = @nick_name where id = @id ";
if (DBAgent.SQLExecuteReturnRows(param) == 1)
{
return(true);
}
return(false);
}
/// <summary>
/// SQL执行方法返回DataTable
/// </summary>
/// <param name="dto"></param>
/// <returns></returns>
public DataTable SQLExecuteReturnTable(SQLExecuteParam dto)
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(this.connectString);
try
{
IEnumerable <dynamic> rows = null;
connection.Open();
if (dto.obj != null)
{
rows = connection.Query(dto.sql, dto.obj);
}
else if (dto.dict.Count > 0)
{
rows = connection.Query(dto.sql, dto.dict);
}
else
{
rows = connection.Query(dto.sql, null);
}
int flag = 0;
foreach (IDictionary <string, object> row in rows)
{
ICollection <string> arrStr = row.Keys;
if (flag == 0)
{
foreach (string str in arrStr)
{
dt.Columns.Add(str);
}
flag = 1;
}
DataRow dtrow = dt.NewRow();
foreach (string str in arrStr)
{
dtrow[str] = (object)row[str];
}
dt.Rows.Add(dtrow);
}
connection.Close();
}
catch (Exception ex)
{
connection.Close();
Log.WriteLog(ex.Message);
return(null);
}
return(dt);
}
public bool CheckUserExistByName(string name)
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = "select count(*) from tbUser where name=@name";
param.dict.Add("name", name);
if (DBAgent.SQLExecuteSingleData <int>(param) == 0)
{
return(false);
}
else
{
return(true);
}
}
/// <summary>
/// 根据ID更新指定表指定字段到指定值
/// </summary>
/// <param name="tbName"></param>
/// <param name="colName"></param>
/// <param name="colValue"></param>
/// <param name="id"></param>
/// <returns></returns>
protected bool ModifyDataColumnById(string tbName, string colName, string colValue, int id)
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = " UPDATE " + tbName + " SET " + colName + " = " + colValue + " WHERE id = @id ";
param.dict.Add("id", id);
if (1 == DBAgent.SQLExecuteReturnRows(param))
{
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 判断是否存在该用户名
/// </summary>
/// <param name="username"></param>
/// <returns>true 存在 false 不存在</returns>
public bool CheckUserNameExist(string username)
{
SQLExecuteParam param = new SQLExecuteParam();
param.obj = new
{
username = username
};
param.sql = "select * from tb_user where user_name = @username";
if (DBAgent.SQLExecuteSingleData <UserDto>(param) != null)
{
return(true);
}
return(false);
}
/// <summary>
/// 登录
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public UserDto Login(string username, string password)
{
UserDto user = null;
SQLExecuteParam param = new SQLExecuteParam();
param.obj = new
{
username = username,
password = password
};
param.sql = "select * from tb_user where user_name = @username and `password` = MD5(@password)";
user = DBAgent.SQLExecuteSingleData <UserDto>(param);
return(user);
}
/// <summary>
/// 注册
/// </summary>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public bool Register(string username, string password)
{
SQLExecuteParam param = new SQLExecuteParam();
param.obj = new
{
username = username,
password = password
};
param.sql = @"INSERT INTO tb_user (user_name, `password`)
VALUES
(@username, MD5(@PASSWORD))" ;
if (DBAgent.SQLExecuteReturnRows(param) == 1)
{
return(true);
}
return(false);
}
public List <AchievementDto> getAchievementDtoList()
{
SQLExecuteParam param = new SQLExecuteParam();
param.sql = @"SELECT
tb_achievement.id,
tb_achievement.title,
tb_achievement.content,
tb_achievement.`status`,
tb_achievement.is_deleted,
tb_achievement.creator,
tb_achievement.create_time,
tb_achievement.pass_time
FROM
tb_achievement order by id desc
";
return(DBAgent.SQLExecuteReturnList <AchievementDto>(param));
}
public int SaveRole(RoleDto role)
{
SQLExecuteParam param = new SQLExecuteParam();
param.obj = role;
if (role.id == 0)
{
//新增
param.sql = "INSERT INTO tbRole (roleName, remarks) VALUES (@roleName, @remarks)";
}
else
{
//更新
param.sql = "update tbRole set roleName=@roleName, remarks=@remarks where id=@id";
}
return(DBAgent.SQLExecuteReturnRows(param));
}
public int SaveUser(UserDto user)
{
SQLExecuteParam param = new SQLExecuteParam();
user.password = MD5Helper.MD5Encrypt(user.password);
param.obj = user;
if (user.id == 0)
{
//新增
param.sql = "insert into tbUser (name,nickName,password,roleId) values (@name,@nickName,@password,@roleId)";
}
else
{
//更新
param.sql = "update tbUser set name=@name,nickName=@nickName,password=@password,roleId=@roleId where id=@id";
}
return(DBAgent.SQLExecuteReturnRows(param));
}
/*
* public bool ChangePassword(int id, string newPassword, string oldPassword) {
* SQLExecuteParam param = new SQLExecuteParam();
*
* //检查老密码是否正确
* param.sql = @"SELECT
*
* FROM tbUser
* WHERE id
* = @id AND password = @password";
*
* param.dict.Add("id", id);
* param.dict.Add("password", MD5Helper.MD5Encrypt(oldPassword));
*
* if (DBAgent.SQLExecuteReturnList<UserDto>(param).Count == 1)
* {
* //设置新密码
* param = new SQLExecuteParam();
*
* param.sql = @"UPDATE tbUser
* SET password = @password
* WHERE id
* = @id";
*
* param.dict.Add("id", id);
* param.dict.Add("password", MD5Helper.MD5Encrypt(newPassword));
*
* if (DBAgent.SQLExecuteReturnRows(param) == 1)
* {
* return true;
* }
* else
* {
* return false;
* }
* }
* else
* {
* return false;
* }
* }
* //*/
public bool ChangePassword(string name, string newPassword, string oldPassword)
{
SQLExecuteParam param = new SQLExecuteParam();
CommDAL DBAgent = new CommDAL(ConfigurationManager.ConnectionStrings["DALconnect2"].ConnectionString);
param.procedureParam.Add("@yh", name, DbType.String, direction: ParameterDirection.Input);
param.procedureParam.Add("@ymm", oldPassword, DbType.String, direction: ParameterDirection.Input);
param.procedureParam.Add("@xmm", newPassword, DbType.String, direction: ParameterDirection.Input);
param.procedureParam.Add("@f", 0, DbType.Int32, direction: ParameterDirection.Output);
param.procedureParam.Add("@result", "", DbType.String, direction: ParameterDirection.Output);
param.sql = "lc0089999.KHCX_MMXG";
DBAgent.SQLExecuteProcedure(param);
int f = param.procedureParam.Get <int>("@f");
string result = param.procedureParam.Get <string>("@result");
if (f == 1)
{
return(true);
}
return(false);
}
int IAgent.SQLExecuteReturnRows(SQLExecuteParam dto)
{
throw new System.NotImplementedException();
}
DataTable IAgent.SQLExecuteReturnTable(SQLExecuteParam dto)
{
throw new System.NotImplementedException();
}
T IAgent.SQLExecuteSingleData <T>(SQLExecuteParam dto)
{
throw new System.NotImplementedException();
}
/// <summary>
/// 根据角色ID获取权限列表
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <PageTreeDto> GetPowerList(int id)
{
List <PageTreeDto> paglist = new List <PageTreeDto>();
SQLExecuteParam SQL = new SQLExecuteParam();
SQL.dict.Add("id", id);
SQL.sql = @"SELECT
A.id,
A.fId,
A.name AS text,
A.type,
B.id AS powerId,
A.controller,
A.param,
A.url,
A.icon
FROM tbPage AS A
LEFT JOIN (SELECT
ROW_NUMBER() OVER (PARTITION BY pageId ORDER BY id) AS No,
*
FROM tbPower WHERE roleId = @id) AS B
ON A.id = B.pageId AND B.No = 1
WHERE A.isDeleted = 0 ORDER BY A.sort ASC";
List <PagePowerDto> powerlist = DBAgent.SQLExecuteReturnList <PagePowerDto>(SQL);
foreach (PagePowerDto item in powerlist)
{
if (item.type == 0)
{
PageTreeDto dto = new PageTreeDto();
dto.pageId = item.id;
dto.state.@checked = item.powerId > 0 ? true : false;
dto.text = item.text;
dto.faicon = item.icon;
dto.url = item.url;
dto.controller = item.controller;
dto.param = item.param;
List <PageTreeMdDto> subpaglist = new List <PageTreeMdDto>();
foreach (PagePowerDto subitem in powerlist)
{
if (subitem.type == 1 && subitem.fId == item.id)
{
PageTreeMdDto subDto = new PageTreeMdDto();
subDto.pageId = subitem.id;
subDto.state.@checked = subitem.powerId > 0 ? true : false;
subDto.text = subitem.text;
subDto.url = subitem.url;
subDto.controller = subitem.controller;
subDto.param = subitem.param;
List <PageTreeEndDto> thrdlist = new List <PageTreeEndDto>();
foreach (PagePowerDto thrditem in powerlist)
{
if (thrditem.type == 2 && thrditem.fId == subitem.id)
{
PageTreeEndDto thrdDto = new PageTreeEndDto();
thrdDto.pageId = thrditem.id;
thrdDto.state.@checked = thrditem.powerId > 0 ? true : false;
thrdDto.text = thrditem.text;
thrdDto.url = thrditem.url;
thrdDto.controller = thrditem.controller;
thrdDto.param = thrditem.param;
thrdlist.Add(thrdDto);
}
}
subDto.nodes = thrdlist;
subpaglist.Add(subDto);
}
}
dto.nodes = subpaglist;
paglist.Add(dto);
}
}
return(paglist);
}
