/// <summary> /// 获取家庭成员列表 /// </summary> /// <param name="familyCode"></param> /// <param name="userId"></param> /// <returns></returns> public List <WeChatAccountModel> GetMembersAccount(string familyCode, long userId) { if (string.IsNullOrEmpty(familyCode)) { return(null); } using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.Query <WeChatAccountModel>(@"SELECT wa.AccountId, wa.AvatarUrl, wf.CreateTime, wa.FamilyCode, wa.Gender, wa.HadBindFamily, wa.Id, wa.Id, wa.IsDel, wa.IsDel, wa.NickName, wa.OpenId, wa.Remarks, wf.UnBindTime UpdateTime FROM wechatfamily wf LEFT JOIN wechataccount wa ON wa.AccountId = wf.UserId WHERE wf.FamilyCode = @FamilyCode AND wa.IsDel = @IsDel AND ( wf.IsDel = @IsDel OR wf.UserId = @CurrentUserId )" , new { IsDel = FlagEnum.HadZore.GetHashCode(), FamilyCode = familyCode, CurrentUserId = userId }).ToList()); } }
/// <summary> /// 添加新的用户并绑定微信账户 /// </summary> /// <param name="sysUser"></param> /// <param name="openId"></param> /// <returns></returns> public long InsertAndBindWechatUser(SysUser sysUser, string openId) { if (sysUser == null) { return(0); } using (var conn = SqlConnectionHelper.GetOpenConnection()) { var wechatUser = conn.GetList <WeChatAccountModel>(new { IsDel = FlagEnum.HadZore.GetHashCode(), OpenId = openId })?.FirstOrDefault(); if (wechatUser == null || wechatUser.AccountId > 0) { return(-1); } IDbTransaction transaction = conn.BeginTransaction(); try { var inId = conn.Insert <long, SysUser>(sysUser, transaction); wechatUser.AccountId = inId; wechatUser.UpDateTime = DateTime.Now; conn.Update(wechatUser, transaction); transaction.Commit(); return(inId); } catch (Exception e) { if (Debugger.IsAttached) { Trace.WriteLine("事务处理失败:" + e.Message); } transaction.Rollback(); return(0); } } }
/// <summary> /// 解除用户绑定 /// </summary> /// <param name="familyModel"></param> /// <param name="weChatAccountModel"></param> /// <returns></returns> public bool UnBindFamilyAndUser(WechatFamilyModel familyModel, WeChatAccountModel weChatAccountModel) { if (familyModel == null || weChatAccountModel == null) { return(false); } using (var conn = SqlConnectionHelper.GetOpenConnection()) { IDbTransaction transaction = conn.BeginTransaction(); try { conn.Update(familyModel, transaction); conn.Update(weChatAccountModel, transaction); transaction.Commit(); return(true); } catch (Exception e) { if (Debugger.IsAttached) { Trace.WriteLine("事务处理失败:" + e.Message); } transaction.Rollback(); return(false); } } }
/// <summary> /// 获取数据库中的表 /// </summary> /// <param name="connectionstring"></param> /// <param name="databaseName"></param> /// <returns></returns> public List <Table> GetTableList(string connectionstring, string databaseName) { SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle); using (IDbConnection conn = SqlConnectionHelper.GetOpenConnection(connectionstring)) { List <Table> tables = new List <Table>(); string sqlCmd = "SELECT table_name,cluster_name FROM user_tables"; var dr = conn.ExecuteReader(sqlCmd); while (dr.Read()) { string id = dr.GetString(0); string displayName = dr.GetString(0); string name = dr.GetString(0); string comment = dr.IsDBNull(1) ? string.Empty : dr.GetString(1); Table table = new Table(id, displayName, name, comment) { OriginalName = name }; tables.Add(table); } dr.Close(); return(tables); } }
/// <summary> /// 查询指定用户的类型,名称 /// </summary> /// <param name="spendType"></param> /// <param name="userId"></param> /// <param name="name"></param> /// <returns></returns> public List <CostTypeModel> GetModels(int spendType, long userId, string name) { using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetList <CostTypeModel>(new { SpendType = spendType, UserId = userId, Name = name, IsDel = FlagEnum.HadZore.GetHashCode() }).ToList()); } }
/// <summary> /// 删除用户 /// </summary> /// <param name="id"></param> public void DelUserModel(int id) { if (id < 1) { return; } var model = GetSysUser(id); if (model == null) { return; } model.IsDel = FlagEnum.HadOne; using (var conn = SqlConnectionHelper.GetOpenConnection()) { conn.Update(model); conn.Execute("update sysusermenu set IsDel=@Del where UserId=@UserId and IsDel=@NotDel", new { Del = FlagEnum.HadOne.GetHashCode(), NotDel = FlagEnum.HadZore.GetHashCode(), UserId = id }); } }
/// <summary> /// 获取信息 /// </summary> /// <param name="beginTime"></param> /// <param name="endTime"></param> /// <param name="hasDeal"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <CustomercommentModel> GetModels(DateTime beginTime, DateTime endTime, FlagEnum hasDeal, int pageIndex, int pageSize) { var where = new StringBuilder(" where HasDeal=@HasDeal and IsDel=@IsDel "); if (beginTime > new DateTime(1900, 1, 1)) { where.Append(" and CreateTime>=@BeginTime "); } if (endTime > new DateTime(1900, 1, 1)) { where.Append(" and CreateTime<@EndTime "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), HasDeal = hasDeal.GetHashCode(), BeginTime = beginTime, EndTime = endTime }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetListPaged <CustomercommentModel>(pageIndex, pageSize, where.ToString(), " CreateTime desc ", param)?.ToList()); } }
/// <summary> /// GetViews /// </summary> /// <param name="connectionString"></param> /// <param name="databaseName"></param> /// <returns></returns> public List <View> GetViews(string connectionString, string databaseName) { SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle); using (IDbConnection conn = SqlConnectionHelper.GetOpenConnection(connectionString)) { List <View> views = new List <View>(); string sqlCmd = $"select view_name,editioning_view from user_views"; var dr = conn.ExecuteReader(sqlCmd); while (dr.Read()) { string id = dr.GetString(0); string displayName = dr.GetString(0); string name = dr.GetString(0); string comment = string.Empty; View view = new View(id, displayName, name, comment) { OriginalName = name }; views.Add(view); } dr.Close(); return(views); } }
/// <summary> /// 获取总记录数 /// </summary> /// <returns></returns> public int GetCount(List <long> userIds, int isValid, string name) { var where = new StringBuilder(" where UserId in @UserId "); where.Append(" and IsDel=@IsDel "); if (isValid >= 0) { where.Append(" and IsValid=@IsValid "); } if (!string.IsNullOrEmpty(name)) { where.Append(" and CostChannelName like @CostChannelName "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), UserId = userIds.ToArray(), IsValid = isValid, CostChannelName = "%" + name + "%" }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.RecordCount <CostChannelModel>(where.ToString(), param)); } }
/// <summary> /// 获取信息 /// </summary> /// <param name="userIds"></param> /// <param name="isValid"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="name"></param> /// <returns></returns> public List <CostChannelModel> GetModels(List <long> userIds, int isValid, int pageIndex, int pageSize, string name) { var where = new StringBuilder(" where UserId in @UserId "); where.Append(" and IsDel=@IsDel "); if (isValid >= 0) { where.Append(" and IsValid=@IsValid "); } if (!string.IsNullOrEmpty(name)) { where.Append(" and CostChannelName like @CostChannelName "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), UserId = userIds.ToArray(), IsValid = isValid, CostChannelName = "%" + name + "%" }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetListPaged <CostChannelModel>(pageIndex, pageSize, where.ToString(), " Sort desc ", param)?.ToList()); } }
/// <summary> /// 获取所有的菜单信息 /// </summary> /// <returns></returns> public List <SysMenuModel> GetAllMenuModels() { using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetList <SysMenuModel>("where IsDel=@IsDel", new { IsDel = FlagEnum.HadZore.GetHashCode() })?.OrderBy(f => f.OrderNo).ToList()); } }
/// <summary> /// 获取总数 /// </summary> /// <param name="preCode"></param> /// <param name="cardNumber"></param> /// <param name="name"></param> /// <returns></returns> public int GetCount(string preCode, string cardNumber, string name) { var where = new StringBuilder(" where 1=1 "); if (!string.IsNullOrEmpty(preCode)) { where.Append(" AND CardNumber like @preCardNumber "); } if (!string.IsNullOrEmpty(cardNumber)) { where.Append(" AND CardNumber like @CardNumber "); } if (!string.IsNullOrEmpty(name)) { where.Append(" and Name like @Name "); } var param = new { Name = "%" + name + "%", preCardNumber = preCode + "%", CardNumber = "%" + cardNumber + "%" }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.RecordCount <SysIdCardContentModel>(where.ToString(), param)); } }
/// <summary> /// 获取总记录数 /// </summary> /// <returns></returns> public int GetCount(int spendType, List <long> userIds, string name) { var where = new StringBuilder(" where UserId in @UserId "); where.Append(" and IsDel=@IsDel "); if (spendType != -1) { where.Append(" and SpendType= @Type "); } if (!string.IsNullOrEmpty(name)) { where.Append(" and Name like @Name "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), Type = spendType, UserId = userIds.ToArray(), Name = "%" + name + "%" }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.RecordCount <CostTypeModel>(where.ToString(), param)); } }
/// <summary> /// 获取总记录数 /// </summary> /// <returns></returns> public int GetCount(DateTime beginTime, DateTime endTime, FlagEnum hasDeal) { var where = new StringBuilder(" where HasDeal=@HasDeal and IsDel=@IsDel "); if (beginTime > new DateTime(1900, 1, 1)) { where.Append(" and CreateTime>=@BeginTime "); } if (endTime > new DateTime(1900, 1, 1)) { where.Append(" and CreateTime<@EndTime "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), HasDeal = hasDeal.GetHashCode(), BeginTime = beginTime, EndTime = endTime }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.RecordCount <CustomercommentModel>(where.ToString(), param)); } }
/// <summary> /// 保存信息 /// </summary> /// <param name="saveModel"></param> public void SaveModel(InusekeyinfoModel saveModel) { if (saveModel == null) { return; } using (var conn = SqlConnectionHelper.GetOpenConnection()) { saveModel.UseYear = saveModel.UseDate.Year; saveModel.UseMonth = saveModel.UseDate.Month; saveModel.UseDay = saveModel.UseDate.Day; if (saveModel.Id < 1) { //新增 var newId = conn.Insert(saveModel); if (newId != null) { saveModel.Id = newId.Value; } } //修改 conn.Update(saveModel); } }
/// <summary> /// 获取总记录数 /// </summary> /// <returns></returns> public int GetCount(string procedureCode, long userId) { var where = new StringBuilder(" where IsDel=@IsDel "); if (!string.IsNullOrEmpty(procedureCode)) { where.Append(" and ProcedureCode= @ProcedureCode "); } if (userId > -1) { where.Append(" and CustomerId=@CustomerId "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), ProcedureCode = procedureCode, CustomerId = userId }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.RecordCount <ProcedureHistoryModel>(where.ToString(), param)); } }
/// <summary> /// 用户登陆 /// </summary> /// <param name="userName"></param> /// <param name="password"></param> /// <returns></returns> public LoginInfoModel UserLogin(string userName, string password) { var userInfo = new LoginInfoModel(); List <SysUser> userList; using (var conn = SqlConnectionHelper.GetOpenConnection()) { userList = conn.GetList <SysUser>(new { UserName = userName, IsDel = FlagEnum.HadZore.GetHashCode(), Password = password })?.ToList(); } if (userList == null || userList.Count < 1) { userInfo.IsLogin = false; userInfo.ErrMessage = "用户名或密码错误"; return(userInfo); } var u = userList.First(); userInfo.ClientIp = Fetch.UserIp; userInfo.IsLogin = true; userInfo.Id = u.Id; userInfo.UserId = u.Id; userInfo.LoginName = userName; userInfo.TrueName = u.TrueName; userInfo.HeadUrl = u.HeadUrl; userInfo.DepartName = string.Empty; userInfo.JobNumber = string.Empty; return(userInfo); }
/// <summary> /// 获取单个用户 /// </summary> /// <param name="id"></param> /// <returns></returns> public SysUser GetSysUser(long id) { using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.Get <SysUser>(id)); } }
/// <summary> /// 分页查询 /// </summary> /// <param name="preCode"></param> /// <param name="cardNumber"></param> /// <param name="name"></param> /// <param name="indexPage"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <SysIdCardContentModel> GetModels(string preCode, string cardNumber, string name, int indexPage, int pageSize) { var where = new StringBuilder(" where 1=1 "); if (!string.IsNullOrEmpty(preCode)) { where.Append(" AND CardNumber like @preCardNumber "); } if (!string.IsNullOrEmpty(cardNumber)) { where.Append(" AND CardNumber like @CardNumber "); } if (!string.IsNullOrEmpty(name)) { where.Append(" and Name like @Name "); } var param = new { Name = "%" + name + "%", preCardNumber = preCode + "%", CardNumber = "%" + cardNumber + "%" }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetListPaged <SysIdCardContentModel>(indexPage, pageSize, where.ToString(), " Id desc ", param)?.ToList()); } }
/// <summary> /// test sql server /// </summary> private static void RunTestSqlServer() { var stopwatch = Stopwatch.StartNew(); var sqltester = new SimpleCrudTest(SimpleCRUD.Dialect.SQLServer); foreach (var method in typeof(SimpleCrudTest).GetMethods(BindingFlags.Public | BindingFlags.Instance | BindingFlags.DeclaredOnly)) { var testwatch = Stopwatch.StartNew(); Console.Write($"Running {method.Name} in sql server"); method.Invoke(sqltester, null); testwatch.Stop(); Console.WriteLine($" -√ {testwatch.ElapsedMilliseconds}'ms "); } stopwatch.Stop(); //write result Console.WriteLine($"Time elapsed :{stopwatch.Elapsed}"); using (var connection = SqlConnectionHelper.GetOpenConnection(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Master;Integrated Security=True")) { try { connection.Execute(@" alter database DapperSimpleCrudTestDb set single_user with rollback immediate; DROP DATABASE DapperSimpleCrudTestDb; "); } catch (Exception) { } } Console.Write("SQL Server testing complete."); }
/// <summary> /// 获取总记录数 /// </summary> /// <returns></returns> public int GetSysUserCount() { using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.RecordCount <SysUser>(new { IsDel = FlagEnum.HadZore.GetHashCode() })); } }
/// <summary> /// 获取信息 /// </summary> /// <param name="spendType"></param> /// <param name="userIds"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="name"></param> /// <returns></returns> public List <CostTypeModel> GetModels(int spendType, List <long> userIds, int pageIndex, int pageSize, string name) { var where = new StringBuilder(" where UserId in @UserId "); where.Append(" and IsDel=@IsDel "); if (spendType != -1) { where.Append(" and SpendType= @Type "); } if (!string.IsNullOrEmpty(name)) { where.Append(" and Name like @Name "); } var param = new { IsDel = FlagEnum.HadZore.GetHashCode(), Type = spendType, UserId = userIds.ToArray(), Name = "%" + name + "%" }; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetListPaged <CostTypeModel>(pageIndex, pageSize, where.ToString(), " Sort desc ", param)?.ToList()); } }
/// <summary> /// GetKeys /// </summary> /// <param name="connectionString"></param> /// <param name="sqlCmd"></param> /// <returns></returns> private Columns GetKeys(string connectionString, string sqlCmd) { Columns columns = new Columns(5); SimpleCRUD.SetDialect(SimpleCRUD.Dialect.Oracle); using (var conn = SqlConnectionHelper.GetOpenConnection(connectionString)) { var dr = conn.ExecuteReader(sqlCmd); while (dr.Read()) { string id = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString(); string displayName = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString(); string name = dr.IsDBNull(3) ? string.Empty : dr.GetValue(3).ToString(); string dataType = ""; string defaultValue = ""; string isNullable = "Y"; string length = ""; string identity = string.Empty; string comment = "主键"; Column column = new Column(id, displayName, name, dataType, comment); column.Length = DataTypeConvertHelper.ToInt(length); column.IsAutoIncremented = false; column.IsNullable = isNullable.Equals("Y"); column.DefaultValue = defaultValue.ToEmpty(); column.DataType = dataType; column.OriginalName = name; columns.Add(id, column); } dr.Close(); return(columns); } }
/// <summary> /// 获取列表用户 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <SysUser> GetSysUsers(int pageIndex, int pageSize) { using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.GetListPaged <SysUser>(pageIndex, pageSize, " where IsDel=@IsDel ", null, new { IsDel = FlagEnum.HadZore.GetHashCode() })?.ToList()); } }
/// <summary> /// 查询配置文件信息 /// </summary> /// <returns></returns> public List <DataBaseLinkConfigModel> GetConfigModels() { SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite); var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString); var result = conn.GetList <DataBaseLinkConfigModel>(); return(result.ToList()); }
public ActionResult Delete(int id) { using (_connection = SqlConnectionHelper.GetOpenConnection()) { _connection.Delete <CarViewModel>(id); } return(RedirectToAction("index")); }
/// <summary> /// 查询指定条件配置信息 /// </summary> /// <param name="linkName"></param> /// <returns></returns> public DataBaseLinkConfigModel GetConfigModelByLinkName(string linkName) { SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite); var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString); var result = conn.GetList <DataBaseLinkConfigModel>($" where LinkName='{linkName}' "); return(result.FirstOrDefault()); }
/// <summary> /// 更新配置文件 /// </summary> /// <param name="newModel"></param> /// <returns></returns> public bool UpdateConfigModel(DataBaseLinkConfigModel newModel) { SimpleCRUD.SetDialect(SimpleCRUD.Dialect.SQLite); var conn = SqlConnectionHelper.GetOpenConnection(ConnectionString); var result = conn.Update(newModel); return(result > 0); }
/// <summary> /// 根据id删除资源信息 /// </summary> /// <param name="ids"></param> /// <returns></returns> public int DeleteResourceById(List <long> ids) { var sql = @"update dailystoryresource set IsDel=@IsDel where Id in @Ids and IsDel!=@IsDel"; using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(conn.Execute(sql, new { Ids = ids.ToArray(), IsDel = FlagEnum.HadOne.GetHashCode() })); } }
/// <summary> /// 获取所有的类型数据 /// </summary> /// <param name="keyType">类型</param> /// <returns></returns> public List <InusekeyinfoModel> GetAllType(string keyType) { using (var conn = SqlConnectionHelper.GetOpenConnection()) { return(string.IsNullOrEmpty(keyType) ? conn.GetList <InusekeyinfoModel>(new { IsDel = FlagEnum.HadZore.GetHashCode() })?.OrderByDescending(f => f.CreateTime).ToList() : conn.GetList <InusekeyinfoModel>(new { IsDel = FlagEnum.HadZore.GetHashCode(), KeyType = keyType })?.OrderByDescending(f => f.CreateTime).ToList()); } }