//功能函数，执行数据库更新语句
private void UpdateByGoodsID()
{
if (!InfoCheck())
{
MessageBox.Show("信息错误:信息空缺或错误！");
return;
}
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
string sql = "update dbo.tb_JhGoodsInfo set ";
sql += "EmpID ='" + txtEmpID.Text + "' ,DepotName='" + txtDepotName.Text + "',JhCompName='" + txtJhCompName.Text;
sql += "',GoodsName='" + txtGoodsName.Text + "',GoodsNum=" + txtGoodsNum.Text + ",GoodsUnit='" + txtGoodsUnit.Text;
sql += "',GoodsJhPrice='" + txtGoodsJhPrice.Text + "',GoodsSellPrice='" + txtGoodsSellPrice.Text;
sql += "',GoodsNeedPrice='" + txtGoodsNeedPrice.Text + "',GoodsNoPrice='" + txtGoodsNoPrice.Text;
sql += "',GoodsRemark='" + txtGoodsRemark.Text + "',GoodsTime='" + txtGoodsTime.Value + "',Flag=0";
sql += " where GoodsID='" + txtGoodsID.Text + "'";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
try
{ int r = sqlCommand.ExecuteNonQuery(); }
catch (SqlException)
{
MessageBox.Show("插入信息错误，请检查是否商品编号重复");
//打开查询界面
}
sqlConnection.Close();
}
private void buttonQuery_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
SqlConnection sqlConnection = new GetSqlConnection().GetCon();
string sql = "select * from dbo.tb_JhGoodsInfo where ";
switch (txtQueryKind.SelectedIndex)
{
case 0: { sql += "GoodsID LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 1: { sql += "GoodsName LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 2: { sql += "JhCompName LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 3: { sql += "EmpID LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
case 4: { sql += "DepotName LIKE \'%" + txtQueryValue.Text + "%\'"; break; }
default: { break; }
}
try
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
while (dataReader.Read())
{
ListViewItem item = new ListViewItem((string)dataReader["GoodsID"]);
item.SubItems.Add((string)dataReader["GoodsName"]);
item.SubItems.Add((string)dataReader["JhCompName"]);
item.SubItems.Add((string)dataReader["EmpID"]);
item.SubItems.Add((string)dataReader["DepotName"]);
/*item.SubItems.Add(dataReader["GoodsNum"].ToString());
* item.SubItems.Add((string)dataReader["GoodsUnit"]);
* item.SubItems.Add((string)dataReader["GoodsJhPrice"]);
* item.SubItems.Add((string)dataReader["GoodsSellPrice"]);
* item.SubItems.Add((string)dataReader["GoodsNeedPrice"]);
* item.SubItems.Add((string)dataReader["GoodsNoPrice"]);*/
listView1.Items.Add(item);
}
if (!dataReader.HasRows)
{
ListViewItem item = new ListViewItem("无查询结果");
listView1.Items.Add(item);
}
dataReader.Close();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("错误！\n输入正确类型！");
}
}
//功能函数，随着数据库语句的执行，刷新DataGridView
private void UpdateDataGridView()
{
//增加对当前行的锁定。
int currentRow = -1;
if (dataGridView1.SelectedRows.Count != 0)
{
currentRow = dataGridView1.CurrentRow.Index;
}
try
{
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
SqlCommand cmd = new SqlCommand("select * from dbo.tb_JhGoodsInfo order by GoodsID asc", sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataReader;
this.dataGridView1.DataSource = bindingSource;
dataGridView1.Columns[0].HeaderCell.Value = "商品ID";
dataGridView1.Columns[1].HeaderCell.Value = "员工";
dataGridView1.Columns[2].HeaderCell.Value = "进货公司";
dataGridView1.Columns[3].HeaderCell.Value = "仓库";
dataGridView1.Columns[4].HeaderCell.Value = "商品名称";
dataGridView1.Columns[5].HeaderCell.Value = "数量";
dataGridView1.Columns[6].HeaderCell.Value = "计量单位";
dataGridView1.Columns[7].HeaderCell.Value = "进价";
dataGridView1.Columns[8].HeaderCell.Value = "售价";
dataGridView1.Columns[9].HeaderCell.Value = "应付价格";
dataGridView1.Columns[10].HeaderCell.Value = "实付价格";
dataGridView1.Columns[11].HeaderCell.Value = "备注";
dataGridView1.Columns[12].HeaderCell.Value = "时间";
dataReader.Close();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("数据库连接失败");
}
if (currentRow != -1)
{
dataGridView1.Rows[0].Selected = false;
dataGridView1.Rows[currentRow].Selected = true;
dataGridView1.FirstDisplayedScrollingRowIndex = currentRow;
}
}
//功能函数，执行数据库删除语句
private void DeleteByGoodsID()
{
try
{
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
string sql = "delete from dbo.tb_JhGoodsInfo where GoodsID=" + txtGoodsID.Text;
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.Prepare();
sqlCommand.ExecuteNonQuery();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("数据库连接失败！");
}
}
//查询按钮
private void queryButton_Click(object sender, EventArgs e)
{
SqlConnection sqlConnection = new GetSqlConnection().GetCon();
string sql = "select * from dbo.tb_JhGoodsInfo where ";
switch (keyType.SelectedIndex)
{
case 0: { sql += "GoodsID LIKE \'%" + keyText.Text + "%\'"; break; }
case 1: { sql += "GoodsName LIKE \'%" + keyText.Text + "%\'"; break; }
case 2: { sql += "JhCompName LIKE \'%" + keyText.Text + "%\'"; break; }
case 3: { sql += "EmpID LIKE \'%" + keyText.Text + "%\'"; break; }
case 4: { sql += "DepotName LIKE \'%" + keyText.Text + "%\'"; break; }
default: { break; }
}
try
{
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlDataReader dataReader = cmd.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = dataReader;
this.dataGridView1.DataSource = bindingSource;
if (!dataReader.HasRows)
{
MessageBox.Show("无查询结果");
}
dataReader.Close();
sqlConnection.Close();
}
catch (SqlException)
{
MessageBox.Show("错误！\n输入正确类型！");
}
}
//功能函数，执行数据库插入语句，同时会检查输入是否正确。
private void InsertIntoSql()
{
if (!InfoCheck())
{
MessageBox.Show("信息错误:信息空缺或错误！");
return;
}
SqlConnection sqlConnection = new GetSqlConnection().GetCon();//连接数据库
string sql = "Insert into dbo.tb_JhGoodsInfo(GoodsID,EmpID,JhCompName,DepotName,GoodsName,GoodsNum,GoodsUnit,GoodsJhPrice,GoodsSellPrice," +
"GoodsNeedPrice,GoodsNoPrice,GoodsRemark,GoodsTime,Flag)values(@GoodsID,@EmpID,@JhCompName,@DepotName,@GoodsName,@GoodsNum,@GoodsUnit," +
"@GoodsJhPrice,@GoodsSellPrice,@GoodsNeedPrice,@GoodsNoPrice,@GoodsRemark,@GoodsTime,@Flag)";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.Prepare();
sqlCommand.Parameters.AddWithValue("@GoodsID", txtGoodsID.Text);
sqlCommand.Parameters.AddWithValue("@EmpID", txtEmpID.Text);
sqlCommand.Parameters.AddWithValue("@JhCompName", txtJhCompName.Text);
sqlCommand.Parameters.AddWithValue("@DepotName", txtDepotName.Text);
sqlCommand.Parameters.AddWithValue("@GoodsName", txtGoodsName.Text);
sqlCommand.Parameters.AddWithValue("@GoodsNum", StringToInt(txtGoodsNum.Text));
sqlCommand.Parameters.AddWithValue("@GoodsUnit", txtGoodsUnit.Text);
sqlCommand.Parameters.AddWithValue("@GoodsJhPrice", txtGoodsJhPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsSellPrice", txtGoodsSellPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsNeedPrice", txtGoodsNeedPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsNoPrice", txtGoodsNoPrice.Text);
sqlCommand.Parameters.AddWithValue("@GoodsRemark", txtGoodsRemark.Text);
sqlCommand.Parameters.AddWithValue("@GoodsTime", txtGoodsTime.Value);
sqlCommand.Parameters.AddWithValue("@Flag", 0);
try
{ int r = sqlCommand.ExecuteNonQuery(); }
catch (SqlException)
{
MessageBox.Show("插入信息错误，请检查是否商品编号重复");
//打开查询界面
}
sqlConnection.Close();
}