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 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); } }
/// <inheritdoc/> public bool UpdateProductCategory(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; var result = command.ExecuteScalar(); return(((int)result) > 0); } }