public static SubsubcategoryModel DataReader_to_SubsubcategoryModel(SqlDataReader reader) { SubsubcategoryModel subsubcategory = new SubsubcategoryModel(); subsubcategory.SubsubcategoryID = (Guid)reader["SubsubcategoryID"]; subsubcategory.SubcategoryID = (Guid)reader["SubcategoryID"]; subsubcategory.CategoryID = (Guid)reader["CategoryID"]; subsubcategory.SubsubcategoryName = (String)reader["SubsubcategoryName"]; subsubcategory.SubsubcategoryNameKey = (String)reader["SubsubcategoryNameKey"]; subsubcategory.OrderID = (int)reader["OrderID"]; subsubcategory.Visible = (bool)reader["Visible"]; subsubcategory.CreatedDate = (DateTime)reader["CreatedDate"]; subsubcategory.Description = (String)reader["Description"]; subsubcategory.Category = new CategoryParentModel { CategoryID = (Guid)reader["CategoryID"], CategoryName = (String)reader["CategoryName"], CategoryNameKey = (String)reader["CategoryNameKey"], FullyQualifiedName = (String)reader["CategoryNameKey"], LocationPath = "/" }; subsubcategory.Subcategory = new SubcategoryParentModel { SubcategoryID = (Guid)reader["SubcategoryID"], SubcategoryName = (String)reader["SubcategoryName"], SubcategoryNameKey = (String)reader["SubcategoryNameKey"], FullyQualifiedName = subsubcategory.Category.CategoryNameKey + "/" + (String)reader["SubcategoryNameKey"], LocationPath = subsubcategory.Category.CategoryNameKey }; subsubcategory.LocationPath = subsubcategory.Category.CategoryNameKey + "/" + subsubcategory.Subcategory.SubcategoryNameKey; subsubcategory.FullyQualifiedName = subsubcategory.LocationPath + "/" + subsubcategory.SubsubcategoryNameKey; return(subsubcategory); }
public static SubsubcategoryDetailsJson Subsubcategory(string accountNameKey, SubsubcategoryModel subsubcategoryIn, bool includeItems, bool includeHidden) { var subsubcategoryObjectOut = new SubsubcategoryDetailsJson(); subsubcategoryObjectOut.subsubcategory = new Models.Json.Categorization.SubsubcategoryJson(); subsubcategoryObjectOut.subsubcategory.subsubsubcategories = new List <CategorizationListItemJson>(); #region include parent objects subsubcategoryObjectOut.category = new CategorizationParentItemJson { name = subsubcategoryIn.Category.CategoryName, nameKey = subsubcategoryIn.Category.CategoryNameKey, fullyQualifiedName = subsubcategoryIn.Category.FullyQualifiedName }; subsubcategoryObjectOut.subcategory = new CategorizationParentItemJson { name = subsubcategoryIn.Subcategory.SubcategoryName, nameKey = subsubcategoryIn.Subcategory.SubcategoryNameKey, fullyQualifiedName = subsubcategoryIn.Subcategory.FullyQualifiedName }; #endregion #region Build out subsubsubcategorization list foreach (SubsubsubcategoryListModel subsubsubcategoryModel in subsubcategoryIn.Subsubsubcategories) { var subsubsubcategoryListItem = new CategorizationListItemJson { //id = subsubsubcategoryModel.SubsubcategoryID.ToString(), name = subsubsubcategoryModel.SubsubsubcategoryName, nameKey = subsubsubcategoryModel.SubsubsubcategoryNameKey, fullyQualifiedName = subsubsubcategoryModel.FullyQualifiedName }; //Get listing images for each subcategory in the list subsubsubcategoryListItem.images = Dynamics.Images.BuildDynamicImagesListForJson(accountNameKey, "subsubsubcategory", subsubsubcategoryModel.SubsubsubcategoryID.ToString(), true); subsubcategoryObjectOut.subsubcategory.subsubsubcategories.Add(subsubsubcategoryListItem); } #endregion #region Build out product list if (includeItems && subsubcategoryIn.Subsubsubcategories.Count == 0) { var account = Common.GetAccountObject(accountNameKey); //Search products string filter = "(locationPath eq '" + subsubcategoryIn.FullyQualifiedName + "')"; var productResults = DataAccess.Search.SearchProducts(account, null, filter, "orderId asc", 0, 1000, false, null, includeHidden); subsubcategoryObjectOut.subsubcategory.items = Dynamics.Products.TransformDynamicProductsListForJson(productResults.Results); } #endregion //categoryObjectOut.count = categoryObjectOut.categories.Count; //Get images for this category subsubcategoryObjectOut.subsubcategory.images = Dynamics.Images.BuildDynamicImagesListForJson(accountNameKey, "subsubcategory", subsubcategoryIn.SubsubcategoryID.ToString(), false); //subcategoryObjectOut.subcategory.id = subcategoryIn.SubcategoryID.ToString(); subsubcategoryObjectOut.subsubcategory.name = subsubcategoryIn.SubsubcategoryName; subsubcategoryObjectOut.subsubcategory.nameKey = subsubcategoryIn.SubsubcategoryNameKey; subsubcategoryObjectOut.subsubcategory.fullyQualifiedName = subsubcategoryIn.FullyQualifiedName; subsubcategoryObjectOut.subsubcategory.description = subsubcategoryIn.Description; return(subsubcategoryObjectOut); }
public JsonNetResult Subsubcategory(string categoryNameKey, string subcategoryNameKey, string subsubcategoryNameKey, bool includeHidden = false, bool includeItems = false) { ExecutionType executionType = ExecutionType.local; Stopwatch stopWatch = new Stopwatch(); stopWatch.Start(); //Get the subdomain (if exists) for the api call string accountNameKey = Common.GetSubDomain(Request.Url); if (String.IsNullOrEmpty(accountNameKey)) { return(new JsonNetResult { Data = "Not found" }); //return Request.CreateResponse(HttpStatusCode.NotFound); } SubsubcategoryModel subsubcategory = null; SubsubcategoryDetailsJson subsubcategoryDetailsObjectJson = null; string localCacheKey = accountNameKey + ":subsubcategory:" + categoryNameKey + ":" + subcategoryNameKey + ":" + subsubcategoryNameKey + ":includeHidden:" + includeHidden + "includeProducts:" + includeItems; #region (Plan A) Get json from local cache try { subsubcategoryDetailsObjectJson = (SubsubcategoryDetailsJson)HttpRuntime.Cache[localCacheKey]; } catch (Exception e) { var error = e.Message; //TODO: Log: error message for local cache call } #endregion if (subsubcategoryDetailsObjectJson == null) { #region (Plan B) Get Public json from second layer of Redis Cache IDatabase cache = CoreServices.RedisConnectionMultiplexers.RedisMultiplexer.GetDatabase(); string pathAndQuery = Common.GetApiPathAndQuery(Request.Url); string hashApiKey = accountNameKey + ":apicache"; string hashApiField = pathAndQuery; try { var redisApiValue = cache.HashGet(hashApiKey, hashApiField); if (redisApiValue.HasValue) { subsubcategoryDetailsObjectJson = JsonConvert.DeserializeObject <SubsubcategoryDetailsJson>(redisApiValue); executionType = ExecutionType.redis_secondary; } } catch { } #endregion if (subsubcategoryDetailsObjectJson == null) { #region (Plan C) Get category data from Redis Cache and rebuild try { //IDatabase cache = CoreServices.RedisConnectionMultiplexers.RedisMultiplexer.GetDatabase(); string hashMainKey = accountNameKey + ":categories"; string hashMainField = categoryNameKey + "/" + subcategoryNameKey + "/" + subsubcategoryNameKey + ":public"; if (includeHidden == true) { hashMainField = categoryNameKey + "/" + subcategoryNameKey + "/" + subsubcategoryNameKey + ":private"; } try { var redisValue = cache.HashGet(hashMainKey, hashMainField); if (redisValue.HasValue) { subsubcategory = JsonConvert.DeserializeObject <ApplicationCategorizationService.SubsubcategoryModel>(redisValue); executionType = ExecutionType.redis_main; } } catch { } } catch (Exception e) { var error = e.Message; //TODO: Log: error message for Redis call } #endregion if (subsubcategory == null) { #region (Plan D) Get data from WCF var applicationCategorizationServiceClient = new ApplicationCategorizationService.ApplicationCategorizationServiceClient(); try { applicationCategorizationServiceClient.Open(); subsubcategory = applicationCategorizationServiceClient.GetSubsubcategoryByNames(accountNameKey, categoryNameKey, subcategoryNameKey, subsubcategoryNameKey, includeHidden, Common.SharedClientKey); executionType = ExecutionType.wcf; WCFManager.CloseConnection(applicationCategorizationServiceClient); } catch (Exception e) { #region Manage Exception string exceptionMessage = e.Message.ToString(); var currentMethod = System.Reflection.MethodBase.GetCurrentMethod(); string currentMethodString = currentMethod.DeclaringType.FullName + "." + currentMethod.Name; // Abort the connection & manage the exception WCFManager.CloseConnection(applicationCategorizationServiceClient, exceptionMessage, currentMethodString); #endregion } #endregion } } #region Transform into json object, add images & cache locally or locally and radisAPI layer if (subsubcategoryDetailsObjectJson != null) { //Just cache locally (we got json from the api redis layer) HttpRuntime.Cache.Insert(localCacheKey, subsubcategoryDetailsObjectJson, null, DateTime.Now.AddMinutes(Common.CategorizationCacheTimeInMinutes), TimeSpan.Zero); } else if (subsubcategory != null) { //Transform categories into JSON and cache BOTH locally AND into redis subsubcategoryDetailsObjectJson = Transforms.Json.CategorizationTransforms.Subsubcategory(accountNameKey, subsubcategory, includeItems, includeHidden); HttpRuntime.Cache.Insert(localCacheKey, subsubcategoryDetailsObjectJson, null, DateTime.Now.AddMinutes(Common.CategorizationCacheTimeInMinutes), TimeSpan.Zero); try { cache.HashSet(hashApiKey, hashApiField, JsonConvert.SerializeObject(subsubcategoryDetailsObjectJson), When.Always, CommandFlags.FireAndForget); } catch { } } #endregion } if (subsubcategoryDetailsObjectJson == null) { //return empty return(new JsonNetResult()); } //Add execution data stopWatch.Stop(); subsubcategoryDetailsObjectJson.executionType = executionType.ToString(); subsubcategoryDetailsObjectJson.executionTime = stopWatch.Elapsed.TotalMilliseconds + "ms"; JsonNetResult jsonNetResult = new JsonNetResult(); jsonNetResult.Formatting = Newtonsoft.Json.Formatting.Indented; jsonNetResult.SerializerSettings.DateTimeZoneHandling = DateTimeZoneHandling.Local; //<-- Convert UTC times to LocalTime jsonNetResult.Data = subsubcategoryDetailsObjectJson; return(jsonNetResult); }
internal static DataAccessResponseType InsertSubsubcategory(string sqlPartition, string schemaId, SubsubcategoryModel subsubcategory, int maxAllowed) { DataAccessResponseType response = new DataAccessResponseType(); StringBuilder SqlStatement = new StringBuilder(); //SQL Statements ============================================================= //Check Row Count =========================================================== //SqlStatement.Append("DECLARE @ObjectCount INT "); SqlStatement.Append("SET @ObjectCount = (SELECT COUNT(*) "); SqlStatement.Append("FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory WHERE SubcategoryID='"); SqlStatement.Append(subsubcategory.SubcategoryID); SqlStatement.Append("') "); SqlStatement.Append("IF @ObjectCount < '"); SqlStatement.Append(maxAllowed); SqlStatement.Append("' "); SqlStatement.Append("BEGIN "); //GET MaxOrderBy ============================================================= //If the highest OrderBy is '0' we insert next as '0' (Alphabetical order) otherwise we +1 the OrderID so the newest categegorization item SqlStatement.Append("DECLARE @MaxOrderBy INT "); SqlStatement.Append("SET @MaxOrderBy = (SELECT MAX(OrderID) FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory WHERE SubcategoryID='"); SqlStatement.Append(subsubcategory.SubcategoryID); SqlStatement.Append("') "); SqlStatement.Append("IF(@MaxOrderBy > 0) "); SqlStatement.Append("BEGIN "); SqlStatement.Append("SET @MaxOrderBy = @MaxOrderBy + 1 "); SqlStatement.Append("END "); SqlStatement.Append("IF(@MaxOrderBy IS NULL) "); SqlStatement.Append("BEGIN "); SqlStatement.Append("SET @MaxOrderBy = 0 "); SqlStatement.Append("END "); //INSERT ============================================================= SqlStatement.Append("INSERT INTO "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory ("); //SqlStatement.Append("CategoryID,"); SqlStatement.Append("SubcategoryID,"); SqlStatement.Append("SubsubcategoryID,"); SqlStatement.Append("SubsubcategoryName,"); SqlStatement.Append("SubsubcategoryNameKey,"); SqlStatement.Append("CreatedDate, "); SqlStatement.Append("OrderID, "); SqlStatement.Append("Visible"); SqlStatement.Append(") VALUES ("); //Using parameterized queries to protect against injection //SqlStatement.Append("@CategoryID, "); SqlStatement.Append("@SubcategoryID, "); SqlStatement.Append("@SubsubcategoryID, "); SqlStatement.Append("@SubsubcategoryName, "); SqlStatement.Append("@SubsubcategoryNameKey, "); SqlStatement.Append("@CreatedDate, "); SqlStatement.Append("@MaxOrderBy, "); SqlStatement.Append("@Visible"); SqlStatement.Append(")"); //CLOSE: Check Row Count =========================================================== SqlStatement.Append(" END"); //SqlCommand sqlCommand = new SqlCommand(SqlStatement.ToString(), Sahara.Core.Settings.Azure.Databases.DatabaseConnections.DatabasePartitionSqlConnection(sqlPartition)); SqlCommand sqlCommand = Sahara.Core.Settings.Azure.Databases.DatabaseConnections.DatabasePartitionSqlConnection(sqlPartition).CreateCommand(); sqlCommand.CommandText = SqlStatement.ToString(); //Using parameterized queries to protect against injection //sqlCommand.Parameters.Add("@CategoryID", SqlDbType.UniqueIdentifier); sqlCommand.Parameters.Add("@SubcategoryID", SqlDbType.UniqueIdentifier); sqlCommand.Parameters.Add("@SubsubcategoryID", SqlDbType.UniqueIdentifier); sqlCommand.Parameters.Add("@SubsubcategoryName", SqlDbType.NVarChar); sqlCommand.Parameters.Add("@SubsubcategoryNameKey", SqlDbType.Text); sqlCommand.Parameters.Add("@CreatedDate", SqlDbType.DateTime); //sqlCommand.Parameters.Add("@OrderID", SqlDbType.Int); sqlCommand.Parameters.Add("@Visible", SqlDbType.Bit); //Assign values //sqlCommand.Parameters["@CategoryID"].Value = subsubcategory.CategoryID; sqlCommand.Parameters["@SubcategoryID"].Value = subsubcategory.SubcategoryID; sqlCommand.Parameters["@SubsubcategoryID"].Value = subsubcategory.SubsubcategoryID; sqlCommand.Parameters["@SubsubcategoryName"].Value = subsubcategory.SubsubcategoryName; sqlCommand.Parameters["@SubsubcategoryNameKey"].Value = subsubcategory.SubsubcategoryNameKey; sqlCommand.Parameters["@CreatedDate"].Value = DateTime.UtcNow; //sqlCommand.Parameters["@OrderID"].Value = subsubcategory.OrderID; sqlCommand.Parameters["@Visible"].Value = subsubcategory.Visible; // Add output parameters SqlParameter objectCount = sqlCommand.Parameters.Add("@ObjectCount", SqlDbType.Int); objectCount.Direction = ParameterDirection.Output; int insertAccountResult = 0; sqlCommand.Connection.OpenWithRetry(); try { insertAccountResult = sqlCommand.ExecuteNonQueryWithRetry(); // returns Int indicating number of rows affected if (insertAccountResult > 0) { response.isSuccess = true; } else { if ((int)objectCount.Value >= maxAllowed) { return(new DataAccessResponseType { isSuccess = false, ErrorMessage = "Your plan does not allow for more than " + maxAllowed + " categories per set. Please upgrade to increase your limits." //ErrorMessage = "You have reached the maximum amount of subsubcategories for this subcateory. Please upgrade your plan or contact support to increase your limits." }); } } } catch (Exception e) { //Log exception and email platform admins PlatformExceptionsHelper.LogExceptionAndAlertAdmins( e, "attempting to insert a application subsubcategory into SQL", System.Reflection.MethodBase.GetCurrentMethod() ); response.isSuccess = false; response.ErrorMessage = e.Message; return(response); } sqlCommand.Connection.Close(); return(response); }
internal static SubsubcategoryModel SelectSubsubcategoryByNames(string sqlPartition, string schemaId, string categoryName, string subcategoryName, string subsubcategoryName, bool includeHiddenSubsubsubcategories) { SubsubcategoryModel subsubcategory = null; StringBuilder SqlStatement = new StringBuilder(); //-- First set of results (Subsubcategory) -- SqlStatement.Append("SELECT TOP 1 "); SqlStatement.Append("subsub.SubsubcategoryID, subsub.SubsubcategoryName, subsub.SubsubcategoryNameKey, subsub.Visible, subsub.OrderID, subsub.CreatedDate, subsub.Description, sub.SubcategoryID, sub.SubcategoryName, sub.SubcategoryNameKey, cat.CategoryID, cat.CategoryName, cat.CategoryNameKey "); SqlStatement.Append("FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory subsub "); SqlStatement.Append("Inner Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subcategory sub "); SqlStatement.Append("ON sub.SubcategoryID = subsub.SubcategoryID "); SqlStatement.Append("Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Category cat "); SqlStatement.Append("ON cat.CategoryID = sub.CategoryID "); SqlStatement.Append("WHERE SubsubcategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(subsubcategoryName)); SqlStatement.Append("' "); SqlStatement.Append("AND SubcategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(subcategoryName)); SqlStatement.Append("' "); SqlStatement.Append("AND CategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(categoryName)); SqlStatement.Append("'"); //-- Get Subsubcategory ID As Variable -- SqlStatement.Append("; DECLARE @subsubcatid AS uniqueidentifier "); SqlStatement.Append("SELECT @subsubcatid = (SELECT TOP 1 subsub.SubsubcategoryID FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory subsub "); SqlStatement.Append("Inner Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subcategory sub "); SqlStatement.Append("ON sub.SubcategoryID = subsub.SubcategoryID "); SqlStatement.Append("Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Category cat "); SqlStatement.Append("ON cat.CategoryID = sub.CategoryID "); SqlStatement.Append("WHERE SubsubcategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(subsubcategoryName)); SqlStatement.Append("' "); SqlStatement.Append("AND SubcategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(subcategoryName)); SqlStatement.Append("' "); SqlStatement.Append("AND CategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(categoryName)); SqlStatement.Append("') "); SqlStatement.Append("SELECT @subsubcatid "); //-- Second set of results (Associated Subsubsubategories) -- SqlStatement.Append("; SELECT subsubsub.* FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubsubcategory subsubsub "); SqlStatement.Append("Left Outer Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory "); SqlStatement.Append("On Subsubcategory.SubsubcategoryID = subsubsub.SubsubcategoryID "); SqlStatement.Append("Where Subsubcategory.SubsubcategoryNameKey = '"); SqlStatement.Append(Sahara.Core.Common.Methods.ObjectNames.ConvertToObjectNameKey(subsubcategoryName)); SqlStatement.Append("' "); SqlStatement.Append("AND subsubsub.SubsubcategoryID = @subsubcatid"); if (includeHiddenSubsubsubcategories == false) { SqlStatement.Append(" AND subsubsub.Visible = '1'"); } SqlStatement.Append(" ORDER BY subsubsub.OrderID Asc, subsubsub.SubsubsubcategoryName Asc"); SqlCommand sqlCommand = Sahara.Core.Settings.Azure.Databases.DatabaseConnections.DatabasePartitionSqlConnection(sqlPartition).CreateCommand(); sqlCommand.CommandText = SqlStatement.ToString(); sqlCommand.Connection.OpenWithRetry(); SqlDataReader reader = sqlCommand.ExecuteReaderWithRetry(); while (reader.Read()) { subsubcategory = Transforms.Transforms.DataReader_to_SubsubcategoryModel(reader); } if (subsubcategory != null) { if (reader.NextResult()) //<-- Move to SubsubcategoryID results { //Skip this, not used } if (reader.NextResult()) //<-- Move to Subsubsubcategory results (if any exist) { subsubcategory.Subsubsubcategories = new List <SubsubsubcategoryListModel>(); while (reader.Read()) { subsubcategory.Subsubsubcategories.Add(Transforms.Transforms.DataReader_to_SubsubsubcategoryModel_List(reader, subsubcategory.Category.CategoryNameKey, subsubcategory.Subcategory.SubcategoryNameKey, subsubcategory.SubsubcategoryNameKey)); } } } sqlCommand.Connection.Close(); return(subsubcategory); }
internal static SubsubcategoryModel SelectSubsubcategoryById(string sqlPartition, string schemaId, string subsubcategoryId, bool includeHidden) { SubsubcategoryModel subsubcategory = null; StringBuilder SqlStatement = new StringBuilder(); SqlStatement.Append("SELECT TOP 1 "); SqlStatement.Append("subsub.SubsubcategoryID, subsub.SubsubcategoryName, subsub.SubsubcategoryNameKey, subsub.Visible, subsub.OrderID, subsub.CreatedDate, subsub.Description, sub.SubcategoryID, sub.SubcategoryName, sub.SubcategoryNameKey, cat.CategoryID, cat.CategoryName, cat.CategoryNameKey "); SqlStatement.Append("FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubcategory subsub "); SqlStatement.Append("Inner Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subcategory sub "); SqlStatement.Append("ON sub.SubcategoryID = subsub.SubcategoryID "); SqlStatement.Append("Join "); SqlStatement.Append(schemaId); SqlStatement.Append(".Category cat "); SqlStatement.Append("ON cat.CategoryID = sub.CategoryID "); SqlStatement.Append("WHERE SubsubcategoryID = '"); SqlStatement.Append(subsubcategoryId); SqlStatement.Append("'"); //-- Second set of results (Associated Subsubsubategories) -- SqlStatement.Append("; SELECT * FROM "); SqlStatement.Append(schemaId); SqlStatement.Append(".Subsubsubcategory "); SqlStatement.Append("WHERE SubsubcategoryID = '"); SqlStatement.Append(subsubcategoryId); SqlStatement.Append("'"); if (includeHidden == false) { SqlStatement.Append(" AND Visible = '1'"); } SqlStatement.Append(" ORDER BY OrderID Asc, SubsubsubcategoryName Asc"); //SqlCommand sqlCommand = new SqlCommand(SqlStatement.ToString(), Sahara.Core.Settings.Azure.Databases.DatabaseConnections.DatabasePartitionSqlConnection(sqlPartition)); SqlCommand sqlCommand = Sahara.Core.Settings.Azure.Databases.DatabaseConnections.DatabasePartitionSqlConnection(sqlPartition).CreateCommand(); sqlCommand.CommandText = SqlStatement.ToString(); sqlCommand.Connection.OpenWithRetry(); SqlDataReader reader = sqlCommand.ExecuteReaderWithRetry(); while (reader.Read()) { subsubcategory = Transforms.Transforms.DataReader_to_SubsubcategoryModel(reader); if (reader.NextResult()) //<-- Move to Subsubsubcategory results (if any exist) { subsubcategory.Subsubsubcategories = new List <SubsubsubcategoryListModel>(); while (reader.Read()) { subsubcategory.Subsubsubcategories.Add(Transforms.Transforms.DataReader_to_SubsubsubcategoryModel_List(reader, subsubcategory.Category.CategoryNameKey, subsubcategory.Subcategory.SubcategoryNameKey, subsubcategory.SubsubcategoryNameKey)); } } } sqlCommand.Connection.Close(); return(subsubcategory); }