public partial class study_eight : Form
{
public study_eight()
{
InitializeComponent();
//页面开始,设立用户管理为开始界面
TextBoxNull();
dt = ExecuteQuery("select * from [study-userset]");
//dt = ds.Tables[0];
dataGridView1.DataSource = dt;
labelshow();
}
DataSet ds = new DataSet();
DataTable dt = new DataTable();
//清空值,且设置不可修改
private void TextBoxNull()
{
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox1.ReadOnly = true;
textBox6.ReadOnly = true;
textBox5.ReadOnly = false;
}
//用户管理点击时事件
private void userToolStripMenuItem_Click(object sender, EventArgs e)
{
TextBoxNull();
dt = ExecuteQuery("select * from [study-userset]");
//dt = ds.Tables[0];
dataGridView1.DataSource = dt;
labelshow();
}
private void DataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
}
//标签获取列名输出
private void labelshow()
{
label1.Text = dataGridView1.Columns[0].HeaderText;
label2.Text = dataGridView1.Columns[1].HeaderText;
label3.Text = dataGridView1.Columns[2].HeaderText;
label4.Text = dataGridView1.Columns[3].HeaderText;
try
{
label5.Text = dataGridView1.Columns[4].HeaderText;
label6.Text = dataGridView1.Columns[5].HeaderText;
}
catch (Exception)
{
label5.Text = "None";
label6.Text = "None";
}
}
//连接到数据库
private string MySqlCon = "Data Source=192.168.26.68;Initial Catalog=Lota;Integrated Security=True";
public DataTable ExecuteQuery(string sqlStr) //用于查询;其实是相当于提供一个可以传参的函数,到时候写一个sql语句,存在string里,传给这个函数,就会自动执行。
{
SqlConnection con = new SqlConnection(@MySqlCon);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
DataTable dt = new DataTable();
SqlDataAdapter msda;
msda = new SqlDataAdapter(cmd);
msda.Fill(dt);
con.Close();
return dt;
}
public int ExecuteUpdate(string sqlStr) //用于增删改;
{
SqlConnection con = new SqlConnection(@MySqlCon);
con.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlStr;
int iud = 0;
iud = cmd.ExecuteNonQuery();
con.Close();
return iud;
}
//订单管理界面
private void ToolStripMenuItem1_Click(object sender, EventArgs e)
{
TextBoxNull();
dt = ExecuteQuery("select * from [order]");
//dt = ds.Tables[0];
dataGridView1.DataSource = dt;
labelshow();
}
//客户管理界面
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
TextBoxNull();
dt = ExecuteQuery("select * from [study-Customer]");
//dt = ds.Tables[0];
dataGridView1.DataSource = dt;
label5.Text = "None";
textBox5.ReadOnly = true;
label6.Text = "None";
textBox6.ReadOnly = true;
labelshow();
}
//双击DataGridView内容事件(获取双击的对象值)
private void DataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
string index = dataGridView1.CurrentRow.Cells[0].Value.ToString();
//进行标签判断,确定要调用哪个表,进行值的输出
if (label1.Text == "num")
{
dt = ExecuteQuery("select *from [study-userset] where num='" + index + "'");
//dt = ds.Tables[0];
DataRow row = dt.Rows[0];
textBox1.Text = row["num"].ToString();
textBox2.Text = row["username"].ToString();
textBox3.Text = row["password"].ToString();
textBox4.Text = row["name"].ToString();
textBox5.Text = row["email"].ToString();
textBox6.Text = row["gettime"].ToString();
textBox6.ReadOnly = false;
}
if (label1.Text == "cid")
{
dt = ExecuteQuery("select *from [study-Customer] where cid='" + index + "'");
//dt = ds.Tables[0];
DataRow row = dt.Rows[0];
textBox1.Text = row["cid"].ToString();
textBox2.Text = row["name"].ToString();
textBox3.Text = row["profession"].ToString();
textBox4.Text = row["company"].ToString();
}
if (label1.Text == "number")
{
dt = ExecuteQuery("select *from [order] where number='" + index + "'");
// dt = ds.Tables[0];
DataRow row = dt.Rows[0];
textBox1.Text = row["number"].ToString();
textBox2.Text = row["name"].ToString();
textBox3.Text = row["goodsName"].ToString();
textBox4.Text = row["goodsnum"].ToString();
textBox5.Text = row["price"].ToString();
textBox6.Text = row["time"].ToString();
textBox6.ReadOnly = false;
}
}
//添加按钮(也是用标签判断---以下都是)
private void Button1_Click(object sender, EventArgs e)
{
int i = 0;
DateTime time = DateTime.Now;
if (label1.Text == "num")
{
string str = textBox3.Text.Trim();
i = ExecuteUpdate("insert into [study-userset](username,password,name,email,gettime)values('" + textBox2.Text + "','" + str + "','" + textBox4.Text + "','"+textBox5.Text+ "','" + time +"')");
}
else if (label1.Text == "cid")
{
i = ExecuteUpdate("insert into [study-Customer](name,profession,company)values('" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text+ "')");
}
else
{
try
{
i = ExecuteUpdate("insert into [order](name,goodsName,goodsnum,price,time)values('" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "','" + textBox5.Text+ "','" + time+ "')");
}
catch (Exception)
{
MessageBox.Show("添加失败");
}
}
if (i > 0)
{
MessageBox.Show("添加成功");
}
else
{
MessageBox.Show("添加失败");
}
//调用SQL储存过程(先在SQL创建“GG”储存过程)
ExecuteQuery("exec GG");
}
//删除按钮
private void Button2_Click(object sender, EventArgs e)
{
int i = 0;
string currentIndex = dataGridView1.CurrentRow.Cells[0].Value.ToString();
if (label1.Text == "num")
{
i = ExecuteUpdate("delete from [study-userset] where num='" + currentIndex + "'");
}
else if (label1.Text == "cid")
{
i = ExecuteUpdate("delete from [study-Customer] where cid='" + currentIndex + "'");
}
else
{
i = ExecuteUpdate("delete from [order] where number='" + currentIndex + "'");
}
if (i > 0)
{
MessageBox.Show("删除成功");
}
else
{
MessageBox.Show("删除失败");
}
}
//修改按钮
private void Button3_Click(object sender, EventArgs e)
{
int i = 0;
if (label1.Text == "num")
{
i = ExecuteUpdate("update [study-userset] set username='" + textBox2.Text + "',password='" + textBox3.Text + "',name='" + textBox4.Text + "',email='" + textBox5.Text + "',gettime='" + textBox6.Text+ "'where num='" + textBox1.Text + "'");
}
if (label1.Text == "cid")
{
i = ExecuteUpdate("update [study-Customer] set name='" + textBox2.Text + "',profession='" + textBox3.Text + "',company='" + textBox4.Text + "'where cid='" + textBox1.Text + "'");
}
if (label1.Text == "number")
{
i = ExecuteUpdate("update [order] set name='" + textBox2.Text + "',goodsName='" + textBox3.Text + "',goodsnum='" + textBox4.Text + "',price='" + textBox5.Text + "',time='" + textBox6.Text + "'where number='" + textBox1.Text + "'");
}
if (i > 0)
{
MessageBox.Show("Succeed!");
}
else
{
MessageBox.Show("Failed!");
}
}
//把数据输出为Excle表
private void Button4_Click(object sender, EventArgs e)
{
if (label1.Text == "num")
{
dt = ExecuteQuery("select *from [study-userset]");
string name = "用户管理";
DataTabletoExcel(dt, name);
MessageBox.Show("Succeed!");
}
if (label1.Text == "cid")
{
dt = ExecuteQuery("select *from [study-Customer]");
string name = "客户管理";
DataTabletoExcel(dt, name);
MessageBox.Show("Succeed!");
}
if (label1.Text == "number")
{
dt = ExecuteQuery("select *from [order]");
string name = "订单管理";
DataTabletoExcel(dt, name);
MessageBox.Show("Succeed!");
}
}
//datatable 输出Excle
private void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
int rowNum = tmpDataTable.Rows.Count;
int columnNum = tmpDataTable.Columns.Count;
int rowIndex = 1;
int columnIndex = 0;
Microsoft.Office.Interop.Excel.Application xlApp =
new Microsoft.Office.Interop.Excel.Application();
xlApp.DefaultFilePath = "D:\\";
xlApp.DisplayAlerts = true;
xlApp.SheetsInNewWorkbook = 1;
Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);
//将DataTable的列名导入Excel表第一行
foreach (DataColumn dc in tmpDataTable.Columns)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
}
//将DataTable中的数据导入Excel中
for (int i = 0; i < rowNum; i++)
{
rowIndex++;
columnIndex = 0;
for (int j = 0; j < columnNum; j++)
{
columnIndex++;
xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
}
}
xlBook.SaveCopyAs(strFileName + ".xls");
}
private void SplitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
{
}
}
VS设计SQL用户管理模块、客户管理模块、订单管理模块对数据进行增删改,并用Excle输出
最新推荐文章于 2024-09-12 15:31:55 发布