private static void AddSqlParameters(ProductCategoryTransferObject productCategory, SqlCommand command)
{
const string categoryNameParameter = "@categoryName";
command.Parameters.Add(categoryNameParameter, SqlDbType.NVarChar, 15);
command.Parameters[categoryNameParameter].Value = productCategory.Name;
const string descriptionParameter = "@description";
command.Parameters.Add(descriptionParameter, SqlDbType.NText);
command.Parameters[descriptionParameter].IsNullable = true;
if (productCategory.Description != null)
{
command.Parameters[descriptionParameter].Value = productCategory.Description;
}
else
{
command.Parameters[descriptionParameter].Value = DBNull.Value;
}
const string pictureParameter = "@picture";
command.Parameters.Add(pictureParameter, SqlDbType.Image);
command.Parameters[pictureParameter].IsNullable = true;
if (productCategory.Picture != null)
{
command.Parameters[pictureParameter].Value = productCategory.Picture;
}
else
{
command.Parameters[pictureParameter].Value = DBNull.Value;
}
}
/// <inheritdoc/>
public async Task <bool> UpdateProductCategoryAsync(ProductCategoryTransferObject productCategory)
{
if (productCategory == null)
{
throw new ArgumentNullException(nameof(productCategory));
}
const string commandText =
@"UPDATE dbo.Categories SET CategoryName = @categoryName, Description = @description, Picture = @picture
WHERE CategoryID = @categoryId
SELECT @@ROWCOUNT";
using (var command = new SqlCommand(commandText, this.connection))
{
AddSqlParameters(productCategory, command);
const string categoryId = "@categoryId";
command.Parameters.Add(categoryId, SqlDbType.Int);
command.Parameters[categoryId].Value = productCategory.Id;
this.OpenSqlConnectionIfItClose();
var result = await command.ExecuteScalarAsync();
return(((int)result) > 0);
}
}
public static ProductCategory ToCategory(this ProductCategoryTransferObject productCategory)
{
var category = new ProductCategory
{
Id = productCategory.Id,
Name = productCategory.Name,
Description = productCategory.Description,
Picture = productCategory.Picture,
};
return(category);
}
/// <inheritdoc/>
public int InsertProductCategory(ProductCategoryTransferObject productCategory)
{
if (productCategory == null)
{
throw new ArgumentNullException(nameof(productCategory));
}
const string commandText =
@"INSERT INTO dbo.Categories (CategoryName, Description, Picture) OUTPUT Inserted.CategoryID
VALUES (@categoryName, @description, @picture)";
using (var command = new SqlCommand(commandText, this.connection))
{
AddSqlParameters(productCategory, command);
var id = command.ExecuteScalar();
return((int)id);
}
}
