SqlManage.cs
- //public static string cnn_string = @"server=DESKTOP-48M8EJT\MSSQLSERVER1;database=tmp2015;integrated security=SSPI";
- public static string cnnstring = @"Server=AN2XOC6HS03APKT;Database=tmp2019;Uid=sa;Pwd=liwei666;";
- public static SqlConnection cnn = null;
- /// <summary>
- /// 数据库查询操作
- /// </summary>
- /// <param name="sql"> 数据库语句 </param>
- public static void ExcuteTable(string sql)
- {
- try
- {
- SqlCommand cmd = new SqlCommand(sql, SqlManage.cnn);
- if (cmd.ExecuteNonQuery()> 0)
- {
- MessageBox.Show("操作成功");
- }
- else
- {
- MessageBox.Show("操作失败!\n 检查数据库是否有该记录");
- }
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message);
- }
- }
- /// <summary>
- /// 数据库更新操作 (包括插入, 修改, 删除)
- /// </summary>
- /// <param name="sql"> 数据库语句 </param>
- /// <returns > 返回查询结果 </returns>
- public static DataTable SelectTable(string sql)
- {
- try
- {
- DataTable table = new DataTable();
- SqlDataAdapter sda = new SqlDataAdapter(sql, SqlManage.cnn);
- sda.Fill(table);
- return table;
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message);
- return null;
- }
- }
- /// <summary>
- /// 设置 DataGridView 控件的属性
- /// </summary>
- /// <param name="d"> 控件名 </param>
- public static void SetDataGridView(DataGridView d)
- {
- d.ReadOnly = true;
- d.AllowUserToAddRows = false;
- d.RowHeadersVisible = false;
- d.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
- }
- /// <summary>
- /// 判断 TextBox 控件是否输入了内容
- /// </summary>
- /// <param name="t"> 控件名 </param>
- /// <returns > 输入了内容发挥 true</returns>
- public static bool JudTextBox(TextBox t)
- {
- return t.Text.Trim().Length> 0 ? true : false;
- }
Program.cs
- Application.EnableVisualStyles();
- Application.SetCompatibleTextRenderingDefault(false);
- try
- {
- SqlManage.cnn = new SqlConnection(SqlManage.cnnstring);
- SqlManage.cnn.Open();
- }
- catch (Exception ex)
- {
- MessageBox.Show(ex.Message);
- Application.Exit();
- return;
- }
- Application.Run(new FormMain());
- SqlManage.cnn.Close();
插入数据:
- if (!SqlManage.JudTextBox(this.textBox1))
- {
- MessageBox.Show("输入员工编号");
- return;
- }
- if (!SqlManage.JudTextBox(this.textBox2))
- {
- MessageBox.Show("输入员工姓名");
- return;
- }
- if (!SqlManage.JudTextBox(this.textBox3))
- {
- MessageBox.Show("输入员工年龄");
- return;
- }
- int age;
- if (!int.TryParse(this.textBox3.Text, out age))
- {
- MessageBox.Show("年龄必须是整数");
- return;
- }
- string sex = this.radioButton1.Checked ? "男" : "女";
- string sql = string.Format("insert into EMPLOYEE values('{0}','{1}','{2}','{3}')",
- this.textBox1.Text.Trim(), this.textBox2.Text.Trim(), sex, age);
- SqlManage.ExcuteTable(sql);
修改数据:
- if (!SqlManage.JudTextBox(this.textBox1))
- {
- MessageBox.Show("输入员工编号");
- return;
- }
- if (!SqlManage.JudTextBox(this.textBox2))
- {
- MessageBox.Show("输入员工姓名");
- return;
- }
- if (!SqlManage.JudTextBox(this.textBox3))
- {
- MessageBox.Show("输入员工年龄");
- return;
- }
- int age;
- if (!int.TryParse(this.textBox3.Text, out age))
- {
- MessageBox.Show("年龄必须是整数");
- return;
- }
- string sex = this.radioButton1.Checked ? "男" : "女";
- string sql = string.Format("update EMPLOYEE set EmpName='{0}',EmpSex='{1}',EmpAge='{2}'where EmpNo='{3}'",
- this.textBox2.Text.Trim(), sex, age, this.textBox1.Text.Trim());
- SqlManage.ExcuteTable(sql);
删除数据:
- if (!SqlManage.JudTextBox(this.textBox1))
- {
- MessageBox.Show("输入员工编号");
- return;
- }
- string sql = string.Format("delete from EMPLOYEE where EmpNo='{0}'", this.textBox1.Text.Trim());
- SqlManage.ExcuteTable(sql);
显示网格:
- SqlManage.SetDataGridView(this.dataGridView1);
- string sql = "select EmpNO as 员工编号, EmpName as 员工姓名, EmpSex as 员工表性别, EmpAge as 员工年龄 from EMPLOYEE";
- DataTable table = SqlManage.SelectTable(sql);
- this.dataGridView1.DataSource = table;
下拉框查找:
- SqlManage.SetDataGridView(this.dataGridView1);
- // 初始化教师编号
- string sql = "select EmpNo from EMPLOYEE";
- DataTable table = SqlManage.SelectTable(sql);
- string empno;
- foreach (DataRow row in table.Rows)
- {
- empno = row["EmpNo"].ToString();
- this.comboBox1.Items.Add(empno);
- }
- if (table.Rows.Count> 0)
- {
- this.comboBox1.SelectedIndex = 0;
- }
- // 初始化教师姓名
- string sql_name = "select EmpName from EMPLOYEE";
- table.Clear();
- table = SqlManage.SelectTable(sql_name);
- string empname;
- foreach (DataRow row in table.Rows)
- {
- empname = row["EmpName"].ToString();
- this.comboBox2.Items.Add(empname);
- }
- if (table.Rows.Count> 0)
- {
- this.comboBox2.SelectedIndex = 0;
- }
- string sql = "";
- if (this.radioButton1.Checked)
- {
- sql = string.Format("select CmpName as 公司名, Salary as 工资 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and WORKS.EmpNo ='{0}'",
- this.comboBox1.Text);
- }
- else if (this.radioButton2.Checked)
- {
- sql = string.Format("select CmpName as 公司名, Salary as 工资 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and EmpName ='{0}'",
- this.comboBox2.Text);
- }
- DataTable table = SqlManage.SelectTable(sql);
- if (table.Rows.Count> 0)
- {
- this.dataGridView1.DataSource = table;
- }
- else
- {
- MessageBox.Show("没有相关内容");
- }
having 子句查询:
- SqlManage.SetDataGridView(this.dataGridView1);
- string sql = "select EmpName as 员工姓名, CmpName as 公司名 from EMPLOYEE,COMPANY,WORKS where EMPLOYEE.EmpNo=WORKS.EmpNo and COMPANY.CmpNo=WORKS.CmpNo and" +
- "WORKS.EmpNo in (select EmpNo from WORKS group by EmpNo having count(*)>1)";
- DataTable table = SqlManage.SelectTable(sql);
- this.dataGridView1.DataSource = table;
来源: http://www.bubuko.com/infodetail-3006589.html