public UserTaskCollection LoadUserTasksByActivity(string activityID, Action <InSqlClauseBuilder> action) { action.NullCheck("action"); InSqlClauseBuilder builder = new InSqlClauseBuilder(); action(builder); string sql = string.Format("SELECT * FROM WF.USER_TASK WHERE ACTIVITY_ID = {0}", TSqlBuilder.Instance.CheckUnicodeQuotationMark(activityID)); if (builder.Count > 0) { sql += " AND " + builder.ToSqlStringWithInOperator(TSqlBuilder.Instance); } UserTaskCollection result = new UserTaskCollection(); 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); }
public UserTaskCollection LoadUserTasks(Action <WhereSqlClauseBuilder> action) { action.NullCheck("action"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); action(builder); string sql = string.Format("SELECT * FROM WF.USER_TASK WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); UserTaskCollection result = new UserTaskCollection(); 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); }
public StartWorkflowJobCollection LoadValidData() { var result = new StartWorkflowJobCollection(); var ds = DbHelper.RunSqlReturnDS(LOAD_VALID_JOB_SQL_CLAUSE, this.GetConnectionName()); ORMapping.DataViewToCollection(result, ds.Tables[0].DefaultView); List <string> jobIDs = new List <string>(); result.ForEach(d => jobIDs.Add(d.JobID)); JobScheduleWithJobIDCollection schedules = JobScheduleAdapter.Instance.LoadByJobID(jobIDs.ToArray()); foreach (JobBase job in result) { IList <JobScheduleWithJobID> list = schedules.FindAll(s => s.JobID == job.JobID); foreach (JobScheduleWithJobID schdule in list) { job.Schedules.Add(schdule); } } return(result); }
/// <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 SOARolePropertyDefinitionCollection LoadByRoleID(string roleID) { roleID.CheckStringIsNullOrEmpty("roleID"); string sql = string.Format("SELECT * FROM WF.ROLE_PROPERTIES_DEFINITIONS WHERE {0} ORDER BY SORT_ORDER", roleID.ToRoleIDCriteria()); using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Suppress)) { DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; SOARolePropertyDefinitionCollection result = new SOARolePropertyDefinitionCollection(); foreach (DataRow row in table.Rows) { SOARolePropertyDefinition property = new SOARolePropertyDefinition(); ORMapping.DataRowToObject(row, property); result.Add(property); } return(result); } }
public EmailMessageCollection LoadCandidateMessages(int batchCount) { string topDesp = batchCount >= 0 ? string.Format(" TOP {0} ", batchCount) : string.Empty; WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("1", 1); builder.AppendTenantCode(); string sql = string.Format("SELECT {0}* FROM MSG.EMAIL_MESSAGES WITH (UPDLOCK, READPAST) WHERE {1} ORDER BY SORT_ID", topDesp, builder.ToSqlString(TSqlBuilder.Instance)); EmailMessageCollection result = new EmailMessageCollection(); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { EmailMessage message = new EmailMessage(); DataRowToEmailMessage(row, message); result.Add(message); } return(result); }
private static DataTable LoadAddresses(string messageID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("MESSAGE_ID", messageID); builder.AppendTenantCode(); string sql = string.Format("SELECT * FROM MSG.EMAIL_ADDRESSES WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); return(DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]); }
private bool CheckScheduleInJob(string schID) { WhereSqlClauseBuilder whereBuilder = new WhereSqlClauseBuilder(); whereBuilder.AppendItem("SCHEDULE_ID", schID); whereBuilder.AppendTenantCode(typeof(JobSchedule)); DataSet ds = DbHelper.RunSqlReturnDS( JobBaseAdapter.SELECT_JOB_SCHEDULES_ID_CLAUSE + whereBuilder.ToSqlString(TSqlBuilder.Instance), GetConnectionName()); return(ds.Tables[0].Rows.Count != 0 ? true : false); }
/// <summary> /// 调用本方法,获取有Mapping关系的内部用户; /// </summary> /// <returns></returns> public DataTable GetMappingRelationUsers() { string sql = @"SELECT EXT_INT_USERMAPPING.EXT_USERID,EXT_INT_USERMAPPING.USERID, USERS.LOGON_NAME,USERS.LAST_NAME,USERS.FIRST_NAME FROM USERS,EXT_INT_USERMAPPING WHERE USERS.GUID = EXT_INT_USERMAPPING.USERID;"; DataSet ds = DbHelper.RunSqlReturnDS(sql, GetConnectionName()); if (ds != null && ds.Tables.Count == 1) { return(ds.Tables[0]); } return(null); }
internal EmailAttachmentCollection LoadAttachments(string messageID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("MESSAGE_ID", messageID); builder.AppendTenantCode(); string sql = string.Format("SELECT * FROM MSG.EMAIL_ATTACHMENTS WHERE {0} ORDER BY SORT_ID", builder.ToSqlString(TSqlBuilder.Instance)); EmailAttachmentCollection result = new EmailAttachmentCollection(); ORMapping.DataViewToCollection(result, DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0].DefaultView); return(result); }
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)); }
public bool Exists(Action <WhereSqlClauseBuilder> whereAction, ORMappingItemCollection mappings) { whereAction.NullCheck("whereAction"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); whereAction(builder); builder.AppendTenantCode(typeof(T)); string sql = string.Format("SELECT TOP 1 * FROM {0}", mappings.TableName); if (builder.Count > 0) { sql = sql + string.Format(" WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); } return((int)DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0].Rows.Count > 0); }
public UserTask LoadSingleUserTaskByID(string taskID) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("TASK_GUID", taskID); string sql = string.Format("SELECT TOP (1) * FROM WF.USER_TASK WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; UserTask userTask = new UserTask(); if (table.Rows.Count > 0) { ORMapping.DataRowToObject(table.Rows[0], userTask); } return(userTask); }
public InvalidAssigneeCollection Load(IConnectiveSqlClause builder) { InvalidAssigneeCollection result = new InvalidAssigneeCollection(); string sql = SelectSQL; if (builder.IsEmpty == false) { sql = string.Format("{0} WHERE {1}", SelectSQL, builder.AppendTenantCodeSqlClause(typeof(InvalidAssignee)).ToSqlString(TSqlBuilder.Instance)); } DataTable dt = DbHelper.RunSqlReturnDS(sql, this.GetConnectionName()).Tables[0]; ORMapping.DataViewToCollection(result, dt.DefaultView); return(result); }
public UploadFileHistory Load(int id) { WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("ID", id); builder.AppendTenantCode(typeof(UploadFileHistory)); string sql = string.Format("SELECT * FROM WF.UPLOAD_FILE_HISTORY WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, AppLogSettings.GetConfig().ConnectionName).Tables[0]; ExceptionHelper.FalseThrow(table.Rows.Count > 0, "不能找到ID为{0}的UploadFileHistory的数据"); UploadFileHistory history = new UploadFileHistory(); ORMapping.DataRowToObject(table.Rows[0], history); return(history); }
protected TDataCollection QueryData <TData, TDataCollection>(ORMappingItemCollection mapping, string sql) where TData : new() where TDataCollection : EditableDataObjectCollectionBase <TData>, new() { TDataCollection result = new TDataCollection(); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { TData data = new TData(); ORMapping.DataRowToObject(row, mapping, data); result.Add(data); } return(result); }
/// <summary> /// 根据角色ID判断哪些角色有了扩展属性定义 /// </summary> /// <param name="roleIDs"></param> /// <returns></returns> public Dictionary <string, bool> AreExist(IEnumerable <string> roleIDs) { roleIDs.NullCheck("roleIDs"); Dictionary <string, bool> result = new Dictionary <string, bool>(); InSqlClauseBuilder builder = new InSqlClauseBuilder(); foreach (string roleID in roleIDs) { if (result.ContainsKey(roleID) == false) { result.Add(roleID, false); builder.AppendItem(roleID); } } if (result.Count > 0) { WhereSqlClauseBuilder tenantBuilder = new WhereSqlClauseBuilder(); tenantBuilder.AppendTenantCode(); string tenantCriteria = string.Empty; if (tenantBuilder.IsEmpty == false) { tenantCriteria = " AND " + tenantBuilder.ToSqlString(TSqlBuilder.Instance); } string sql = string.Format("SELECT ROLE_ID FROM WF.ROLE_PROPERTIES_DEFINITIONS WHERE ROLE_ID IN ({0}){1} GROUP BY ROLE_ID", builder.ToSqlString(TSqlBuilder.Instance), tenantCriteria); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { result[row["ROLE_ID"].ToString()] = true; } } return(result); }
/// <summary> /// 根据CategoryID获取Category集合 /// </summary> /// <param name="categoryID"></param> /// <returns></returns> public TaskCategoryCollection GetCategoriesByCategoryIDs(params string[] categoryID) { string strSQL = string.Empty; TaskCategoryCollection taskCategories = new TaskCategoryCollection(); if (categoryID.Length > 0) { InSqlClauseBuilder inSQL = new InSqlClauseBuilder("CATEGORY_GUID"); inSQL.AppendItem(categoryID); strSQL = string.Format("SELECT * FROM WF.USER_TASK_CATEGORY WHERE {0} ORDER BY INNER_SORT_ID", inSQL.AppendTenantCodeSqlClause(typeof(TaskCategory)).ToSqlString(TSqlBuilder.Instance)); DataView dv = DbHelper.RunSqlReturnDS(strSQL, ConnectionDefine.DBConnectionName).Tables[0].DefaultView; taskCategories.LoadFromDataView(dv); } return(taskCategories); }
/// <summary> /// 根据SQL获取category /// </summary> /// <param name="where"></param> /// <param name="orderby"></param> /// <param name="selector"></param> /// <returns></returns> public TaskCategoryCollection GetCategoriesByWhereCondition(string where, string orderby, List <string> selector) { string sql = string.Format("SELECT"); if (selector != null && selector.Count > 0) { foreach (string sel in selector) { sql += TSqlBuilder.Instance.CheckQuotationMark(sel, true); } } else { sql += " * "; } WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("USER_ID", DeluxeIdentity.CurrentUser.ID); builder.AppendTenantCode(typeof(TaskCategory)); sql += string.Format("FROM WF.USER_TASK_CATEGORY WHERE {0}", builder.ToSqlString(TSqlBuilder.Instance)); if (where != string.Empty && where != null) { sql += string.Format("AND ({0})", where); } if (orderby != string.Empty && orderby != null) { sql += string.Format("ORDER BY {0}", orderby); } DataView dv = DbHelper.RunSqlReturnDS(sql, ConnectionDefine.DBConnectionName).Tables[0].DefaultView; TaskCategoryCollection tcc = new TaskCategoryCollection(); tcc.LoadFromDataView(dv); return(tcc); }
public UserTaskCollection LoadUserTasks(string tableName, IConnectiveSqlClause wBuilder, OrderBySqlClauseBuilder orderByBuilder, int count, bool nolock = false) { UserTaskCollection result = new UserTaskCollection(); string top = string.Empty; if (count >= 0) { top = string.Format("TOP {0}", count); } if (wBuilder.IsEmpty == false) { if (nolock) { tableName += " (NOLOCK)"; } string sql = string.Format("SELECT {0} * FROM {1} WHERE {2}", top, tableName, wBuilder.ToSqlString(TSqlBuilder.Instance)); if (orderByBuilder.Count > 0) { sql += " ORDER BY " + orderByBuilder.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); }
public bool CheckGroupManager(WfGroup group, IUser manger) { string sql = string.Format("SELECT * FROM WF.GROUP_USERS WHERE GROUP_ID = {0} AND USER_ID= {1}", TSqlBuilder.Instance.CheckQuotationMark(group.GroupID, true), TSqlBuilder.Instance.CheckQuotationMark(manger.ID, true)); DataSet ds = DbHelper.RunSqlReturnDS(sql, GetConnectionName()); if (ds == null) { return(false); } if (ds.Tables.Count == 1 && ds.Tables[0].Rows.Count > 0) { return(true); } else { return(false); } }
/// <summary> /// 根据RoleID加载行信息 /// </summary> /// <param name="roleID"></param> /// <param name="role"></param> /// <param name="definition">列定义</param> /// <returns></returns> public SOARolePropertyRowCollection LoadByRoleID(string roleID, IRole role, SOARolePropertyDefinitionCollection definition) { roleID.CheckStringIsNullOrEmpty("roleID"); definition.NullCheck("definition"); StringBuilder strB = new StringBuilder(); strB.AppendFormat("SELECT * FROM WF.ROLE_PROPERTIES_ROWS WHERE {0} ORDER BY ROW_NUMBER", roleID.ToRoleIDCriteria()); strB.Append(TSqlBuilder.Instance.DBStatementSeperator); strB.AppendFormat("SELECT * FROM WF.ROLE_PROPERTIES_CELLS WHERE {0} ORDER BY PROPERTIES_ROW_ID", roleID.ToRoleIDCriteria()); SOARolePropertyRowCollection result = new SOARolePropertyRowCollection(role); using (TransactionScope scope = TransactionScopeFactory.Create(TransactionScopeOption.Suppress)) { DataSet ds = DbHelper.RunSqlReturnDS(strB.ToString(), GetConnectionName()); Dictionary <int, SOARolePropertyValueCollection> propertyValues = SOARolePropertyValueCollection.LoadAndGroup(ds.Tables[1].DefaultView, definition); foreach (DataRow row in ds.Tables[0].Rows) { SOARolePropertyRow property = new SOARolePropertyRow(role); ORMapping.DataRowToObject(row, property); SOARolePropertyValueCollection values = null; if (propertyValues.TryGetValue(property.RowNumber, out values)) { property.Values.CopyFrom(values); } result.Add(property); } } return(result); }
protected TCollection QueryData(ORMappingItemCollection mapping, string sql) { TCollection result = new TCollection(); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; foreach (DataRow row in table.Rows) { T data = CreateNewData(row); ORMapping.DataRowToObject(row, mapping, data); if (data is ILoadableDataEntity) { ((ILoadableDataEntity)data).Loaded = true; } result.Add(data); } return(result); }
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); }
private static EmailMessage LoadEmailMessage(string messageID, string tableName) { messageID.CheckStringIsNullOrEmpty("messageID"); WhereSqlClauseBuilder builder = new WhereSqlClauseBuilder(); builder.AppendItem("MESSAGE_ID", messageID); builder.AppendTenantCode(); string sql = string.Format("SELECT * FROM {0} WHERE {1}", tableName, builder.ToSqlString(TSqlBuilder.Instance)); DataTable table = DbHelper.RunSqlReturnDS(sql, GetConnectionName()).Tables[0]; (table.Rows.Count > 0).FalseThrow("不能找到MESSAGE_ID为{0}的记录", messageID); EmailMessage message = new EmailMessage(); DataRowToEmailMessage(table.Rows[0], message); return(message); }