public static int UpdateCategory(EtCategory category)
{
List <EtCategory> categories = QueryByCategoryID(category.CategoryID);
if (categories.Count == 0)
{
return(-1);
}
DBHelper helper = new DBHelper();
string sql = "UPDATE category SET categoryName = @categoryName," +
"parentCategoryID = @parentCategoryID," +
"parentCategoryName = @parentCategoryName," +
"unit = @unit, color = @color, firm = @firm," +
"minStock = @minStock, maxStock = @maxStock, " +
"expirationDate = @expirationDate, isValid = @isValid " +
"WHERE categoryID = @categoryID;";
MySqlParameter[] prams =
{
new MySqlParameter("@categoryName", category.CategoryName ?? (object)DBNull.Value),
new MySqlParameter("@parentCategoryID", category.ParentCategoryID),
new MySqlParameter("@parentCategoryName", category.ParentCategoryName ?? (object)DBNull.Value),
new MySqlParameter("@unit", category.Unit ?? (object)DBNull.Value),
new MySqlParameter("@color", category.Color ?? (object)DBNull.Value),
new MySqlParameter("@firm", category.Firm ?? (object)DBNull.Value),
new MySqlParameter("@minStock", category.MinStock),
new MySqlParameter("@maxStock", category.MaxStock),
new MySqlParameter("@expirationDate", category.ExpirationDate),
new MySqlParameter("@isValid", category.IsValid),
new MySqlParameter("@categoryID", category.CategoryID)
};
return(helper.RunNonQuerySQL(sql, prams));
}
private static List <EtCategory> GetListByDataReader(MySqlDataReader dr)
{
List <EtCategory> categories = new List <EtCategory>();
try
{
while (dr.Read())
{
EtCategory category = new EtCategory
{
CategoryID = dr.GetInt32("categoryID"),
CategoryName = dr["categoryName"] is DBNull ? null : dr.GetString("categoryName"),
ParentCategoryID = dr["parentCategoryID"] is DBNull ? ECategory.未定义 : (ECategory)dr.GetInt16("parentCategoryID"),
ParentCategoryName = dr["parentCategoryName"] is DBNull ? null : dr.GetString("parentCategoryName"),
Unit = dr["unit"] is DBNull ? null : dr.GetString("unit"),
Color = dr["color"] is DBNull ? null : dr.GetString("color"),
Firm = dr["firm"] is DBNull ? null : dr.GetString("firm"),
MinStock = dr["minStock"] is DBNull ? 0 : dr.GetInt32("minStock"),
MaxStock = dr["maxStock"] is DBNull ? 0 : dr.GetInt32("maxStock"),
ExpirationDate = dr["expirationDate"] is DBNull ? 0 : dr.GetInt32("expirationDate"),
IsValid = dr["isValid"] is DBNull ? EValid.已删除 : (EValid)dr.GetInt16("isValid")
};
categories.Add(category);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
return(categories);
}
public static int InsertCategory(EtCategory category)
{
List <EtCategory> categories = QueryByCategoryID(category.CategoryID);
if (categories.Count > 0)
{
return(-1);
}
DBHelper helper = new DBHelper();
string sql = "INSERT INTO " +
"category(categoryName,parentCategoryID,parentCategoryName,unit,color,firm,minStock,maxStock,expirationDate,isValid) " +
"VALUE(@categoryName,@parentCategoryID,@parentCategoryName,@unit,@color,@firm,@minStock,@maxStock,@expirationDate,@isValid)";
MySqlParameter[] prams =
{
new MySqlParameter("@categoryName", category.CategoryName ?? (object)DBNull.Value),
new MySqlParameter("@parentCategoryID", category.ParentCategoryID),
new MySqlParameter("@parentCategoryName", category.ParentCategoryName ?? (object)DBNull.Value),
new MySqlParameter("@unit", category.Unit ?? (object)DBNull.Value),
new MySqlParameter("@color", category.Color ?? (object)DBNull.Value),
new MySqlParameter("@firm", category.Firm ?? (object)DBNull.Value),
new MySqlParameter("@minStock", category.MinStock),
new MySqlParameter("@maxStock", category.MaxStock),
new MySqlParameter("@expirationDate", category.ExpirationDate),
new MySqlParameter("@isValid", category.IsValid)
};
return(helper.RunNonQuerySQL(sql, prams));
}
private void BtnAdd_Click(object sender, EventArgs e)
{
FrmCategoryInsert fci = new FrmCategoryInsert {
StartPosition = FormStartPosition.CenterParent
};
fci.ShowDialog();
if (category != null)
{
categories.Add(category);
category = null;
if (categories.Count > 1)
{
categories[categories.Count - 1].CategoryID = categories[categories.Count - 2].CategoryID + 1;
}
DgvCategory.Rows.Add(new object[] {
categories[categories.Count - 1].CategoryID,
categories[categories.Count - 1].CategoryName,
categories[categories.Count - 1].ParentCategoryName,
categories[categories.Count - 1].Firm,
categories[categories.Count - 1].Unit,
categories[categories.Count - 1].Color,
categories[categories.Count - 1].ExpirationDate,
categories[categories.Count - 1].MinStock,
categories[categories.Count - 1].MaxStock,
categories[categories.Count - 1].IsValid
});
hasUpdated = true;
}
hasUpdated = true;
}
private void BtnUpdate_Click(object sender, EventArgs e)
{
if (DgvCategory.SelectedRows.Count == 1)
{
int index = DgvCategory.SelectedRows[0].Index;
category = categories[index];
FrmCategoryUpdate fmu = new FrmCategoryUpdate {
StartPosition = FormStartPosition.CenterParent
};
fmu.ShowDialog();
if (category != null)
{
DgvCategory.Rows[index].Cells["Column1"].Value = category.CategoryID;
DgvCategory.Rows[index].Cells["Column2"].Value = category.CategoryName;
DgvCategory.Rows[index].Cells["Column3"].Value = category.ParentCategoryName;
DgvCategory.Rows[index].Cells["Column7"].Value = category.Firm;
DgvCategory.Rows[index].Cells["Column5"].Value = category.Unit;
DgvCategory.Rows[index].Cells["Column6"].Value = category.Color;
DgvCategory.Rows[index].Cells["Column10"].Value = category.ExpirationDate;
DgvCategory.Rows[index].Cells["Column8"].Value = category.MinStock;
DgvCategory.Rows[index].Cells["Column9"].Value = category.MaxStock;
DgvCategory.Rows[index].Cells["Column11"].Value = category.IsValid;
categories[index] = category;
category = null;
}
hasUpdated = true;
}
else
{
MsgBoxUtil.ErrMsgBox("请选择要修改的商品信息！");
}
}
public FrmCategory()
{
InitializeComponent();
CmbCategory.SelectedIndex = 0;
category = new EtCategory();
categories = CategoryDao.QueryAll();
hasUpdated = false;
DgvShow();
}
private void BtnDelete_Click(object sender, EventArgs e)
{
if (DgvCategory.SelectedRows.Count == 1)
{
int index = DgvCategory.SelectedRows[0].Index;
category = categories[index];
if (EValid.效 == category.IsValid)
{
if (DialogResult.OK == MsgBoxUtil.QuestionMsgBox("是否删除当前商品？"))
{
CategoryDao.DeleteByCategoryID(category.CategoryID);
categories[index].IsValid = EValid.已删除;
DgvCategory.Rows[index].Cells["Column11"].Value = EValid.已删除;
category = null;
}
}
}
else
{
MsgBoxUtil.ErrMsgBox("请选择要删除的商品！");
}
}