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