登陆页面
账单设计页面
账单增删改查代码如下:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient; //连接MySQL需添加该using
namespace 今天吃了吗
{
public partial class 账单增删改查 : Form
{
String mysqlStr = "Database=restaurant;Data Source=localhost;User Id=root;Password=*****;pooling=false;CharSet=utf8;port=3306";
//与Mysql库的连接:Database库名;DataSource数据域安装时默认为localhost
//UserId用户名 默认为root;Password密码;popling Charset port默认
public 账单增删改查()
{
InitializeComponent();
}
private void btbsearch1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();//数据容器
string strsql = "select * from bill where bnum=@bnum";
MySqlParameter mspbnum = new MySqlParameter("@bnum", txtbnum.Text);//类参数定义
using (MySqlConnection con = new MySqlConnection(mysqlStr))
//using{}外自动销毁
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
//执行语句命令(语句,库)
{
cmd.Parameters.Add(mspbnum);//添加参数
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))//管道式类(源)
{
msda.Fill(dt);//送到dt显示
}
}
}
dt3.DataSource = dt;
}
private void btadd_Click(object sender, EventArgs e)
{
string strsql = "insert into bill(bnum,btime,tnum,cnum,mname,mnamenum,bprice) ";
strsql += "values(@bnum,@btime,@tnum,@cnum,@mname,@mnamenum,@bprice)";
MySqlParameter mspbnum = new MySqlParameter("@bnum", txtbnum.Text);
string nowTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
MySqlParameter mspbtime = new MySqlParameter("@btime",nowTime);
MySqlParameter msptnum = new MySqlParameter("@tnum", cbbtnum.Text);
MySqlParameter mspcnum = new MySqlParameter("@cnum", txtcnum.Text);
MySqlParameter mspmname = new MySqlParameter("@mname", cbbmname.Text);
MySqlParameter mspmnamenum = new MySqlParameter("@mnamenum", txtmnamenum.Text);
MySqlParameter mspbprice = new MySqlParameter("@bprice", txtbprice.Text);
using (MySqlConnection con = new MySqlConnection(mysqlStr))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
{
cmd.Parameters.Add(mspbnum);
cmd.Parameters.Add(mspbtime);
cmd.Parameters.Add(msptnum);
cmd.Parameters.Add(mspcnum);
cmd.Parameters.Add(mspmname);
cmd.Parameters.Add(mspmnamenum);
cmd.Parameters.Add(mspbprice);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("添加成功!");
}
else
{
MessageBox.Show("添加失败!");
}
}
}
}
private void btbsearch2_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string strsql = "select * from bill ";
using (MySqlConnection con = new MySqlConnection(mysqlStr))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
{
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
{
msda.Fill(dt);
}
}
}
dt3.DataSource = dt;
}
private void btdelete_Click(object sender, EventArgs e)
{
string strsql = "delete from bill where bnum=@bnum";
MySqlParameter mspbnum = new MySqlParameter("@bnum", txtbnum.Text);
using (MySqlConnection con = new MySqlConnection(mysqlStr))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
{
cmd.Parameters.Add(mspbnum);
if (cmd.ExecuteNonQuery() > 0)
{
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
}
}
}
private void btchange_Click(object sender, EventArgs e)
{
}
private void btnsday_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string strsql = "SELECT DISTINCT bnum,SUM(bprice) 总消费 FROM bill WHERE MONTH(btime) =@btimed AND DAY(btime)=@btimem GROUP BY bnum";
MySqlParameter mspbtimed = new MySqlParameter("@btimed",cbbday.Text);
MySqlParameter mspbtimem = new MySqlParameter("@btimem",cbbmon.Text);
using (MySqlConnection con = new MySqlConnection(mysqlStr))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
{
cmd.Parameters.Add(mspbtimed);
cmd.Parameters.Add(mspbtimem);
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
{
msda.Fill(dt);
}
}
}
dt3.DataSource = dt;
}
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string strsql = "SELECT DISTINCT MONTH(btime) 月份,SUM(bprice) 销售额 FROM bill WHERE YEAR(btime)=@btimey GROUP BY 月份";
MySqlParameter mspbtimey = new MySqlParameter("@btimey", cbbyear.Text);
using (MySqlConnection con = new MySqlConnection(mysqlStr))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
{
cmd.Parameters.Add(mspbtimey);
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
{
msda.Fill(dt);
}
}
}
dt3.DataSource = dt;
}
private void btnmm_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
string strsql = "SELECT mname 菜名,COUNT(mnamenum) 销售量 FROM bill WHERE MONTH(btime) =@btimem GROUP BY mname";
MySqlParameter mspbtimem = new MySqlParameter("@btimem", cbbmon2.Text);
using (MySqlConnection con = new MySqlConnection(mysqlStr))
{
con.Open();
using (MySqlCommand cmd = new MySqlCommand(strsql, con))
{
cmd.Parameters.Add(mspbtimem);
using (MySqlDataAdapter msda = new MySqlDataAdapter(cmd))
{
msda.Fill(dt);
}
}
}
dt3.DataSource = dt;
}