public static Dictionary <Guid, KeyValuePair <Guid, string> > GetChildProjects(Guid projectId, UserConnection userConnection) { Query projectSelect = new Terrasoft.Core.DB.Select(userConnection) .Column("Id").As("Id") .Column("Name").As("Name") .Column("ParentProjectId").As("ParentProjectId") .From("Project"); projectSelect.Parameters.Add(new QueryParameter("StartingUnitId", null)); projectSelect.Parameters.Add(new QueryParameter("MaxDepth", null)); projectSelect.InitializeParameters(); HierarchicalSelectOptions options = new HierarchicalSelectOptions() { PrimaryColumnName = "Id", ParentColumnName = "ParentProjectId", SelectType = HierarchicalSelectType.Children, MaxDepthParameter = projectSelect.Parameters.GetByName("MaxDepth") }; QueryCondition startingCondition = options.StartingPrimaryColumnCondition; startingCondition.LeftExpression = new QueryColumnExpression("Id"); startingCondition.IsEqual(Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input)); string sqlText = userConnection.DBEngine.GetQuerySqlText(projectSelect as Select, options); var parameters = new QueryParameterCollection(); parameters.Add(new QueryParameter() { Name = "ParentProjectId", Value = projectId }); var list = new Dictionary <Guid, KeyValuePair <Guid, string> >(); using (var dbExecutor = userConnection.EnsureDBConnection()) { using (var reader = dbExecutor.ExecuteReader(sqlText, parameters)) { while (reader.Read()) { if (!reader.IsDBNull(0)) { var parentId = Guid.Empty; if (!reader.IsDBNull(2)) { parentId = userConnection.DBTypeConverter.DBValueToGuid(reader[2]); } var id = userConnection.DBTypeConverter.DBValueToGuid(reader[0]); list.Add(id, new KeyValuePair <Guid, string>(parentId, reader[1].ToString())); } } } } return(list); }
private static System.Collections.ObjectModel.Collection <KeyValuePair <Guid, string> > GetParentProjects(Guid projectId, UserConnection userConnection) { Query projectSelect = new Terrasoft.Core.DB.Select(userConnection) .Column("Id").As("Id") .Column("Name").As("Name") .Column("ParentProjectId").As("ParentProjectId") .From("Project"); projectSelect.Parameters.Add(new QueryParameter("StartingUnitId", null)); projectSelect.Parameters.Add(new QueryParameter("MaxDepth", null)); projectSelect.InitializeParameters(); HierarchicalSelectOptions options = new HierarchicalSelectOptions() { PrimaryColumnName = "Id", ParentColumnName = "ParentProjectId", SelectType = HierarchicalSelectType.Parents, MaxDepthParameter = projectSelect.Parameters.GetByName("MaxDepth") }; QueryCondition startingCondition = options.StartingPrimaryColumnCondition; startingCondition.LeftExpression = new QueryColumnExpression("Id"); startingCondition.IsEqual(Column.Parameter(projectId, "ParentProjectId", Terrasoft.Common.ParameterDirection.Input)); string sqlText = userConnection.DBEngine.GetQuerySqlText(projectSelect as Select, options); var parameters = new QueryParameterCollection(); parameters.Add(new QueryParameter() { Name = "ParentProjectId", Value = projectId }); var list = new System.Collections.ObjectModel.Collection <KeyValuePair <Guid, string> >(); using (var dbExecutor = userConnection.EnsureDBConnection()) { using (var reader = dbExecutor.ExecuteReader(sqlText, parameters)) { while (reader.Read()) { Guid id = Guid.Empty; if (Guid.TryParse(reader[0].ToString(), out id)) { list.Add(new KeyValuePair <Guid, string>(id, reader[1].ToString())); } } } } return(list); }
/// <summary> /// ##### ## ########### ############## #### ###### ###### # ########## ############## #### ######## ############### /// (###########, #############, ############ ### #######) ### ############## ##### ####### ##########. /// </summary> /// <param name="parentRoleId">############# ############### ### ############## ####.</param> /// <returns>###### ############### #### ########### ############### #####, ####### ##########.</returns> public object[] GetChildAdminUnits(string parentRoleId) { Query adminUnitSelect = new Terrasoft.Core.DB.Select(UserConnection).Column("Id") .As("Id") .Column("Name") .As("Name") .Column("ParentRoleId") .As("ParentRoleId") .From("SysAdminUnit") .Where("SysAdminUnitTypeValue") .In(Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Organisation, "AdminUnitType_Organisation", Common.ParameterDirection.Input), Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Department, "AdminUnitType_Department", Common.ParameterDirection.Input), Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Manager, "AdminUnitType_Manager", Common.ParameterDirection.Input), Column.Parameter(Terrasoft.Core.DB.SysAdminUnitType.Team, "AdminUnitType_Team", Common.ParameterDirection.Input), Column.Parameter(6, "AdminUnitType_FunctionalRole", Common.ParameterDirection.Input)); adminUnitSelect.Parameters.Add(new QueryParameter("StartingUnitId", null)); adminUnitSelect.Parameters.Add(new QueryParameter("MaxDepth", null)); adminUnitSelect.InitializeParameters(); HierarchicalSelectOptions options = new HierarchicalSelectOptions() { PrimaryColumnName = "Id", ParentColumnName = "ParentRoleId", SelectType = HierarchicalSelectType.Children, MaxDepthParameter = adminUnitSelect.Parameters.GetByName("MaxDepth") }; QueryCondition startingCondition = options.StartingPrimaryColumnCondition; startingCondition.LeftExpression = new QueryColumnExpression("ParentRoleId"); startingCondition.IsEqual(Column.Parameter(parentRoleId, "FolderId", Common.ParameterDirection.Input)); string sqlText = UserConnection.DBEngine.GetQuerySqlText(adminUnitSelect as Select, options); var parameters = new QueryParameterCollection(); parameters.Add(new QueryParameter() { Name = "AdminUnitType_Organisation", Value = Terrasoft.Core.DB.SysAdminUnitType.Organisation, ValueTypeName = "Integer" }); parameters.Add(new QueryParameter() { Name = "AdminUnitType_Department", Value = Terrasoft.Core.DB.SysAdminUnitType.Department, ValueTypeName = "Integer" }); parameters.Add(new QueryParameter() { Name = "AdminUnitType_Manager", Value = Terrasoft.Core.DB.SysAdminUnitType.Manager, ValueTypeName = "Integer" }); parameters.Add(new QueryParameter() { Name = "AdminUnitType_Team", Value = Terrasoft.Core.DB.SysAdminUnitType.Team, ValueTypeName = "Integer" }); parameters.Add(new QueryParameter() { Name = "AdminUnitType_FunctionalRole", Value = 6, ValueTypeName = "Integer" }); parameters.Add(new QueryParameter() { Name = "FolderId", Value = parentRoleId, ValueTypeName = "Guid" }); var list = new List <object>(); list.Add(new Guid(parentRoleId)); using (DBExecutor dbExecutor = UserConnection.EnsureDBConnection()) { using (IDataReader reader = dbExecutor.ExecuteReader(sqlText, parameters)) { while (reader.Read()) { Guid id = reader.GetColumnValue <Guid>("Id"); list.Add(id); } } } return(list.ToArray()); }