/// <summary>
/// 事务方法
/// </summary>
/// <param name="sqlList">命令集合</param>
/// <returns>返回影响的行数</returns>
public static int ExecuteNonQueryTransaction(List <string> sqlList)
{
SqlConnection conn = new SqlConnection();
SqlTransaction trans = null; // 定义事务
int cnt = 0;
try
{
conn.ConnectionString = GuGuGuHelper.GetConnectionString();
conn.Open();
trans = conn.BeginTransaction(); // 创建事务
foreach (string sql in sqlList)
{
SqlCommand comm = conn.CreateCommand();
comm.CommandText = sql;
comm.Transaction = trans; //命令事务对象
cnt += comm.ExecuteNonQuery();
}
trans.Commit(); // 提交事务
return(cnt);
}
catch (Exception)
{
trans.Rollback();// 回滚事务
return(-1);
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
private void button1_Click(object sender, EventArgs e)
{
if (TbUserName.Text.Trim() == "" || TbPWS.Text.Trim() == "")
{
MessageBox.Show("请正确输入账号和密码");
return;
}
List <SqlParameter> sqlList = new List <SqlParameter>()
{
GuGuGuHelper.CreatePara("@EmployeeID", SqlDbType.Char, ParameterDirection.Input, TbUserName.Text.Trim(), 10),
GuGuGuHelper.CreatePara("@password", SqlDbType.Char, ParameterDirection.Input, TbPWS.Text.Trim(), 16),
GuGuGuHelper.CreatePara("@returnValue", SqlDbType.Int, ParameterDirection.ReturnValue),
};
GuGuGuHelper.ExecuteNonQuery("prcLogin", sqlList, CommandType.StoredProcedure);
int returnValue = Convert.ToInt32(sqlList.ElementAt(2).Value);
if (returnValue == 1)
{
MessageBox.Show("登陆成功!");
GuGuGu fm = new GuGuGu(TbUserName.Text.Trim());
Hide();
fm.ShowDialog();
Close();
}
else
{
MessageBox.Show("登陆失败!");
}
}
private void BtnChangePassWord_Click(object sender, EventArgs e)
{
if (TbxNewPassword.Text.Trim() == "" || TbxPassWord.Text.Trim() == "")
{
MessageBox.Show("密码不能为空！！");
return;
}
if (TbxPassWord.Text.Trim() == TbxNewPassword.Text.Trim())
{
string sql = "Update Login set Password = '******' where EmployeeID = '" + TbxEmployeeID.Text.Trim() + "'";
sql = string.Format(sql, this.TbxNewPassword.Text);
int cnt = GuGuGuHelper.ExecuteNonQuery(sql);
if (cnt >= 1)
{
MessageBox.Show("修改成功");
}
else
{
MessageBox.Show("修改失败");
}
}
else
{
MessageBox.Show("两次输入的密码不一致，请重新输入");
}
}
private void Delete()
{
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVCategories))
{
string id = DGVCategories.Rows[selectRow].Cells["商品类别编号"].Value.ToString();
{
string sql = "select * from Products where CategoryID = " + id + "'";
if (GuGuGuHelper.ExcuteExist(sql))
{
MessageBox.Show("该类别下含有商品，禁止删除！");
return;
}
}
{
string sql = "delete from Categories where CategoryID = '" + id + "'";
int cnt = GuGuGuHelper.ExecuteNonQuery(sql);
if (cnt >= 1)
{
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
CateRefresh();
}
}
else
{
MessageBox.Show("请选择正确的行");
}
}
/// <summary>
/// 离线查询数据DataTable(返回表)
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commType">要查询语句的类型</param>
/// <returns>返回查询得到的DataTable</returns>
public static DataTable ExecuteReaderDataTable(string sql, List <SqlParameter> paraList = null, CommandType commType = CommandType.Text)
{
try
{
SqlConnection conn = new SqlConnection(GuGuGuHelper.GetConnectionString());
SqlCommand comm = new SqlCommand
{
CommandText = sql,
CommandType = commType,
Connection = conn,
CommandTimeout = 600,
};
if (paraList != null)
{
foreach (var para in paraList)
{
comm.Parameters.Add(para);
}
}
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(comm);
DataTable table = new DataTable();
sqlDataAdapter.Fill(table);
return(table);
}
catch (Exception)
{
//MessageBox.Show(ex.ToString());
throw;
}
}
private void BtnAddCustomer_Click(object sender, EventArgs e)
{
SetEnable(false, true, true, true, false, true, "当前操作模式：添加", true, "添加", "add", true);
TextBoxEmpty();
TbCustomerID.Text = GuGuGuHelper.GetCustomerID();
TbCustomerCre.Text = "0";
}
/// <summary>
/// 执行sql语句并返回受影响的行数
/// </summary>
/// <param name="sql">要查询的语句</param>
/// <param name="paraList">变量列表Sqlparameters(可选)</param>
/// <param name="commType">要查询语句的类型</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(string sql, List <SqlParameter> paraList = null, CommandType commType = CommandType.Text)
{
int value = -1;
try
{
SqlConnection conn = new SqlConnection(GuGuGuHelper.GetConnectionString());
conn.Open();
SqlCommand comm = new SqlCommand()
{
Connection = conn,
CommandType = commType,
CommandText = sql,
CommandTimeout = 600,
};
if (paraList != null)
{
foreach (var para in paraList)
{
comm.Parameters.Add(para);
}
}
return(comm.ExecuteNonQuery());
}
catch (Exception)
{
throw;
value = -1;
}
return(value);
}
private void BtnAdd_Click(object sender, EventArgs e)
{
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVProView))
{
bool flag = true;
DGVProViewFillTextBox();
DataRow row = NewRow();
for (int i = 0; i < DGVStock.RowCount - 1; i++)
{
if (DGVStock.Rows[i].Cells["商品号"].Value.ToString().Trim() == TbProID.Text.Trim())
{
flag = false;
DGVStock.Rows[i].Cells["数量"].Value = (Convert.ToInt32(DGVStock.Rows[i].Cells["数量"].Value) + Convert.ToInt32(TbQuantity.Text.Trim())).ToString();
}
}
if (flag)
{
table.Rows.Add(row);
}
DGVStock.DataSource = table;
sum += Convert.ToDouble(TbUnitPrice.Text.Trim()) * Convert.ToDouble(TbDiscount.Text.Trim()) * Convert.ToDouble(TbQuantity.Text.Trim());
LbSum.Text = sum.ToString();
BtnDel.Enabled = true;
}
}
private void BtnDel_Click(object sender, EventArgs e)
{
if (DGVStock.CurrentCell != null)
{
selectRow = DGVStock.CurrentCell.RowIndex;
}
else
{
BtnDel.Enabled = false;
}
TextBoxEmpty();
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVStock))
{
DGVStockFillTextBox();
table.Rows.RemoveAt(selectRow);
DGVStock.DataSource = table;
sum -= Convert.ToDouble(TbUnitPrice.Text.Trim()) * Convert.ToDouble(TbDiscount.Text.Trim()) * Convert.ToDouble(TbQuantity.Text.Trim());
LbSum.Text = sum.ToString();
if (DGVStock.CurrentCell != null)
{
selectRow = DGVStock.CurrentCell.RowIndex;
DGVStockFillTextBox();
}
else
{
BtnDel.Enabled = false;
}
}
//MessageBox.Show(selectRow.ToString());
}
private void Search()
{
string sql = "select CustomerID as '客户编号',CustomerName as '客户名',Address as'地址',Phone as '电话',Credits as '积分',Notes as '客户类别' from Customers where 1=1";
if (TbCustomerID.Text != "")
{
sql += " and CustomerID like'%" + TbCustomerID.Text.Trim() + "%'";
}
if (TbCustomerName.Text != "")
{
sql += " and CustomerName like'%" + TbCustomerName.Text.Trim() + "%'";
}
if (TbCustomerAddr.Text != "")
{
sql += " and Address like'%" + TbCustomerAddr.Text.Trim() + "%'";
}
if (TbCustomerTel.Text != "")
{
sql += " and Phone like'%" + TbCustomerTel.Text.Trim() + "%'";
}
if (TbCustomerCre.Text != "")
{
sql += " and Credits like'%" + TbCustomerCre.Text.Trim() + "%'";
}
if (TbCustomerNote.Text != "")
{
sql += " and Notes like'%" + TbCustomerNote.Text.Trim() + "%'";
}
CustomerMain.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
}
private void Insert()
{
List <string> sqlList = new List <string>();
{
string sql = "Insert into Products values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
sql = string.Format(sql, TbProductID.Text, TbProductName.Text, CbxCompanyName.SelectedValue, CbxCategoryName.SelectedValue, TbUnitPrice.Text, TbProductName.Text, TbValue.Text.Trim(), RbOK.Checked);
sqlList.Add(sql);
}
{
string sql = "Insert into Inventory values('{0}','{1}','{2}')";
sql = string.Format(sql, TbProductID.Text, "0", "千克/箱");
sqlList.Add(sql);
}
int cnt = GuGuGuHelper.ExecuteNonQueryTransaction(sqlList);
if (cnt == 2)
{
MessageBox.Show("添加成功");
}
else
{
MessageBox.Show("添加失败");
}
ProRefresh();
}
private void Delete()
{
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVProduct))
{
string checkSql = "select * from Inventory where ProductID = '" + TbProductID.Text + "'and quantity!=0";
if (!GuGuGuHelper.ExcuteExist(checkSql))
{
List <string> sqlList = new List <string>();
{
string sql = "delete from dbo.Inventory where ProductID = '" + TbProductID.Text + "'";
sqlList.Add(sql);
}
{
string sql = "delete from dbo.Products where ProductID = '" + TbProductID.Text + "'";
sqlList.Add(sql);
}
int cnt = GuGuGuHelper.ExecuteNonQueryTransaction(sqlList);
if (cnt >= 2)
{
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
}
else
{
MessageBox.Show("该商品已出售或者其他未知错误");
}
TextBoxFill();
}
}
private void LoadView()
{
ActiveUser.Text = "当前工号:[" + activeUser + "]";
string sql = "select Name from Employees where EmployeeID = '" + activeUser + "'";
activeUserName = GuGuGuHelper.ExecuteScalar(sql).ToString();
ActiveUserName.Text = "姓名:" + activeUserName;
string tip = "您好！";
int hh = Convert.ToInt32(DateTime.Now.ToString("HH"));
if (hh >= 6 && hh <= 8)
{
tip = "早上好！";
}
else if (hh >= 9 && hh < 12)
{
tip = "上午好！";
}
else if (hh >= 12 && hh < 14)
{
tip = "中午好！";
}
else if (hh >= 14 && hh < 19)
{
tip = "下午好！";
}
else if (hh >= 19 && hh < 24)
{
tip = "晚上好！";
}
LogoTip.Text = activeUserName + "，" + tip;
}
/// <summary>
/// 顾客编号自动生成
/// </summary>
/// <returns>返回生成的顾客编号</returns>
public static string GetCustomerID()
{
string sql = "select CustomerID from Customers order by CustomerID desc";
string head = "CUS";
string rear = (Convert.ToInt32(GuGuGuHelper.ExecuteScalar(sql).ToString().Substring(3, 6)) + 1).ToString("000000");
return(head + rear);
}
/// <summary>
/// 商品编号自动生成
/// </summary>
/// <returns>返回自动生成的商品编号</returns>
public static string GetProductID()
{
string sql = "select ProductID from Products order by ProductID desc";
string rear = (Convert.ToInt32(GuGuGuHelper.ExecuteScalar(sql).ToString().Substring(1, 6)) + 1).ToString("000000");
string head = "P";
return(head + rear);
}
private void BtnAlert_Click(object sender, EventArgs e)
{
SetEnable(false, false, false, false, false, "当前操作模式：商品告急目录", false, "确定", "other", false);
string sql = "select Inventory.ProductID as '商品编号',ProductName as '商品名称',CategoryID as'供应商编号',Quantity as '库存',ThrValue as '阈值' from Products left join Inventory on Products.ProductID=Inventory.ProductID where Products.ThrValue > Inventory.Quantity";
DGVInventory.DataSource = GuGuGuHelper.ExecuteReaderDataTable(sql);
TextBoxEmpty();
}
/// <summary>
/// 自动生成类别号
/// </summary>
/// <returns>返回类别号</returns>
public static string GetCategoryID()
{
string sql = "select CategoryID from Categories order by CategoryID desc";
string rear = (Convert.ToInt32(GuGuGuHelper.ExecuteScalar(sql).ToString().Substring(4, 6)) + 1).ToString("000000");
string head = "CATE";
return(head + rear);
}
/// <summary>
/// 自动生成订单号
/// </summary>
/// <returns>返回订单号</returns>
public static string GetOrderID()
{
string sql = "select OrderID from Orders order by OrderDate desc";
string rear = ((Convert.ToInt32(GuGuGuHelper.ExecuteScalar(sql).ToString().Substring(8, 4)) + 10) % 10000).ToString("0000");
string head = DateTime.Now.ToString("yyyyMMdd");
return(head + rear);
}
private void DataProduct_CellClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVProduct) && op != "add")
{
TextBoxFill();
}
}
private void DGVStock_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVStock))
{
TextBoxEmpty();
BtnDel.PerformClick();
}
}
private void BtnDelete_Click(object sender, EventArgs e)
{
TextBoxEmpty();
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVProduct))
{
TextBoxFill();
SetEnable(false, false, false, false, false, false, false, "当前操作模式：删除", true, "删除", true, "delete");
}
}
private void DGVProView_CellClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
TextBoxEmpty();
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVProView))
{
DGVProViewFillTextBox();
}
}
private void DGVOrder_CellClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVOrder))
{
LbTip.Text = "当前订单号：" + DGVOrder.Rows[selectRow].Cells["订单号"].Value.ToString();
OrderDetailsRefresh();
}
}
private void Updates()
{
string sql = @"update dbo.Customers set CustomerName='{0}',Address='{1}',Phone='{2}',Credits='{3}',Notes='{4}' where CustomerID='{5}'";
sql = string.Format(sql, TbCustomerID.Text.Trim(), TbCustomerName.Text.Trim(), TbCustomerAddr.Text.Trim(), TbCustomerTel.Text.Trim(), TbCustomerCre.Text.Trim(), TbCustomerNote.Text.Trim());
int cnt = GuGuGuHelper.ExecuteNonQuery(sql);
MessageBox.Show((cnt >= 1) ? "修改成功" : "修改失败");
}
private void Insert()
{
string sql = @"INSERT INTO dbo.Customers VALUES( '{0}','{1}','{2}','{3}','{4}','{5}')";
sql = string.Format(sql, TbCustomerID.Text.Trim(), TbCustomerName.Text.Trim(), TbCustomerAddr.Text.Trim(), TbCustomerTel.Text.Trim(), TbCustomerCre.Text.Trim(), TbCustomerNote.Text.Trim());
int cnt = GuGuGuHelper.ExecuteNonQuery(sql);
MessageBox.Show((cnt >= 1) ? "添加成功" : "添加失败");
}
private void CustomerMain_CellClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
if (GuGuGuHelper.IsInDataGridView(selectRow, CustomerMain) && op != "add")
{
TextBoxEmpty();
TextBoxFill();
}
}
private void DGVProView_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
selectRow = e.RowIndex;
TextBoxEmpty();
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVProView))
{
DGVProViewFillTextBox();
BtnAdd.PerformClick();
}
}
private void DGVCategories_CellClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVCategories))
{
if (op == "delete" || op == "update")
{
TextBoxFill();
}
}
}
private void BtnAddCategories_Click(object sender, EventArgs e)
{
TbDe.Enabled = true;
TbID.Enabled = false;
TbName.Enabled = true;
BtnOk.Enabled = true;
BtnOk.Text = "添加";
op = "add";
LbTip.Text = "当前操作模式：添加";
TextBoxEmpty();
TbID.Text = GuGuGuHelper.GetCategoryID();
}
private void DGVInventory_CellClick(object sender, DataGridViewCellEventArgs e)
{
selectRow = e.RowIndex;
if (GuGuGuHelper.IsInDataGridView(selectRow, DGVInventory))
{
TextBoxFill();
}
else
{
TextBoxEmpty();
}
}