protected override void OnBuildQueryCondition(QueryCondition qc) { qc.FromClause = "SC.OperationLog"; qc.SelectFields = "*"; if (string.IsNullOrEmpty(qc.OrderByClause)) qc.OrderByClause = "CreateTime DESC"; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); if (this.catelog.IsNotEmpty()) { builder.AppendItem("SchemaType", this.catelog); } if (this.operationType.IsNotEmpty()) { builder.AppendItem("OperationType", operationType); } if (builder.IsEmpty == false && qc.WhereClause.IsNotEmpty()) { qc.WhereClause += " AND "; } qc.WhereClause += builder.ToSqlString(TSqlBuilder.Instance); }
protected override void OnBuildQueryCondition(QueryCondition qc) { qc.FromClause = TimePointContext.Current.UseCurrentTime ? "SC.SchemaUserSnapshot_Current O INNER JOIN SC.UserAndContainerSnapshot_Current R ON O.ID = R.UserID" : "SC.SchemaUserSnapshot O INNER JOIN SC.UserAndContainerSnapshot R ON O.ID = R.UserID"; qc.SelectFields = "O.*"; if (string.IsNullOrEmpty(this.orderBy)) qc.OrderByClause = "R.VersionStartTime DESC"; else qc.OrderByClause = orderBy; base.OnBuildQueryCondition(qc); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); var timeCondition1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("R."); var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O."); builder.AppendItem("R.ContainerID", this.containerId); InnerBuildWhere(builder); builder.AppendItem("O.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem("R.Status", (int)SchemaObjectStatus.Normal); var allConditions = new ConnectiveSqlClauseCollection(timeCondition1, timeCondition2, builder); if (string.IsNullOrEmpty(qc.WhereClause)) { qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance); } else { qc.WhereClause = allConditions.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")"; } }
public DataView Query(int startRowIndex, int maximumRows, string appName, string programName, WfApplicationAuthType authType, string where, string orderBy, ref int totalCount) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); if (string.IsNullOrEmpty(appName) == false) { builder.AppendItem("APPLICATION_NAME", appName); if (string.IsNullOrEmpty(programName) == false) { builder.AppendItem("PROGRAM_NAME", programName); } } if (authType != WfApplicationAuthType.None) { builder.AppendItem("AUTH_TYPE", authType.ToString()); } string sql = builder.ToSqlString(TSqlBuilder.Instance); if (string.IsNullOrEmpty(where) == false) { sql = (string.IsNullOrEmpty(sql) ? where : sql + " AND (" + where + ")"); } return base.Query(startRowIndex, maximumRows, sql, orderBy, ref totalCount); }
//计算薪资 public Int32 CalculatePayMonthInfo(String yearMonth) { int result = 0; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth); builder.AppendItem(FeeMonthInfoDBConst.UseFlag, Status.True.ToString("D")); builder.AppendItem(FeeMonthInfoDBConst.FeeType, FeeType.Parameter.ToString("D"), "<>"); builder.AppendItem(FeeMonthInfoDBConst.FeeType, FeeType.Tax.ToString("D"), "<>"); builder.AppendItem(String.Concat("ISNULL(",FeeMonthInfoDBConst.CalculateExp,",'')<>''")); List<FeeInfo> feeList = FeeInfoAdapter.Instance.GetFeeInfoList(yearMonth, builder); foreach (FeeMonthInfo feeMonthInfo in feeList.Where(fe => !String.IsNullOrEmpty(fe.CalculateExp))) { String caculateExp = FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeMonthInfo.CalculateExp); //if (feeMonthInfo.FeeType.Equals(FeeType.Tax)) //{ // caculateExp = "ISNULL(DBO.CACULATETAX(''" + yearMonth + "'',''" + FeeInfoAdapter.Instance.GetTaxFeeID(yearMonth, feeMonthInfo.FeeID) + "''," + FeeInfoAdapter.Instance.ConvertExp(yearMonth, FeeInfoAdapter.Instance.GetTaxFeeCalculateExp(yearMonth, feeMonthInfo.FeeID)) + "),0)"; //} //else //{ // caculateExp = FeeInfoAdapter.Instance.ConvertExp(yearMonth, feeMonthInfo.CalculateExp); //} String sql = String.Format(@"DECLARE @SQL VARCHAR(8000) SELECT @SQL=ISNULL(@SQL + '],[','') + Convert(nvarchar(32),FEE_ID) FROM FEE_MONTH F WHERE (F.FEE_TYPE={5} OR F.FEE_TYPE={6}) AND ISNULL(CALCULATE_EXP,'')='' AND YEAR_MONTH={0} EXEC ('INSERT INTO PAY_MONTH(YEAR_MONTH,PERSON_ID,PERSON_NAME,FEE_ID,FEE_NAME,FEE_VALUE,PAY_MONEY,FORMULA) SELECT ''{0}'' AS ''YEAR_MONTH'', PERSON_ID, PERSON_NAME, ''{1}'' AS ''FEE_ID'', ''{2}'' AS ''FEE_NAME'', {3} AS ''FEE_VALUE'', {3} AS ''PAY_MONEY'',''{4}'' AS ''FORMULA'' FROM (SELECT PERSON_ID, PERSON_NAME, FEE_ID,PAY_MONEY FROM PAY_MONTH WHERE YEAR_MONTH=''{0}'')P PIVOT(MAX(PAY_MONEY) FOR FEE_ID IN ([' + @SQL + ']))B') " , yearMonth, feeMonthInfo.FeeID, feeMonthInfo.FeeName, caculateExp, caculateExp.Replace("'", ""), FeeType.Common.ToString("D"), FeeType.Parameter.ToString("D")); result += _DataHelper.ExecuteSql(sql); } return result; }
/// <summary> /// 获取与指定对象关联的条件历史 /// </summary> /// <param name="id">对象ID</param> /// <param name="type">类型,如果为<see langword="null"/>则不限。</param> /// <returns></returns> public IEnumerable<SCCondition> GetConditionHistoryEntries(string id, string type) { SCConditionCollection entries = new SCConditionCollection(); using (var context = DbHelper.GetDBContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("OwnerID", id); if (type != null) { builder.AppendItem("Type", type); } var sql = @"SELECT * FROM [SC].[Conditions] WHERE " + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY [VersionEndTime] DESC, SortID ASC"; var cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { ORMapping.DataReaderToCollection(entries, dr); } return entries; } }
public System.Collections.Generic.Stack<SchemaNavPathNode> Query(Guid id, DateTime timePoint) { System.Collections.Generic.Stack<SchemaNavPathNode> stack = new Stack<SchemaNavPathNode>(); StringBuilder sb = new StringBuilder(); var db = DbHelper.GetDBDatabase(); SchemaNavPathNode node; do { node = null; sb.Clear(); sb.Append(TimePointContext.Current.UseCurrentTime ? @" SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType FROM SC.SchemaOrganizationSnapshot_Current AS O INNER JOIN SC.SchemaRelationObjects_Current AS R ON O.ID = R.ObjectID WHERE " : @" SELECT R.ObjectID, R.ParentID, O.ID, O.Name, O.DisplayName,O.SchemaType FROM SC.SchemaOrganizationSnapshot AS O INNER JOIN SC.SchemaRelationObjects AS R ON O.ID = R.ObjectID WHERE "); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem<string>("R.ParentSchemaType", "Organizations"); var timeCondition = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "O."); builder.AppendItem<int>("O.Status", (int)SchemaObjectStatus.Normal); var timeCondition2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "R."); builder.AppendItem<int>("R.Status", (int)SchemaObjectStatus.Normal); builder.AppendItem<string>("R.ObjectID", id.ToString()); var schemaConditon1 = DataSourceUtil.SchemaTypeCondition("R.ParentSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames()); var schemaConditon2 = DataSourceUtil.SchemaTypeCondition("O.SchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames()); var schemaConditon3 = DataSourceUtil.SchemaTypeCondition("O.ChildSchemaType", SchemaInfo.FilterByCategory("Organizations").ToSchemaNames()); sb.Append(new ConnectiveSqlClauseCollection(builder, timeCondition, timeCondition2, schemaConditon1, schemaConditon2, schemaConditon3).ToSqlString(TSqlBuilder.Instance)); using (var dr = db.ExecuteReader(System.Data.CommandType.Text, sb.ToString())) { if (dr.Read()) { node = new SchemaNavPathNode() { ID = dr.GetString(0), ParentId = dr.GetString(1), Name = dr.GetString(2), Description = dr.GetString(3), NodeType = dr.GetString(4) }; stack.Push(node); id = Guid.Parse(node.ParentId); } } } while (node != null && node.ID != SCOrganization.RootOrganizationID); return stack; }
public Int32 DeleteReportFee(String reportID, String feeID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ReportFeeDBConst.ReportID, reportID); builder.AppendItem(ReportFeeDBConst.FeeID, feeID); String sql = String.Format("DELETE FROM {0} WHERE {1} ", ReportFeeDBConst.TableName, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public SCConditionCollection GetRoleConditions(string roleId) { var role = DbUtil.GetEffectiveObject<SCRole>(roleId); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("OwnerID", role.ID); where.AppendItem("Status", (int)SchemaObjectStatus.Normal); return PC.Adapters.SCConditionAdapter.Instance.Load(where, DateTime.MinValue); }
protected override void OnConnectConditions(ConnectiveSqlClauseCollection allConditions) { base.OnConnectConditions(allConditions); allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("OU.")); allConditions.Add(VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("O.")); WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder(); where1.AppendItem("OU.Status", (int)SchemaObjectStatus.Normal); where1.AppendItem("O.Status", (int)SchemaObjectStatus.Normal); allConditions.Add(where1); }
public Int32 DeletePersonBaseFeeInfo(String yearMonth, String personID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(PersonBaseFeeInfoDBConst.PersonID, personID); if (!String.IsNullOrEmpty(yearMonth)) { builder.AppendItem(PersonBaseFeeMonthInfoDBConst.YearMonth, yearMonth); } String sql = String.Format("DELETE FROM {0} WHERE {1} ", String.IsNullOrEmpty(yearMonth) ? PersonBaseFeeInfoDBConst.TableName : ORMapping.GetMappingInfo<PersonBaseFeeMonthInfo>().TableName, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
private string BuildFrom(string fullPath, string scopeType) { string sql = @"SELECT QM.MemberID FROM SC.SchemaRelationObjectsSnapshot QR INNER JOIN SC.SchemaMembersSnapshot QM ON QR.ObjectID= QM.ContainerID WHERE "; WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().NormalFor("QR.Status").NormalFor("QM.Status"); where.AppendItem("QM.ContainerSchemaType", AUCommon.SchemaAdminUnit); where.AppendItem("QM.MemberSchemaType", AUCommon.SchemaAUAdminScope); where.AppendItem("QR.FullPath", TSqlBuilder.Instance.EscapeLikeString(fullPath) + "%", "LIKE"); var time1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("QR."); var time2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("QM."); return sql + new ConnectiveSqlClauseCollection(time1, time2, where).ToSqlString(TSqlBuilder.Instance); }
private static WhereSqlClauseBuilder GenerateBuilder() { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ID", UuidHelper.NewUuidString()); builder.AppendItem("Name", "Turtle"); builder.AppendItem("Age", 250); builder.LogicOperator = LogicOperatorDefine.Or; return builder; }
/// <summary> /// 根据userID和一组ContainerID,加载该Member所拥有的权限 /// </summary> /// <param name="userID"></param> /// <param name="containerIDs"></param> /// <returns></returns> public SCContainerAndPermissionCollection LoadCurrentContainerAndPermissions(string userID, IEnumerable<string> containerIDs) { var ids = (from s in containerIDs select s).ToArray(); SCContainerAndPermissionCollection result = null; if (ids.Length > 0) { var timeConditon1 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("A."); var timeConditon2 = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder("U."); var condition = new WhereSqlClauseBuilder(); condition.AppendItem("A.Status", (int)SchemaObjectStatus.Normal); condition.AppendItem("U.Status", (int)SchemaObjectStatus.Normal); condition.AppendItem("U.UserID", userID); InSqlClauseBuilder inSql = new InSqlClauseBuilder("A.ContainerID"); inSql.AppendItem(ids); var sql = string.Format( "SELECT A.* FROM SC.Acl_Current A INNER JOIN SC.UserAndContainerSnapshot_Current U ON U.ContainerID = A.MemberID WHERE {0} ORDER BY SortID ", new ConnectiveSqlClauseCollection(timeConditon1, condition, inSql).ToSqlString(TSqlBuilder.Instance)); result = new SCContainerAndPermissionCollection(); using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { using (IDataReader reader = DbHelper.RunSqlReturnDR(sql, this.GetConnectionName())) { while (reader.Read()) { string containerID = (string)reader["ContainerID"]; string permission = (string)reader["ContainerPermission"]; if (result.ContainsKey(containerID, permission) == false) { result.Add(new SCContainerAndPermission() { ContainerID = containerID, ContainerPermission = permission }); } } return result; } } } else { result = new SCContainerAndPermissionCollection(); } return result; }
/// <summary> /// 根据ownerID和type加载ConditionOwner对象 /// </summary> /// <param name="ownerID"></param> /// <param name="type"></param> /// <param name="timePoint"></param> /// <returns></returns> public SCConditionCollection Load(string ownerID, string type, DateTime timePoint) { if (type.IsNullOrEmpty()) type = "Default"; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("OwnerID", ownerID); builder.AppendItem("Type", type); SCConditionCollection conditions = this.Load(builder, timePoint); return conditions; }
public Decimal CalculatePersonBaseFeeDepartmentProjectInfo(String yearMonth, String personID, String feeID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); if (!String.IsNullOrEmpty(yearMonth)) { builder.AppendItem(PersonBaseFeeDepartmentProjectMonthInfoDBConst.YearMonth, yearMonth); } builder.AppendItem(PersonBaseFeeDepartmentProjectInfoDBConst.PersonID, personID); builder.AppendItem(PersonBaseFeeDepartmentProjectInfoDBConst.FeeID, feeID); String sql = String.Format("SELECT ISNULL(SUM(STATION_MONEY),0) FROM {0} WHERE {1} " , String.IsNullOrEmpty(yearMonth) ? PersonBaseFeeDepartmentProjectInfoDBConst.TableName : ORMapping.GetMappingInfo<PersonBaseFeeDepartmentProjectMonthInfo>().TableName , builder.ToSqlString()); DataTable dt = _DataHelper.GetDataTable(sql); return Decimal.Parse(dt.Rows[0][0].ToString()); }
public WfClientProcessCurrentInfoPageQueryResult QueryProcesses(WfClientProcessQueryCondition condition, int startRowIndex, int maximumRows, string orderBy, int totalCount) { condition.NullCheck("condition"); OperationContext.Current.FillContextToOguServiceContext(); WfProcessQueryCondition serverCondition = null; WfClientProcessQueryConditionConverter.Instance.ClientToServer(condition, ref serverCondition); if (orderBy.IsNullOrEmpty()) { orderBy = "START_TIME DESC"; } ConnectiveSqlClauseCollection connective = serverCondition.ToSqlBuilder(); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("COMMITTED", "1"); builder.AppendTenantCode(); connective.Add(builder); QueryCondition qc = new QueryCondition(startRowIndex, maximumRows, ORMapping.GetSelectFieldsNameSql <WfProcessCurrentInfo>(), ORMapping.GetMappingInfo(typeof(WfProcessCurrentInfo)).TableName, orderBy); qc.WhereClause += connective.ToSqlString(TSqlBuilder.Instance); return(QueryProcessInfo(qc, totalCount)); }
/// <summary> /// 根据模式类型,查询ID类型和ID和时间点检索对象 /// </summary> /// <param name="schemaType">表示模式类型的字符串</param> /// <param name="idType">表示ID类型的<see cref="SnapshotQueryIDType"/>值之一</param> /// <param name="id">对象的ID</param> /// <param name="timePoint"></param> /// <returns></returns> public DESchemaObjectBase LoadByID(string schemaType, DESnapshotQueryIDType idType, string id, DateTime timePoint) { schemaType.CheckStringIsNullOrEmpty("schemaType"); id.CheckStringIsNullOrEmpty("id"); DESchemaDefine schema = DESchemaDefine.GetSchema(schemaType); var timeConditon = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SN."); WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); EnumItemDescriptionAttribute attr = EnumItemDescriptionAttribute.GetAttribute(idType); whereBuilder.AppendItem("SN." + attr.ShortName, id); string sql = string.Format("SELECT SO.* FROM DE.SchemaObject SO INNER JOIN {0} SN ON SO.ID = SN.ID WHERE {1}", schema.SnapshotTable, new ConnectiveSqlClauseCollection(whereBuilder, timeConditon).ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; DESchemaObjectBase result = null; if (table.Rows.Count > 0) { result = SchemaExtensions.CreateObject(schemaType); result.FromString((string)table.Rows[0]["Data"]); ORMapping.DataRowToObject(table.Rows[0], result); } return(result); }
private static WhereSqlClauseBuilder GetWhereSqlClauseBuilderFromMapping( object condition, ConditionMappingItemCollection mapping, bool ignoreDefaultValue, AdjustConditionValueDelegate acv) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); foreach (ConditionMappingItem item in mapping) { object data = GetValueFromObject(item, condition); if (data != null) { if (ignoreDefaultValue == false || (ignoreDefaultValue == true && IsTypeDefaultValue(item, data) == false)) { bool ignored = false; data = AdjustValueBeforeAppendToBuilder(item, data); if (acv != null) { data = acv(item.PropertyName, data, ref ignored); } if (ignored == false) { builder.AppendItem(item.DataFieldName, data, item.Operation, item.Template, item.IsExpression); } } } } return(builder); }
/// <summary> /// 按照ResourceID来更新数据 /// </summary> /// <param name="resourceID"></param> /// <param name="assignees"></param> public virtual void Update(string resourceID, TTaskAssigneeCollection assignees) { resourceID.CheckStringIsNullOrEmpty("resourceID"); assignees.NullCheck("assignees"); StringBuilder strB = new StringBuilder(); ORMappingItemCollection mappings = GetMappingInfo(new Dictionary <string, object>()); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("RESOURCE_ID", resourceID); wBuilder.AppendTenantCode(); strB.AppendFormat("DELETE {0} WHERE {1}", mappings.TableName, wBuilder.ToSqlString(TSqlBuilder.Instance)); foreach (TTaskAssignee assignee in assignees) { strB.Append(TSqlBuilder.Instance.DBStatementSeperator); InsertSqlClauseBuilder builder = ORMapping.GetInsertSqlClauseBuilder(assignee, mappings); strB.AppendFormat("INSERT INTO {0}{1}", mappings.TableName, builder.ToSqlString(TSqlBuilder.Instance)); } DbHelper.RunSqlWithTransaction(strB.ToString(), GetConnectionName()); }
private static string PrepareUpdateData(SCLock lockData, bool forceOverride) { ORMappingItemCollection mappingInfo = GetMappingInfo(); UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(lockData, mappingInfo); WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(lockData, mappingInfo); if (forceOverride == false) { wBuilder.AppendItem("LockTime", "DATEADD(SECOND, -EffectiveTime, GETDATE())", "<", true); } StringBuilder sql = new StringBuilder(); sql.AppendFormat("UPDATE {0} SET {1} WHERE {2}", mappingInfo.TableName, uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance)); sql.Append(TSqlBuilder.Instance.DBStatementSeperator); sql.Append("SELECT @@ROWCOUNT"); sql.Append(TSqlBuilder.Instance.DBStatementSeperator); sql.AppendFormat("SELECT * FROM {0} WHERE LockID = {1}", GetMappingInfo().TableName, TSqlBuilder.Instance.CheckUnicodeQuotationMark(lockData.LockID)); return(sql.ToString()); }
public SchemaObjectCollection LoadScopeItems(string[] ids, string scopeType, bool normalOnly, DateTime timePoint) { ids.NullCheck("ids"); string sql = "SELECT * FROM SC.SchemaObject WHERE "; if (ids.Length > 0) { InSqlClauseBuilder inSql = new InSqlClauseBuilder("ID").AppendItem(ids);; WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); if (normalOnly) { where.NormalFor("Status"); } where.AppendItem("SchemaType", scopeType); var timeBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); sql += new ConnectiveSqlClauseCollection(inSql, timeBuilder, where).ToSqlString(TSqlBuilder.Instance); return(LoadSchemaObjects(sql)); } else { return(new SchemaObjectCollection()); } }
/// <summary> /// 更新一个容器下的Acl信息 /// </summary> /// <param name="containerID"></param> /// <param name="aclItems"></param> public void Update(DEAclContainer container) { container.NullCheck("container"); container.FillMembersProperties(); ORMappingItemCollection mappings = this.GetMappingInfo(); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ContainerID", container.ContainerID); string sql = this.GetUpdateSql(container.ContainerID, container.Members); using (TransactionScope scope = TransactionScopeFactory.Create()) { DBTimePointActionContext.Current.TimePoint = (DateTime)DbHelper.RunSqlReturnScalar( sql, this.GetConnectionName()); //foreach (SCAclItem aclItem in container.Members) //{ // SCSnapshotBasicAdapter.Instance.UpdateCurrentSnapshot(mappings.TableName, // mappings.TableName + "_Current", // ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(aclItem, mappings)); //} scope.Complete(); } }
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); }
private static StringBuilder BuildSqlClause(WfMatrixQueryParamCollection queryParams) { var matrixIdParam = queryParams.Find(p => p.QueryName == DB_FIELD_MATRIX_ID); string inSqlClause = string.Empty; foreach (var para in queryParams) { if (para == matrixIdParam) { continue; //|| string.IsNullOrEmpty(para.QueryValue) 空值也作为条件 } inSqlClause = BuilderSubSqlClause(para, matrixIdParam.QueryValue, inSqlClause); } StringBuilder result = new StringBuilder(SELECT_SQL_CLAUSE); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("R." + matrixIdParam.QueryName, matrixIdParam.QueryValue); builder.AppendTenantCode("R.TENANT_CODE"); result.Append(builder.ToSqlString(TSqlBuilder.Instance)); if (!string.IsNullOrEmpty(inSqlClause)) { result.AppendFormat(" AND R.{0} IN ({1})", DB_FIELD_MATRIX_ROW_ID, inSqlClause); } return(result); }
public int Delete(string wfMatrixID) { WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); whereBuilder.AppendItem(DB_FIELD_MATRIX_ID, wfMatrixID); whereBuilder.AppendTenantCode(); StringBuilder strBuilder = new StringBuilder(); strBuilder.Append(DELETE_SQL_CLAUSE); strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance)); strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator); strBuilder.Append(DELETE_MR_SQL_CLAUSE); strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance)); strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator); strBuilder.Append(DELETE_MC_SQL_CLAUSE); strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance)); strBuilder.Append(TSqlBuilder.Instance.DBStatementSeperator); strBuilder.Append(DELETE_MU_SQL_CLAUSE); strBuilder.Append(whereBuilder.ToSqlString(TSqlBuilder.Instance)); return(DbHelper.RunSqlWithTransaction(strBuilder.ToString(), ConnectionDefine.DBConnectionName)); }
/// <summary> /// 获取可用的排序号 /// </summary> /// <param name="userID">用户ID</param> /// <returns></returns> public int GetMaxSort(string userID) { ExceptionHelper.TrueThrow <ArgumentNullException>(null == userID, "userID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("USER_ID", DeluxeIdentity.CurrentUser.ID); builder.AppendTenantCode(typeof(TaskCategory)); int result = 0; string strSQL = string.Format("SELECT MAX(INNER_SORT_ID) AS NUM FROM WF.USER_TASK_CATEGORY WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); object num = DbHelper.RunSqlReturnScalar(strSQL); if (num.ToString() != string.Empty) { result = (int)num; //当排序号以达到最大值999时,仍返回最大值 if (result < 999) { result++; } } return(result); }
public void Update(string processID, WfProcessCurrentAssigneeCollection assignees) { processID.CheckStringIsNullOrEmpty("processID"); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PROCESS_ID", processID); wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity)); string sqlDelete = string.Format( "DELETE WF.PROCESS_CURRENT_ASSIGNEES WHERE {0}", wBuilder.ToSqlString(TSqlBuilder.Instance)); StringBuilder strB = new StringBuilder(); foreach (WfProcessCurrentAssignee assignee in assignees) { if (strB.Length > 0) { strB.Append(TSqlBuilder.Instance.DBStatementSeperator); } strB.Append(ORMapping.GetInsertSql(assignee, TSqlBuilder.Instance)); } string sql = sqlDelete; if (strB.Length > 0) { sql += TSqlBuilder.Instance.DBStatementSeperator + strB.ToString(); } DbHelper.RunSqlWithTransaction(sql, GetConnectionName()); }
private static SqlCommand ReadUpdateSql(string table, Action <List <string> > whereColunmAction, Action <List <string> > updateColumnAction) { SqlCommand cmd = new SqlCommand(); List <string> whereColunms = new List <string>(); whereColunmAction(whereColunms); List <string> updateColumns = new List <string>(); updateColumnAction(updateColumns); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); whereColunms.ForEach(w => { var p = AddParameter(cmd, w); where.AppendItem(w, p.ParameterName, "=", true); }); UpdateSqlClauseBuilder update = new UpdateSqlClauseBuilder(); updateColumns.ForEach(u => { var p = AddParameter(cmd, u); update.AppendItem(u, p.ParameterName, "=", true); } ); string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", table, update.ToSqlString(TSqlBuilder.Instance), where.ToSqlString(TSqlBuilder.Instance)); cmd.CommandText = sql; cmd.CommandType = CommandType.Text; cmd.UpdatedRowSource = UpdateRowSource.None; return(cmd); }
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("TASK_GUID", Request.QueryString["id"]); dataSourceMain.Condition = where; }
private string BuildWhereCondition() { WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.Condition, new AdjustConditionValueDelegate(AdjustQueryConditionValue)); builder.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID); WhereSqlClauseBuilder whereExpireTime = new WhereSqlClauseBuilder(LogicOperatorDefine.Or); whereExpireTime.AppendItem("EXPIRE_TIME", "null", "is", true); whereExpireTime.AppendItem("EXPIRE_TIME", "getdate()", ">", true); WhereSqlClauseBuilder whereStatus = new WhereSqlClauseBuilder(); whereStatus.AppendItem("STATUS", ((int)TaskStatus.Yue).ToString()); var conditionGroup1 = new ConnectiveSqlClauseCollection(whereExpireTime, whereStatus); WhereSqlClauseBuilder whereStatus2 = new WhereSqlClauseBuilder(); whereStatus2.AppendItem("STATUS", ((int)TaskStatus.Ban).ToString()); //collection2.Add(collection1); ConnectiveSqlClauseCollection collection2 = new ConnectiveSqlClauseCollection(LogicOperatorDefine.Or, conditionGroup1, whereStatus2); var allConditions = new ConnectiveSqlClauseCollection(builder, collection2); return(allConditions.ToSqlString(TSqlBuilder.Instance)); }
protected override void OnBuildQueryCondition(QueryCondition qc) { qc.FromClause = TimePointContext.Current.UseCurrentTime ? @"SC.UserAndContainerSnapshot_Current C INNER JOIN SC.SchemaUserSnapshot_Current U ON U.ID =C.UserID INNER JOIN SC.SchemaRoleSnapshot_Current R ON R.ID = C.ContainerID INNER JOIN SC.SchemaMembersSnapshot_Current S ON S.MemberID = R.ID INNER JOIN SC.SchemaApplicationSnapshot_Current A ON A.ID = S.ContainerID" : @"SC.UserAndContainerSnapshot C INNER JOIN SC.SchemaUserSnapshot U ON U.ID =C.UserID INNER JOIN SC.SchemaRoleSnapshot R ON R.ID = C.ContainerID INNER JOIN SC.SchemaMembersSnapshot S ON S.MemberID = R.ID INNER JOIN SC.SchemaApplicationSnapshot A ON A.ID = S.ContainerID"; qc.SelectFields = "R.*, A.ID AS AppID,A.DisplayName AS AppName,A.Name As AppName2,A.CodeName as AppCodeName"; base.OnBuildQueryCondition(qc); WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder(); wb.AppendItem("C.UserID", this.userId); if (string.IsNullOrWhiteSpace(orderBy)) { qc.OrderByClause = "A.DisplayName"; } else { qc.OrderByClause = orderBy; } if (string.IsNullOrWhiteSpace(qc.WhereClause)) { qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance); } else { qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance) + " AND (" + qc.WhereClause + ")"; } }
public SchemaObjectCollection QueryApplications(string[] schemaTypes, bool includingDeleted, DateTime timePoint) { SchemaObjectCollection result = new SchemaObjectCollection(); ConnectiveSqlClauseCollection timeConditionC = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint, "SC."); InSqlClauseBuilder schemaBuilder = new InSqlClauseBuilder("SC.SchemaType"); schemaBuilder.AppendItem(schemaTypes); WhereSqlClauseBuilder builderC = new WhereSqlClauseBuilder(); if (includingDeleted == false) builderC.AppendItem("SC.Status", (int)SchemaObjectStatus.Normal); ConnectiveSqlClauseCollection connectiveBuilder = new ConnectiveSqlClauseCollection(schemaBuilder, builderC, timeConditionC); string sql = string.Format("SELECT SC.*" + "\nFROM SC.SchemaObject SC" + "\nWHERE {0}", connectiveBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView); return result; }
private void GridViewDataBind() { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); TreeNode tn = this.trvProjectClass.SelectedNode; if (tn != null) { String projectClassId = tn.Value; if (!String.IsNullOrEmpty(projectClassId)) { builder.AppendItem(ProjectInfoDBConst.ProjectClassID, projectClassId + "%", "LIKE"); } else { btnEditProjectClass.Enabled = false; } } else { btnEditProjectClass.Enabled = false; } List <ProjectInfo> infoList = ProjectInfoAdapter.Instance.GetProjectInfoList(builder); GridViewControl.GridViewDataBind <ProjectInfo>(this.gvList, infoList); //this.gvList.DataSource = projectList; //this.gvList.DataBind(); }
public IEnumerable <ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId) { ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection(); WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder(); where1.AppendItem("R.ObjectID", objId); WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder(); where2.AppendItem("R.ParentID", objId); var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0} UNION SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC ", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance)); using (var context = DbHelper.GetDBContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); var cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { ORMapping.DataReaderToCollection(list, dr); } } return(list); }
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { var allConditions = new ConnectiveSqlClauseCollection(this.DeluxeSearch.GetCondition()); var condition = this.CurrentAdvancedSearchCondition; WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(condition); string categoryID = Request.QueryString["categoryID"]; if (!string.IsNullOrEmpty(categoryID)) { //DECategory dcg = CategoryAdapter.Instance.GetByID(categoryID); //dcg.FullPath += "%"; if (categoryID != "undefined") { builder.AppendItem("SchemaType", categoryID); } } allConditions.Add(builder); this.dataSourceMain.Condition = allConditions; }
private static ConnectiveSqlClauseCollection SchemaStrict(ConnectiveSqlClauseCollection c, string prefix, PC.StandardObjectSchemaType schema) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem(prefix + "SchemaType", schema); c.Add(where); return c; }
/// <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> /// 读取没有安排到任务中的JobID的集合 /// </summary> /// <param name="batchCount"></param> /// <returns></returns> public IList <string> LoadUnTaskedJobs(int batchCount, TimeSpan timeOffset) { string sqlTemplate = "SELECT {0} J.JOB_ID FROM WF.JOBS J LEFT JOIN WF.SYS_NOT_RUNNING_TASK T WITH(UPDLOCK, READPAST) ON J.JOB_ID = T.RESOURCE_ID WHERE (T.STATUS IS NULL) AND {1} ORDER BY J.CREATE_TIME"; string top = batchCount >= 0 ? "TOP " + batchCount : string.Empty; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ENABLED", "1"); WhereSqlClauseBuilder builder2 = new WhereSqlClauseBuilder(LogicOperatorDefine.Or); builder2.AppendItem("LAST_START_EXE_TIME", string.Format("DATEADD(SECOND, -{0}, GETDATE())", (int)timeOffset.TotalSeconds), "<", true); builder2.AppendItem("LAST_START_EXE_TIME", "NULL", "IS", true); string sql = string.Format(sqlTemplate, top, new ConnectiveSqlClauseCollection(builder, builder2).ToSqlString(TSqlBuilder.Instance)); List <string> result = new List <string>(800); using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); DataTable table = db.ExecuteDataSet(CommandType.Text, sql).Tables[0]; foreach (DataRow row in table.Rows) { result.Add(row["JOB_ID"].ToString()); } } return(result); }
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { if (this.IsPostBack == false && string.IsNullOrEmpty(this.Request.QueryString["id"]) == false) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ID", this.Request.QueryString["id"]); this.dataSourceMain.Condition = where; } else { //if (this.AdvanceSearchEnabled) { var condition = this.CurrentAdvancedSearchCondition; WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(condition); this.dataSourceMain.Condition = new ConnectiveSqlClauseCollection(builder, this.DeluxeSearch.GetCondition()); } //else //{ // this.dataSourceMain.Condition = this.DeluxeSearch.GetCondition(); //} } }
public static void IfEmptyFillDefault(this WhereSqlClauseBuilder builder) { if (builder.IsEmpty) { builder.AppendItem("1 = 1"); } }
public TaxInfo LoadTaxInfo(String yearMonth, String taxID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(TaxInfoDBConst.TaxID, taxID); return(GetTaxInfoList(yearMonth, builder).FirstOrDefault()); }
private void BindDataGrid() { WhereSqlClauseBuilder builder = ConditionMapping.GetWhereSqlClauseBuilder(this.UserUploadCondition); if (this.processUsers.ReadOnly) { this.ObjectDataSourceUploadLogs.Condition = builder; } else { ConnectiveSqlClauseCollection collection = new ConnectiveSqlClauseCollection(LogicOperatorDefine.And); collection.Add(builder); WhereSqlClauseBuilder userwherebuilder = new WhereSqlClauseBuilder(); userwherebuilder.LogicOperator = LogicOperatorDefine.Or; foreach (var item in this.processUsers.SelectedOuUserData) { userwherebuilder.AppendItem("OPERATOR_ID", item.ID); } collection.Add(userwherebuilder); this.ObjectDataSourceUploadLogs.Condition = collection.ToSqlString(TSqlBuilder.Instance); } this.ObjectDataSourceUploadLogs.LastQueryRowCount = -1; this.DeluxeGridUploadLog.PageIndex = 0; this.DeluxeGridUploadLog.SelectedKeys.Clear(); this.DeluxeGridUploadLog.DataBind(); }
public AUSchemaCategory LoadByID(string id, DateTime timePoint) { var conditions = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(timePoint); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ID", id); conditions.Add(where); AUSchemaCategory cate = null; AUCommon.DoDbAction(() => { using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { VersionedObjectAdapterHelper.Instance.FillData(ORMapping.GetMappingInfo(typeof(AUSchemaCategory)).TableName, conditions, this.GetConnectionName(), reader => { if (reader.Read()) { cate = new AUSchemaCategory(); ORMapping.DataReaderToObject <AUSchemaCategory>(reader, cate); } }); } }); return(cate); }
internal OAMQMessages LoadByProcessInstanceID(int processInstanceID) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); //where.AppendItem("Status", 0); //where.AppendItem("Flowmess", 1); where.AppendItem("Viewtype", -2); where.AppendItem("FlowID", processInstanceID); StringBuilder sql = new StringBuilder(); sql.AppendFormat(" SELECT * FROM {0} WHERE {1} ORDER BY Sendertime ASC", ORMapping.GetTableName(typeof(OAMQMessages)), where.ToSqlString(TSqlBuilder.Instance)); List <OAMQMessages> result = ExecuteQuery(sql.ToString()); return(result.Count > 0 ? result[0] : null); }
public List <ReportFee> GetReportFeeList(string reportID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ReportFeeDBConst.ReportID, reportID); return(GetReportFeeList(builder)); }
/// <summary> /// 获得指定ID的Material /// </summary> /// <param name="materialID">指定ID</param> /// <returns>MaterialList</returns> public MaterialList LoadMaterialByMaterialID(string materialID) { ExceptionHelper.CheckStringIsNullOrEmpty(materialID, "materialID"); MaterialList materials = new MaterialList(); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("ID", materialID); wBuilder.AppendTenantCode(typeof(Material)); string sql = "SELECT * FROM WF.MATERIAL WHERE " + wBuilder.ToSqlString(TSqlBuilder.Instance); using (DbContext dbi = DbHelper.GetDBContext(GetConnectionName())) { using (IDataReader dr = DbHelper.RunSqlReturnDR(sql, GetConnectionName())) { while (dr.Read()) { Material material = new Material(); ORMapping.DataReaderToObject(dr, material); materials.Add(material); } } } DecorateMaterials(materials); return(materials); }
public SchemaObjectCollection LoadCurrentUsers(string ownerID) { ownerID.CheckStringIsNullOrEmpty("ownerID"); var timePointBuilder = VersionStrategyQuerySqlBuilder.Instance.TimePointToBuilder(DateTime.MinValue); WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); whereBuilder.AppendItem("C.OwnerID", ownerID); timePointBuilder.Add(whereBuilder); string sql = string.Format( "SELECT SC.* FROM {0} SC INNER JOIN SC.ConditionCalculateResult C ON SC.ID = C.UserID WHERE {1}", ORMapping.GetMappingInfo(typeof(SchemaObjectBase)).TableName, timePointBuilder.ToSqlString(TSqlBuilder.Instance)); SchemaObjectCollection result = new SchemaObjectCollection(); using (DbContext context = DbContext.GetContext(this.GetConnectionName())) { result.LoadFromDataView(DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0].DefaultView); } return result; }
public WfClientUserOperationLogPageQueryResult QueryUserOperationLogByProcessID(string processID, int startRowIndex, int maximumRows, string orderBy, int totalCount) { processID.CheckStringIsNullOrEmpty("processID"); OperationContext.Current.FillContextToOguServiceContext(); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("PROCESS_ID", processID); builder.AppendTenantCode(); if (orderBy.IsNullOrEmpty()) { orderBy = "ID DESC"; } QueryCondition qc = new QueryCondition(startRowIndex, maximumRows, "*", ORMapping.GetMappingInfo(typeof(UserOperationLog)).TableName, orderBy); qc.WhereClause = builder.ToSqlString(TSqlBuilder.Instance); return(QueryUserOperationLog(qc, totalCount)); }
/// <summary> /// 根据ResourceID和ClassName加载相关的数据 /// </summary> /// <typeparam name="TRelative"></typeparam> /// <typeparam name="TRelativeCollection"></typeparam> /// <param name="resourceID"></param> /// <param name="className"></param> /// <returns></returns> public TRelativeCollection LoadRelativeData <TRelative, TRelativeCollection>( string resourceID, string className) where TRelative : GenericFormRelativeData, new() where TRelativeCollection : EditableDataObjectCollectionBase <TRelative>, new() { resourceID.CheckStringIsNullOrEmpty("resourceID"); className.CheckStringIsNullOrEmpty("className"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("RESOURCE_ID", resourceID).AppendItem("CLASS", className); builder.AppendTenantCode(typeof(TRelative)); string sql = string.Format("SELECT * FROM WF.GENERIC_FORM_RELATIVE_DATA WHERE {0} ORDER BY SORT_ID", builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; TRelativeCollection result = new TRelativeCollection(); ORMapping.DataViewToCollection(result, table.DefaultView); foreach (TRelative item in result) { XmlDocument xmlDoc = XmlHelper.CreateDomDocument(item.XmlContent); XmlHelper.DeserializeToObject(xmlDoc, item); } return(result); }
public IEnumerable<ReferenceHistoryEntry> GetReferenceHistoryEntries(string objId) { ReferenceHistoryEntryCollection list = new ReferenceHistoryEntryCollection(); WhereSqlClauseBuilder where1 = new WhereSqlClauseBuilder(); where1.AppendItem("R.ObjectID", objId); WhereSqlClauseBuilder where2 = new WhereSqlClauseBuilder(); where2.AppendItem("R.ParentID", objId); var sql = string.Format(@"SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ParentID = O.ID WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {0} UNION SELECT R.[ParentID], R.[ObjectID], R.[VersionStartTime], R.[VersionEndTime], R.[Status], R.[IsDefault], R.[InnerSort], R.[CreatorID], R.[CreatorName],O.Name, O.DisplayName FROM [SC].[SchemaRelationObjectsSnapshot] R LEFT JOIN SC.SchemaObjectSnapshot_Current O ON R.ObjectID = O.ID WHERE O.VersionStartTime <=R.VersionStartTime AND (O.VersionEndTime IS NULL OR O.VersionEndTime>=R.VersionEndTime) AND {1} ORDER BY VersionStartTime ASC ", where1.ToSqlString(TSqlBuilder.Instance), where2.ToSqlString(TSqlBuilder.Instance)); using (var context = DbHelper.GetDBContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); var cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { ORMapping.DataReaderToCollection(list, dr); } } return list; }
/// <summary> /// 根据ownerID和type加载ConditionOwner对象 /// </summary> /// <param name="ownerID"></param> /// <param name="type"></param> /// <param name="timePoint"></param> /// <returns></returns> public SCConditionCollection Load(string ownerID, string type, DateTime timePoint) { if (type.IsNullOrEmpty()) { type = "Default"; } WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("OwnerID", ownerID); builder.AppendItem("Type", type); SCConditionCollection conditions = this.Load(builder, timePoint); return(conditions); }
private void BindTaskList() { WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); whereBuilder.AppendItem("ENABLED", "1"); whereBuilder.AppendItem("JOB_STATUS", 0); string category = txtCategory.Text.Trim(); string name = txtName.Text.Trim(); if (string.IsNullOrEmpty(category) == false) whereBuilder.AppendItem("JOB_CATEGORY", category); if (string.IsNullOrEmpty(name) == false) whereBuilder.AppendItem("JOB_NAME", name); this.WfJobs = JobBaseAdapter.Instance.UPDLOCKLoadJobs(decimal.ToInt32(numericUpDown1.Value), whereBuilder); this.dataGridView1.DataSource = this.WfJobs; }
public ProjectInfo LoadProjectInfo(String projectId) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ProjectInfoDBConst.ProjectID, projectId); return(GetProjectInfoList(builder).FirstOrDefault()); }
public Int32 DeleteDepartmentInfo(String deptID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(DepartmentInfoDBConst.DepartmentID, deptID); String sql = String.Format("DELETE FROM {0} WHERE {1} ", DepartmentInfoDBConst.TableName, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public Int32 ChangeStatus(String projectClassId, Int32 userFlag) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ProjectClassInfoDBConst.ProjectClassID, projectClassId); String sql = String.Format("UPDATE {0} SET USE_FLAG={1} WHERE {2} ", ProjectClassInfoDBConst.TableName, userFlag, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public Int32 ChangeDimission(String personID, bool dimission) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(PersonInfoDBConst.PersonID, personID); String sql = String.Format("UPDATE {0} SET DIMISSION={1} WHERE {2} ", PersonInfoDBConst.TableName, dimission ? 1 : 0, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public Int32 DeleteUserInfo(String userId) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(UserInfoDBConst.UserId, userId); String sql = String.Format("DELETE FROM {0} WHERE {1} ", UserInfoDBConst.TableName, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public Int32 ChangeStatus(String userId, Int32 status) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(UserInfoDBConst.UserId, userId); String sql = String.Format("UPDATE {0} SET LOGOUT={1} WHERE {2} ", UserInfoDBConst.TableName, status, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public Int32 ChangePassword(String userId, String password) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(UserInfoDBConst.UserId, userId); String sql = String.Format("UPDATE {0} SET PASSWORD='******' WHERE {2} ", UserInfoDBConst.TableName, CryptoHelper.Encode(password), builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
protected void dataSourceMain_Selecting(object sender, ObjectDataSourceSelectingEventArgs e) { if (this.IsPostBack == false && this.Request["id"] != null) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("SynchronizeID", this.Request["id"]); this.dataSourceMain.Condition = builder; } }