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 + ")"; } }
protected override void OnBuildQueryCondition(QueryCondition qc) { base.OnBuildQueryCondition(qc); WhereSqlClauseBuilder wb = new WhereSqlClauseBuilder(); wb.AppendItem("LogID", this.syncID); if (string.IsNullOrEmpty(qc.WhereClause)) qc.WhereClause = wb.ToSqlString(TSqlBuilder.Instance); else qc.WhereClause = " AND " + wb.ToSqlString(TSqlBuilder.Instance); }
/// <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; } }
/// <summary> /// /// </summary> /// <param name="fillAssignees"></param> /// <param name="action"></param> /// <returns></returns> public WfProcessCurrentInfoCollection Load(bool fillAssignees, Action<WhereSqlClauseBuilder> action) { action.NullCheck("action"); WfProcessCurrentInfoCollection result = new WfProcessCurrentInfoCollection(); WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); action(whereBuilder); string fieldNames = ORMapping.GetSelectFieldsNameSql<WfProcessInstanceData>("Data"); string sql = string.Format("SELECT {0} FROM WF.PROCESS_INSTANCES WHERE {1}", fieldNames, whereBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView); if (fillAssignees) FillAssignees(result); return result; }
public static IEnumerable<SCSimpleObject> QueryParents(string unitID, bool includingSelf, DateTime timePoint) { if (string.IsNullOrEmpty(unitID) == false) { string timeString = timePoint == DateTime.MinValue ? TSqlBuilder.Instance.DBCurrentTimeFunction : TSqlBuilder.Instance.FormatDateTime(timePoint); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder().AppendCondition("ID", unitID); string sql = string.Format(sqlTemplate, timeString, where.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, ConnectionName).Tables[0]; DataView view = new DataView(table); view.Sort = "ID"; SCSimpleObjectCollection tempParents = new SCSimpleObjectCollection(); FillAllParents(unitID, view, includingSelf, tempParents); tempParents.Reverse(); return tempParents; } else { return emptyResult; } }
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); }
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); }
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; }
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 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 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 DeleteReportFee(String reportID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ReportFeeDBConst.ReportID, reportID); String sql = String.Format("DELETE FROM {0} WHERE {1} ", ReportFeeDBConst.TableName, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); return result; }
public String CreatePersonCode(String departID) { DepartmentInfo departInfo = DepartmentInfoAdapter.Instance.LoadDepartmentInfo(departID); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(PersonInfoDBConst.DepartmentID, departID); String sql = String.Format("SELECT ISNULL(MAX(PERSON_CODE) + 1,'{0}01') FROM {1} WHERE {2}", departInfo.DepartmentCode, PersonInfoDBConst.TableName, builder.ToSqlString()); DataTable dt = _DataHelper.GetDataTable(sql); return dt.Rows[0][0].ToString(); }
/// <summary> /// 增加ROLE_ID的查询条件 /// </summary> /// <param name="roleID"></param> /// <returns></returns> public static string ToRoleIDCriteria(this string roleID) { roleID.CheckStringIsNullOrEmpty("roleID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ROLE_ID", roleID); builder.AppendTenantCode(); return builder.ToSqlString(TSqlBuilder.Instance); }
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; }
/// <summary> /// 获取与制定对象关联的 /// </summary> /// <param name="id"></param> /// <param name="schemaType"></param> /// <returns></returns> public IEnumerable<HistoryEntry> GetAllHistoryEntry(string id, ref string schemaType) { HistoryEntryCollection entries = new HistoryEntryCollection(); schemaType = null; using (var context = DbHelper.GetDBContext(this.GetConnectionName())) { Database db = DatabaseFactory.Create(context); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ID", id); var sql = "SELECT TOP 1 SchemaType FROM [SC].[SchemaObjectSnapshot] WHERE " + builder.ToSqlString(TSqlBuilder.Instance); sql += @" SELECT [VersionStartTime] ,[VersionEndTime] ,[CreatorID] ,[CreatorName] ,[Status] FROM [SC].[SchemaObjectSnapshot] WHERE " + builder.ToSqlString(TSqlBuilder.Instance) + " ORDER BY VersionStartTime DESC"; var cmd = db.GetSqlStringCommand(sql); using (var dr = db.ExecuteReader(cmd)) { if (dr.Read()) { schemaType = dr.GetString(0); if (dr.NextResult()) { ORMapping.DataReaderToCollection(entries, dr); } else { cmd.Cancel(); } } } return entries; } }
protected override string GetUpdateSql(WeChatFriend data, ORMappingItemCollection mappings, Dictionary <string, object> context) { UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "OpenID"); WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings); string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", mappings.TableName, uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance)); return(sql); }
/// <summary> /// 拿到已存在的验证码信息,如果不存在,返回NULL /// </summary> /// <param name="authenticationID"></param> /// <returns></returns> public AuthenticationCode Load(string authenticationID) { authenticationID.CheckStringIsNullOrEmpty("AUTHENTICATION_ID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("AUTHENTICATION_ID", authenticationID); string sql = string.Format("SELECT *, CASE WHEN EXPIRE_TIME > GETDATE() THEN 1 ELSE 0 END IS_VALID FROM {0} WHERE {1}", this.GetMappingInfo().TableName, builder.ToSqlString(TSqlBuilder.Instance)); return(this.Query(sql)); }
public bool ExistsProcessKey(string processKey) { processKey.CheckStringIsNullOrEmpty("processKey"); WhereSqlClauseBuilder builder = PrepareProcessKeyWhereBuilder(processKey); string sql = string.Format("SELECT COUNT(*) FROM WF.PROCESS_DESCRIPTORS WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); int count = (int)DbHelper.RunSqlReturnScalar(sql, this.GetConnectionName()); return(count > 0); }
public List <ProcessNode> LoadList(int processID, string processType) { string ProcessNodeTableName = ORMapping.GetTableName(typeof(ProcessNode)); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("processID", processID); where.AppendItem("processType", processType); string sqlCommand = string.Format("SELECT * FROM {0} WHERE {1} AND {2} ORDER BY NodeSeq ASC", ProcessNodeTableName, where.ToSqlString(TSqlBuilder.Instance), NotDeleted); return(ExecuteQuery(sqlCommand)); }
public AMSEventChannelCollection LoadEventAndChannel(string eventID) { eventID.CheckStringIsNullOrEmpty("eventID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("EventID", eventID); string sql = string.Format("SELECT * FROM AMS.EventsChannels WHERE {0} ORDER BY IsDefault DESC", builder.ToSqlString(TSqlBuilder.Instance)); return(this.QueryData <AMSEventChannel, AMSEventChannelCollection>(ORMapping.GetMappingInfo <AMSEventChannel>(), sql)); }
public override void Delete(WfRelativeProcess data) { ORMappingItemCollection mapping = ORMapping.GetMappingInfo <WfRelativeProcess>(); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PROCESS_ID", data.ProcessID); wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity)); string sql = string.Format("DELETE {0} WHERE {1}", mapping.TableName, wBuilder.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(sql, GetConnectionName()); }
public void DeleteExpiredTasks(TimeSpan expiredTime) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("START_TIME", string.Format("DATEADD(second, -{0}, GETUTCDATE())", (int)expiredTime.TotalSeconds), "<", true); builder.AppendTenantCode(); string sql = string.Format("DELETE {0} WHERE {1}", this.GetTableName(), builder.ToSqlString(TSqlBuilder.Instance)); DbHelper.RunSql(sql, this.GetConnectionName()); }
private static int eventContainer_UpdateUserTask(UserTask task, UserTaskIDType idType, UserTaskFieldDefine fields, Dictionary <object, object> context) { ExceptionHelper.FalseThrow <ArgumentNullException>(task != null, "task"); WhereSqlClauseBuilder wBuilder = GetWhereSqlClauseBuilderByUserTask(task, idType); UpdateSqlClauseBuilder uBuilder = GetUpdateSqlClauseBuilderByUserTask(task, fields); string sql = string.Format("UPDATE WF.USER_TASK SET {0} WHERE {1}", uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance)); return(DbHelper.RunSql(sql, GetConnectionName())); }
public List <OAMQMessages> LoadList(int count) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("Status", 0); StringBuilder sql = new StringBuilder(); sql.AppendFormat(" SELECT TOP {0} * FROM {1} WHERE {2} ORDER BY Sendertime ASC", count, ORMapping.GetTableName(typeof(OAMQMessages)), where.ToSqlString(TSqlBuilder.Instance)); return(ExecuteQuery(sql.ToString())); }
public void DeleteList(int processInstanceId) { string tableName = ORMapping.GetTableName(typeof(ProcessNodeInstance)); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ProcessInstanceID", processInstanceId); string sqlCommand = string.Format("delete from {0} where {1}", tableName, where.ToSqlString(TSqlBuilder.Instance)); ExecuteQuery(sqlCommand); }
public List<UserInfo> GetUserInfoList(WhereSqlClauseBuilder builder) { String sql = String.Format("SELECT * FROM {0} WHERE {1} ", UserInfoDBConst.TableName, builder.ToSqlString()); DataTable dt = _DataHelper.GetDataTable(sql); List<UserInfo> result = new List<UserInfo>(); for (Int32 i = 0; i < dt.Rows.Count; i++) { UserInfo info = new UserInfo(); ORMapping.DataRowToObject(dt.Rows[i], info); result.Add(info); } return result; }
public List <ProcessNodeInstance> LoadListByBizID(int bizProcessID) { string tableName = ORMapping.GetTableName(typeof(ProcessNodeInstance)); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("BizProcessID", bizProcessID); string sqlCommand = string.Format("select * from {0} where {1} AND {2} ORDER BY NodeSeq ASC", tableName, where.ToSqlString(TSqlBuilder.Instance), NotDeleted); return(ExecuteQuery(sqlCommand)); }
/// <summary> /// 创建逻辑删除语句 /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="data">传入的对象</param> /// <returns>逻辑删除语句</returns> public static string GetLogicDeleteSql <T>(T data) { ORMappingItemCollection mappings = ORMapping.GetMappingInfo <T>(); //创建Where子句表达式 WhereSqlClauseBuilder builder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings); ExceptionHelper.FalseThrow(builder.Count > 0, "必须为对象{0}指定关键字", typeof(T)); return(String.Format(" UPDATE {0} SET ValidStatus=0 WHERE {1} {2}", mappings.TableName, builder.ToSqlString(TSqlBuilder.Instance), TSqlBuilder.Instance.DBStatementSeperator)); }
private static string GetUpdateSql <T>(T graph, DataModel mapping, ISqlBuilder builder, string[] ignorProperties) { ExceptionHelper.FalseThrow <ArgumentNullException>(graph != null, "graph"); ExceptionHelper.FalseThrow <ArgumentNullException>(mapping != null, "mapping"); ExceptionHelper.FalseThrow <ArgumentNullException>(builder != null, "builder"); UpdateSqlClauseBuilder updateBuilder = GetUpdateSqlClauseBuilder(graph, mapping, ignorProperties); WhereSqlClauseBuilder whereBuilder = GetWhereSqlClauseBuilderByPrimaryKey(graph, mapping); return(string.Format("UPDATE {0} SET {1} WHERE {2}", mapping.TableName, updateBuilder.ToSqlString(builder), whereBuilder.ToSqlString(builder))); }
public JobType GetJobType(string jobid) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("JOB_ID", jobid); builder.AppendTenantCode(); string sql = string.Format(@"SELECT JOB_TYPE FROM WF.JOBS WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); object obj = DbHelper.RunSqlReturnScalar(sql); return((JobType)Enum.Parse(typeof(JobType), obj.ToString())); }
public Int32 DeletePersonInfo(String personID) { using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Required)) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(PersonInfoDBConst.PersonID, personID); String sql = String.Format("DELETE FROM {0} WHERE {1} ", PersonInfoDBConst.TableName, builder.ToSqlString()); int result = _DataHelper.ExecuteSql(sql); PersonBaseFeeDepartmentProjectInfoAdapter.Instance.DeletePersonBaseFeeDepartmentProjectInfo(null, builder); PersonBaseFeeInfoAdapter.Instance.DeletePersonBaseFeeInfo(null, personID); scope.Complete(); return result; } }
public PartlyCollection <ApprovalLog> LoadList(int processInstanceId) { StringBuilder sql = new StringBuilder(); sql.Append(ORMapping.GetSelectSql(typeof(ApprovalLog), TSqlBuilder.Instance)); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ProcessInstanceID", processInstanceId); sql.AppendFormat(" where {0} AND {1}", where.ToSqlString(TSqlBuilder.Instance), NotDeleted); sql.Append(" ORDER BY CompletedTime desc"); return((PartlyCollection <ApprovalLog>)ExecuteQuery(sql.ToString())); }
public AMSAdmin CheckPassword(string logonName, string password) { logonName.CheckStringIsNullOrEmpty("logonName"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("LogonName", logonName); builder.AppendItem("[Password]", CalculatePassword(password)); string sql = string.Format("SELECT * FROM {0} WHERE {1}", this.GetTableName(), builder.ToSqlString(TSqlBuilder.Instance)); return(this.QueryData(sql).SingleOrDefault()); }
private SysTaskCollection InnerQueryBatch(int batchCount) { string batchClause = batchCount >= 0 ? "TOP " + batchCount : string.Empty; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("1", 1); builder.AppendTenantCode(); string sql = string.Format("SELECT {0} * FROM WF.SYS_NOT_RUNNING_TASK WITH(UPDLOCK, READPAST) WHERE {1} ORDER BY SORT_ID ASC", batchClause, builder.ToSqlString(TSqlBuilder.Instance)); return(this.QueryData(sql)); }
protected override string GetUpdateSql(SysTaskProcess data, ORMappingItemCollection mappings, Dictionary <string, object> context) { UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "UpdateTag"); uBuilder.AppendItem("UPDATE_TAG", "UPDATE_TAG + 1", "=", true); WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings); wBuilder.AppendItem("UPDATE_TAG", data.UpdateTag); return(string.Format("UPDATE {0} SET {1} WHERE {2}", mappings.TableName, uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance))); }
// Retrieve IList <T> IRetrievable <T> .RetrieveAll() { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); string isDeletedField = "ISDELETED"; //TODO , 通过ORM 反射找到对应的Field; 默认暂时认为都是使用ISDELETED字段 where.AppendItem(isDeletedField, true); string sqlString = ORMapping.GetSelectSql <T>(TSqlBuilder.Instance) + " where " + where.ToSqlString(TSqlBuilder.Instance); return(this.ExecuteQuery(sqlString, null)); }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { var groupID = WebUtility.GetRequestQueryValue("groupID", ""); if (groupID.IsNotEmpty()) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("g.GroupID", groupID); gridDataSource.Condition = builder.ToSqlString(TSqlBuilder.Instance); gridMain.DataBind(); } } }
/// <summary> /// 获取用户操作权限 /// </summary> /// <param name="userID">用户id</param> /// <param name="privilegeType">权限类型</param> /// <param name="privilegeType">权限类型</param> /// <returns></returns> public IList <VUserPrivilege> GetByUserIDAndType(string userID, string privilegeType) { string sql = ORMapping.GetSelectSql <VUserPrivilege>(TSqlBuilder.Instance); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("userid", SqlTextHelper.SafeQuote(userID)); if (null != privilegeType && privilegeType != "") { where.AppendItem("privilegeType", privilegeType); } sql += " where " + where.ToSqlString(TSqlBuilder.Instance); return(ExecuteQuery(sql)); }
public TodoWork Load(int piid, int userID) { string tableName = ORMapping.GetTableName(typeof(TodoWork)); WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("ProcessInstanceID", piid); where.AppendItem("userID", userID); string sqlCommand = string.Format("select top 1 * from {0} where {1} AND {2}", tableName, where.ToSqlString(TSqlBuilder.Instance), NotDeleted); List <TodoWork> result = ExecuteQuery(sqlCommand); return(result.Count > 0 ? result[0] : null); }
protected override string GetUpdateSql(WfProcessDescriptorDimension data, ORMappingItemCollection mappings, Dictionary <string, object> context) { UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings); uBuilder.AppendTenantCode(); WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings); wBuilder.AppendTenantCode(); return(string.Format("UPDATE {0} SET {1} WHERE {2}", mappings.TableName, uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance))); }
private static int DeleteRelatedData(string wfMatrixID) { WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); whereBuilder.AppendItem(DB_FIELD_MATRIX_ID, wfMatrixID); whereBuilder.AppendTenantCode(); StringBuilder strBuilder = new StringBuilder(); 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.RunSql(strBuilder.ToString())); }
protected override string GetUpdateSql(T data, ORMappingItemCollection mappings, Dictionary <string, object> context) { UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, "UpdateTime"); uBuilder.AppendItem("UpdateTime", "GETDATE()", "=", true); WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data, mappings); string sql = string.Format("UPDATE {0} SET {1} WHERE {2}", mappings.TableName, uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance)); return(sql); }
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()); }
protected override string GetUpdateSql(TxProcess data, ORMappingItemCollection mappings, Dictionary <string, object> context, string[] ignoreProperties) { UpdateSqlClauseBuilder uBuilder = ORMapping.GetUpdateSqlClauseBuilder(data, mappings, ignoreProperties); string serilizedProcess = GetSerilizedProcess(data); uBuilder.AppendItem("DATA", serilizedProcess); context["SerilizedProcess"] = serilizedProcess; WhereSqlClauseBuilder wBuilder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(data); return(string.Format("UPDATE {0} SET {1} WHERE {2}", this.GetTableName(), uBuilder.ToSqlString(TSqlBuilder.Instance), wBuilder.ToSqlString(TSqlBuilder.Instance))); }
public ProcessInstance LoadByBizProcessID(int bizProcessID) { WhereSqlClauseBuilder where = new WhereSqlClauseBuilder(); where.AppendItem("BizProcessID", bizProcessID); StringBuilder sql = new StringBuilder(); sql.AppendFormat("SELECT * FROM {0} WHERE {1} AND {2} ", ORMapping.GetTableName(typeof(ProcessInstance)), where.ToSqlString(TSqlBuilder.Instance), NotDeleted ); List <ProcessInstance> List = ExecuteQuery(sql.ToString()); return(List == null || List.Count == 0 ? null : List[0]); //返回第一个 }
/// <summary> /// 移动到已经完成系统任务中 /// </summary> /// <param name="taskID">被移动的任务的ID</param> /// <param name="status">重置任务的状态</param> public SysAccomplishedTask MoveToCompletedSysTask(SysTask task, SysTaskStatus status, string statusText) { SysAccomplishedTask result = null; PerformanceMonitorHelper.GetDefaultMonitor().WriteExecutionDuration(string.Format("MoveToAccomplished({0})", this.GetType().FullName), () => { Dictionary <string, object> context = new Dictionary <string, object>(); ORMappingItemCollection mappings = GetMappingInfo(context); WhereSqlClauseBuilder builder = ORMapping.GetWhereSqlClauseBuilderByPrimaryKey(task, mappings); ORMappingItemCollection mappingsCompleted = ORMapping.GetMappingInfo <SysAccomplishedTask>(); SysAccomplishedTask taskCompleted = new SysAccomplishedTask(task); taskCompleted.Status = status; taskCompleted.StatusText = statusText; StringBuilder sql = new StringBuilder(); sql.AppendFormat("DELETE FROM {0} WHERE {1}", mappingsCompleted.TableName, builder.ToSqlString(TSqlBuilder.Instance)); sql.Append(TSqlBuilder.Instance.DBStatementSeperator); sql.Append(GetMoveSysTaskSql(taskCompleted, context)); sql.Append(TSqlBuilder.Instance.DBStatementSeperator); sql.Append("IF @@ROWCOUNT = 0"); sql.Append(TSqlBuilder.Instance.DBStatementSeperator); sql.Append("\t" + ORMapping.GetInsertSql(taskCompleted, mappingsCompleted, TSqlBuilder.Instance)); sql.Append(TSqlBuilder.Instance.DBStatementSeperator); sql.AppendFormat("DELETE FROM {0} WHERE {1}", mappings.TableName, builder.ToSqlString(TSqlBuilder.Instance)); using (TransactionScope scope = TransactionScopeFactory.Create()) { DbHelper.RunSql(sql.ToString(), this.GetConnectionName()); scope.Complete(); } result = taskCompleted; }); return(result); }
protected void ShowToDoList() { lblTitle.Text = "待办事项"; //LDM 在这里设置页面的页大小,原来的代码设定为固定值20 UserSettings userSettings = UserSettings.GetSettings(DeluxeIdentity.CurrentUser.ID); GridViewTask.PageSize = userSettings.GetPropertyValue("CommonSettings", "ToDoListPageSize", this.GridViewTask.PageSize); WhereSqlClauseBuilder wscb = new WhereSqlClauseBuilder(); wscb.AppendItem("SEND_TO_USER", DeluxeIdentity.CurrentUser.ID); whereCondition.Value = wscb.ToSqlString(TSqlBuilder.Instance); //保存原始待办人ID到控件中 this.hiddenOriginalUserID.Value = DeluxeIdentity.CurrentUser.ID; ExecQuery(); }
/// <summary> /// 是否存在且没有归档 /// </summary> /// <param name="resourceID"></param> /// <returns></returns> public bool ExistsAndNotArchived(string resourceID) { resourceID.CheckStringIsNullOrEmpty("resourceID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("RESOURCE_ID", resourceID); builder.AppendTenantCode(typeof(AppCommonInfo)); string sql = string.Format("SELECT ARCHIVE_STATUS FROM WF.APPLICATIONS_COMMON_INFO WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); object result = DbHelper.RunSqlReturnScalar(sql, GetConnectionName()); return(result != null && result.ToString() != "1"); }
public String CreateProjectClassId(String parentClassId) { String sql; if (String.IsNullOrEmpty(parentClassId)) { sql = String.Format("SELECT ISNULL(MAX(PROJECT_CLASS_ID) + 1,1) FROM {0} WHERE ISNULL(PARENT_CLASS_ID,'')=''", ProjectClassInfoDBConst.TableName); } else { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(ProjectClassInfoDBConst.ParentClassID, parentClassId); sql = String.Format("SELECT ISNULL(MAX(PROJECT_CLASS_ID) + 1,'{0}01') FROM {1} WHERE {2}", parentClassId, ProjectClassInfoDBConst.TableName, builder.ToSqlString()); } DataTable dt = _DataHelper.GetDataTable(sql); return dt.Rows[0][0].ToString(); }
public List <TaxInfo> GetTaxInfoList(String yearMonth, WhereSqlClauseBuilder builder) { String sql = String.Format("SELECT * FROM {0} WHERE {1} ORDER BY QUANTUM_START" , String.IsNullOrEmpty(yearMonth) ? TaxInfoDBConst.TableName : ORMapping.GetMappingInfo <TaxMonthInfo>().TableName , builder.ToSqlString()); DataTable dt = _DataHelper.GetDataTable(sql); List <TaxInfo> result = new List <TaxInfo>(); for (Int32 i = 0; i < dt.Rows.Count; i++) { TaxInfo info = String.IsNullOrEmpty(yearMonth) ? new TaxInfo() : new TaxMonthInfo(); ORMapping.DataRowToObject(dt.Rows[i], info); result.Add(info); } return(result); }
public String CreateDepartmentCode(String parentDeptID) { String sql; if(String.IsNullOrEmpty(parentDeptID)) { sql = String.Format("SELECT ISNULL(MAX(DEPARTMENT_CODE) + 1,1) FROM {0} WHERE ISNULL(PARENT_ID,'')=''", DepartmentInfoDBConst.TableName); } else { DepartmentInfo departInfo = this.LoadDepartmentInfo(parentDeptID); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(DepartmentInfoDBConst.ParentID, parentDeptID); sql = String.Format("SELECT ISNULL(MAX(DEPARTMENT_CODE) + 1,'{0}01') FROM {1} WHERE {2}", departInfo.DepartmentCode, DepartmentInfoDBConst.TableName, builder.ToSqlString()); } DataTable dt = _DataHelper.GetDataTable(sql); return dt.Rows[0][0].ToString(); }
public static CheckLockResult CheckLock(string lockID, string personID) { ExceptionHelper.CheckStringIsNullOrEmpty(lockID, "lockID"); ExceptionHelper.CheckStringIsNullOrEmpty(personID, "personID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("LOCK_ID", lockID); builder.AppendTenantCode(); string sql = string.Format("SELECT *, GETDATE() AS [CURRENT_TIME] FROM WF.LOCK WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql).Tables[0]; return new CheckLockResult(personID, table); }
/// <summary> /// 返回流程所有的已流转过的活动节点 /// </summary> /// <param name="processID"></param> /// <returns></returns> public WfProcessCurrentActivityCollection Load(string processID) { processID.CheckStringIsNullOrEmpty(processID); WfProcessCurrentActivityCollection result = new WfProcessCurrentActivityCollection(); WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PROCESS_ID", processID); wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity)); string sql = string.Format("SELECT * FROM WF.PROCESS_CURRENT_ACTIVITIES WHERE {0}", wBuilder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; result.LoadFromDataView(table.DefaultView); return result; }
public Int32 ChangeStatus(String yearMonth, String feeID, Int32 userFlag) { FeeInfo feeInfo = this.LoadFeeInfo(yearMonth, feeID); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem(FeeInfoDBConst.FeeID, feeID); if (!String.IsNullOrEmpty(yearMonth)) { builder.AppendItem(FeeMonthInfoDBConst.YearMonth, yearMonth); } String sql = String.Format("UPDATE {0} SET USE_FLAG={1} WHERE {2} ", String.IsNullOrEmpty(yearMonth) ? FeeInfoDBConst.TableName : ORMapping.GetMappingInfo<FeeMonthInfo>().TableName, userFlag, builder.ToSqlString()); using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Required)) { int result = _DataHelper.ExecuteSql(sql); if (!String.IsNullOrEmpty(feeInfo.ParentID)) { this.UpdateCalculateExp(yearMonth, feeInfo.ParentID); } scope.Complete(); return result; } }
public WfClientUserOperationLogPageQueryResult QueryUserOperationLogByResourceID(string resourceID, int startRowIndex, int maximumRows, string orderBy, int totalCount) { resourceID.CheckStringIsNullOrEmpty("resourceID"); OperationContext.Current.FillContextToOguServiceContext(); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("RESOURCE_ID", resourceID); 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); }
public void Update(string processID, WfProcessCurrentActivityCollection pcas) { processID.CheckStringIsNullOrEmpty("processID"); pcas.NullCheck("pcas"); StringBuilder strB = new StringBuilder(); InSqlClauseBuilder deleteActivityIDs = new InSqlClauseBuilder("ACTIVITY_ID"); foreach (WfProcessCurrentActivity pca in pcas) { if (strB.Length > 0) strB.Append(TSqlBuilder.Instance.DBStatementSeperator); strB.Append(ORMapping.GetInsertSql(pca, TSqlBuilder.Instance)); deleteActivityIDs.AppendItem(pca.ActivityID); } WhereSqlClauseBuilder wBuilder = new WhereSqlClauseBuilder(); wBuilder.AppendItem("PROCESS_ID", processID); wBuilder.AppendTenantCodeSqlClause(typeof(WfProcessCurrentActivity)); string sqlDelete = string.Format("DELETE WF.PROCESS_CURRENT_ACTIVITIES WHERE {0}", wBuilder.ToSqlString(TSqlBuilder.Instance)); if (deleteActivityIDs.Count > 0) sqlDelete += string.Format(" AND {0}", deleteActivityIDs.ToSqlString(TSqlBuilder.Instance)); string sql = sqlDelete; if (strB.Length > 0) sql += TSqlBuilder.Instance.DBStatementSeperator + strB.ToString(); DbHelper.RunSqlWithTransaction(sql, GetConnectionName()); }