public static string[] QueryGuidsByCondition(IConnectiveSqlClause condition) { QueryCondition qc = new QueryCondition(); qc.FromClause = "WF.PROCESS_INSTANCES (NOLOCK)"; qc.SelectFields = "INSTANCE_ID"; qc.OrderByClause = "CREATE_TIME DESC"; qc.WhereClause = condition.ToSqlString(TSqlBuilder.Instance); string sql = string.Format("SELECT {0} FROM {1} WHERE 1 = 1 {2} {3} ORDER BY {4}", qc.SelectFields, qc.FromClause, qc.WhereClause.IsNotEmpty() ? " AND " + qc.WhereClause : string.Empty, qc.GroupBy.IsNotEmpty() ? "GROUP BY " + qc.GroupBy : string.Empty, qc.OrderByClause); using (DbContext context = DbContext.GetContext(WorkflowSettings.GetConfig().ConnectionName)) { Database db = DatabaseFactory.Create(context); using (var dr = db.ExecuteReader(CommandType.Text, sql)) { List <string> guids = new List <string>(); while (dr.Read()) { guids.Add(dr.GetString(0)); } return(guids.ToArray()); } } }
public StartWorkflowJob LoadSingleData(IConnectiveSqlClause whereClause) { StartWorkflowJob result = null; if (whereClause.IsEmpty == false) { using (DbContext context = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SingleData_StartWorkflowJob, whereClause.AppendTenantCodeSqlClause(typeof(StartWorkflowJob)).ToSqlString(TSqlBuilder.Instance)), GetConnectionName())) { while (dr.Read()) { result = new StartWorkflowJob(); ORMapping.DataReaderToObject(dr, result); break; } } if (result != null) { result.InitJobBaseData(JobBaseAdapter.Instance.LoadSingleDataByJobID(whereClause)); } } } return(result); }
public SCAclMemberCollection LoadMembers(IConnectiveSqlClause condition, DateTime timePoint) { ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(condition, timePointBuilder); string sql = string.Format( "SELECT * FROM {0} WHERE {1} ORDER BY SortID", this.GetLoadingTableName(timePoint), connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); SCAclMemberCollection result = new SCAclMemberCollection(); AUCommon.DoDbAction(() => { using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName())) { ORMapping.DataReaderToCollection(result, reader); } } }); return(result); }
/// <summary> /// 按照Builder查询应用所包含的元素(角色和权限) /// </summary> /// <param name="builder"></param> /// <param name="includingDeleted"></param> /// <param name="timePoint"></param> /// <returns></returns> public SchemaObjectCollection QueryApplicationObjectsByBuilder(IConnectiveSqlClause builder, bool includingDeleted, DateTime timePoint) { builder.NullCheck("builder"); SchemaObjectCollection result = new SchemaObjectCollection(); if (builder.IsEmpty == false) { IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint, "SC.", "A.", "M."); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, extraBuilder); string resourcePath = "QueryApplicationObjects_Current.sql"; if (timePoint != DateTime.MinValue && includingDeleted == true) resourcePath = "QueryApplicationObjects.sql"; string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath)); string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView); } return result; }
/// <summary> /// /// </summary> /// <param name="ownerKeyBuilder"></param> /// <param name="mapping"></param> /// <param name="newObjs"></param> /// <param name="addCurrentTimeVar">是否添加@currentTime变量</param> /// <param name="ignoreProperties">需要忽略的属性</param> /// <returns></returns> public string ToUpdateCollectionSql(IConnectiveSqlClause ownerKeyBuilder, ORMappingItemCollection mapping, IEnumerable <T> newObjs, bool addCurrentTimeVar = true, params string[] ignoreProperties) { return(VersionStrategyUpdateSqlHelper.ConstructUpdateSql(null, (strB, context) => { ConnectiveSqlClauseCollection existedKeys = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or); foreach (T obj in newObjs) { if (obj != null) { if (strB.Length > 0) { strB.Append(TSqlBuilder.Instance.DBStatementSeperator); } this.PrepareSingleObjectUpdateSql(strB, obj, mapping, false, () => this.PrepareUpdateCollectionItemSql(obj, mapping, ignoreProperties), () => this.PrepareInsertSql(obj, mapping, ignoreProperties), ignoreProperties); WhereSqlClauseBuilder keyBuilder = GetExistedKeysBuilder(ownerKeyBuilder, obj, mapping); existedKeys.Add(keyBuilder); } } if (strB.Length > 0) { strB.Append(TSqlBuilder.Instance.DBStatementSeperator); } strB.Append(this.PrepareUpdateNotMatchedCollectionItemSql(ownerKeyBuilder, existedKeys, mapping)); }, addCurrentTimeVar)); }
public int GetMaxInnerSort(string id, string[] childSchemaTypes, DateTime timePoint) { IConnectiveSqlClause timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ParentID", id); InSqlClauseBuilder inSql = null; if (childSchemaTypes != null && childSchemaTypes.Length > 0) { inSql = new InSqlClauseBuilder("ChildSchemaType").AppendItem(childSchemaTypes); } ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(where, timeBuilder); if (inSql != null) { connectiveBuilder.Add(inSql); } string sql = "SELECT ISNULL(MAX(InnerSort), 0) AS C FROM " + GetMappingInfo().TableName + " WHERE " + connectiveBuilder.ToSqlString(TSqlBuilder.Instance); int result = (int)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName()); return(result); }
public InvokeWebServiceJob LoadSingleData(IConnectiveSqlClause whereClause) { InvokeWebServiceJob result = null; if (whereClause.IsEmpty == false) { using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SingleData_InvokeWebService, whereClause.ToSqlString(TSqlBuilder.Instance)), GetConnectionName())) { while (dr.Read()) { result = new InvokeWebServiceJob(); ORMapping.DataReaderToObject(dr, result); break; } } if (result != null) { XElementFormatter formatter = new XElementFormatter(); formatter.OutputShortType = WorkflowSettings.GetConfig().OutputShortType; XElement root = XElement.Parse(result.XmlData); result.SvcOperationDefs = (WfServiceOperationDefinitionCollection)formatter.Deserialize(root); result.InitJobBaseData(JobBaseAdapter.Instance.LoadSingleDataByJobID(whereClause)); } } } return(result); }
/// <summary> /// 根据<see cref="IConnectiveSqlClause"/>指定的条件和时间点载入对象 /// </summary> /// <param name="condition">表示条件的<see cref="IConnectiveSqlClause"/></param> /// <param name="timePoint">时间点 - 或 - <see cref="DateTime.MinValue"/>表示当前时间</param> /// <returns>一个<see cref="DESchemaObjectCollection"/>,包含条件指定的对象。</returns> public DESchemaObjectCollection Load(IConnectiveSqlClause condition, DateTime timePoint, bool loadNormalStatus = true) { var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); DESchemaObjectCollection result = new DESchemaObjectCollection(); if (condition.IsEmpty == false) { ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(condition, timePointBuilder); if (loadNormalStatus) { WhereSqlClauseBuilder statusBuilder = new WhereSqlClauseBuilder(); statusBuilder.AppendItem("Status", (int)SchemaObjectStatus.Normal); connectiveBuilder.Add(statusBuilder); } using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { VersionedObjectAdapterHelper.Instance.FillData(GetMappingInfo().TableName, connectiveBuilder, this.GetConnectionName(), reader => { result.LoadFromDataReader(reader); }); } } return(result); }
/// <summary> /// /// </summary> /// <param name="ownerKeyBuilder"></param> /// <param name="existedKeys"></param> /// <param name="mapping"></param> /// <returns></returns> protected virtual string PrepareUpdateNotMatchedCollectionItemSql(IConnectiveSqlClause ownerKeyBuilder, IConnectiveSqlClause existedKeys, ORMappingItemCollection mapping) { string innerSql = this.GetJoinedExistedCountSql(ownerKeyBuilder, existedKeys, mapping); string endTimeFieldName = GetPropertyFieldName("VersionEndTime", mapping); UpdateSqlClauseBuilder updateBuilder = new UpdateSqlClauseBuilder(); updateBuilder.AppendItem(endTimeFieldName, DBTimePointActionContext.CurrentTimeTSqlVarName, SqlClauseBuilderBase.EqualTo, true); WhereSqlClauseBuilder veBuilder = new WhereSqlClauseBuilder(); veBuilder.AppendItem(endTimeFieldName, DBTimePointActionContext.MaxVersionEndTime); veBuilder.AppendItem(GetPropertyFieldName("VersionStartTime", mapping), DBTimePointActionContext.CurrentTimeTSqlVarName, SqlClauseBuilderBase.NotEqualTo, true); ConnectiveSqlClauseCollection connective = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And, ownerKeyBuilder, veBuilder); string sql = string.Format("UPDATE {0} SET {1} FROM {0} A WHERE {2} AND ({3}) = 0", this.GetTableName(default(T), mapping), updateBuilder.ToSqlString(TSqlBuilder.Instance), connective.ToSqlString(TSqlBuilder.Instance), innerSql); return(sql); }
public JobCollection UPDLOCKLoadJobs(int batchCount, IConnectiveSqlClause whereClause) { JobCollection result = new JobCollection(); if (whereClause.IsEmpty == false) { using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty; Database db = DatabaseFactory.Create(context); using (IDataReader dr = db.ExecuteReader(CommandType.Text, string.Format(UPDLOCK_LOAD_JOBS, top, whereClause.AppendTenantCodeSqlClause(typeof(JobBase)).ToSqlString(TSqlBuilder.Instance)))) { ORMapping.DataReaderToCollection(result, dr); } } AfterLoad(result); } return(result); }
/// <summary> /// 按照Builder查询应用所包含的元素(角色和权限) /// </summary> /// <param name="builder"></param> /// <param name="includingDeleted"></param> /// <param name="timePoint"></param> /// <returns></returns> public SchemaObjectCollection QueryApplicationObjectsByBuilder(IConnectiveSqlClause builder, bool includingDeleted, DateTime timePoint) { builder.NullCheck("builder"); SchemaObjectCollection result = new SchemaObjectCollection(); if (builder.IsEmpty == false) { IConnectiveSqlClause extraBuilder = CreateStatusAndTimePointBuilder(includingDeleted, timePoint, "SC.", "A.", "M."); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, extraBuilder); string resourcePath = "QueryApplicationObjects_Current.sql"; if (timePoint != DateTime.MinValue && includingDeleted == true) { resourcePath = "QueryApplicationObjects.sql"; } string sqlTemplate = ResourceHelper.LoadStringFromResource(Assembly.GetExecutingAssembly(), string.Concat("MCS.Library.SOA.DataObjects.Security.Adapters.Templates.", resourcePath)); string sql = string.Format(sqlTemplate, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView); } return(result); }
/// <summary> /// 没有TenantCode查找单一Job /// </summary> /// <param name="whereClause"></param> /// <returns></returns> public JobBase LoadSingleDataByJobID(IConnectiveSqlClause whereClause) { JobBase result = null; if (whereClause.IsEmpty == false) { using (DbContext context = DbHelper.GetDBContext(GetConnectionName())) { //using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SINGLEATA_JOB, // whereClause.AppendTenantCodeSqlClause(typeof(JobBase)).ToSqlString(TSqlBuilder.Instance)), GetConnectionName())) //{ // while (dr.Read()) // { // result = new JobBase(); // ORMapping.DataReaderToObject(dr, result); // break; // } //} using (IDataReader dr = DbHelper.RunSqlReturnDR(string.Format(SINGLE_DATA_JOB, whereClause.ToSqlString(TSqlBuilder.Instance)), GetConnectionName())) { while (dr.Read()) { result = new JobBase(); ORMapping.DataReaderToObject(dr, result); break; } } } } return(result); }
/// <summary> /// 批量更新某些字段的值 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="action"></param> /// <param name="where"></param> /// <returns></returns> public static string BathUpdate <T>(IConnectiveSqlClause where, Action <UpdateSqlClauseBuilder> action) { var updateBuilder = new UpdateSqlClauseBuilder(); action(updateBuilder); return(String.Format("UPDATE {0} SET {1} WHERE {2}", ORMapping.GetMappingInfo <T>().TableName, updateBuilder.ToSqlString(TSqlBuilder.Instance), where.ToSqlString(TSqlBuilder.Instance))); }
/// <summary> /// /// </summary> /// <param name="obj"></param> /// <param name="mapping"></param> /// <param name="ignoreProperties">需要忽略的参数</param> /// <returns></returns> public virtual string PrepareUpdateSql(T obj, ORMappingItemCollection mapping, params string[] ignoreProperties) { IConnectiveSqlClause primaryKeyBuilder = this.PrepareWhereSqlBuilder(obj, mapping, ignoreProperties); UpdateSqlClauseBuilder updateBuilder = this.PrepareUpdateSqlBuilder(obj, mapping, ignoreProperties); return(string.Format("UPDATE {0} SET {1} WHERE {2}", GetTableName(obj, mapping), updateBuilder.ToSqlString(TSqlBuilder.Instance), primaryKeyBuilder.ToSqlString(TSqlBuilder.Instance))); }
private void BindGrid(IConnectiveSqlClause whereSqlClause) { LastQueryRowCount = -1; var whereCondition = (HtmlInputHidden)this.FindControlByID("whereCondition", true); whereCondition.Value = whereSqlClause.ToSqlString(TSqlBuilder.Instance); this.ProcessDescInfoDeluxeGrid.SelectedKeys.Clear(); this.ProcessDescInfoDeluxeGrid.PageIndex = 0; }
protected void ObjectDataSourceTask_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { if (this.Condition == null) { var condition = this.CurrentAdvancedSearchCondition; WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(condition, new AdjustConditionValueDelegate(AdjustQueryConditionValue)); this.Condition = new ConnectiveSqlClauseCollection(builder, this.GetCommonCondition(), this.search1.GetCondition()); } this.src1.Condition = this.Condition; }
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action<DataView> action) { StringBuilder strB = new StringBuilder(); strB.AppendFormat("SELECT * FROM {0} WHERE {1}", tableName, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataView view = DbHelper.RunSqlReturnDS(strB.ToString(), connectionName).Tables[0].DefaultView; if (action != null) action(view); }
internal AUSchemaRoleCollection LoadAUSchemaRoles(IConnectiveSqlClause condition, bool normalOnly, DateTime timePoint) { string sql = @"SELECT O.* FROM SC.SchemaObject O INNER JOIN SC.AUSchemaRoleSnapshot S ON O.ID = S.ID AND O.VersionStartTime = S.VersionStartTime AND O.SchemaType = S.SchemaType INNER JOIN SC.SchemaMembers R ON O.ID = R.MemberID AND O.SchemaType = R.MemberSchemaType WHERE "; var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "S."); var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O."); sql += new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, condition).ToSqlString(TSqlBuilder.Instance); return LoadSchemaObjects<AUSchemaRole, AUSchemaRoleCollection>(sql); }
/// <summary> /// 载入管理单元 /// </summary> /// <param name="condition">条件</param> /// <param name="timePoint">时间点</param> /// <returns></returns> /// <remarks>O为SchemaObject,S为AdminUnitSnapshot</remarks> internal SchemaObjectCollection LoadAU(IConnectiveSqlClause condition, DateTime timePoint) { //TODO:测试 string sql = @"SELECT O.* FROM SC.SchemaObject O INNER JOIN SC.AdminUnitSnapshot S ON O.ID = S.ID AND O.VersionStartTime = S.VersionStartTime AND O.SchemaType = S.SchemaType WHERE 1=1 AND "; var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "S."); var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O."); sql += new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, condition).ToSqlString(TSqlBuilder.Instance); return LoadSchemaObjects(sql); }
public SCOperationLogCollection Load(IConnectiveSqlClause sqlClause) { SCOperationLogCollection result = null; VersionedObjectAdapterHelper.Instance.FillData(GetMappingInfo().TableName, sqlClause, this.GetConnectionName(), (view) => { result = new SCOperationLogCollection(); ORMapping.DataViewToCollection(result, view); }); return result; }
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action<IDataReader> action) { StringBuilder strB = new StringBuilder(); strB.AppendFormat("SELECT * FROM {0} WHERE {1}", tableName, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); using (IDataReader reader = DbHelper.RunSqlReturnDR(strB.ToString(), connectionName)) { if (action != null) action(reader); } }
public SCOperationLogCollection Load(IConnectiveSqlClause sqlClause) { SCOperationLogCollection result = null; VersionedObjectAdapterHelper.Instance.FillData(GetMappingInfo().TableName, sqlClause, this.GetConnectionName(), (view) => { result = new SCOperationLogCollection(); ORMapping.DataViewToCollection(result, view); }); return(result); }
/// <summary> /// 更新集合对象中某些 /// </summary> /// <param name="obj"></param> /// <param name="mapping"></param> /// <param name="ignoreProperties">需要忽略的属性</param> /// <returns></returns> protected virtual string PrepareUpdateCollectionItemSql(T obj, ORMappingItemCollection mapping, string[] ignoreProperties) { IConnectiveSqlClause primaryKeyBuilder = ModifyTimeFieldsInWhereBuilder(obj, mapping, ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(obj, mapping)); WhereSqlClauseBuilder changedFieldsBuilder = ORMapping.GetWhereSqlClauseBuilderByChangedFields(obj, mapping, false); ConnectiveSqlClauseCollection connective = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And, primaryKeyBuilder, changedFieldsBuilder); UpdateSqlClauseBuilder updateBuilder = this.PrepareUpdateSqlBuilder(obj, mapping, ignoreProperties); return(string.Format("UPDATE {0} SET {1} WHERE {2}", GetTableName(obj, mapping), updateBuilder.ToSqlString(TSqlBuilder.Instance), connective.ToSqlString(TSqlBuilder.Instance))); }
private static ConnectiveSqlClauseCollection MergeConditionForRole(IConnectiveSqlClause where) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("R.SchemaType", "Roles"); builder.AppendItem("A.SchemaType", "Applications"); builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem("M0.Status", (int)SchemaObjectStatus.Normal); var versionRole = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R."); var versionApp = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("A."); var versionM0 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("M0."); return(new ConnectiveSqlClauseCollection(where, builder, versionRole, versionM0, versionApp)); }
internal SchemaObjectCollection LoadSubUnits(DateTime timePoint, IConnectiveSqlClause where) { string sql = @"SELECT O.* FROM SC.SchemaObject O INNER JOIN SC.AdminUnitSnapshot S ON O.ID = S.ID AND O.VersionStartTime = S.VersionStartTime AND O.SchemaType = S.SchemaType INNER JOIN SC.SchemaRelationObjects R ON O.ID = R.ObjectID AND O.SchemaType = R.ChildSchemaType WHERE 1=1 AND "; var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "S."); var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O."); var timeCondition3 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R."); sql += new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, timeCondition3, where).ToSqlString(TSqlBuilder.Instance); return LoadSchemaObjects(sql); }
/// <summary> /// 根据外界的builder加载数据 /// </summary> /// <param name="builder"></param> /// <param name="orderByBuilder"></param> /// <returns></returns> public virtual TCollection LoadByBuilder(IConnectiveSqlClause builder, OrderBySqlClauseBuilder orderByBuilder) { builder.NullCheck("builder"); TCollection result = null; PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("LoadByBuilder({0})", this.GetType().FullName), () => { result = InnerLoadByBuilder(builder.ToSqlString(TSqlBuilder.Instance), orderByBuilder, this.GetQueryMappingInfo()); }); return(result); }
/// <summary> /// /// </summary> /// <param name="ownerKeyBuilder"></param> /// <param name="data"></param> /// <param name="ignoreProperties">需要忽略的属性</param> public void UpdateCollectionInContext(IConnectiveSqlClause ownerKeyBuilder, IEnumerable <T> data, params string[] ignoreProperties) { ownerKeyBuilder.NullCheck("ownerKeyBuilder"); data.NullCheck("objs"); Dictionary <string, object> context = new Dictionary <string, object>(); SqlContextItem sqlContext = this.GetSqlContext(); this.BeforeInnerUpdateCollectionInContext(data, sqlContext, context); string sql = VersionStrategyUpdateSqlBuilder <T> .DefaultInstance.ToUpdateCollectionSql(ownerKeyBuilder, this.GetMappingInfo(), data, false, ignoreProperties); sqlContext.AppendSqlWithSperatorInContext(TSqlBuilder.Instance, sql); }
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action <DataView> action) { StringBuilder strB = new StringBuilder(); strB.AppendFormat("SELECT * FROM {0} WHERE {1}", tableName, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataView view = DbHelper.RunSqlReturnDS(strB.ToString(), connectionName).Tables[0].DefaultView; if (action != null) { action(view); } }
/// <summary> /// 根据条件,时间点载入视图 /// </summary> /// <param name="inBuilder">包含条件的<see cref="IConnectiveSqlClause"/></param> /// <param name="timePoint"></param> /// <returns></returns> public DataView Load(IConnectiveSqlClause inBuilder, DateTime timePoint) { var whereBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(inBuilder, whereBuilder); DataView result = null; VersionedObjectAdapterHelper.Instance.FillData("SC.SchemaObjectSnapshot", connectiveBuilder, this.GetConnectionName(), view => { result = view; }); return(result); }
public void FillData(string tableName, IConnectiveSqlClause connectiveBuilder, string connectionName, Action <IDataReader> action) { StringBuilder strB = new StringBuilder(); strB.AppendFormat("SELECT * FROM {0} WHERE {1}", tableName, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); using (IDataReader reader = DbHelper.RunSqlReturnDR(strB.ToString(), connectionName)) { if (action != null) { action(reader); } } }
/// <summary> /// 从Tenant上下文中获取TenantCode并且在某个条件子句添加租户编码的过滤字段 /// </summary> /// <param name="connective"></param> /// <param name="tenantCodeFieldName"></param> /// <returns></returns> public static ConnectiveSqlClauseCollection AppendTenantCodeSqlClause(this IConnectiveSqlClause connective, string tenantCodeFieldName = "TENANT_CODE") { ConnectiveSqlClauseCollection result = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And); if (connective != null) { WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendTenantCode(tenantCodeFieldName); result.Add(connective); result.Add(wBuilder); } return(result); }
private string GetJoinedExistedCountSql(IConnectiveSqlClause ownerKeyBuilder, IConnectiveSqlClause existedKeys, ORMappingItemCollection mapping) { HashSet <string> fields = MergeFields(ownerKeyBuilder.GetFields(), existedKeys.GetFields()); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); foreach (string field in fields) { builder.AppendItem("A." + field, "B." + field, SqlClauseBuilderBase.EqualTo, true); } string innerSql = this.GetInnerExistedObjsSql(ownerKeyBuilder, existedKeys, mapping); string sql = string.Format("SELECT COUNT(1) FROM ({0}) B WHERE {1}", innerSql, builder.ToSqlString(TSqlBuilder.Instance)); return(sql); }
/// <summary> /// 根据类型上的TenantRelativeObjectAttribute以及TenantContext.Current.Enabled决定是否在某个条件子句添加租户编码的过滤字段 /// </summary> /// <param name="connective"></param> /// <param name="type"></param> /// <returns></returns> public static ConnectiveSqlClauseCollection AppendTenantCodeSqlClause(this IConnectiveSqlClause connective, Type type) { ConnectiveSqlClauseCollection result = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And); if (connective != null) { WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendTenantCode(type); result.Add(connective); result.Add(wBuilder); } return(result); }
/// <summary> /// 得到更新当前快照的SQL /// </summary> /// <param name="srcTableName"></param> /// <param name="targetTableName"></param> /// <param name="builder"></param> /// <returns></returns> private string GetInternalUpdateCurrentSnapshotSql(string srcTableName, string targetTableName, IConnectiveSqlClause builder) { builder.NullCheck("builder"); StringBuilder strB = new StringBuilder(); if (builder.IsEmpty == false) { IConnectiveSqlClause timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(builder, timePointBuilder); string sql = string.Format("SELECT * FROM {0} WHERE Status = {1} AND {2}", srcTableName, (int)SchemaObjectStatus.Normal, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; if (table.Rows.Count > 0) { UpdateSqlClauseBuilder updateBuilder = new UpdateSqlClauseBuilder(); DataRowToBuilder(table.Rows[0], updateBuilder, "RowUniqueID"); InsertSqlClauseBuilder insertBuilder = new InsertSqlClauseBuilder(); DataRowToBuilder(table.Rows[0], insertBuilder); strB.AppendFormat("UPDATE {0} SET {1} WHERE {2}", targetTableName, updateBuilder.ToSqlString(TSqlBuilder.Instance), builder.ToSqlString(TSqlBuilder.Instance)); strB.Append(TSqlBuilder.Instance.DBStatementSeperator); strB.Append("IF @@ROWCOUNT = 0"); strB.Append(TSqlBuilder.Instance.DBStatementSeperator); strB.AppendFormat("INSERT INTO {0} {1}", targetTableName, insertBuilder.ToSqlString(TSqlBuilder.Instance)); } else { strB.AppendFormat("DELETE {0} WHERE {1}", targetTableName, builder.ToSqlString(TSqlBuilder.Instance)); } } return(strB.ToString()); }
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause builder) { UserTaskCollection result = new UserTaskCollection(); if (builder.IsEmpty == false) { string sql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { UserTask task = new UserTask(); ORMapping.DataRowToObject(row, task); result.Add(task); } } return result; }
/// <summary> /// 根据condition所提供的SQL查询条件查询 /// </summary> /// <param name="condition"></param> /// <param name="timePoint"></param> /// <returns></returns> public SCConditionCollection Load(IConnectiveSqlClause condition, DateTime timePoint) { ConnectiveSqlClauseCollection timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(condition, timePointBuilder); string sql = string.Format("SELECT * FROM {0} WHERE {1} ORDER BY SortID", this.GetMappingInfo().TableName, connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); SCConditionCollection result = new SCConditionCollection(); AUCommon.DoDbAction(() => { using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName())) { ORMapping.DataReaderToCollection(result, reader); } } }); return result; }