/// <summary> /// 分页 /// </summary> /// <typeparam name="T">实体</typeparam> /// <param name="page">当前页索引</param> /// <param name="pageSize">分页大小</param> /// <param name="sql">SQL Script</param> /// <param name="isPage">是否分页(false:不分页,查询全部 ,true:分页)</param> /// <param name="args">参数列表</param> /// <returns></returns> public Page <T> Page(long page, long pageSize, string sql = null, List <object> args = null, bool isPage = true) { WriteDatabase.Comment = new StringExtension.SqlComment { Author = "BASEDAL", FileName = "BaseDal.cs", ForUse = "分页查询", FunName = "Page" }; if (!isPage) { IEnumerable <T> result; if (args == null || args.Count.Equals(0)) { result = WriteDatabase.Query <T>(sql); } else { result = WriteDatabase.Query <T>(sql, args.ToArray()); } return(new Page <T> { Items = result.ToList(), CurrentPage = page, ItemsPerPage = pageSize }); } return(ReadDatabase.Page <T>(page, pageSize, sql, (args ?? new List <object>()).ToArray())); }
/// <summary> /// 事务保存实体 /// add by lpl /// 2019-1-2 /// </summary> /// <param name="entity"></param> /// <param name="listentity"></param> /// <returns></returns> public bool SaveGongGao(GongGaoMangerEntity entity, List <GongGaoDetialEntity> listentity) { //事务开始 WriteDatabase.BeginTransaction(); try { //公告管理表插入 PersistNewItem(entity); //发送公告到个人表 foreach (var liEntity in listentity) { new GongGaoDetialDal().PersistNewItem(liEntity); } } catch (Exception e) { //事务回滚 WriteDatabase.AbortTransaction(); throw e; } //事务提交 WriteDatabase.CompleteTransaction(); return(true); }
/// <summary> /// 验证名称是否存在 /// </summary> /// <param name="id">ID</param> /// <param name="name">名称</param> /// <param name="mobileType">手机类型</param> /// <returns></returns> public bool NameExists(string name, int id, int mobileType) { if (string.IsNullOrEmpty(name)) { return(true); } var args = new List <object> { new { Name = name, Id = id, MobileType = mobileType } }; var sbWhere = new StringBuilder("SELECT COUNT(*) FROM VersionManagement "); sbWhere.AppendFormat(" Where RowStatus = 1 "); sbWhere.AppendFormat(" and Name = @Name "); sbWhere.AppendFormat(" and MobileType = @MobileType "); if (id > 0) { sbWhere.Append(" and Id <> @Id "); } WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComVersionManagementDal.cs", ForUse = "查询", FunName = "NameExists" }; return(WriteDatabase.ExecuteScalar <int>(sbWhere.ToString(), args.ToArray()) > 0); }
/// <summary> /// 查询 /// </summary> /// <param name="search">查询实体</param> /// <returns></returns> public Page <ComVersionManagementEntity> GetSearchResult(ComVersionManagementEntity search) { var args = new List <object>(); var sbWhere = new StringBuilder(); sbWhere.AppendFormat(" Where RowStatus=1 "); if (search.MobileType > 0) { sbWhere.Append(Database.GetFormatSql <ComVersionManagementEntity>(" and {0}=@MobileType ", t => t.MobileType)); args.Add(new { MobileType = search.MobileType }); } //排序 sbWhere.AppendFormat(" order by Id desc"); WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComVersionManagementDal.cs", ForUse = "查询", FunName = "GetSearchResult" }; return(WriteDatabase.Page <ComVersionManagementEntity>(search.page.CurrentPage, search.page.PageSize, sbWhere.ToString(), args.ToArray())); }
/// <summary> /// 验证帐号ID是否存在 /// </summary> /// <param name="id">ID</param> /// <param name="accountId">帐号ID</param> /// <returns></returns> public bool AccountIdExists(string accountId, int id) { if (string.IsNullOrEmpty(accountId)) { return(true); } var args = new List <object> { new { p1 = accountId, p2 = id } }; var sbWhere = new StringBuilder("SELECT COUNT(*) FROM ComApiAccount "); sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>("Where {0}=1 ", t => t.ROWSTATUS)); sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>(" and {0} = @p1 ", t => t.ACCOUNTID)); if (id > 0) { sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>(" and {0} <> @p2 ", t => t.ID)); } WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComApiAccountDal.cs", ForUse = "验证帐号ID是否存在", FunName = "AccountIdExists" }; return(WriteDatabase.ExecuteScalar <int>(sbWhere.ToString(), args.ToArray()) > 0); }
/// <summary> /// 查询 /// </summary> /// <param name="search">查询实体</param> /// <returns></returns> public Page <ComApiAccountEntity> GetSearchResult(ComApiAccountEntity search) { var sbWhere = new StringBuilder(); var args = new List <object>(); sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>("Where {0}=1 ", u => u.ROWSTATUS)); if (!string.IsNullOrEmpty(search.ACCOUNTNAME)) { sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>(" and {0} LIKE '%'+@p1+'%'", t => t.ACCOUNTNAME)); args.Add(new { p1 = search.ACCOUNTNAME }); } //排序 sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>(" order by {0} desc", t => t.ID)); WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComApiAccountDal.cs", ForUse = "查询", FunName = "GetSearchResult" }; return(WriteDatabase.Page <ComApiAccountEntity>(search.page.CurrentPage, search.page.PageSize, sbWhere.ToString(), args.ToArray())); }
/// <summary> /// 直接查询list /// add by lpl /// 2019-1-2 /// </summary> /// <returns></returns> public List <GongGaoMangerEntity> QueryList() { var strSql = new StringBuilder("SELECT * FROM dbo.GongWenManger WHERE 1=1"); var args = new List <object>(); return(WriteDatabase.Query <GongGaoMangerEntity>(strSql.ToString(), args.ToArray()).ToList()); }
/// <summary> /// 更新实体 /// Add BY lxy on 2013-01-08 /// </summary> /// <param name="item">实体</param> /// <returns></returns> public bool PersistUpdatedItem(T item) { WriteDatabase.Comment = new StringExtension.SqlComment { Author = String.Format("系统项目组-{0}", ""), FileName = @"Persistance\Base\BasePersistance.cs", ForUse = "更新实体加版本控制", FunName = "PersistUpdatedItem" }; return(WriteDatabase.Update(item) > 0); }
/// <summary> /// 删除实体 /// </summary> /// <param name="key">实体</param> /// <returns>成功为true,错误为false</returns> public bool PersistDeletedItem(object key) { WriteDatabase.Comment = new StringExtension.SqlComment() { Author = String.Format("系统项目组-{0}", ""), FileName = @"Persistance\Base\BasePersistance.cs", ForUse = "删除实体", FunName = "PersistDeletedItem" }; return(WriteDatabase.Delete <T>(key) > 0); }
/// <summary> /// 新建实体 /// </summary> /// <param name="item"> /// 实体 /// </param> /// <returns> /// 数据库中记录ID /// </returns> public bool PersistNewItem(T item) { WriteDatabase.Comment = new StringExtension.SqlComment() { Author = String.Format("系统项目组-{0}", ""), FileName = @"Persistance\Base\BasePersistance.cs", ForUse = "新建实体", FunName = "PersistNewItem" }; return(!string.IsNullOrEmpty(WriteDatabase.Insert(item).ToString())); }
/// <summary> /// 直接查询list /// add by lpl /// 2019-1-2 /// </summary> /// <returns></returns> public List <GongGaoDetialEntity> QueryList(string userid = null) { var strSql = new StringBuilder("SELECT * FROM dbo.GongGaoDetial WHERE 1=1"); var args = new List <object>(); if (string.IsNullOrEmpty(userid)) { strSql.AppendFormat(" UserId = '{0}'", userid); } return(WriteDatabase.Query <GongGaoDetialEntity>(strSql.ToString(), args.ToArray()).ToList()); }
/// <summary> /// 直接查询list,不分页 /// ad by lpl /// 2019-1-3 /// </summary> /// <returns></returns> public List <PowerItemEntity> QueryList(PowerItemEntity powerItemEntity) { var strSql = new StringBuilder(@"SELECT Id, Item, CFZL, CFYJ, CFYJYW, WFFG, WFFGYW, (SELECT RsKey FROM dbo.ComResource WHERE ParentId = '0058' AND RsValue = LX) AS LX,LX AS LXValue FROM dbo.PowerItem WHERE RowStatus = 1 "); var args = new List <object>(); if (!string.IsNullOrEmpty(powerItemEntity.LX)) { strSql.AppendFormat("AND LX = '{0}'", powerItemEntity.LX); } return(WriteDatabase.Query <PowerItemEntity>(strSql.ToString(), args.ToArray()).ToList()); }
/// <summary> /// 公文管理表 /// 分页查询 /// add by lpl /// 2019-1-2 /// 效率为什么这么慢??? /// </summary> /// <param name="page"></param> /// <param name="pageSize"></param> /// <returns></returns> public Page <GongGaoMangerEntity> Query(long page, long pageSize) { try { var args = new List <object>(); var strSql = new StringBuilder(); strSql.Append(Database.GetFormatSql <GongGaoMangerEntity>(" Where {0} = @p1", t => t.RowStatus)); args.Add(new { p1 = 1 }); return(WriteDatabase.Page <GongGaoMangerEntity>(page, pageSize, strSql.ToString(), args.ToArray())); } catch (Exception e) { throw e; } }
/// <summary> /// 条件搜索 /// </summary> /// <param name="entity"></param> /// <param name="starttime"></param> /// <param name="endtime"></param> /// <returns></returns> public List <DocumentNotificationEntity> SearchQuery(DocumentNotificationEntity entity, string limittime = null, string jieshouGuid = null) { try { var strSql = new StringBuilder("SELECT * FROM dbo.DocumentNotification WHERE 1=1"); //标题搜索 if (!string.IsNullOrEmpty(entity.Title)) { strSql.AppendFormat("AND Title LIKE '%{0}%'", entity.Title); } //时间范围搜索 if (!string.IsNullOrEmpty(limittime)) { string[] arryTime = limittime.Split('至'); string startTime = arryTime[0]; string endTime = arryTime[1]; strSql.AppendFormat("AND ReleaseTime > '{0}' AND ReleaseTime < '{1}'", startTime, endTime); } //通告类型 if (!string.IsNullOrEmpty(entity.Category)) { strSql.AppendFormat("AND Category = '{0}'", entity.Category); } if (!string.IsNullOrEmpty(jieshouGuid)) { strSql.AppendFormat("AND Id IN ({0})", jieshouGuid); } var args = new List <object>(); return(WriteDatabase.Query <DocumentNotificationEntity>(strSql.ToString(), args.ToArray()).ToList()); } catch (Exception e) { throw e; } }
/// <summary> /// 批量删除 /// </summary> /// <param name="ids">Id集合(以,分隔)</param> /// <returns></returns> public bool BatchDelete(string ids) { if (string.IsNullOrEmpty(ids) || ids.Trim().Split(',').Length.Equals(0)) { return(false); } var sbSql = new StringBuilder(); sbSql.AppendFormat("UPDATE [ComApiAccount] SET [RowStatus] =0 WHERE [Id] IN ({0})", ids.Trim(',')); WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComApiAccountDal.cs", ForUse = "批量删除", FunName = "BatchDelete" }; return(WriteDatabase.Execute(sbSql.ToString()) > 0); }
/// <summary> /// 根据接口账号获取实体 /// </summary> /// <param name="accountId">帐号ID</param> /// <returns></returns> public ComApiAccountEntity GetComApiAccount(string accountId) { var sbWhere = new StringBuilder(); var args = new List <object> { new { AccountId = accountId } }; sbWhere.Append(Database.GetFormatSql <ComApiAccountEntity>("WHERE {0}=1 ", t => t.ROWSTATUS)); sbWhere.AppendFormat(" AND AccountId =@AccountId"); WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComApiAccountDal.cs", ForUse = "根据接口账号获取实体", FunName = "GetComApiAccount" }; return(WriteDatabase.FirstOrDefault <ComApiAccountEntity>(sbWhere.ToString(), args.ToArray())); }
/// <summary> /// 验证文件版本号 /// </summary> /// <param name="fileVersionNo">验证的文件版本号</param> /// <param name="mobileType">手机类型</param> /// <returns></returns> public bool FileVersionExists(string fileVersionNo, int mobileType) { var args = new List <object> { new { FileVersion = fileVersionNo, MobileType = mobileType } }; var sbWhere = new StringBuilder("SELECT COUNT(*) FROM ComVersionManagement "); sbWhere.AppendFormat(" Where RowStatus=1 "); sbWhere.AppendFormat(" and FileVersion = @FileVersion "); sbWhere.AppendFormat(" and MobileType = @MobileType "); WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComVersionManagementDal.cs", ForUse = "验证文件版本号", FunName = "FileVersionExists" }; return(WriteDatabase.ExecuteScalar <int>(sbWhere.ToString(), args.ToArray()) > 0); }
/// <summary> /// 根据手机类型获取最新版本信息 /// </summary> /// <returns></returns> public ComVersionManagementEntity GetNewVersionInfo(int mobileType) { var strsql = new StringBuilder(); strsql.AppendFormat( @"SELECT TOP 1 * FROM ComVersionManagement WITH(NOLOCK) Where RowStatus=1 AND MobileType={0} ORDER BY VersionNo DESC", mobileType); WriteDatabase.Comment = new StringExtension.SqlComment { Author = "", FileName = "ComVersionManagementDal.cs", ForUse = "根据手机类型获取最新版本信息", FunName = "GetNewVersionInfo" }; var dt = WriteDatabase.Query(strsql.ToString()); var list = ConvertListHelper <ComVersionManagementEntity> .ConvertToList(dt); return(list.Any() ? list[0] : new ComVersionManagementEntity()); }
/// <summary> /// 数据查询 /// </summary> /// <param name="search"></param> /// <param name="expressions"></param> /// <returns></returns> public List <DocumentNotificationEntity> GetAllResult(DocumentNotificationEntity search) { var sbWhere = new StringBuilder(); sbWhere.Append("Where ROWSTATUS = 1 "); var args = new List <object>(); if (!string.IsNullOrEmpty(search.Id)) { sbWhere.Append(Database.GetFormatSql <DocumentNotificationEntity>(" and {0} = @p1 ", t => t.Id)); args.Add(new { p1 = search.Id }); } if (!string.IsNullOrEmpty(search.Title)) { sbWhere.Append(Database.GetFormatSql <DocumentNotificationEntity>(" and Title like @p1 ")); args.Add(new { p1 = "%" + search.Title + "%" }); } sbWhere.Append(" ORDER BY CreateOn"); return(WriteDatabase.Query <DocumentNotificationEntity>(sbWhere.ToString(), args.ToArray()).ToList()); }
/// <summary> /// 结束事务 /// </summary> public void AbortTransaction() { WriteDatabase.AbortTransaction(); }
/// <summary> /// 提交事务 /// </summary> public void CommitTransaction() { WriteDatabase.CompleteTransaction(); }
/// <summary> /// 开始事务 /// </summary> public void BeginTransaction() { WriteDatabase.BeginTransaction(); }