public List <CrudeProductCategoryData> FetchTopLevel()
{
var dataList = new List <CrudeProductCategoryData>();
string sql = @"
select product_category_id, product_category_became_id, product_category_parent_id, product_category_code, product_category_name, product_category_position, state_rcd, user_id, date_time
from product_category as pc
where pc.product_category_id = pc.product_category_parent_id
and pc.product_category_became_id is null
order by pc.product_category_position
";
using (var conn = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) {
conn.Open();
using (var command = new SqlCommand(sql, conn)) {
IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
while (reader.Read())
{
var data = new CrudeProductCategoryData();
data.Populate(reader);
dataList.Add(data);
}
}
return(dataList);
}
}
public Category(Guid productCategoryId)
{
// category base info
ProductCategory = new CrudeProductCategoryData();
ProductCategory.FetchByProductCategoryId(productCategoryId);
// category images
//var crudeImage = new CrudeProductCategoryImageData();
ProductCategoryImage = CrudeProductCategoryImageData.FetchByProductCategoryId(productCategoryId);
// category documentation
//var crudeDocumentation = new CrudeProductCategoryDocumentationData();
ProductCategoryDocumentation = CrudeProductCategoryDocumentationData.FetchByProductCategoryId(productCategoryId);
}
public void Save(Guid userId)
{
// start transaction
using (var connection = new SqlConnection(ConfigurationManager.AppSettings["Conn"])) {
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try {
// keep copy of 'old' product
CrudeProductData oldProduct = ObjectCopier.Clone(product);
// save new product
product.ProductId = Guid.NewGuid();
product.StateRcd = DefaultStateRef.Updated;
product.UserId = userId;
product.DateTime = DateTime.UtcNow;
product.Insert(connection, transaction);
// duplicate old products category mapping
CrudeProductCategoryData.InsertNewCategoryMapping(
connection,
transaction,
oldProduct.ProductId,
product.ProductId,
userId
);
// update product to be 'old' version
oldProduct.ProductBecameId = product.ProductId;
oldProduct.UpdateBecame(connection, transaction);
// change identifier keys, save
foreach (CrudeProductIdentifierData identifier in ProductIdentifier)
{
identifier.ProductIdentifierId = Guid.NewGuid();
identifier.ProductId = product.ProductId;
identifier.UserId = userId;
identifier.DateTime = DateTime.UtcNow;
identifier.Insert(connection, transaction);
}
// change attribute keys, save
foreach (CrudeProductAttributeData attribute in ProductAttribute)
{
attribute.ProductAttributeId = Guid.NewGuid();
attribute.ProductId = product.ProductId;
attribute.UserId = userId;
attribute.DateTime = DateTime.UtcNow;
attribute.Insert(connection, transaction);
}
// change info keys, save
foreach (CrudeProductInfoData info in ProductInfo)
{
info.ProductInfoId = Guid.NewGuid();
info.ProductId = product.ProductId;
info.UserId = userId;
info.DateTime = DateTime.UtcNow;
info.Insert(connection, transaction);
}
// change documentation keys, save
foreach (CrudeProductDocumentationData documentation in ProductDocumentation)
{
documentation.ProductDocumentationId = Guid.NewGuid();
documentation.ProductId = product.ProductId;
documentation.UserId = userId;
documentation.DateTime = DateTime.UtcNow;
documentation.Insert(connection, transaction);
}
// change image keys, save
foreach (CrudeProductImageData image in ProductImage)
{
image.ProductImageId = Guid.NewGuid();
image.ProductId = product.ProductId;
image.UserId = userId;
image.DateTime = DateTime.UtcNow;
image.Insert(connection, transaction);
}
// commit transaction
transaction.Commit();
} catch (Exception ex) {
transaction.Rollback();
throw new Exception("DataAccessLayer, Product, Save, Failed", ex);
}
}
}
