public static async Task <Response> IsHavePower(IsPowerIng ResultMsg, int Id) { Response response = new Response { code = Convert.ToInt32(StatusEnum.Failed) }; response.msg = "暂无权限"; try { var list = await SqlDapperHelper.ReturnListTAsync <Role>("select SR.Id,SR.AdminId,SR.PowerSetId,SR.CreateTime,SP.ActionName,SP.ControllerNmae,SP.PowerName,SP.MenuId from Sys_Role SR join Sys_PowerSet SP on SR.PowerSetId=SP.Id where SR.AdminId=@AdminId", new { AdminId = Id }); var isHave = list.Any(x => x.ControllerNmae == ResultMsg.ControllerName && x.ActionName == ResultMsg.ActionName); if (isHave) { response.code = Convert.ToInt32(StatusEnum.Succeed); response.msg = "正常"; } else { response.code = Convert.ToInt32(StatusEnum.Error); response.msg = "暂无权限"; } } catch (Exception ex) { LogHelp.Error(ex); } return(response); }
/// <summary> /// 根据应用ID 查询分组 /// </summary> /// <param name="secret_id"></param> /// <param name="start"></param> /// <param name="length"></param> /// <returns></returns> public static async Task <List <user_face_storage_group> > GetUser_Face_Storage_Groups_BySecretIdAsunc(string secret_id, int start, int length) { string where = " AND secret_id = @secret_id AND is_delete = @is_delete"; string limit = " limit @length offset @start "; return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage_group>(SelectSql + where + limit, new { secret_id, length, start, is_delete = false })); }
/// <summary> /// 修改用户金额 /// </summary> /// <param name="user_Amount">日志实体类</param> /// <param name="tra">事务</param> /// <returns></returns> public static async Task <long> MinusUserAmount_By_User_id(user_amount_detail user_Amount, IDbTransaction tra = null) { string set = "use_amount = use_amount+(@change_amount) , updated_at = @updated_at"; string where = " AND id = @user_id"; return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set), new { user_Amount.user_id, user_Amount.updated_at, user_Amount.change_amount }, tra)); }
/// <summary> /// 根据应用,分组,用户查询 /// </summary> /// <param name="user_id"></param> /// <param name="group_id"></param> /// <param name="secret_id"></param> /// <returns></returns> public static async Task <List <user_face_storage> > GetUserList_By_UGS(string user_id, List <long> group_ids, string secret_id) { string where = @" AND api_user_id = @user_id AND api_group_id = ANY(@group_ids) AND secret_id = @secret_id AND is_delete=@is_delete"; return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage>(Select + where, new { user_id, group_ids, secret_id, is_delete = false })); }
public int Insert(SqlDapperHelper db) { if (string.IsNullOrWhiteSpace(TITLE)) { throw new Exception("제목은 빈값일 수 없습니다"); } string sql = @" INSERT INTO dbo.T_BOARD ( SEQ ,TITLE ,CONTENTS ,REG_U_ID ,REG_NAME ,REG_DATE ,STATUS ) SELECT ISNULL((SELECT MAX(SEQ)+1 FROM dbo.T_BOARD), 1) ,@TITLE ,@CONTENTS ,@REG_U_ID ,@REG_NAME ,@REG_DATE ,@STATUS "; return(db.Execute(sql, this)); }
/// <summary> /// 根据 应用ID 和 分组ID 查询用户 /// </summary> /// <param name="secret_id"></param> /// <param name="group_id"></param> /// <param name="start"></param> /// <param name="length"></param> /// <returns></returns> public static async Task <List <user_face_storage> > GetUser_Face_Storage_By_UGS_Asunc_All(string secret_id, List <long> group_ids) { string sql = "SELECT PUBLIC.user_face_storage.api_user_id FROM PUBLIC.user_face_storage WHERE 1 = 1 "; string where = " AND secret_id = @secret_id AND api_group_id = ANY(@group_ids) AND is_delete = @is_delete"; return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage>(sql + where, new { secret_id, group_ids, is_delete = false })); }
public ActionResult Index() { using (var db = new SqlDapperHelper()) { return(View(BoardModel.GetList(db, ""))); } }
public static List <BoardModel> GetList(SqlDapperHelper db, string search) { if (string.IsNullOrWhiteSpace(search) == false) { search = search.Trim(); } string sql = @" SELECT A.SEQ ,A.TITLE ,A.CONTENTS ,A.REG_U_ID ,A.REG_NAME ,A.REG_DATE ,A.STATUS ,A.UPDATE_DATE FROM dbo.T_BOARD A WHERE A.TITLE LIKE @search + '%' "; var list = db.Query <BoardModel>(sql, new { search = search }); return(list); }
private void button1_Click(object sender, EventArgs e) { using (var db = new SqlDapperHelper()) { dataGridView1.DataSource = new BindingList <BoardModel>(BoardModel.GetList(db, "")); } }
/// <summary> /// 修改是否删除状态 /// </summary> /// <param name="group"></param> /// <param name="tra"></param> /// <returns></returns> public static async Task <long> Remove_storage_group(user_face_storage_group group, IDbTransaction tra = null) { string set = "is_delete = @is_delete , updated_at = @updated_at"; string where = "AND user_id = @user_id AND group_name = @group_name AND secret_id = @secret_id"; return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(UpdateSql + where, set), group, tra)); }
/// <summary> /// 修改预存数量 /// </summary> /// <param name="detail"></param> /// <param name="tra"></param> /// <returns></returns> public static async Task <long> MinusUsernumber_By_User_id(user_face_compare_prepayment_detail detail, IDbTransaction tra = null) { string set = " number = number+(@change_number),updated_at=@updated_at"; string where = " AND user_id = @user_id"; return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set), new { detail.change_number, detail.user_id, detail.updated_at }, tra)); }
private void button2_Click(object sender, EventArgs e) { var list = dataGridView1.DataSource as BindingList <BoardModel>; using (var db = new SqlDapperHelper()) { db.BeginTransaction(); try { foreach (var item in list) { switch (item.ChangedFlag) { case ChangedFlagEnum.Inserted: item.Insert(db); break; case ChangedFlagEnum.Updated: item.Update(db); break; } } db.Commit(); } catch (Exception ex) { db.Rollback(); MessageBox.Show(ex.Message); } } }
/// <summary> /// 根据应用,分组,用户查询 /// </summary> /// <param name="user_id"></param> /// <param name="secret_id"></param> /// <returns></returns> public static async Task <List <T> > GetStorage_By_UGS_ALL <T>(string user_id, string secret_id) { string sql = "SELECT * FROM PUBLIC.user_face_storage S left join PUBLIC.user_face_storage_group G on S.api_group_id=G.id WHERE 1 = 1"; string where = " AND S.api_user_id = @user_id AND S.secret_id = @secret_id AND S.is_delete=@is_delete"; return(await SqlDapperHelper.ExecuteReaderRetListAsync <T>(sql + where, new { user_id, secret_id, is_delete = false })); }
/// <summary> /// 根据 应用ID 和 分组ID 查询用户 /// </summary> /// <param name="secret_id"></param> /// <param name="group_id"></param> /// <param name="start"></param> /// <param name="length"></param> /// <returns></returns> public static async Task <List <user_face_storage> > GetUser_Face_Storage_By_UGS_Asunc(string secret_id, long group_id, int start, int length) { string sql = "SELECT PUBLIC.user_face_storage.api_user_id FROM PUBLIC.user_face_storage WHERE 1 = 1 "; string where = " AND secret_id = @secret_id AND api_group_id = @group_id AND is_delete = @is_delete"; string limit = " limit @length offset @start "; return(await SqlDapperHelper.ExecuteReaderRetListAsync <user_face_storage>(sql + where + limit, new { secret_id, group_id, length, start, is_delete = false })); }
public int DeleteByIds(List <long> ids) { if (ids.Count == 0) { return(0); } string sql = "DELETE FROM [examtotalcount_without] WHERE [id] IN @ids"; return(SqlDapperHelper.Execute(sql, new { ids = ids })); }
public T QueryById <T>(long id, string returnFields = null) { if (returnFields == null) { returnFields = "[id],[exam_id],[grade_class],[exam_subject],[exam_avg],[exam_stdevp],[exam_max],[exam_allpassnum],[exam_onepassnum],[exam_twopassnum],[exam_threepassnum],[exam_greatnum],[exam_passnum],[exam_allrank],[exam_onerank],[exam_tworank],[exam_threerank]"; } string sql = "SELECT " + returnFields + " FROM [examtotalcount_without] WHERE [id] = @id"; return(SqlDapperHelper.Query <T>(sql, new { id = id }).FirstOrDefault()); }
public T QueryById <T>(long id, string returnFields = null) { if (returnFields == null) { returnFields = "[id],[school_id],[schoolyear_nowyear],[schoolyear_mester],[grade_section],[exam_name],[exam_time],[exam_level]"; } string sql = "SELECT " + returnFields + " FROM [exam] WHERE [id] = @id"; return(SqlDapperHelper.Query <T>(sql, new { id = id }).FirstOrDefault()); }
public int DeleteByIds(List <int> ids) { if (ids.Count == 0) { return(0); } string sql = "DELETE FROM [admins] WHERE EXISTS(SELECT 1 FROM @table as t WHERE t.id=[admins].[id])"; return(SqlDapperHelper.Execute(sql, new { table = ids.AsTableValuedParameter("type_int") })); }
/// <summary> /// 根据应用,分组,用户查询 /// </summary> /// <param name="user_id"></param> /// <param name="group_id"></param> /// <param name="secret_id"></param> /// <returns></returns> public static async Task <user_face_storage> GetUser_By_UGS(string user_id, long group_id, string face_token, string secret_id) { string where = @" AND api_user_id = @user_id AND api_group_id = @group_id AND face_token = @face_token AND secret_id = @secret_id AND is_delete = @is_delete"; return(await SqlDapperHelper.ExecuteReaderRetTAsync <user_face_storage>(Select + where, new { user_id, group_id, secret_id, face_token, is_delete = false })); }
public T QueryById <T>(int id, string returnFields = null) { if (returnFields == null) { returnFields = "[id],[username],[pwd],[sex]"; } string sql = "SELECT " + returnFields + " FROM [admins] WHERE [id] = @id"; return(SqlDapperHelper.Query <T>(sql, new { id = id }).FirstOrDefault()); }
/// <summary> /// 获取人脸坐标(单个文件) /// </summary> /// <param name="files"></param> /// <returns></returns> public async Task <ActionResult> GetFaceLocation(IFormFile files) { var date = SqlDapperHelper.ExecuteReaderReturnList <object>("SELECT * FROM \"user\""); var rectangle = await GetRectanglesAsync(new List <IFormFile>() { files }); if (rectangle.Count <= 0) { return(Ok(null)); } return(Ok(rectangle[0])); }
/// <summary> /// 从所有组中删除用户 /// </summary> /// <param name="user"></param> /// <param name="tra"></param> /// <returns></returns> public static async Task <long> Remove_Storage_By_ApiUserID_All(user_face_storage user, IDbTransaction tra = null) { string set = "is_delete = @is_delete , updated_at = @updated_at"; string where = " AND secret_id = @secret_id"; return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set), new { is_delete = user.is_delete, updated_at = user.updated_at, secret_id = user.secret_id }, tra)); }
public JsonResult Login(Login login) { Response <object> result = new Response <object>() { code = Convert.ToInt32(Status.Failed) }; var count = SqlDapperHelper.ReturnT <int>("select count(1) from [User] where Phone=@Phone and Password=@Password", login); if (count > 0) { result.code = Convert.ToInt32(Status.Succeed); result.msg = "登录成功"; Login tokens = SqlDapperHelper.ReturnT <Login>("select * from [User] where Phone=@Phone and Password=@Password", login); try { //存redis RedisManager.redisHelp.SetValue("Login", JsonConvert.SerializeObject(tokens)); } catch (Exception ex) { LogHelp.Error(ex); } try { //登录加密保存信息 //var token = AESEncrypt(JsonConvert.SerializeObject(tokens)); // Convert.ToBase64String(Encode(Encoding.UTF8.GetBytes(password))); var token = Convert.ToBase64String(AESEncrypt(JsonConvert.SerializeObject(tokens))); //SignIn(tokens, true).Wait(); //登录成功,信息加入MQ PushMQ.SendMQ(tokens, Key.PushMQUserKey); } catch (Exception ex) { LogHelp.Error(ex); } return(Json(new { result })); } //测试日志 LogHelp.Debug("登录失败:账号" + login.Phone + " 密码:" + login.Password); result.msg = "登录失败"; return(Json(new { result })); }
public int UpdateAll(examTable entity, string updateFields = null) { if (updateFields == null) { updateFields = "[school_id]=@school_id,[schoolyear_nowyear]=@schoolyear_nowyear,[schoolyear_mester]=@schoolyear_mester,[grade_section]=@grade_section,[exam_name]=@exam_name,[exam_time]=@exam_time,[exam_level]=@exam_level"; } else { updateFields = StringHelper.SqlUpdateFields(updateFields); } string sql = "UPDATE [exam] SET " + updateFields; return(SqlDapperHelper.Execute(sql, entity)); }
public int UpdateAll(adminsTable entity, string updateFields = null) { if (updateFields == null) { updateFields = "[username]=@username,[pwd]=@pwd,[sex]=@sex"; } else { updateFields = StringHelper.SqlUpdateFields(updateFields); } string sql = "UPDATE [admins] SET " + updateFields; return(SqlDapperHelper.Execute(sql, entity)); }
public int UpdateAll(examtotalcount_withoutTable entity, string updateFields = null) { if (updateFields == null) { updateFields = "[exam_id]=@exam_id,[grade_class]=@grade_class,[exam_subject]=@exam_subject,[exam_avg]=@exam_avg,[exam_stdevp]=@exam_stdevp,[exam_max]=@exam_max,[exam_allpassnum]=@exam_allpassnum,[exam_onepassnum]=@exam_onepassnum,[exam_twopassnum]=@exam_twopassnum,[exam_threepassnum]=@exam_threepassnum,[exam_greatnum]=@exam_greatnum,[exam_passnum]=@exam_passnum,[exam_allrank]=@exam_allrank,[exam_onerank]=@exam_onerank,[exam_tworank]=@exam_tworank,[exam_threerank]=@exam_threerank"; } else { updateFields = StringHelper.SqlUpdateFields(updateFields); } string sql = "UPDATE [examtotalcount_without] SET " + updateFields; return(SqlDapperHelper.Execute(sql, entity)); }
/// <summary> /// 根据用户组将用户的状态变为删除 /// </summary> /// <param name="user"></param> /// <param name="tra"></param> /// <returns></returns> Remove_Storage_By_Group public static async Task <long> Remove_Storage_By_Group(user_face_storage_group group, IDbTransaction tra = null) { string set = "is_delete = @is_delete , updated_at = @updated_at"; string where = " AND api_group_id = @api_group_id AND secret_id = @secret_id"; return(await SqlDapperHelper.ExecuteSqlIntAsync(string.Format(Update + where, set), new { is_delete = group.is_delete, updated_at = group.updated_at, api_group_id = group.id, secret_id = group.secret_id }, tra)); }
/// <summary> /// 删除用户组 /// </summary> /// <returns></returns> public async Task<ActionResult> RemoveGroup(FaceDatabaseModel.GroupModel model) { RequestFaceModel request = new RequestFaceModel() { Status = 500, Message = null, FaceList = null }; // 基本验证 var userFaceSet = Verify(ref request, model); if (userFaceSet == null) { return Ok(request); } // 是否重复验证 var group = await Service_user_face_storage_group.Get_storage_group_BySecretGroupAsync(model.secret_id, model.group_id); if (group == null) { request.Enum = RequestEnum.数据不存在; return Ok(request); } group.is_delete = true;//是否移除 group.updated_at = DateTime.Now; try { using (var tra = SqlDapperHelper.GetOpenConnection().BeginTransaction()) { // 修改用户的删除状态 await Service_user_face_storage.Remove_Storage_By_Group(group, tra); // 修改用户组的删除状态 await Service_user_face_storage_group.Remove_storage_group(group, tra); tra.Commit(); request.Enum = RequestEnum.Succeed; } } catch (Exception ex) { LogHelperNLog.Error(ex, "操作日志添加失败"); request.Enum = RequestEnum.操作日志添加失败; } return Ok(request); }
public int UpdateFieldsByWhere(examtotalcount_withoutTable entity, string whereFields, string updateFields = null) { if (updateFields == null) { updateFields = "[exam_id]=@exam_id,[grade_class]=@grade_class,[exam_subject]=@exam_subject,[exam_avg]=@exam_avg,[exam_stdevp]=@exam_stdevp,[exam_max]=@exam_max,[exam_allpassnum]=@exam_allpassnum,[exam_onepassnum]=@exam_onepassnum,[exam_twopassnum]=@exam_twopassnum,[exam_threepassnum]=@exam_threepassnum,[exam_greatnum]=@exam_greatnum,[exam_passnum]=@exam_passnum,[exam_allrank]=@exam_allrank,[exam_onerank]=@exam_onerank,[exam_tworank]=@exam_tworank,[exam_threerank]=@exam_threerank"; } else { updateFields = StringHelper.SqlUpdateFields(updateFields); } string where = "WHERE " + StringHelper.SqlWhereFields(whereFields); string sql = string.Format("UPDATE [examtotalcount_without] SET {0} " + where, updateFields); return(SqlDapperHelper.Execute(sql, entity)); }
public int UpdateFieldsByWhere(adminsTable entity, string whereFields, string updateFields = null) { if (updateFields == null) { updateFields = "[username]=@username,[pwd]=@pwd,[sex]=@sex"; } else { updateFields = StringHelper.SqlUpdateFields(updateFields); } string where = "WHERE " + StringHelper.SqlWhereFields(whereFields); string sql = string.Format("UPDATE [admins] SET {0} " + where, updateFields); return(SqlDapperHelper.Execute(sql, entity)); }