SCUT2014机试数据库
如题
注意事项：
创建文件夹在 D 盘目录下，新建目录如下：D：2014 研究生复试\你的名字\
源代码新建一个文件夹（source）写一个 readme.txt 说明开发环境。
外籍教师管理系统：
数据库（35）
TEACHERS
教师号 教师名 所在系 职称号
T01 张三 计算机 Z01
T02 李四 物理 Z02
T03 王五 数学 Z02
T04 赵六 计算机 Z03
REMUNERATIONS
职称号 薪酬
Z01 80
Z02 60
Z03 40
COURSES
课程号 课程名 学时 教师号
C01 数据库 64 T01
C02 大学物理 56 T02
C03 高等数学 80 T03
C04 操作系统 60 T04
C05 线性代数 50 T03
C06 微积分 40 T03
C07 数据结构 60 T04
要求在数据库上建表，约束关系等
一个老师可以教多门课程，但是一门课程只能由一个老师教。
插入数据
备份数据库
数据库编程：（65）
1、维护教师表的信息，实现增删改查，要求每一个功能都各用一个按钮实现，院系要
单选（现在假定只有计算机、数学、物理三个系）
2、根据老师名或者老师编号找出要上的课程名字和课时（要求下拉框）。
3、统计只上一门课的教师，要求输出该教师的教师名以及该课程的信息
4、统计每个教师的总薪酬，要求输出每个教师的教师名、总薪酬并按总薪酬高低排序
建立一个 teachersInfo.doc 文档，如有必要写上自己程序上面的参数；如果有功能运
行不了，贴上代码。依然是延续了以往的方法和布局，只不过这个窗口组件的属性通过代码设定了。
实现
窗口控件及布局：
获取Connection的工具类(使用单例模式)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
namespace Demo2014
{
class DBUtils
{
static String strCon = @"Data Source=.\SQLEXPRESS;Initial Catalog=TeacherManagement;Integrated Security=True";
static SqlConnection con = null;
public static SqlConnection getCon()
{
if (con == null) //在多线程情况下这种方式不安全
con = new SqlConnection(strCon);
else if(con.State==System.Data.ConnectionState.Closed)
con.Open();
return con;
}
}
}
封装数据的类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Demo2014
{
public class Teacher
{
private String TNo="";
private String TName = "";
private String deptName = "";
private String RNo = "";
public void setTNo(String TNo)
{
this.TNo = TNo;
}
public String getTNo()
{
return TNo;
}
public void setTName(String TName)
{
this.TName = TName;
}
public String getTName()
{
return TName;
}
public void setdeptName(String deptName)
{
this.deptName = deptName;
}
public String getdeptName()
{
return deptName;
}
public void setRNo(String RNo)
{
this.RNo = RNo;
}
public String getRNo()
{
return RNo;
}
}
}
Form1.cs
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 System.Data.SqlClient;
namespace Demo2014
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
listView1.View = View.Details;
listView1.GridLines = true;
listView1.FullRowSelect = true;
}
String sql = "";
//查询按钮
private void button1_Click(object sender, EventArgs e)
{
Form2 f2 = new Form2("查询教师信息");
DialogResult dr= f2.ShowDialog();
if (dr == DialogResult.OK) //点击确认按钮
{
Teacher teacher = f2.teacher;
sql = "select TNo as 教师号,TName as 教师名,deptName as 所在系,RNo as 职称号 from TEACHERS where 1=1 ";
DataSet ds= execute(teacher,sql);
updateListview(ds, listView1);
}
}
//这一段代码好难记啊！！！ 更新ListView显示
private void updateListview(DataSet ds, ListView lv)
{
DataTable table= ds.Tables["TeacherManagement"];//数据库名
//添加数据前先清一下
lv.Clear();
//添加列头
for (int i = 0; i < table.Columns.Count; i++)
{
ColumnHeader header = new ColumnHeader();
header.Text = table.Columns[i].ToString();
header.Width = 100;
lv.Columns.Add(header);
//MessageBox.Show("" + table.Columns[i].ToString());
}
//添加表格数据
for (int i = 0; i < table.Rows.Count; i++)
{
//1.创建行对象,并把第一列值赋值进去，因此下面的j从第二列开始(0是第一列)
ListViewItem item = new ListViewItem(table.Rows[i][0].ToString());
for (int j = 1; j < table.Columns.Count; j++)
{
//2. 给行对象赋值
item.SubItems.Add(table.Rows[i][j].ToString());
}
//3.添加到listview中去
lv.Items.Add(item);
}
}
private DataSet execute(Teacher teacher, String sql)
{
SqlParameter TNo=null;
SqlParameter TName = null;
SqlParameter deptName = null;
SqlParameter RNo = null;
if (teacher == null) //防空指针异常
teacher = new Teacher();
//MessageBox.Show("----->"+("".Equals(teacher.getTNo().ToString())));
if (!"".Equals(teacher.getTNo()))
{
sql += " and [email protected] ";
TNo = new SqlParameter("@TNo", teacher.getRNo());
}
if (!"".Equals(teacher.getTName()))
{
sql += " and [email protected] ";
TName = new SqlParameter("@TName", teacher.getTName());
}
if (!"".Equals(teacher.getdeptName()))
{
sql += " and [email protected] ";
deptName = new SqlParameter("@deptName", teacher.getdeptName());
}
if (!"".Equals(teacher.getRNo()))
{
sql += " and [email protected] ";
RNo = new SqlParameter("@RNo", teacher.getRNo());
}
//MessageBox.Show(sql);
SqlConnection con = DBUtils.getCon();
SqlCommand com = new SqlCommand(sql, con);
SqlDataAdapter sda = new SqlDataAdapter(com);
if(TNo!=null)
sda.SelectCommand.Parameters.Add(TNo);
if (TName != null)
sda.SelectCommand.Parameters.Add(TName);
if (deptName != null)
sda.SelectCommand.Parameters.Add(deptName);
if (RNo != null)
sda.SelectCommand.Parameters.Add(RNo);
DataSet ds = new DataSet();
try
{
sda.Fill(ds, "TeacherManagement");
}
catch (Exception e)
{
MessageBox.Show("出错了...."+e);
//return null;
}
finally
{
con.Close();
}
//显示到lv中
return ds;
}
//修改按钮
private void button3_Click(object sender, EventArgs e)
{
if (listView1.SelectedItems.Count > 0)
{
String TNo = listView1.SelectedItems[0].Text;
String TName = listView1.SelectedItems[0].SubItems[1].Text;
String deptName = listView1.SelectedItems[0].SubItems[2].Text;
String RNo = listView1.SelectedItems[0].SubItems[3].Text;
Teacher teacher = new Teacher();
teacher.setTNo(TNo);
teacher.setTName(TName);
teacher.setdeptName(deptName);
teacher.setRNo(RNo);
Form2 f2 = new Form2("修改用户信息", teacher);
DialogResult dr= f2.ShowDialog();
teacher = f2.teacher;
if (dr == DialogResult.OK)
{
sql = "update teachers set [email protected] ,[email protected] , [email protected] , [email protected] where TNo='" + teacher.getTNo() + "'";
SqlParameter TNop = null;
SqlParameter TNamep = null;
SqlParameter deptNamep = null;
SqlParameter RNop = null;
if (teacher.getTNo() != "")
{
TNop = new SqlParameter("@TNo", teacher.getTNo());
}
if (teacher.getTName() != "")
{
TNamep = new SqlParameter("@TName", teacher.getTName());
}
if (teacher.getdeptName() != "")
{
deptNamep = new SqlParameter("@deptName", teacher.getdeptName());
}
if (teacher.getRNo() != "")
{
RNop = new SqlParameter("@RNo", teacher.getRNo());
}
SqlConnection con = DBUtils.getCon();
SqlCommand com = new SqlCommand(sql, con);
if (TNop != null)
com.Parameters.Add(TNop);
if (TNamep != null)
com.Parameters.Add(TNamep);
if (deptNamep != null)
com.Parameters.Add(deptNamep);
if (RNop != null)
com.Parameters.Add(RNop);
int rows=com.ExecuteNonQuery();
if (rows == 1)
{
MessageBox.Show("修改成功！");
Form1_Load(null, null);
}
}
}
else
{
MessageBox.Show("请选择要修改的数据！");
}
}
//刚开始加载时把所有教师信息全部显示出来
private void Form1_Load(object sender, EventArgs e)
{
sql = "select TNo as 教师号,TName as 教师名,deptName as 所在系,RNo as 职称号 from teachers";
updateListview(execute(new Teacher(), sql), listView1);
}
//菜单栏事件
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
if (tabControl1.SelectedTab == tabPage1)
{
Form1_Load(null, null);
}
else if (tabControl1.SelectedTab == tabPage2)
{
//先把下拉框的数据读取并显示
sql = "select TName,TNo from teachers ";
DataSet ds=execute(new Teacher(), sql);
DataTable table = ds.Tables["teachermanagement"];
comboBox1.Items.Clear();
comboBox2.Items.Clear();
for (int i = 0; i < table.Rows.Count; i++)
{
comboBox1.Items.Add(table.Rows[i][0]); //教师名
comboBox2.Items.Add(table.Rows[i][1]); //教师号
}
comboBox1.SelectedIndex=0;
comboBox2.SelectedIndex = 0;
comboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
comboBox2.DropDownStyle = ComboBoxStyle.DropDownList;
}
else if (tabControl1.SelectedTab == tabPage3)
{
sql = "select TName as 教师名,CName as 课程名,STime as 学时 from TEACHERS inner join COURSES on TEACHERS.TNo=COURSES.TNo inner join (select TNo from COURSES group by TNo having COUNT(COURSES.TNo)=1 )as t1 on COURSES.TNo= t1.TNo;";
dataGridView2.DataSource = (execute(new Teacher(), sql).Tables["teachermanagement"]);
}
else if (tabControl1.SelectedTab == tabPage4)
{
sql = "select Tname as 教师名,SUM(salary) as 总薪酬 from COURSES inner join TEACHERS on COURSES.TNo=TEACHERS.TNo inner join REMUNERATIONS on TEACHERS.RNo=REMUNERATIONS.RNo group by TName,salary order by SUM(salary) desc";
dataGridView3.DataSource = (execute(new Teacher(), sql).Tables["teachermanagement"]);
}
}
//根据教师名查询出要上的课程名字和课时
private void button5_Click(object sender, EventArgs e)
{
String TName = comboBox1.Text.Trim();
Teacher teacher = new Teacher();
teacher.setTName(TName);
sql = "select CName as 课程名,STime as 课时 from COURSES inner join TEACHERS on COURSES.TNo=TEACHERS.TNo where 1=1 ";
DataSet ds=execute(teacher, sql);
dataGridView1.DataSource = ds.Tables["teachermanagement"];
}
private void button6_Click(object sender, EventArgs e)
{
String TNo = comboBox2.Text.Trim();
sql = "select CName as 课程名,STime as 课时 from COURSES inner join TEACHERS on COURSES.TNo=TEACHERS.TNo where TEACHERS.TNo ='"+TNo+"'";
DataSet ds=execute(new Teacher(), sql);
dataGridView1.DataSource = ds.Tables["teachermanagement"];
}
}
}
Form2.cs
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;
namespace Demo2014
{
public partial class Form2 : Form
{
//private string fName;
public Form2()
{
InitializeComponent();
this.rb1.Checked = true;
rb1.AutoCheck = false;
}
public Form2(string p)
{
InitializeComponent();
this.Text = p;
this.rb1.Checked = true;
// rb1.AutoCheck = false;
}
//修改教师信息时，要把选择的教师信息显示到修改窗口中
public Form2(string p, Teacher teacher)
{
InitializeComponent();
this.Text = p;
tb1.Text = teacher.getTNo();
tb1.Enabled = false; //教师编号不能随意更改
tb2.Text = teacher.getTName();
tb3.Text = teacher.getRNo();
String deptName = teacher.getdeptName();
if ("计算机".Equals(deptName))
rb1.Checked = true;
else if ("数学".Equals(deptName))
rb2.Checked=true;
else if ("物理".Equals(deptName))
rb3.Checked=true;
}
public Teacher teacher ;
private void button1_Click(object sender, EventArgs e)
{
teacher = new Teacher();
teacher.setTNo(tb1.Text.Trim());
teacher.setTName(tb2.Text.Trim());
String deptName = "";
if (rb1.Checked == true) deptName = "计算机";
else if (rb2.Checked == true) deptName = "数学";
else if (rb3.Checked == true) deptName = "物理";
teacher.setdeptName(deptName);
teacher.setRNo(tb3.Text.Trim());
this.DialogResult = DialogResult.OK;
this.Dispose();
}
private void button2_Click(object sender, EventArgs e)
{
this.DialogResult = DialogResult.Cancel;
this.Dispose();
}
}
}
运行结果
题目1：
题目2：
题目3：
题目4：
我亦无他，唯手熟尔。