学生管理系统
数据库:
DROP DATABASE IF EXISTS School
CREATE DATABASE School;
USE School;
DROP TABLE IF EXISTS SysUser
CREATE TABLE SysUser --管理员
(
ID NCHAR(20) PRIMARY KEY,
UserPassWord NCHAR(32) NOT NULL,
);
DROP TABLE IF EXISTS SysLog
CREATE TABLE SysLog --登录
(
UserID NCHAR(20) ,
DateAndTime datetime,
UserOperation NCHAR(200)
);
DROP TABLE IF EXISTS Student
CREATE TABLE Student
(
Sno CHAR(20) PRIMARY KEY,
Sname CHAR(20),
Ssex CHAR(2),
Sbrith datetime,
Sdept CHAR(20),
UserPassWord NCHAR(32) NOT NULL,
Photo image
);
DROP TABLE IF EXISTS Course
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40) NOT NULL,
);
DROP TABLE IF EXISTS SC
CREATE TABLE SC
(
Sno CHAR(20),
Cno CHAR(4),
Grade SMALLINT CHECK (Grade>=0 AND Grade <=100),
PRIMARY KEY (Sno,Cno), --主码
FOREIGN KEY (Sno) REFERENCES Student(Sno), --Sno是外码,被参照表是Student
FOREIGN KEY (Cno) REFERENCES Course(Cno) --Cno是外码,被参照表是Course
);
INSERT INTO SysUser VALUES ('admin','123');
INSERT INTO Student (Sno,Sname,Ssex,Sdept,UserPassWord) VALUES ('201215121','李勇','男','CS','698d51a19d8a121ce581499d7b701668');
INSERT INTO Student (Sno,Sname,Ssex,Sdept,UserPassWord) VALUES ('201215122','刘晨','女','CS','698d51a19d8a121ce581499d7b701668');
INSERT INTO Student (Sno,Sname,Ssex,Sdept,UserPassWord) VALUES ('201215123','王敏','女','MA','698d51a19d8a121ce581499d7b701668');
INSERT INTO Student (Sno,Sname,Ssex,Sdept,UserPassWord) VALUES ('201215125','张立','男','IS','698d51a19d8a121ce581499d7b701668');
INSERT INTO Student (Sno,Sname,Ssex,Sdept,UserPassWord) VALUES ('201215128','陈冬','男','IS','698d51a19d8a121ce581499d7b701668');
INSERT INTO Course(Cno,Cname) VALUES ('1','数据库');
INSERT INTO Course(Cno,Cname) VALUES ('2','数学');
INSERT INTO Course(Cno,Cname) VALUES ('3','信息系统');
INSERT INTO Course(Cno,Cname) VALUES ('4','操作系统');
INSERT INTO Course(Cno,Cname) VALUES ('5','数据结构');
INSERT INTO Course(Cno,Cname) VALUES ('6','数据处理');
INSERT INTO Course(Cno,Cname) VALUES ('7','Pascal语言');
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','1',92);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','2',85);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215121 ','3',88);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','2',90);
INSERT INTO SC(Sno,Cno,Grade) VALUES ('201215122 ','3',80);
IF(OBJECT_ID('regist_recorder') is not null) -- 判断名为 regist_recorder 的触发器是否存在
DROP TRIGGER regist_recorder -- 删除触发器
GO
CREATE TRIGGER regist_recorder
ON SysUser
AFTER
INSERT
AS
declare @UserName nchar(20)
declare @DateTime datetime
declare @UserOperation nchar(200)
select @UserName = system_user
select @DateTime = CONVERT(datetime,GETDATE(),120)
declare @op varchar(10)
select @op=case when exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Update'
when exists(select 1 from inserted) and not exists(select 1 from deleted)
then 'Insert'
when not exists(select 1 from inserted) and exists(select 1 from deleted)
then 'Delete' end
select @UserOperation = @op
INSERT INTO SysLog(UserID,DateAndTime,UserOperation)
VALUES (@UserName,@DateTime,@UserOperation)
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace School
{
public partial class FormLogin : Form
{
public string code;//验证码
public static string uid;//记录登录学号
public static string getuid()
{
return uid;
}
public FormLogin()
{
InitializeComponent();
}
private void FormLogin_Load(object sender, EventArgs e)
{
Random ran = new Random(); //随机实例化
int number;
char code1;
code = null;
for (int i = 0; i < 4; i++) //取四个数
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26)); //转化为字符
code += code1.ToString();
}
labelCheck.Text = code;
}
private void buttonCheck_Click(object sender, EventArgs e) //刷新验证码
{
Random ran = new Random(); //随机实例化
int number;
char code1;
code = null;
for (int i = 0; i < 4; i++) //取四个数
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26)); //转化为字符
code += code1.ToString();
}
labelCheck.Text = code;
}
public static string EncryptWithMD5(string source) //MD5加密
{
byte[] sor = Encoding.UTF8.GetBytes(source);
MD5 md5 = MD5.Create();
byte[] result = md5.ComputeHash(sor);
StringBuilder strbul = new StringBuilder(40);
for (int i = 0; i < result.Length; i++)
{
strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
}
return strbul.ToString();
}
private void linkLabelRegister_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)//注册
{
Register register = new Register();
register.ShowDialog();
}
private void buttonOK_Click(object sender, EventArgs e)
{
string username = textBoxUsername.Text.Trim(); //取出账号
string password = EncryptWithMD5(textBoxPassword.Text.Trim()); //取出密码并加密
string myConnString = "Data Source=.;Initial Catalog=School;Persist Security Info=True;User ID=sa;Password=***";
SqlConnection sqlConnectionM = new SqlConnection(myConnString); //实例化连接对象
sqlConnectionM.Open();
string sqlM = "select ID,UserPassword from SysUser where ID = '" + username + "' and UserPassword = '" + password + "'"; //编写SQL命令
SqlCommand sqlCommandM = new SqlCommand(sqlM, sqlConnectionM);
SqlDataReader sqlDataReaderM = sqlCommandM.ExecuteReader();
SqlConnection sqlConnectionS = new SqlConnection(myConnString); //实例化连接对象
sqlConnectionS.Open();
string sqlS = "select Sno,UserPassword from Student where Sno = '" + username + "' and UserPassword = '" + password + "'"; //编写SQL命令
SqlCommand sqlCommandS = new SqlCommand(sqlS, sqlConnectionS);
SqlDataReader sqlDataReaderS = sqlCommandS.ExecuteReader();
if (sqlDataReaderM.HasRows && textBoxCheck.Text == code)
{
//MessageBox.Show("欢迎使用!"); //管理员登录成功
MainM form2 = new MainM();
form2.Show();
this.Hide();
}
else if (sqlDataReaderS.HasRows && textBoxCheck.Text == code)
{
//MessageBox.Show("欢迎使用!"); //学生登录成功
MainS form2 = new MainS();
form2.Show();
this.Hide();
uid = username;
}
else
{
MessageBox.Show("登录失败!");
return;
}
sqlDataReaderM.Close();
sqlM = "insert into SysLog values ( '" + username + "' , '" + DateTime.Now + "' , '" + "Login" + "')"; //编写SQL命令
sqlCommandM = new SqlCommand(sqlM, sqlConnectionM);
sqlCommandM.ExecuteNonQuery();
sqlConnectionM.Close();
sqlDataReaderS.Close();
sqlS = "insert into SysLog values ( '" + username + "' , '" + DateTime.Now + "' , '" + "Login" + "')"; //编写SQL命令
sqlCommandS = new SqlCommand(sqlS, sqlConnectionS);
sqlCommandS.ExecuteNonQuery();
sqlConnectionS.Close();
}
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace School
{
public partial class Register : Form
{
public Register()
{
InitializeComponent();
}
public static string EncryptWithMD5(string source)
{
byte[] sor = Encoding.UTF8.GetBytes(source);
MD5 md5 = MD5.Create();
byte[] result = md5.ComputeHash(sor);
StringBuilder strbul = new StringBuilder(40);
for (int i = 0; i < result.Length; i++)
{
strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
}
return strbul.ToString();
}
private void buttonCancel_Click(object sender, EventArgs e)
{
this.Close();
}
private void textBoxID_TextChanged(object sender, EventArgs e)
{
//使用regex(正则表达式)进行格式设置 至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符。
Regex regex = new Regex(@"(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{3,20}");
if (regex.IsMatch(textBoxID.Text))//判断格式是否符合要求
labelID.Text = null;
else
labelID.Text = "至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符";
}
private void textBoxPassword_TextChanged(object sender, EventArgs e)
{
if (textBoxPassword.Text.Trim() != "")
labelPassword.Text = null;
else
labelPassword.Text = "密码不能为空";
}
private void buttonOK_Click(object sender, EventArgs e)
{
if (labelID.Text.Trim() == "至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符" || labelPassword.Text.Trim() == "密码不能为空")
{
MessageBox.Show("请正确填写用户名、密码!");
}
else
{
try
{
string connString = "Data Source=.;Initial Catalog=School;Persist Security Info=True;User ID=sa;Password=***";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
string sql = "insert into SysUser (ID,UserPassWord) " + "values (@id, @userpassword)";
SqlCommand command = new SqlCommand(sql, connection);
SqlParameter sqlParameter = new SqlParameter("@id", textBoxID.Text);
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userpassword", EncryptWithMD5(textBoxPassword.Text));
command.Parameters.Add(sqlParameter);
//打开数据库连接
connection.Open();
command.ExecuteNonQuery();
connection.Close();
MessageBox.Show("注册成功");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
this.Close();
}
}
}
}
管理员界面:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace School
{
public partial class MainM : Form
{
public MainM()
{
InitializeComponent();
}
private void MainM_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“sCHOOLDataSet4.SysUser”中。您可以根据需要移动或删除它。
this.sysUserTableAdapter.Fill(this.sCHOOLDataSet4.SysUser);
// TODO: 这行代码将数据加载到表“sCHOOLDataSet4.SysLog”中。您可以根据需要移动或删除它。
this.sysLogTableAdapter.Fill(this.sCHOOLDataSet4.SysLog);
// TODO: 这行代码将数据加载到表“sCHOOLDataSet4.SC”中。您可以根据需要移动或删除它。
this.sCTableAdapter2.Fill(this.sCHOOLDataSet4.SC);
// TODO: 这行代码将数据加载到表“sCHOOLDataSet4.Course”中。您可以根据需要移动或删除它。
this.courseTableAdapter1.Fill(this.sCHOOLDataSet4.Course);
// TODO: 这行代码将数据加载到表“sCHOOLDataSet4.Student”中。您可以根据需要移动或删除它。
this.studentTableAdapter2.Fill(this.sCHOOLDataSet4.Student);
}
private void buttonStuC_Click(object sender, EventArgs e)//学生增
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql = "insert into Student (Sno,Sname,Ssex,Sdept,Sbrith,UserPassWord) " + "values (@sno, @sname,@ssex,@sdept,@sbrith,'698d51a19d8a121ce581499d7b701668')";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", textBoxStuSno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@sname", textBoxSname.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ssex", comboBoxSsex.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@sdept", textBoxSdept.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@sbrith", dateTimePickerSbrith.Value);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.studentTableAdapter2.Fill(this.sCHOOLDataSet4.Student);
}
private void buttonStuU_Click(object sender, EventArgs e)//学生删
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string select_id = dataGridViewStudent.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是ID
string delete_by_id = "delete from Student where Sno=" + select_id;//sql删除语句
SqlCommand cmd = new SqlCommand(delete_by_id, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
con.Close();
}
this.studentTableAdapter2.Fill(this.sCHOOLDataSet4.Student);
}
private void buttonStuR_Click(object sender, EventArgs e)//学生改
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
if (!textBoxSname.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET Sname = @sname WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", textBoxStuSno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@sname", textBoxSname.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
if (!comboBoxSsex.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET Ssex = @ssex WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", textBoxStuSno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ssex", comboBoxSsex.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
if (!textBoxSdept.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET Sdept = @s WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", textBoxStuSno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@s", textBoxSdept.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.studentTableAdapter2.Fill(this.sCHOOLDataSet4.Student);
}
private void dateTimePickerSbrith_ValueChanged(object sender, EventArgs e)//学生改生日
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
string sql = "UPDATE Student SET Sbrith = @s WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", textBoxStuSno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@s", dateTimePickerSbrith.Value);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
private void buttonStuD_Click(object sender, EventArgs e)//学生查
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
int c = 0;
string sql = "select * from Student where ";
SqlParameter sqlParameterSno = new SqlParameter("@sno", textBoxStuSno.Text.Trim());
SqlParameter sqlParameterSname = new SqlParameter("@sname", textBoxSname.Text.Trim());
SqlParameter sqlParameterSsex = new SqlParameter("@ssex", comboBoxSsex.Text.Trim());
SqlParameter sqlParameterSdept = new SqlParameter("@sdept", textBoxSdept.Text.Trim());
SqlParameter sqlParameterSbrith = new SqlParameter("@sbrith", dateTimePickerSbrith.Value);
if (!textBoxStuSno.Text.Trim().Equals(""))
{
sql += " Sno=@sno";
c++;
}
if (!textBoxSname.Text.Trim().Equals(""))
{
if (c != 0) sql += " and Sname LIKE @sname";
else sql += " Sname LIKE @sname";
c++;
}
if (!comboBoxSsex.Text.Trim().Equals(""))
{
if (c != 0) sql += " and Ssex=@ssex";
else sql += " Ssex=@ssex";
c++;
}
if (!textBoxSdept.Text.Trim().Equals(""))
{
if (c != 0) sql += " and Sdept=@sdept";
else sql += " Sdept=@sdept";
c++;
}
SqlCommand command = new SqlCommand(sql, con);
if (!textBoxStuSno.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterSno);
if (!textBoxSname.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterSname);
if (!comboBoxSsex.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterSsex);
if (!textBoxSdept.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterSdept);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewStudent.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
private void buttonStuNew_Click(object sender, EventArgs e)//学生刷新
{
dataGridViewStudent.DataSource = this.sCHOOLDataSet4.Student;
}
private void button17_Click(object sender, EventArgs e)//学生清空
{
textBoxStuSno.Text = null;
textBoxSname.Text = null;
comboBoxSsex.Text = null;
textBoxSdept.Text = null;
}
private void buttonCourseC_Click(object sender, EventArgs e)//课程增
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql = "insert into Course (Cno,Cname) " + "values (@cno, @cname)";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@cno", textBoxCno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@Cname", textBoxCname.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.courseTableAdapter1.Fill(this.sCHOOLDataSet4.Course);
}
private void buttonCourseU_Click(object sender, EventArgs e)//课程删
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string select_id = dataGridViewCourse.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是ID
string delete_by_id = "delete from Course where Cno=" + select_id;//sql删除语句
SqlCommand cmd = new SqlCommand(delete_by_id, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
con.Close();
}
this.courseTableAdapter1.Fill(this.sCHOOLDataSet4.Course);
}
private void buttonCourseR_Click(object sender, EventArgs e)//课程改
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
string cno = textBoxCno.Text.Trim();
string cname = textBoxCname.Text.Trim();
try
{
con.Open();
string insertStr = "UPDATE Course SET Cname = '" + cname + "' WHERE Cno = '" + cno + "'";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.courseTableAdapter1.Fill(this.sCHOOLDataSet4.Course);
}
private void buttonCourseD_Click(object sender, EventArgs e)//课程查
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
int c = 0;
string sql = "select * from Course where ";
SqlParameter sqlParameterCno = new SqlParameter("@cno", textBoxCno.Text.Trim());
SqlParameter sqlParameterCname = new SqlParameter("@cname", textBoxCname.Text.Trim());
if (!textBoxCno.Text.Trim().Equals(""))
{
sql+="Cno=@cno";
c++;
}
if (!textBoxCname.Text.Trim().Equals(""))
{
if (c != 0) sql+=" and Cname=@cname";
else sql+="Cname=@cname";
}
SqlCommand command = new SqlCommand(sql, con);
if (!textBoxCno.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterCno);
if (!textBoxCname.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterCname);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewCourse.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
private void buttonCourseNew_Click(object sender, EventArgs e)//课程刷新
{
dataGridViewCourse.DataSource = this.sCHOOLDataSet4.Course;
}
private void buttonCourseClc_Click_1(object sender, EventArgs e)//课程清空
{
textBoxCno.Text = null;
textBoxCname.Text = null;
}
private void button1_Click(object sender, EventArgs e)//选修刷新
{
dataGridViewSC.DataSource = this.sCHOOLDataSet4.SC;
}
private void buttonClc_Click(object sender, EventArgs e)//选修清空
{
textBoxSCno.Text = null;
textBoxSno.Text = null;
textBoxGrade.Text = null;
}
private void buttonscC_Click(object sender, EventArgs e)//选修增
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql;
if (textBoxGrade.Text.Trim().Equals(""))
{
sql = "insert into SC (Sno,Cno) " + "values (@sno, @cno)";
}
else
{
sql = "insert into SC (Sno,Cno,Grade) " + "values (@sno, @cno,@grade)";
}
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", textBoxSno.Text.Trim());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@cno", textBoxSCno.Text.Trim());
command.Parameters.Add(sqlParameter);
if (!textBoxGrade.Text.Trim().Equals(""))
{
sqlParameter = new SqlParameter("@grade", textBoxGrade.Text.Trim());
command.Parameters.Add(sqlParameter);
}
con.Open();
command.ExecuteNonQuery();
con.Close();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.sCTableAdapter2.Fill(this.sCHOOLDataSet4.SC);
}
private void buttonscU_Click(object sender, EventArgs e)//选修删
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string select_s = dataGridViewSC.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是ID
string select_c = dataGridViewSC.SelectedRows[0].Cells[1].Value.ToString();
string delete_by_id = "delete from SC where Cno=" + select_c + " and Sno=" +select_s;//sql删除语句
SqlCommand cmd = new SqlCommand(delete_by_id, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
con.Close();
}
this.sCTableAdapter2.Fill(this.sCHOOLDataSet4.SC);
}
private void buttonscR_Click(object sender, EventArgs e)//选修改
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
string cno = textBoxSCno.Text.Trim();
string sno = textBoxSno.Text.Trim();
string grade = textBoxGrade.Text.Trim();
try
{
con.Open();
string insertStr = "UPDATE SC SET Grade = '" + grade + "' WHERE Cno = '" + cno + "'" +" and Sno = '" + sno + "'";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.sCTableAdapter2.Fill(this.sCHOOLDataSet4.SC);
}
private void buttonscD_Click(object sender, EventArgs e)//选修查
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
int c = 0;
string sql = "select * from SC where ";
SqlParameter sqlParameterCno = new SqlParameter("@cno", textBoxSCno.Text.Trim());
SqlParameter sqlParameterSno = new SqlParameter("@sno", textBoxSno.Text.Trim());
SqlParameter sqlParameterG = new SqlParameter("@grade", textBoxGrade.Text.Trim());
if (!textBoxSCno.Text.Trim().Equals(""))
{
sql += "Cno=@cno";
c++;
amm();
}
if (!textBoxSno.Text.Trim().Equals(""))
{
if (c != 0) sql += " and Sno=@sno";
else sql += "Sno=@sno";
c++;
}
if (!textBoxGrade.Text.Trim().Equals(""))
{
if (c != 0) sql += " and Grade=@grade";
else sql += "Grade = @grade";
}
if (radioButton1.Checked)
{
sql += " ORDER BY Grade";
}
if (radioButton2.Checked)
{
sql += " ORDER BY Grade DESC";
}
SqlCommand command = new SqlCommand(sql, con);
if (!textBoxSCno.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterCno);
if (!textBoxSno.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterSno);
if (!textBoxGrade.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterG);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewSC.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
public void amm()//avg,max,min
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
string sql = "SELECT AVG(Grade)'平均分',MAX(Grade)'最高分',MIN(Grade)'最低分' FROM SC WHERE Cno= @cno;";
SqlCommand com = new SqlCommand(sql, con);
SqlParameter sqlParameterCno = new SqlParameter("@cno", textBoxSCno.Text.Trim());
com.Parameters.Add(sqlParameterCno);
con.Open();
com.ExecuteNonQuery();
SqlDataReader sqlDataRead = com.ExecuteReader();
BindingSource bindingSour = new BindingSource();
bindingSour.DataSource = sqlDataRead;
dataGridView1.DataSource = bindingSour;
}
private void buttonUnew_Click(object sender, EventArgs e)//用户返回
{
dataGridViewUser.DataSource = this.sCHOOLDataSet4.SysUser;
}
private void buttonU_Click(object sender, EventArgs e)//用户删
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string select_s = dataGridViewUser.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是ID
string delete_by_id = "delete from SysUser where ID='" + select_s+"'";//sql删除语句
SqlCommand cmd = new SqlCommand(delete_by_id, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
con.Close();
}
this.sysUserTableAdapter.Fill(this.sCHOOLDataSet4.SysUser);
}
private void button1_Click_1(object sender, EventArgs e)//用户查
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
int c = 0;
string sql = "select * from SysUser where ID=@id";
SqlParameter sqlParameter = new SqlParameter("@id", textBoxID.Text.Trim());
SqlCommand command = new SqlCommand(sql, con);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewUser.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
private void button4_Click(object sender, EventArgs e)//登录删除
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string insertStr = "delete from SysLog";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.sysLogTableAdapter.Fill(this.sCHOOLDataSet4.SysLog);
}
private void button2_Click(object sender, EventArgs e)//登录返回
{
dataGridViewLog.DataSource = this.sCHOOLDataSet4.SysLog;
}
private void button3_Click(object sender, EventArgs e)//登录查询
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql = "select * from SysLog where UserID=@s";
SqlParameter sqlParameterSno = new SqlParameter("@s", textBoxLog.Text.Trim());
SqlCommand command = new SqlCommand(sql, con);
command.Parameters.Add(sqlParameterSno);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewLog.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace School
{
public partial class MainS : Form
{
public MainS()
{
InitializeComponent();
}
public static string EncryptWithMD5(string source)
{
byte[] sor = Encoding.UTF8.GetBytes(source);
MD5 md5 = MD5.Create();
byte[] result = md5.ComputeHash(sor);
StringBuilder strbul = new StringBuilder(40);
for (int i = 0; i < result.Length; i++)
{
strbul.Append(result[i].ToString("x2"));//加密结果"x2"结果为32位,"x3"结果为48位,"x4"结果为64位
}
return strbul.ToString();
}
public Byte[] mybyte = new byte[0];
private void button3_Click(object sender, EventArgs e)//浏览图片
{
//打开浏览图片对话框
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.ShowDialog();
string picturePath = openFileDialog.FileName;//获取图片路径
//文件的名称,每次必须更换图片的名称,这里很为不便
//创建FileStream对象
FileStream fs = new FileStream(picturePath, FileMode.Open, FileAccess.Read);
//声明Byte数组
mybyte = new byte[fs.Length];
//读取数据
fs.Read(mybyte, 0, mybyte.Length);
pictureBox1.Image = Image.FromStream(fs);
fs.Close();
}
private void buttonOK_Click(object sender, EventArgs e)//改学生信息
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
if (!textBoxSname.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET Sname = @sname WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@sname", textBoxSname.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
if (!comboBoxSsex.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET Ssex = @ssex WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@ssex", comboBoxSsex.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
if (!textBoxSdept.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET Sdept = @s WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@s", textBoxSdept.Text.Trim());
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
if (!textBoxPassword.Text.Trim().Equals(""))
{
string sql = "UPDATE Student SET UserPassWord = @s WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@s", EncryptWithMD5(textBoxPassword.Text.Trim()));
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
if (!pictureBox1.Image.Equals(null))
{
string sql = "UPDATE Student SET Photo = @userphoto WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@userphoto", SqlDbType.VarBinary, mybyte.Length, ParameterDirection.Input, false, 0, 0, null, DataRowVersion.Current, mybyte);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
try
{
string connString = "Data Source=.;Initial Catalog=School;Persist Security Info=True;User ID=sa;Password=***";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
//打开数据库连接
connection.Open();
//创建SQL语句
sql = "select Photo from Student where Sno = @sno ";
//创建SqlCommand对象
command = new SqlCommand(sql, connection);
sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
//创建DataAdapter对象
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
//创建DataSet对象
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Student");
int c = dataSet.Tables["Student"].Rows.Count;
if (c > 0)
{
Byte[] mybyte = new byte[0];
mybyte = (Byte[])(dataSet.Tables["Student"].Rows[c - 1]["Photo"]);
MemoryStream ms = new MemoryStream(mybyte);
pictureBox1.Image = Image.FromStream(ms);
}
else
pictureBox1.Image = null;
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
//查看信息
string sql1 = "select * from Student where Sno=@sno";
SqlParameter sqlParameterSno = new SqlParameter("@sno", FormLogin.getuid());
SqlCommand command1 = new SqlCommand(sql1, con);
command1.Parameters.Add(sqlParameterSno);
con.Open();
command1.ExecuteNonQuery();
SqlDataReader sqlDataReader = command1.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewS.DataSource = bindingSource;
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
}
private void MainS_Load(object sender, EventArgs e)
{
// TODO: 这行代码将数据加载到表“sCHOOLDataSet4.Course”中。您可以根据需要移动或删除它。
this.courseTableAdapter.Fill(this.sCHOOLDataSet4.Course);
}
private void button1_Click(object sender, EventArgs e)//查看信息
{
labelStuSno.Text = FormLogin.getuid();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql = "select * from Student where Sno=@sno";
SqlParameter sqlParameterSno = new SqlParameter("@sno", FormLogin.getuid());
SqlCommand command = new SqlCommand(sql, con);
command.Parameters.Add(sqlParameterSno);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewS.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
try
{
string connString = "Data Source=.;Initial Catalog=School;Persist Security Info=True;User ID=sa;Password=***";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
//打开数据库连接
connection.Open();
//创建SQL语句
string sql = "select Photo from Student where Sno = '" + FormLogin.getuid() + "'";
//创建SqlCommand对象
SqlCommand command = new SqlCommand(sql, connection);
//创建DataAdapter对象
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
//创建DataSet对象
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "Student");
int c = dataSet.Tables["Student"].Rows.Count;
if (c > 0)
{
Byte[] mybyte = new byte[0];
mybyte = (Byte[])(dataSet.Tables["Student"].Rows[c - 1]["Photo"]);
MemoryStream ms = new MemoryStream(mybyte);
pictureBox1.Image = Image.FromStream(ms);
}
else
pictureBox1.Image = null;
connection.Close();
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
}
}
private void dateTimePickerSbrith_ValueChanged(object sender, EventArgs e)//改生日
{
//MessageBox.Show("生日");
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
string sql = "UPDATE Student SET Sbrith = @s WHERE Sno = @sno ";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@s", dateTimePickerSbrith.Value);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
private void buttonCourse_Click(object sender, EventArgs e)//查课程
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql = "select Course.Cno,Course.Cname,SC.Grade from Course,SC where SC.Sno=@s and SC.Cno=Course.Cno";
SqlParameter sqlParameter = new SqlParameter("@s", FormLogin.getuid());
SqlCommand command = new SqlCommand(sql, con);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewG.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
private void buttonGrade_Click(object sender, EventArgs e)//查成绩
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
string sql = "select Course.Cno,Course.Cname,SC.Grade from Course,SC " +
"where SC.Sno=@s and SC.Cno=Course.Cno and SC.Grade is not null";
if(radioButton1.Checked)
{
sql += " ORDER BY SC.Grade";
}
if (radioButton2.Checked)
{
sql += " ORDER BY SC.Grade DESC";
}
SqlParameter sqlParameter = new SqlParameter("@s", FormLogin.getuid());
SqlCommand command = new SqlCommand(sql, con);
command.Parameters.Add(sqlParameter);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridViewG.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
private void button2_Click(object sender, EventArgs e)//查课程
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
int c = 0;
string sql = "select * from Course where ";
SqlParameter sqlParameterCno = new SqlParameter("@cno", textBox1.Text.Trim());
SqlParameter sqlParameterCname = new SqlParameter("@cname", textBox2.Text.Trim());
if (!textBox1.Text.Trim().Equals(""))
{
sql += "Cno=@cno";
c++;
}
if (!textBox2.Text.Trim().Equals(""))
{
if (c != 0) sql += " and Cname=@cname";
else sql += "Cname=@cname";
}
SqlCommand command = new SqlCommand(sql, con);
if (!textBox1.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterCno);
if (!textBox2.Text.Trim().Equals("")) command.Parameters.Add(sqlParameterCname);
con.Open();
command.ExecuteNonQuery();
SqlDataReader sqlDataReader = command.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
con.Close();
}
}
private void button4_Click(object sender, EventArgs e)//课程返回
{
dataGridView1.DataSource = this.sCHOOLDataSet4.Course;
}
private void button5_Click(object sender, EventArgs e)//课程增
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string select_c = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string sql = "insert into SC (Sno,Cno) " + "values (@sno, @cno)";
SqlCommand command = new SqlCommand(sql, con);
SqlParameter sqlParameter = new SqlParameter("@sno", FormLogin.getuid());
command.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@cno", select_c);
command.Parameters.Add(sqlParameter);
command.ExecuteNonQuery();
con.Close();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
con.Close();
}
this.courseTableAdapter.Fill(this.sCHOOLDataSet4.Course);
}
private void button6_Click(object sender, EventArgs e)//课程删
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=School;User ID=sa;Password=***");
try
{
con.Open();
string select_c = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
string delete_by_id = "delete from SC where Cno=" + select_c + " and Sno=" + FormLogin.getuid()+" and Grade is null";//sql删除语句
SqlCommand cmd = new SqlCommand(delete_by_id, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
con.Close();
}
this.courseTableAdapter.Fill(this.sCHOOLDataSet4.Course);
}
演示:
20200531