/// <summary>
/// 把添加商品的窗口中的数据给数据库中的商品表添加
/// 添加商品数量的存储过程
/// </summary>
/// <param name="produt"></param>
/// <param name="inventory"></param>
/// <returns></returns>
public bool InsertProduct(ProdutsModel produt, ProductInventoryModel inventory)
{
List <string> procList = new List <string>()
{
"InsertProduct", //添加商品的存储过程
"InsertInventory" //添加商品数量的存储过程
};
List <SqlParameter[]> psList = new List <SqlParameter[]>();
SqlParameter[] prodPs = new SqlParameter[]
{
new SqlParameter("@productId", produt.ProductId),
new SqlParameter("@productName", produt.ProductName),
new SqlParameter("@unitPrice", produt.UnitPrice),
new SqlParameter("@unit", produt.Unit),
new SqlParameter("@discount", produt.Discount),
new SqlParameter("@categoryId", produt.CategoryId)
};
SqlParameter[] inventPs = new SqlParameter[]
{
new SqlParameter("@productId", inventory.ProductId),
new SqlParameter("@minCount", inventory.MinCount),
new SqlParameter("@maxCount", inventory.MaxCount)
};
psList.Add(prodPs);
psList.Add(inventPs);
return(SQLHelper.UpdateByTran(procList, psList));
}
public int AddInventory(ProductInventoryModel model)
{
int res = 0;
try
{
string str = @"data source=DESKTOP-93R7Q3C\SQLEXPRESS; initial catalog = ShopBridge;integrated security = true";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("Add_Into_Inventory", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProductName", model.ProductName);
cmd.Parameters.AddWithValue("@Suffix", model.Suffix);
cmd.Parameters.AddWithValue("@ManufacturerName", model.ManufacturerName);
cmd.Parameters.AddWithValue("@ModelNumber", model.ModelNumber);
cmd.Parameters.AddWithValue("@MDate", model.ManufactureDate);
cmd.Parameters.AddWithValue("@Categories", model.ProductCategories);
cmd.Parameters.AddWithValue("@MName", model.ModelName);
cmd.Parameters.AddWithValue("@Cost", model.CostPerUnit);
cmd.Parameters.AddWithValue("@Quantity", model.Quantity);
cmd.Parameters.AddWithValue("@PDescription", model.ProductDescription);
cmd.ExecuteNonQuery();
res = 1;
}
catch (Exception ex)
{
res = 0;
}
return(res);
}
public int Delete(ProductInventoryModel model)
{
int res = 0;
try
{
string str = @"data source=DESKTOP-93R7Q3C\SQLEXPRESS; initial catalog = ShopBridge;integrated security = true";
SqlConnection con = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("Delete_From_Inventory", con);
con.Open();
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProductID", model.ProductID);
cmd.ExecuteNonQuery();
res = 1;
}
catch (Exception ex)
{
res = 0;
}
return(res);
}
/// <summary>
/// 添加商品
/// （1）根据商品Id添加新商品
/// （2）新加的Id不能和数据库Id重复
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnAddProduct_Click(object sender, EventArgs e)
{
//判断每个文本框是否是正确的，如果不正确直接return下面不在执行
if (txtProductId.CheckData("^\\d+$", "商品编号必须是至少6位数字") * txtProductName.CheckNullOrEmpty() * txtProductUnitprice.CheckData(@"^(([1-9]\d*)|(\d*.\d{1,2}))$", "输入金额有误") * txtDiscount.CheckData(@"^((\d)|(\d.\d))$", "折扣输入有误") * txtMinCount.CheckData(@"^\d+$", "最小库存输入有误") * txtMaxCount.CheckData(@"^\d+$", "最大库存输入有误") == 0)
{
return;
}
else
{
//【一】当录入商品的时候要做一些判断：判断商品编号必须是唯一的，其二判断商品的名称必须是唯一的
//productList获取数据库中商品表的所有内容
List <ProdutsModel> productList = manager2.GetAllProduct();
var obj1 = from objPro in productList
where objPro.ProductId == txtProductId.Text
select objPro;
//【1】商品的编号不能重复；判断商品编号必须是唯一的
if (obj1.Count() > 0)
{
MessageBox.Show("商品编号已经存在,请重新录入!", "提示");
txtProductId.SelectAll();
return;
}
//判断数据库中所有商品名objPro.ProductName是否==txtProductName.Text里面的名字有相同的
//productList获取商品的所有名字；获取数据库中商品表的所有内容
var obj2 = from objPro in productList
where objPro.ProductName == txtProductName.Text
select objPro;
//【3】断商品的名称必须是唯一的；obj2.Count() > 0表示有重复的名字
if (obj2.Count() > 0)
{
MessageBox.Show("商品名称已经存在,请重新录入!", "提示");
txtProductName.SelectAll();
return;
}
//判断最大库存量不能小于最小库存量
if (int.Parse(txtMinCount.Text) > int.Parse(txtMaxCount.Text))
{
MessageBox.Show("最大库存量不能小于最小库存量", "提示");
return;
}
else//【二】添加商品
{
//cmbUnit.SelectedValue单位是否==和数据库中的单位相同
var pu = from item in units where item.Id == Convert.ToInt32(cmbUnit.SelectedValue) select item;
//商品表
ProdutsModel produts = new ProdutsModel()
{
ProductId = txtProductId.Text.Trim(),
ProductName = txtProductName.Text.Trim(),
Discount = Convert.ToSingle(txtDiscount.Text.Trim()),
UnitPrice = Convert.ToDecimal(txtProductUnitprice.Text.Trim()),
CategoryId = Convert.ToInt32(cmbCategory.SelectedValue),
Unit = pu.FirstOrDefault().Unit
};
//商品数量表
ProductInventoryModel inventory = new ProductInventoryModel()
{
ProductId = txtProductId.Text.Trim(),
MinCount = Convert.ToInt32(txtMinCount.Text.Trim()),
MaxCount = Convert.ToInt32(txtMaxCount.Text.Trim())
};
//获取商品表和商品数量表
bool res = manager2.InsertProduct(produts, inventory);
if (res)
{
if (MessageBox.Show("添加商品成功，是否继续添加", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
{
InitializeProduct();
txtProductId.Focus();
return;
}
else
{
if (MessageBox.Show("是否对该商品进行入库？", "提示", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
this.DialogResult = DialogResult.OK;
this.Tag = produts;
}
else
{
this.DialogResult = DialogResult.Cancel;
}
this.Close();
}
}
else
{
MessageBox.Show("添加商品失败！", "提示");
return;
}
}
}
}
/// <summary>
/// 把添加商品的窗口中的数据给数据库中的商品表添加
/// </summary>
/// <param name="produt"></param>
/// <param name="inventory"></param>
/// <returns></returns>
public bool InsertProduct(ProdutsModel produt, ProductInventoryModel inventory)
{
return(GetManager2.InsertProduct(produt, inventory));
}
public int Delete(ProductInventoryModel model)
{
return(dal.Delete(model));
}
public int UpdateInventory(ProductInventoryModel model)
{
return(dal.UpdateInventory(model));
}
public int AddInventory(ProductInventoryModel model)
{
return(dal.AddInventory(model));
}
public void IntegrationTest()
{
var connection = TestSession.GetConnection();
connection.Open();
#region good insertion and select by id test
ProductInventoryModel inserted = new ProductInventoryModel();
inserted.ProductID = TestSession.Random.Next();
inserted.LocationID = TestSession.Random.RandomShort();
inserted.Shelf = TestSession.Random.RandomString(10);
inserted.Bin = Convert.ToByte(TestSession.Random.RandomString(3));
inserted.Quantity = TestSession.Random.RandomShort();
inserted.rowguid = Guid.NewGuid();
inserted.ModifiedDate = TestSession.Random.RandomDateTime();
_tested.Insert(connection, new[] { inserted });
var selectedAfterInsertion = _tested.GetByPrimaryKey(connection, new ProductInventoryModelPrimaryKey()
{
ProductID = inserted.ProductID,
LocationID = inserted.LocationID,
});
CollectionAssert.IsNotEmpty(selectedAfterInsertion);
var selectedAfterInsert = selectedAfterInsertion.Single();
Assert.AreEqual(inserted.ProductID, selectedAfterInsert.ProductID);
Assert.AreEqual(inserted.LocationID, selectedAfterInsert.LocationID);
Assert.AreEqual(inserted.Shelf, selectedAfterInsert.Shelf);
Assert.AreEqual(inserted.Bin, selectedAfterInsert.Bin);
Assert.AreEqual(inserted.Quantity, selectedAfterInsert.Quantity);
Assert.AreEqual(inserted.rowguid, selectedAfterInsert.rowguid);
Assert.AreEqual(inserted.ModifiedDate, selectedAfterInsert.ModifiedDate);
#endregion
#region update and select by id test
inserted.Shelf = TestSession.Random.RandomString(10);
inserted.Bin = Convert.ToByte(TestSession.Random.RandomString(3));
inserted.Quantity = TestSession.Random.RandomShort();
inserted.rowguid = Guid.NewGuid();
inserted.ModifiedDate = TestSession.Random.RandomDateTime();
_tested.Update(connection, new[] { inserted });
var selectedAfterUpdateAddresss = _tested.GetByPrimaryKey(connection, new ProductInventoryModelPrimaryKey()
{
ProductID = inserted.ProductID,
LocationID = inserted.LocationID,
});
CollectionAssert.IsNotEmpty(selectedAfterUpdateAddresss);
var selectedAfterUpdate = selectedAfterUpdateAddresss.Single();
Assert.AreEqual(inserted.ProductID, selectedAfterUpdate.ProductID);
Assert.AreEqual(inserted.LocationID, selectedAfterUpdate.LocationID);
Assert.AreEqual(inserted.Shelf, selectedAfterUpdate.Shelf);
Assert.AreEqual(inserted.Bin, selectedAfterUpdate.Bin);
Assert.AreEqual(inserted.Quantity, selectedAfterUpdate.Quantity);
Assert.AreEqual(inserted.rowguid, selectedAfterUpdate.rowguid);
Assert.AreEqual(inserted.ModifiedDate, selectedAfterUpdate.ModifiedDate);
#endregion
#region delete test
_tested.Delete(connection, new[] { inserted });
var selectedAfterDeleteAddresss = _tested.GetByPrimaryKey(connection, new ProductInventoryModelPrimaryKey()
{
ProductID = inserted.ProductID,
LocationID = inserted.LocationID,
});
CollectionAssert.IsEmpty(selectedAfterDeleteAddresss);
#endregion
connection.Close();
}
