public void Add(IProductsModel model)
{
string sqlQuery = "insert into Products(SupplierId, CategoryId, GroupId, NameWebStore, NameSupplier, CodeWebStore, " +
"CodeSupplier, UnitId, PriceWebStore, PriceSupplier, Available, LinkWebStore, LinkSupplier, Notes) " +
"values(@SupplierId, @CategoryId, @GroupId, @NameWebStore, @NameSupplier, @CodeWebStore, @CodeSupplier, " +
"@UnitId, @PriceWebStore, @PriceSupplier, @Available, @LinkWebStore, @LinkSupplier, @Notes)";
using (var db = new SqlCeConnection(connectionString))
{
db.Open();
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@SupplierId", model.SupplierId);
cmd.Parameters.AddWithValue("@CategoryId", model.CategoryId);
cmd.Parameters.AddWithValue("@GroupId", model.GroupId);
cmd.Parameters.AddWithValue("@NameWebStore", model.NameWebStore);
cmd.Parameters.AddWithValue("@NameSupplier", model.NameSupplier);
cmd.Parameters.AddWithValue("@CodeWebStore", model.CodeWebStore);
cmd.Parameters.AddWithValue("@CodeSupplier", model.CodeSupplier);
cmd.Parameters.AddWithValue("@UnitId", model.UnitId);
cmd.Parameters.AddWithValue("@PriceWebStore", model.PriceWebStore);
cmd.Parameters.AddWithValue("@PriceSupplier", model.PriceSupplier);
cmd.Parameters.AddWithValue("@Available", model.Available);
cmd.Parameters.AddWithValue("@LinkWebStore", model.LinkWebStore);
cmd.Parameters.AddWithValue("@LinkSupplier", model.LinkSupplier);
cmd.Parameters.AddWithValue("@Notes", model.Notes);
cmd.ExecuteNonQuery();
db.Close();
}
}
public void Update(IProductsModel model)
{
var sqlQuery = "update Products set SupplierId=@SupplierId, CategoryId=@CategoryId, GroupId=@GroupId, " +
"NameWebStore=@NameWebStore, NameSupplier=@NameSupplier, CodeWebStore=@CodeWebStore, CodeSupplier=@CodeSupplier, " +
"UnitId=@UnitId, PriceWebStore=@PriceWebStore, PriceSupplier=@PriceSupplier, Available=@Available, " +
"LinkWebStore=@LinkWebStore, LinkSupplier=@LinkSupplier, Notes=@Notes where Id=@Id";
using (var db = new SqlCeConnection(connectionString))
{
db.Open();
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@SupplierId", model.SupplierId);
cmd.Parameters.AddWithValue("@CategoryId", model.CategoryId);
cmd.Parameters.AddWithValue("@GroupId", model.GroupId);
cmd.Parameters.AddWithValue("@NameWebStore", model.NameWebStore);
cmd.Parameters.AddWithValue("@NameSupplier", model.NameSupplier);
cmd.Parameters.AddWithValue("@CodeWebStore", model.CodeWebStore);
cmd.Parameters.AddWithValue("@CodeSupplier", model.CodeSupplier);
cmd.Parameters.AddWithValue("@UnitId", model.UnitId);
cmd.Parameters.AddWithValue("@PriceWebStore", model.PriceWebStore);
cmd.Parameters.AddWithValue("@PriceSupplier", model.PriceSupplier);
cmd.Parameters.AddWithValue("@Available", model.Available);
cmd.Parameters.AddWithValue("@LinkWebStore", model.LinkWebStore);
cmd.Parameters.AddWithValue("@LinkSupplier", model.LinkSupplier);
cmd.Parameters.AddWithValue("@Notes", model.Notes);
cmd.Parameters.AddWithValue("@Id", model.Id);
cmd.ExecuteNonQuery();
db.Close();
}
}
/// <summary>
/// Додає товар
/// </summary>
/// <param name="productsModel">Екземпляр товару</param>
public void Add(IProductsModel productsModel)
{
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
string sqlQuery = "insert into Products(SupplierId, CategoryId, GroupId, NameWebStore, NameSupplier, CodeWebStore, " +
"CodeSupplier, UnitId, PriceWebStore, PriceSupplier, Available, LinkWebStore, LinkSupplier, Notes) " +
"values(@SupplierId, @CategoryId, @GroupId, @NameWebStore, @NameSupplier, @CodeWebStore, @CodeSupplier, " +
"@UnitId, @PriceWebStore, @PriceSupplier, @Available, @LinkWebStore, @LinkSupplier, @Notes)";
if (productsModel.GroupId == null)
{
sqlQuery = sqlQuery.Replace("@GroupId, ", "");
sqlQuery = sqlQuery.Replace("GroupId, ", "");
}
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@SupplierId", productsModel.SupplierId);
cmd.Parameters.AddWithValue("@CategoryId", productsModel.CategoryId);
if (productsModel.GroupId != null)
{
cmd.Parameters.AddWithValue("@GroupId", productsModel.GroupId);
}
cmd.Parameters.AddWithValue("@NameWebStore", productsModel.NameWebStore);
cmd.Parameters.AddWithValue("@NameSupplier", productsModel.NameSupplier);
cmd.Parameters.AddWithValue("@CodeWebStore", productsModel.CodeWebStore);
cmd.Parameters.AddWithValue("@CodeSupplier", productsModel.CodeSupplier);
cmd.Parameters.AddWithValue("@UnitId", productsModel.UnitId);
cmd.Parameters.AddWithValue("@PriceWebStore", productsModel.PriceWebStore);
cmd.Parameters.AddWithValue("@PriceSupplier", productsModel.PriceSupplier);
cmd.Parameters.AddWithValue("@Available", productsModel.Available);
cmd.Parameters.AddWithValue("@LinkWebStore", productsModel.LinkWebStore);
cmd.Parameters.AddWithValue("@LinkSupplier", productsModel.LinkSupplier);
cmd.Parameters.AddWithValue("@Notes", productsModel.Notes);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlCeException)
{
throw new Exception("Помилка створення товару в базі даних.");
}
}
}
/// <summary>
/// Оновлює товар
/// </summary>
/// <param name="productsModel">Екземпляр товару</param>
public void Update(IProductsModel productsModel)
{
using (var db = new SqlCeConnection(connectionString))
{
try
{
db.Open();
}
catch (SqlCeException)
{
throw new Exception("Немає підключення до бази даних.");
}
var sqlQuery = "update Products set SupplierId=@SupplierId, CategoryId=@CategoryId, GroupId=@GroupId, " +
"NameWebStore=@NameWebStore, NameSupplier=@NameSupplier, CodeWebStore=@CodeWebStore, CodeSupplier=@CodeSupplier, " +
"UnitId=@UnitId, PriceWebStore=@PriceWebStore, PriceSupplier=@PriceSupplier, Available=@Available, " +
"LinkWebStore=@LinkWebStore, LinkSupplier=@LinkSupplier, Notes=@Notes where Id=@Id";
var cmd = new SqlCeCommand(sqlQuery, db);
cmd.Prepare();
cmd.Parameters.AddWithValue("@SupplierId", productsModel.SupplierId);
cmd.Parameters.AddWithValue("@CategoryId", productsModel.CategoryId);
if (productsModel.GroupId != null)
{
cmd.Parameters.AddWithValue("@GroupId", productsModel.GroupId);
}
cmd.Parameters.AddWithValue("@NameWebStore", productsModel.NameWebStore);
cmd.Parameters.AddWithValue("@NameSupplier", productsModel.NameSupplier);
cmd.Parameters.AddWithValue("@CodeWebStore", productsModel.CodeWebStore);
cmd.Parameters.AddWithValue("@CodeSupplier", productsModel.CodeSupplier);
cmd.Parameters.AddWithValue("@UnitId", productsModel.UnitId);
cmd.Parameters.AddWithValue("@PriceWebStore", productsModel.PriceWebStore);
cmd.Parameters.AddWithValue("@PriceSupplier", productsModel.PriceSupplier);
cmd.Parameters.AddWithValue("@Available", productsModel.Available);
cmd.Parameters.AddWithValue("@LinkWebStore", productsModel.LinkWebStore);
cmd.Parameters.AddWithValue("@LinkSupplier", productsModel.LinkSupplier);
cmd.Parameters.AddWithValue("@Notes", productsModel.Notes);
cmd.Parameters.AddWithValue("@Id", productsModel.Id);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlCeException)
{
throw new Exception("Помилка оновлення товару в базі даних.");
}
}
}