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);
}
