直接上代码
string CAPTION = "操作提示";
/// <summary>
/// 查询按钮事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnSearch_Click(object sender, EventArgs e)
{
FillListView();
}
public void FillListView()
{
//先清空ListView中的数据‘
if (lvCommodity.Items.Count > 0)
{
lvCommodity.Items.Clear();
}
//创建模糊查询语句
StringBuilder sql = new StringBuilder();
sql.AppendLine("select GoodsID,EmpId,[Type],GoodsName,GoodsNum,GoodsUnit,GoodsJhPrice,GoodsSellPrice,GoodTime,GoodsRemark,j.Falg ");
sql.AppendLine("from tb_JhGoodsInfo as j");
sql.AppendLine("right outer join GoodsType as g on g.ID=j.JhType");
sql.AppendFormat("where GoodsName like '%{0}%' and j.Falg=0", this.txtCommdityName.Text.Trim());
//查询并显示
DBHelper dbh = new DBHelper();
try
{
//创建Command对象
SqlCommand comm = new SqlCommand(sql.ToString(), dbh.Connection);
//打开数据库连接
dbh.OpenDB();
//执行命令
SqlDataReader reader = comm.ExecuteReader();
//如果结果没有数据行就弹出提示
if (!reader.HasRows)
{
MessageBox.Show("没有要查询的记录!", CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
{
while (reader.Read())
{
//获得查询到的数据
int goodsId =Convert.ToInt32(reader["GoodsID"]); //商品编号
string id = goodsId.ToString();
string empId = reader["EmpId"].ToString(); //进货人员工号
string type = reader["Type"].ToString(); //商品类型
string goodsName = reader["GoodsName"].ToString(); //商品名称
int goodsNum = Convert.ToInt32(reader["GoodsNum"]); //进货数量
string num = goodsNum.ToString();
string goodsUnit = reader["GoodsUnit"].ToString(); //商品单位
string goodsJhPrice = reader["GoodsJhPrice"].ToString(); //进货价格
string goodsSellPrice = reader["GoodsSellPrice"].ToString(); //出售价格
DateTime time = Convert.ToDateTime(reader["GoodTime"]); //进货时间
string time1 = time.ToString();
string goodsRemark = reader["GoodsRemark"].ToString(); //备注
int falg = Convert.ToInt32(reader["Falg"]); //是否删除
string fal = falg.ToString();
//创建ListViewItem
ListViewItem item = new ListViewItem(id);
//添加子项
item.SubItems.Add(empId);
item.SubItems.Add(type);
item.SubItems.Add(goodsName);
item.SubItems.Add(num);
item.SubItems.Add(goodsUnit);
item.SubItems.Add(goodsJhPrice);
item.SubItems.Add(goodsSellPrice);
item.SubItems.Add(time1);
item.SubItems.Add(goodsRemark);
item.SubItems.Add(fal);
//将ListViewItem添加到ListView中
lvCommodity.Items.Add(item);
}
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show("系统出现错误!" + ex.Message, CAPTION, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
//关闭数据库连接
dbh.CloseDB();
}
}