/// <summary>
/// 通过lambda表达式的方式执行数据库事务
/// </summary>
public static T ExecuteTransaction <T>(this IPersistBroker broker, Func <T> func, string transId = null)
{
try
{
broker.DbClient.Open();
broker.DbClient.BeginTransaction();
var t = default(T);
if (func != null)
{
t = func();
}
broker.DbClient.CommitTransaction();
return(t);
}
catch (Exception ex)
{
broker.DbClient.Rollback();
throw ex;
}
finally
{
broker.DbClient.Close();
}
}
/// <summary>
/// 透过DataConnect生成PersistBroker
/// </summary>
/// <returns></returns>
public IPersistBroker GetPersistBroker(RptViewDataConnect dataConnect)
{
if (dataConnect == null)
{
return(null);
}
IPersistBroker persistBroker = null;
string strPassword = BenQGuru.eMES.Common.Helper.EncryptionHelper.DESDecryption(dataConnect.Password);
if (dataConnect.ServerType == DatabaseType.SQLServer)
{
string strConn = string.Format("Data Source={0};User ID={1};Password={2};Initial Catalog={3};", dataConnect.ServiceName, dataConnect.UserName, strPassword, dataConnect.DefaultDatabase);
SqlPersistBroker sql = new SqlPersistBroker(strConn);
_connection = sql.Connection;
persistBroker = new BenQGuru.eMES.Common.PersistBroker.SqlPersistBroker(strConn);
}
else if (dataConnect.ServerType == DatabaseType.Oracle)
{
string strConn = string.Format("Provider=OraOLEDB.Oracle;Data Source={0};User ID={1};Password={2};Persist Security Info=True;", dataConnect.ServiceName, dataConnect.UserName, strPassword);
OLEDBPersistBroker ole = new OLEDBPersistBroker(strConn);
_connection = ole.Connection;
persistBroker = new BenQGuru.eMES.Common.PersistBroker.OLEDBPersistBroker(strConn);
}
return(persistBroker);
}
/// <summary>
/// 检查用户信息
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
private void CheckUserInfo(BaseEntity entity, IPersistBroker broker)
{
var allowUpdateRole = new SysRoleService(broker).AllowCreateOrUpdateRole(entity["roleid"].ToString());
AssertUtil.CheckBoolean <SpException>(!allowUpdateRole, $"你没有权限修改角色为[{entity["roleidName"]}]", "2ABD2CBA-A7CB-4F61-841F-7CD4E6C1BD69");
AssertUtil.CheckBoolean <SpException>(entity.Id == "00000000-0000-0000-0000-000000000000", "系统管理员信息禁止更新", "");
}
/// <summary>
/// 权限差选
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="broker"></param>
/// <param name="sql"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public static IList <T> FilteredRetrieveMultiple <T>(this IPersistBroker broker, string sql, Dictionary <string, object> paramList = null) where T : BaseEntity, new()
{
var sysEntity = EntityCache.GetEntity(new T().EntityName);
AssertUtil.CheckBoolean <InvalidCredentialException>(!AuthAccess.CheckReadAccess(sysEntity.Id), $"用户没有实体[{sysEntity.name}]的查询权限", "451FC4BA-46B2-4838-B8D0-69617DFCAF39");
return(broker.RetrieveMultiple <T>(sql, paramList));
}
/// <summary>
/// 权限删除
/// </summary>
/// <param name="broker"></param>
/// <param name="entityName"></param>
/// <param name="id"></param>
/// <returns></returns>
public static int FilteredDelete(this IPersistBroker broker, string entityName, string id)
{
var sysEntity = EntityCache.GetEntity(entityName);
AssertUtil.CheckBoolean <InvalidCredentialException>(!AuthAccess.CheckDeleteAccess(sysEntity.Id), $"用户没有实体[{sysEntity.name}]的删除权限", "451FC4BA-46B2-4838-B8D0-69617DFCAF39");
return(broker.Delete(entityName, id));
}
/// <summary>
/// 权限查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="broker"></param>
/// <param name="id"></param>
/// <returns></returns>
public static T FilteredRetrieve <T>(this IPersistBroker broker, string id) where T : BaseEntity, new()
{
var sysEntity = EntityCache.GetEntity(new T().EntityName);
AssertUtil.CheckBoolean <InvalidCredentialException>(!AuthAccess.CheckReadAccess(sysEntity.Id), $"用户没有实体[{sysEntity.name}]的查询权限", "451FC4BA-46B2-4838-B8D0-69617DFCAF39");
return(broker.Retrieve <T>(id));
}
/// <summary>
/// 权限更新
/// </summary>
/// <param name="broker"></param>
/// <param name="entity"></param>
/// <returns></returns>
public static int FiltededUpdate(this IPersistBroker broker, BaseEntity entity)
{
var sysEntity = EntityCache.GetEntity(entity.EntityName);
AssertUtil.CheckBoolean <InvalidCredentialException>(!AuthAccess.CheckWriteAccess(sysEntity.Id), $"用户没有实体[{sysEntity.name}]的更新权限", "451FC4BA-46B2-4838-B8D0-69617DFCAF39");
return(broker.Update(entity));
}
private void CreateOrUpdateMenu(IPersistBroker broker, classification data)
{
var menu = broker.Retrieve <sys_menu>("SELECT * FROM sys_menu WHERE router = @code", new Dictionary <string, object>()
{
{ "@code", $"blogs/{data.code}" }
});
if (menu != null)
{
menu.menu_Index = data.index;
menu.name = data.name;
broker.Update(menu);
}
else
{
menu = new sys_menu()
{
Id = Guid.NewGuid().ToString(),
name = data.name,
parentid = "8201EFED-76E2-4CD1-A522-4803D52D4D92",
parentIdName = "博客管理",
router = $"blogs/{data.code}",
menu_Index = data.index,
stateCode = 1,
stateCodeName = "启用"
};
broker.Create(menu);
}
}
private void Init()
{
//_persistBroker = PersistBrokerManager.PersistBroker(@"Provider=Microsoft.Jet.OLEDB.4.0;Password=;Data Source=" + this.FullPath + ";Persist Security Info=True", null);
//add by klaus
_persistBroker = PersistBrokerManager.PersistBroker(@"Provider=Microsoft.ACE.OLEDB.12.0;Password=;Data Source=" + this.FullPath + ";Persist Security Info=False", null);
//end
_domainDataProvider = DomainDataProviderManager.DomainDataProvider(_persistBroker, null);
_languageHashtable = new System.Collections.Hashtable();
this.CacheLanguage();
}
/// <summary>
/// 批量创建或更新
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="broker"></param>
/// <param name="dataList"></param>
public static void BulkCreateOrUpdate <TEntity>(this IPersistBroker broker, List <TEntity> dataList, List <string> updateFieldList = null) where TEntity : BaseEntity, new()
{
if (dataList.IsEmpty())
{
return;
}
var client = broker.DbClient;
var mainKeyName = new TEntity().MainKeyName; // 主键
var tableName = new TEntity().EntityName; // 表名
// 1. 创建临时表
var tempTableName = client.CreateTemporaryTable(tableName);
// 2. 查询临时表结构
var dt = client.Query($"SELECT * FROM {tempTableName}");
// 3. 拷贝数据到临时表
client.BulkCopy(dataList.ToDataTable(dt.Columns), tempTableName);
// 4. 获取更新字段
if (updateFieldList.IsEmpty())
{
updateFieldList = new List <string>();
foreach (DataColumn column in dt.Columns)
{
// 主键去除
if (!column.ColumnName.Equals(mainKeyName, StringComparison.InvariantCultureIgnoreCase))
{
updateFieldList.Add(column.ColumnName);
}
}
}
// 5. 拼接Set语句
var updateFieldSql = updateFieldList.Select(item => string.Format(" {1} = {0}.{1} ", tempTableName, item)).Aggregate((a, b) => a + " , " + b);
// 6. 更新
client.Execute($@"
UPDATE {tableName}
SET {updateFieldSql} FROM {tempTableName}
WHERE {tableName}.{mainKeyName} = {tempTableName}.{mainKeyName}
AND {tempTableName}.{mainKeyName} IS NOT NULL
");
// 7. 新增
client.Execute($@"
INSERT INTO {tableName}
SELECT * FROM {tempTableName}
WHERE NOT EXISTS(SELECT 1 FROM {tableName} WHERE {tableName}.{mainKeyName} = {tempTableName}.{mainKeyName})
AND {tempTableName}.{mainKeyName} IS NOT NULL
");
// 8. 删除临时表
client.DropTable(tempTableName);
}
/// <summary>
/// 删除用户认证信息
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
private void DeleteAuthInfo(BaseEntity entity, IPersistBroker broker)
{
var sql = @"
DELETE FROM auth_user WHERE user_infoid = @id
";
broker.Execute(sql, new Dictionary <string, object>()
{
{ "@id", entity["user_infoId"]?.ToString() }
});
}
/// <summary>
/// 执行SQL查询，返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet ExecuteSql(string sql)
{
IPersistBroker persistBroker = this.GetPersistBroker(_dataConnect);
if (persistBroker == null)
{
return(null);
}
DataSet ds = new DataSet();
ds = persistBroker.Query(sql);
return(ds);
}
/// <summary>
/// 批量创建
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="broker"></param>
/// <param name="dataList"></param>
public static void BulkCreate <TEntity>(this IPersistBroker broker, List <TEntity> dataList) where TEntity : BaseEntity, new()
{
var client = broker.DbClient;
if (dataList.IsEmpty())
{
return;
}
var tableName = dataList[0].EntityName;
var dt = client.Query($"select * from {tableName}");
BulkCreate(broker, dataList.ToDataTable(dt.Columns), tableName);
}
/// <summary>
/// 清除用户权限信息缓存
/// </summary>
public static void Clear(IPersistBroker broker)
{
UserPrivliege.Clear();
ServiceContainer.ResolveAll <IRole>().Each(item =>
{
(item as BasicRole).Broker = broker;
item.ClearCache();
MemoryCacheUtil.RemoveCacheItem(item.GetRoleKey);
MemoryCacheUtil.Set(item.GetRoleKey, new RolePrivilegeModel()
{
Role = item.GetSysRole(), Privileges = item.GetRolePrivilege()
}, 3600 * 12);
});
}
public void Execute(string id)
{
if (Broker == null)
{
Broker = PersistBrokerFactory.GetPersistBroker();
}
Broker.ExecuteTransaction(() =>
{
blog data = Broker.Retrieve <blog>(id);
var contentUrl = $"{SystemConfig.Config.Protocol}://{SystemConfig.Config.Domain}/#/blog/{id}";
AssertUtil.CheckIsNullOrEmpty <SpException>(data.surfaceid, "请上传博客封面", "4365FB1F-2EE7-40CF-852C-F6CFA71E8DE2");
// 如果封面素材未上传则创建封面素材
string mediaId = Broker.Retrieve <wechat_material>("SELECT * FROM wechat_material WHERE sys_fileid = @id", new Dictionary <string, object>()
{
{ "@id", data.surfaceid }
})?.media_id;
if (string.IsNullOrEmpty(mediaId))
{
mediaId = new WeChatMaterialService(Broker).CreateData(MaterialType.image, data.big_surfaceid)?.media_id;
}
// 未创建图文素材则创建，已创建则更新
if (string.IsNullOrEmpty(data.wechat_newsid))
{
data.wechat_newsid = new WeChatNewsService(Broker).CreateData(data.title, mediaId, data.createdByName, "", true, data.html_content, contentUrl, true, false);
}
else
{
// 如果图文素材已经被删除，则创建
var news = Broker.Retrieve <wechat_news>(data.wechat_newsid);
if (news == null)
{
data.wechat_newsid = new WeChatNewsService(Broker).CreateData(data.title, mediaId, data.createdByName, "", true, data.html_content, contentUrl, true, false);
}
else
{
news.html_content = data.html_content;
news.thumb_media_id = mediaId;
news.content_source_url = contentUrl;
new WeChatNewsService(Broker).UpdateData(news);
}
}
// 图文素材id回写到博客
Broker.Update(data);
});
}
public static IDomainDataProvider DomainDataProvider(IPersistBroker persistBroker, System.Globalization.CultureInfo cultureInfo)
{
if (cultureInfo == null)
{
cultureInfo = new System.Globalization.CultureInfo("en-US", false);
}
if (persistBroker == null) //目前只返回sqlserver的privider
{
return(new SQLDomainDataProvider(persistBroker, cultureInfo));
}
else
{
return(new SQLDomainDataProvider(persistBroker, cultureInfo));
}
}
public static IDomainDataProvider DomainDataProvider(IPersistBroker persistBroker, System.Globalization.CultureInfo cultureInfo)
{
if (cultureInfo == null)
{
cultureInfo = new System.Globalization.CultureInfo("en-US", false);
}
if (persistBroker == null)
{
return(new SQLDomainDataProvider(PersistBrokerManager.PersistBroker(cultureInfo), cultureInfo));
}
else
{
return(new SQLDomainDataProvider(persistBroker, cultureInfo));
}
}
/// <summary>
/// 更新用户信息
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
private void UpdateAuthInfo(BaseEntity entity, IPersistBroker broker)
{
var sql = @"
SELECT * FROM auth_user
WHERE user_infoid = @id
";
var authInfo = broker.Retrieve <auth_user>(sql, new Dictionary <string, object>()
{
{ "@id", entity["user_infoId"]?.ToString() }
});
AssertUtil.CheckNull <SpException>(authInfo, "用户Id不能为空", "C37CCF94-6B27-4BF4-AF29-DBEDC9E53E5D");
authInfo.name = entity["name"]?.ToString();
authInfo.roleid = entity["roleid"]?.ToString();
authInfo.roleidName = entity["roleidName"]?.ToString();
new AuthUserService(broker).UpdateData(authInfo);
}
/// <summary>
/// 获取缺失实体权限
/// </summary>
/// <returns></returns>
protected IEnumerable <sys_entity> GetMissingEntityPrivileges(IPersistBroker broker)
{
var role = GetSysRole();
var paramList = new Dictionary <string, object>()
{
{ "@id", role.Id }
};
var sql = @"
SELECT * FROM sys_entity
WHERE sys_entityid NOT IN (
SELECT objectid FROM sys_role_privilege
WHERE object_type = 'sys_entity' AND sys_roleid = @id
)
";
return(broker.RetrieveMultiple <sys_entity>(sql, paramList));
}
/// <summary>
/// 创建用户认证信息
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
private void CreateAuthInfo(BaseEntity entity, IPersistBroker broker)
{
var authInfo = new auth_user()
{
auth_userId = entity.GetAttributeValue <string>("user_infoId"),
name = entity.GetAttributeValue <string>("name"),
code = entity.GetAttributeValue <string>("code"),
password = SystemConfig.Config.DefaultPassword,
user_infoid = entity.GetAttributeValue <string>("user_infoId"),
roleid = entity.GetAttributeValue <string>("roleid"),
roleidName = entity.GetAttributeValue <string>("roleidName"),
is_lock = false,
is_lockName = "否"
};
new AuthUserService(broker).CreateData(authInfo);
}
/// <summary>
/// 检查数据库连接是否能连接
/// </summary>
/// <param name="dataConnect"></param>
/// <returns></returns>
public bool CheckDataConnect(RptViewDataConnect dataConnect)
{
IPersistBroker persistBroker = this.GetPersistBroker(dataConnect);
if (persistBroker == null)
{
return(false);
}
try
{
_connection.Open();
_connection.Close();
return(true);
}
catch
{
return(false);
}
}
/// <summary>
/// 通过lambda表达式的方式执行数据库事务
/// </summary>
public static void ExecuteTransaction(this IPersistBroker broker, Action func)
{
try
{
broker.DbClient.Open();
broker.DbClient.BeginTransaction();
func?.Invoke();
broker.DbClient.CommitTransaction();
}
catch (Exception ex)
{
broker.DbClient.Rollback();
throw ex;
}
finally
{
broker.DbClient.Close();
}
}
/// <summary>
/// 批量创建数据
/// </summary>
/// <param name="broker"></param>
/// <param name="dataTable"></param>
/// <param name="tableName"></param>
public static void BulkCreate(this IPersistBroker broker, DataTable dataTable, string tableName)
{
if (dataTable.IsEmpty())
{
return;
}
var client = broker.DbClient;
// 1. 创建临时表
var tempName = client.CreateTemporaryTable(tableName);
// 2. 拷贝数据到临时表
client.BulkCopy(dataTable, tempName);
// 3. 将临时表数据插入到目标表中
client.Execute(string.Format("INSERT INTO {0} SELECT * FROM {1} WHERE NOT EXISTS(SELECT 1 FROM {0} WHERE {0}.{2}id = {1}.{2}id)", tableName, tempName, tableName));
// 4. 删除临时表
client.DropTable(tempName);
}
/// <summary>
/// 执行SQL查询，返回Schema
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet QuerySchemaSql(string sql)
{
IPersistBroker persistBroker = this.GetPersistBroker(_dataConnect);
if (persistBroker == null)
{
return(null);
}
DataSet ds = new DataSet();
string strSql = "";
if (_dataConnect.ServerType == DatabaseType.Oracle)
{
//strSql = "select * from (" + sql + ") where rownum=1 ";
strSql = "select * from (" + sql + ") where 1<>1 ";
}
else
{
strSql = "select top 1 * from (" + sql + ") TempTableName ";
}
ds = persistBroker.Query(strSql);
return(ds);
}
/// <summary>
/// 重复字段检查
/// </summary>
/// <param name="entity"></param>
/// <param name="broker"></param>
private void CheckDuplicate(BaseEntity entity, IPersistBroker broker)
{
var attrs = entity.GetType().GetCustomAttributes(typeof(KeyAttributesAttribute), false);
if (attrs.Length == 0)
{
return;
}
attrs.Select(item => item as KeyAttributesAttribute)
.Each(item =>
{
if (item.AttributeList == null || item.AttributeList.Count == 0)
{
return;
}
var paramList = new Dictionary <string, object>()
{
{ "@id", entity.Id }
};
var sqlParam = new List <string>()
{
$" AND {entity.EntityName}Id <> @id"
}; // 排除自身
item.AttributeList.Distinct().Each(attr =>
{
var keyValue = ParseSqlUtil.GetSpecialValue($"@{attr}", entity[attr]);
sqlParam.Add($" AND {attr} = {keyValue.name}");
paramList.Add(keyValue.name, keyValue.value);
});
var sql = string.Format(@"SELECT {0}Id FROM {0} WHERE 1 = 1 ", entity.EntityName) + string.Join("", sqlParam);
AssertUtil.CheckBoolean <SpException>(broker.Query <string>(sql, paramList)?.Count() > 0, item.RepeatMessage, "7293452C-AFCA-408D-9EBD-B1CECD206A7D");
});
}
public WeChatKeywordsService(IPersistBroker broker)
{
this._context = new EntityContext <wechat_keywords>(broker);
}
/// <summary>
/// 查询数量
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="broker"></param>
/// <param name="sql"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public static int QueryCount(this IPersistBroker broker, string sql, IDictionary <string, object> paramList = null)
{
return(ConvertUtil.ConToInt(broker.ExecuteScalar(sql, paramList)));
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="broker"></param>
/// <param name="sql"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public static IEnumerable <T> Query <T>(this IPersistBroker broker, string sql, IDictionary <string, object> paramList = null)
{
return(broker.DbClient.Query <T>(sql, paramList));
}
public ClassificationService(IPersistBroker broker)
{
this._context = new EntityContext <classification>(broker);
}
/// <summary>
/// 查询
/// </summary>
/// <param name="broker"></param>
/// <param name="sql"></param>
/// <param name="paramList"></param>
/// <returns></returns>
public static DataTable Query(this IPersistBroker broker, string sql, IDictionary <string, object> paramList = null)
{
return(broker.DbClient.Query(sql, paramList));
}
