一、实验目的
本实验的目的是设计一个简单的学生信息管理的程序,实现对数据的增删改查。
二、实验器材
Visual Studio
SQL server 2008
三、实验内容
教学管理人员能够使用该程序对学生基本信息、课程信息进行管理,包括数据的添加、修改删除和浏览;能够对学生选课进行管理,包括添加学生选课信息、录入成绩;能使用查询功能,快速查看到指定学生的选课信息;能够对学生选课情况进行简单的统计,包括所选的总的课程数、总学分数及平均成绩。
在添加学生基本信息、课程基本信息相关数据时,学号和课程号不能重复;在添加学生选课信息时,要求该学生和课程必须是存在的,而且不能添加重复的选课信息。
应用程序提供操作界面,可以方便用户进行功能选择,实现 信息的管理和查询,并可以清晰地显示相关信息。
四、实验要点
①建立数据库,连接数据库与Visual Studio
②通过VS实现对数据库的增删改查
③登录界面的优化控制,权限的设置
五、实验步骤
①建立数据库,插入数据
③建立数据库SQL和visual studio 的连接,界面设计
登录主界面
学生登录界面
老师登录界面
④插入功能实现的代码(增删改查)
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 sql_homework_end
{
public partial class main : Form
{
public main()
{
InitializeComponent();
}
//绑定并显示相关信息
DataSet ds = new DataSet();
DataTable dt = new DataTable();
private void 学生信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_student");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
private void 课程信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_course");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
private void 选课信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_student_course");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
dataGridView1.Columns["sc_id"].DisplayIndex = 0;
}
private void 管理员信息ToolStripMenuItem_Click(object sender, EventArgs e)
{
ds = sqlhelper.ExecuteDataSet("select * from tb_admin");
dt = ds.Tables[0];
dataGridView1.DataSource = dt;
}
//添加按钮事件
private void btn_insert_Click(object sender, EventArgs e)
{
if (dataGridView1.Columns[0].HeaderText=="student_num")
{
string sql = @"insert tb_student (student_num,student_name,student_password,student_sex
,student_age,student_dept)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" +
dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value
+ "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[4].Value
+ "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[5].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText=="course_num")
{
string sql = @"insert tb_course (course_num,course_name,course_credit,course_semester)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText=="sc_id")
{
try
{
string sql = @"insert tb_student_course (sc_id,student_num,course_num,grade)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
catch (Exception)
{
MessageBox.Show("学号或姓名不存在,请重新添加。");
}
}
else if (dataGridView1.Columns[0].HeaderText == "admin_id")
{
string sql = @"insert tb_admin (admin_id,admin_name,admin_password,remark)values('" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[0].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[1].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[2].Value + "','" + dataGridView1.Rows[dataGridView1.RowCount - 2].Cells[3].Value + "')";
sqlhelper.ExecuteNoQuery(sql);
}
MessageBox.Show("添加成功");
}
//更新按钮事件
private void btn_update_Click(object sender, EventArgs e)
{
if (dataGridView1.Columns[0].HeaderText == "student_num")
{
update("tb_student","student_num");
}
else if (dataGridView1.Columns[0].HeaderText == "course_num")
{
update("tb_course","course_num");
}
else if (dataGridView1.Columns[0].HeaderText == "admin_id")
{
update("tb_admin","admin_id");
}
else if (dataGridView1.Columns[0].HeaderText == "sc_id")
{
try
{
update("tb_studnet_course","sc_id");
}
catch (Exception)
{
MessageBox.Show("学号或课程号不存在,请重新输入");
throw;
}
}
}
//更新方法
private void update(string table,string head_id)
{
for (int i = 0; i < dataGridView1.RowCount; i++)
{
int id = Convert.ToInt32(dataGridView1.Rows[i].Cells[0].Value);
for (int j = 1; j < dataGridView1.ColumnCount; j++)
{
if (dataGridView1.Columns[j].Visible == true)
{
string columnName = dataGridView1.Columns[j].Name.ToString();
string sql = "update " + table + " set " + columnName + "='" + dataGridView1.Rows[i].Cells[j].Value + "' where " + head_id + "='" + id + "'";
sqlhelper.ExecuteNoQuery(sql);
}
}
}
MessageBox.Show("更新成功");
}
//删除事件
private void btn_delete_Click(object sender, EventArgs e)
{
if (dataGridView1.Columns[0].HeaderText == "student_num")
{
string sql = "delete from tb_student where student_num='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText == "course_num")
{
string sql = "delete from tb_course where course_num='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText == "sc_id")
{
string sql = "delete from tb_student_course where sc_id='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
else if (dataGridView1.Columns[0].HeaderText == "admin_id")
{
string sql = "delete from tb_admin where admin_id='" + dataGridView1.SelectedCells[0].Value + "'";
sqlhelper.ExecuteNoQuery(sql);
}
MessageBox.Show("删除成功");
}
//界面载入显示身份和登录时间
private void main_Load(object sender, EventArgs e)
{
lbl_username.Text = "Welcome," + login.GlobelValue + "";
lbl_logintime.Text ="登录时间:" +DateTime.Now.ToString()+"";
}
}
}
⑤项目目录