public void Update(CategoryTree category)
{
if (category == null)
throw new ArgumentNullException("category");
if (category.Id == Guid.Empty)
throw new ArgumentNullException("Id");
if (string.IsNullOrEmpty(category.Key))
throw new ArgumentNullException("Key");
if (string.IsNullOrEmpty(category.Name))
throw new ArgumentNullException("Name");
var record = GetById(category.Id);
var alterColumns = new List<string>();
var alterValues = new List<SqlParameter>();
if (!String.Equals(record.Key, category.Key))
{
alterColumns.Add("[KEY] = @KEY");
alterValues.Add(new SqlParameter("@KEY", category.Key));
/// add check constraint
}
if (!String.Equals(record.Name, category.Name))
{
alterColumns.Add("[NAME] = @NAME");
alterValues.Add(new SqlParameter("@NAME", category.Name));
}
if (!Nullable<Guid>.Equals(record.ParentId, category.ParentId))
{
alterColumns.Add("[PARENT_GUID] = @PARENT_GUID");
alterValues.Add(new SqlParameter("@PARENT_GUID", category.ParentId));
/// add check constraint
}
if (alterColumns.Any())
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = String.Format(
"update [BUSINESS.CATEGORY_TREE] set {0} where [GUID_RECORD] = @GUID_RECORD",
String.Join(", ", alterColumns));
command.Parameters.AddWithValue("@GUID_RECORD", category.Id);
command.Parameters.AddRange(alterValues.ToArray());
command.ExecuteNonQuery();
}
}
}
}
public void Create(CategoryTree category)
{
if (category == null)
throw new ArgumentNullException("category");
if (category.Id == Guid.Empty)
category.Id = Guid.NewGuid();
if (string.IsNullOrEmpty(category.Key))
throw new ArgumentNullException("Key");
if (string.IsNullOrEmpty(category.Name))
throw new ArgumentNullException("Name");
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = connection.CreateCommand())
{
/// Check id
command.CommandText = string.Format("select 1 from [BUSINESS.CATEGORY_TREE] where [GUID_RECORD] = '{0}'", category.Id);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
throw new Exception("Record already exists");
}
/// Check key
command.CommandText = string.Format("select 1 from [BUSINESS.CATEGORY_TREE] where [KEY] = '{0}'", category.Key);
using (var reader = command.ExecuteReader())
{
if (reader.HasRows)
throw new Exception("Record already exists");
}
/// Check parent id
if (category.ParentId.HasValue && category.ParentId.Value != Guid.Empty)
{
command.CommandText = string.Format("select 1 from [BUSINESS.CATEGORY_TREE] where [GUID_RECORD] = '{0}'", category.ParentId);
using (var reader = command.ExecuteReader())
{
if (!reader.HasRows)
throw new Exception("Record does't exists");
}
}
command.CommandText = @"insert into [BUSINESS.CATEGORY_TREE] (
[GUID_RECORD],
[KEY],
[NAME],
[PARENT_GUID],
[BATCH_GUID],
[HIDDEN],
[DELETED])
values (@GUID_RECORD, @KEY, @NAME, @PARENT_GUID, @BATCH_GUID, @HIDDEN, @DELETED)";
command.Parameters.AddWithValue("@GUID_RECORD", category.Id);
command.Parameters.AddWithValue("@KEY", category.Key);
command.Parameters.AddWithValue("@NAME", category.Name);
command.Parameters.AddWithValue("@PARENT_GUID", category.ParentId.HasValue ? category.ParentId.Value : (object)DBNull.Value);
command.Parameters.AddWithValue("@BATCH_GUID", DBNull.Value);
command.Parameters.AddWithValue("@HIDDEN", 0);
command.Parameters.AddWithValue("@DELETED", 0);
command.ExecuteNonQuery();
}
}
}
public void Create(CategoryTree category)
{
CreateAsync(category).Wait();
}