软件:SQL Server;Visual Studio
语言:C#,SQL
两个身份,管理员和学生。
管理员功能:管理学生专业信息、课程信息、选课信息(增删改查),查看已注册过的同学信息(密码不可见,是乱码)以及照片。
学生功能:注册自己的信息,查看自己的信息包括专业信息、注册时的信息、选课及成绩,修改自己的密码。
在SQL Server创建数据库,在新数据库中新建需要用的表并添加数据。
create database curricula_variable_system;//创建数据库
USE curricula_variable_system;
//建表,记录注册信息的
CREATE TABLE SysUser
(
UserID NCHAR(20) ,
UserPassWord NCHAR(32) , /*密码32位加密*/
UserSchoolID NCHAR(20) PRIMARY KEY,
UserMobile NCHAR(11),
UserBirthday datetime,
UserIdentity NCHAR(20),
UserPhoto image
);
//建表,记录登录信息的
CREATE TABLE SysLog
(
UserID NCHAR(20) ,
DateAndTime datetime,
UserOperation NCHAR(200)
);
//建管理员表,存管理员的账号密码
CREATE TABLE Teacher
(
UserID NCHAR(20) ,
UserPassWord NCHAR(32) , /*密码32位加密*/
);
//建学生表
CREATE TABLE Student
(
Sno CHAR(9) PRIMARY KEY, /* 列级完整性约束条件,Sno是主码*/
Sname CHAR(20) UNIQUE, /* Sname取唯一值*/
Ssex CHAR(2),
Sage SMALLINT,
Sdept CHAR(20)
);
//课程表
CREATE TABLE Course
(
Cno CHAR(4) PRIMARY KEY,
Cname CHAR(40),
Cpno CHAR(4),
Ccredit SMALLINT,
FOREIGN KEY (Cpno) REFERENCES Course(Cno)
);
//选课表
CREATE TABLE SC
(
Sno CHAR(9),
Cno CHAR(4),
Grade SMALLINT,
PRIMARY KEY (Sno,Cno), /* 主码由两个属性构成,必须作为表级完整性进行定义*/
FOREIGN KEY (Sno) REFERENCES Student(Sno), /* 表级完整性约束条件,Sno是外码,被参照表是Student */
FOREIGN KEY (Cno)REFERENCES Course(Cno) /* 表级完整性约束条件, Cno是外码,被参照表是Course*/
);
//插入数据
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215121','李勇','男','CS',20);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215122','刘晨','女','CS',19);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215123','王敏','女','MA',18);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215125','张立','男','IS',19);
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage) VALUES ('201215128','陈冬','男','IS',20);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('1','数据库',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('2','数学',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('3','信息系统',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('4','操作系统',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('5','数据结构',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('6','数据处理',NULL,4);
INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES ('7','Pascal语言',NULL,4);
UPDATE Course SET Cpno = '5' WHERE Cno = '1'
UPDATE Course SET Cpno = '1' WHERE Cno = '3'
UPDATE Course SET Cpno = '6' WHERE Cno = '4'
UPDATE Course SET Cpno = '7' WHERE Cno = '5'
UPDATE Course SET Cpno = '6' WHERE Cno = '7'
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);
//新建触发器
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)
刚开始的登录页面
点击按钮显示新的窗体,这是其中一个按钮的代码。
Form2 form2 = new Form2();//新建窗体
form2.Show();//显示新建窗体
this.Hide();//隐藏当前窗体
确定登录
string username = textBoxtea.Text.Trim(); //取出账号
string password = EncryptWithMD5(textBoxcher.Text.Trim()); //取出密码并加密
string myConnString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//连接数据库
SqlConnection sqlConnection = new SqlConnection(myConnString); //实例化连接对象
sqlConnection.Open();
string sql = "select UserID,UserPassWord from Teacher where UserID = '" + username + "' and UserPassWord = '" + password + "'";
//教工号:201210,密码:123
//编写SQL命令
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
if (sqlDataReader.HasRows && textBoxyan.Text == code)
{
MessageBox.Show("欢迎使用!"); //登录成功
Form6 form6 = new Form6();
form6.Show();
this.Hide();
}
else
{
MessageBox.Show("登录失败!");
return;
}
sqlDataReader.Close();
sqlConnection.Close();
密码加密
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();
}
验证码
点击窗体|在事件里找Load|双击,然后输入以下代码
public string code;
//随机实例化
Random ran = new Random();
int number;
char code1;
//取五个数
for (int i = 0; i < 5; i++)
{
number = ran.Next();
if (number % 2 == 0)
code1 = (char)('0' + (char)(number % 10));
else
code1 = (char)('A' + (char)(number % 26)); //转化为字符
this.code += code1.ToString();
}
label5.Text = code;
查看照片,根据学号查看
try
{
string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
//打开数据库连接
connection.Open();
//创建SQL语句
string sql = "select UserPhoto from SysUser where UserSchoolID = '" + textBox1.Text + "'";
//创建SqlCommand对象
SqlCommand command = new SqlCommand(sql, connection);
//创建DataAdapter对象
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
//创建DataSet对象
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "SysUser");
int c = dataSet.Tables["SysUser"].Rows.Count;
if (c > 0)
{
Byte[] mybyte = new byte[0];
mybyte = (Byte[])(dataSet.Tables["SysUser"].Rows[c - 1]["UserPhoto"]);
MemoryStream ms = new MemoryStream(mybyte);
pictureBox2.Image = Image.FromStream(ms);
}
else
{
pictureBox2.Image = null;
MessageBox.Show("无照片");
}
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
返回上一界面
Form6 form6 = new Form6();//上一界面的窗体
form6.Show();//显示
this.Hide();//隐藏当前窗体
我对性别的填写进行了限定只能是”男“或“女”。学号也限定是201215开头再加三位数字。
SQL语句如下
alter table Student add constraint c1 check(Sno between 201215000 and 201215999)
alter table Student add constraint c2 check(Ssex IN('男','女'))
删除
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
con.Open();
string select_id = dataGridView1.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();
}
this.studentTableAdapter.Fill(this.curricula_variable_systemDataSet3.Student);//Form10_Load里的那条代码
修改,根据学号修改姓名
String StuID = textBox1.Text.Trim();
String StuName = textBox2.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
con.Open();
string insertStr = "UPDATE Student SET Sname = '" + StuName + "' WHERE Sno = '" + StuID + "'";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.studentTableAdapter.Fill(this.curricula_variable_systemDataSet3.Student);//Form10_Load里的那条代码
查询,根据学号
String StuID = textBox1.Text.Trim();
String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
SqlConnection sqlConnection = new SqlConnection(conn); //实例化连接对象
try
{
sqlConnection.Open();
String select_by_id = "select * from Student where Sno='" + StuID + "'";
SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection.Close();
}
清空文本行
textBox1.Text = null;
textBox2.Text = null;
textBox3.Text = null;
textBox4.Text = null;
textBox5.Text = null;
添加课程
string Coucno = textBox1.Text.Trim();
string Couname = textBox2.Text.Trim();
string Coucredit = textBox3.Text.Trim();
string Coupno = textBox4.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
con.Open();//打开
string insertStr = "INSERT INTO Course (Cno,Cname,Cpno,Ccredit) " +
"VALUES ('" + Coucno + "','" + Couname + "','" + Coupno + "','" + Coucredit + "')";
SqlCommand cmd = new SqlCommand(insertStr, con);//使用
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();//释放
}
this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);
删除
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
con.Open();
string select_id = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();//选择的当前行第一列的值,也就是Cno那列
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();
}
this.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);
修改,根据课程号修改课程名
string Cno = textBox1.Text.Trim();
string Cname = textBox2.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
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.courseTableAdapter.Fill(this.curricula_variable_systemDataSet4.Course);
查询,根据课程号
string Cno = textBox1.Text.Trim();
String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
SqlConnection sqlConnection = new SqlConnection(conn); //实例化连接对象
try
{
sqlConnection.Open();
String select_by_id = "select * from Course where Cno='" + Cno + "'";
SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection.Close();
}
查询
string StuID = textBox1.Text.Trim();
String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
SqlConnection sqlConnection = new SqlConnection(conn); //实例化连接对象
try
{
sqlConnection.Open();
String select_by_id = "select * from SC where Sno='" + StuID + "'";
SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection.Close();
}
修改,根据学号、课程号修改成绩
string StuID = textBox1.Text.Trim();
string Cno = textBox2.Text.Trim();
string Grade = textBox3.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
con.Open();
string insertStr = "UPDATE SC SET Grade = '" + Grade + "' WHERE Cno = '" + Cno + "'AND Sno='"+ StuID+"'";
SqlCommand cmd = new SqlCommand(insertStr, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("输入数据违反要求!");
}
finally
{
con.Dispose();
}
this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);
添加
string StuID = textBox1.Text.Trim();
string Cno = textBox2.Text.Trim();
string Grade = textBox3.Text.Trim();
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
string stu = "select Sno from Student where Sno='" + StuID + "'";
if (stu != "")
{
con.Open();//打开
string insertStr = "INSERT INTO SC (Sno,Cno,Grade) " +
"VALUES ('" + StuID + "','" + Cno + "','" + Grade + "')";
SqlCommand cmd = new SqlCommand(insertStr, con);//使用
cmd.ExecuteNonQuery();
}
else
{
MessageBox.Show("没有该学生!请重新输入");
}
}
catch
{
MessageBox.Show("输入数据违反要求!");//新加的学号在已有学号中,课程号在已有的课程中,成绩在0到100之间
}
finally
{
con.Dispose();//释放
}
this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);
删除,输入学号、课程号删除对应行。因为选课表是学号课程号一起作为主码的所以和前边的删除方式不同。
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql");//定义
try
{
con.Open();
string StuID = textBox1.Text.Trim();
string Cno = textBox2.Text.Trim();
string delete_by_stc= "delete from SC where Sno='"+ textBox1.Text + "' and Cno='" + textBox2.Text + "'";
SqlCommand cmd = new SqlCommand(delete_by_stc, con);
cmd.ExecuteNonQuery();
}
catch
{
MessageBox.Show("请正确选择行!");
}
finally
{
con.Dispose();
}
this.sCTableAdapter2.Fill(this.curricula_variable_systemDataSet7.SC);
查看专业信息(查看专业信息代码类似,就是SQL语句那改成SC表)
String conn = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
SqlConnection sqlConnection = new SqlConnection(conn); //实例化连接对象
String StuID = textBox1.Text.Trim();
if(textBox1.Text=="")
{
MessageBox.Show("请先输入学号!");
return;
}
else
{
try
{
sqlConnection.Open();
String select_by_id = "select * from Student where Sno='" + StuID + "'";
SqlCommand sqlCommand = new SqlCommand(select_by_id, sqlConnection);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection.Close();
}
}
查看个人信息
String StuID = textBox1.Text.Trim();
String conn1 = "Data Source=.;Initial Catalog=curricula_variable_system;User ID=sa;Password=sql";
SqlConnection sqlConnection1 = new SqlConnection(conn1); //实例化连接对象
if (textBox1.Text == "")
{
MessageBox.Show("请先输入学号!");
return;
}
else
{
try
{
sqlConnection1.Open();
String select_by_id1 = "select UserID,UserSchoolID,UserMobile,UserBirthday,UserIdentity from SysUser where UserSchoolID='" + StuID + "'";
SqlCommand sqlCommand = new SqlCommand(select_by_id1, sqlConnection1);
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
BindingSource bindingSource = new BindingSource();
bindingSource.DataSource = sqlDataReader;
dataGridView1.DataSource = bindingSource;
}
catch
{
MessageBox.Show("查询语句有误,请认真检查SQL语句!");
}
finally
{
sqlConnection1.Close();
}
}
查看照片
if (textBox1.Text == "")
{
MessageBox.Show("请先输入学号!");
return;
}
else
{
try
{
string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";//数据库连接字符串
SqlConnection connection = new SqlConnection(connString);//创建connection对象
//打开数据库连接
connection.Open();
//创建SQL语句
string sql = "select UserPhoto from SysUser where UserSchoolID = '" + textBox1.Text + "'";
//创建SqlCommand对象
SqlCommand command = new SqlCommand(sql, connection);
//创建DataAdapter对象
SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
//创建DataSet对象
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet, "SysUser");
int c = dataSet.Tables["SysUser"].Rows.Count;
if (c > 0)
{
Byte[] mybyte = new byte[0];
mybyte = (Byte[])(dataSet.Tables["SysUser"].Rows[c - 1]["UserPhoto"]);
MemoryStream ms = new MemoryStream(mybyte);
pictureBox2.Image = Image.FromStream(ms);
}
else
pictureBox2.Image = null;
connection.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
修改密码
确定修改,使用正则表达式约束新密码的格式
if (textBox3.Text == "")
{
MessageBox.Show("学号不能为空!");
}
if(textBox1.Text=="")
{
MessageBox.Show("新密码不能为空!");
}
if (textBox2.Text == "")
{
MessageBox.Show("确认密码不能为空!");
}
if(textBox1.Text.Trim()!="")//新密码不为空时,输入满足正则表达式
{
//使用regex(正则表达式)进行格式设置 至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符。
Regex regex = new Regex(@"(?=.*[0-9])(?=.*[a-z])(?=.*[A-Z]).{3,20}");
if (regex.IsMatch(textBox1.Text))//判断格式是否符合要求
{
//MessageBox.Show("输入密码格式正确!");
}
else
{
MessageBox.Show("至少有数字、大写字母、小写字母各一个。最少3个字符、最长20个字符!");
return;
}
}
if (textBox1.Text == textBox2.Text)
{
string sql = "update SysUser set UserPassWord='"+ EncryptWithMD5(textBox1.Text)+"' where UserSchoolID='"+ textBox3.Text.Trim()+"'";
string connString = "Data Source=.;Initial Catalog=curricula_variable_system;Persist Security Info=True;User ID=sa;Password=sql";
SqlConnection con = new SqlConnection(connString);//创建connection对象
con.Open();
SqlCommand command = new SqlCommand(sql, con);
command.ExecuteNonQuery();
MessageBox.Show("新密码已经修改完成");
con.Close();
}
else
{
MessageBox.Show("请输入两次相同的密码");
}
对密码加密
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();
}
主要代码都在上面了,参考的时候结合自己的稍加改动就可以,背景图片是pictureBox组件然后选自己喜欢的照片就可以了,注意一下大小模式这里,选这个图片才显示完整。
所有代码我压缩放在github上了,需要的可以下载然后在Visual studio打开看一下点这里
视频讲解:点这里