/// <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();
}
