第一步,登录窗体
点击右下角按钮,显示作者信息窗体
用户名为教师工号,在这简化些,就是一8位数,20181101
输入的用户不存在时
密码错误时
数据库中Teacher表就一行数据
登陆成功后,显示个人信息
根据登录时输入的账号,查到到相应的整行数据,转换成Teacher对象后,传给主窗体,并将信息显示出来。右下角那个按钮没实际作用,只是感觉右下角空白部分太多,就放了个按钮。
进入学生管理页面
学生表中就这三行数据
增加学生
删除直接选中行,删除就行
如果没有选中,会提示选中行
修改学生
同样,如果不选中要修改的行,会提示选中
选中后,根据选中行学生的信息,将对象传递给修改窗体,修改窗体加载时,将信息一并加载
查询学生
根据学号或姓名,进行模糊查询
查询带 姓张的
查询不到时
课程管理
右侧表显示该老师所开的课,左侧表显示选修该老师课的学生。
增加课程直接在右下角文本框输入课程号,课程名,会根据该老师的ID,所属学院,创建一个课程对象,并将数据插入到表中。删除课程,直接选中行,删除即可。
左侧成绩表,采用分页查询,一页默认显示16行数据,显然表中的数据不够16行。学生的成绩,可以在此表中直接修改,数据库中表里的成绩也会相应修改,修改出错时才会提示。底部两个按钮,表示上一页和下一页,中间文本表示当前为第几页,一共有多少页,多少条数据。
在这我讲数据结构与算法成绩改成了98分。
修改个人信息
加载该页面时,将个人信息也加载出来。
修改图片,目前只允许上传的.png、.jpg格式的图片
做这个是为了防止 用户上传一段视频,文本文件等其他文件。在这我选里面那张student.png
点击确定
回到个人信息界面,显示的是刚刚上传的图像
那张是学生的默认图像,所以还换回原来的图像
点击修改密码
同时支持键盘输入
在这截屏工具和点击这个键盘有些冲突,点开键盘后,去点截屏,键盘就自动消失了,所以在此用手机拍照。
设置
这个不知道做些什么,复杂的功能做不出来,就直接在此放了张图
同时支持,左侧菜单栏隐藏
挑了部分代码
实现分页的存储过程
create proc [dbo].[usp_getStudentScoreByPage]
@pageSize int =16, --每页记录条数
@pageIndex int=1, --当前要查看第几页的记录
@TID varchar(11) ,--教师ID
@recordCount int output, --总的记录条数
@pageCount int output-- 总的页数
as
begin
select StudentID,StudentName,CName,Score,CID from
(select s.StudentID,s.StudentName,c.CName,sg.Score ,c.CID,rn=ROW_NUMBER() over(order by AutoID asc) from Student_Grade as sg
inner join Student as s on s.StudentID=sg.SID
inner join Course as c on c.CID=sg.CID
where sg.CID in (select Course.CID from Course where Teacher= @TID)) as sc
where sc.rn between (@pageIndex-1)*@pageSize and @pageSize*@pageIndex
--计算总的记录条数
set @recordCount=(select count(*) from Student_Grade)
--计算总页数
set @pageCount=CEILING(@recordCount*1.0/@pageSize)
end
对教师表的数据操作层
using EAM.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Drawing;
namespace EAM.DAL
{
public class TeacherDAL
{
/// <summary>
/// 根据输入的用户名查询 用户
/// </summary>
/// <param name="ID">用户名</param>
/// <returns>用户对象</returns>
public Teacher GetTeacherInfo(string ID)
{
string sql = "select * from Teacher where TID=@Id";
SqlParameter pms = new SqlParameter("@Id", SqlDbType.VarChar, 11) { Value = ID };
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text, pms);
Teacher teacher = null;
if (dt.Rows.Count>0)
{
teacher = RowToTeacherInfo(dt.Rows[0]);
}
return teacher;
}
//将查到的一行数据 转换为一个对象
private Teacher RowToTeacherInfo(DataRow dr)
{
Teacher tea = new Teacher();
tea.TID = dr["TID"].ToString();
tea.PassWord = dr["PassWord"].ToString();
tea.TName = dr["TName"].ToString();
tea.TGender = (Convert.ToBoolean(dr["TGender"])) ? "男" : "女";
tea.Birthday = Convert.ToDateTime(dr["Birthday"]);
tea.AdmissionTime = Convert.ToDateTime(dr["AdmissionTime"]);
tea.Departments = dr["Departments"].ToString();
tea.TSalary = Convert.ToDecimal(dr["TSalary"]);
tea.Address = dr["Address"].ToString();
if (!dr.IsNull("Image"))
{
byte[] imageByte = (byte[])dr["Image"];
MemoryStream stream = new MemoryStream(imageByte);
tea.Image = new Bitmap(stream);
}
return tea;
}
/// <summary>
/// 修改教师信息
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public int EditTeacher(Teacher t)
{
string sql = "update Teacher set TID=@ID, TName=@name,TGender=@gender,Birthday=@bir,AdmissionTime=@adm,Departments=@depart,TSalary=@salary,Image=@image,Address=@add where TID="+t.TID;
byte[] buffer;
using(MemoryStream ms=new MemoryStream())
{
t.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
buffer = ms.GetBuffer();
}
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@ID",t.TID),
new SqlParameter("@name",t.TName),
new SqlParameter("@gender", SqlDbType.Bit){Value=t.TGender=="男"?true:false},
new SqlParameter("@bir", SqlDbType.DateTime){Value=t.Birthday},
new SqlParameter("@adm", SqlDbType.Date){Value=t.AdmissionTime},
new SqlParameter("@depart", t.Departments),
new SqlParameter("@salary",t.TSalary),
new SqlParameter("@add",t.Address),
new SqlParameter("@image", SqlDbType.Image){Value=buffer}
};
try
{
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// 修改密码
/// </summary>
/// <param name="pwd">新密码</param>
/// <param name="id">教师ID</param>
/// <returns></returns>
public int EditPwd(string pwd,string id)
{
string sql = "update Teacher set PassWord=@pwd where TID=" + id;
SqlParameter pms = new SqlParameter("@pwd", pwd);
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
}
}
对学生表的数据操作层
这里只是教师窗体中需要的对学生操作的代码,学生窗体还没做
using EAM.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EAM.DAL
{
public class StudentDAL
{
/// <summary>
/// 获得所有学生信息
/// </summary>
/// <returns></returns>
public List<Student> GetAllStudents()
{
//string sql = "select StudentID,StudentName,StudentGender,Birthday,AdmissionTime,Departments,Class from Student";
string sql = "select StudentID,StudentName,StudentGender,Birthday,AdmissionTime,college.COName,class.CLName from Student as student inner join Class as class on student.Class=class.CLID inner join College as college on student.Departments=college.COID";
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
List<Student> list = null ;
if (dt.Rows.Count>0)
{
list = new List<Student>();
foreach (DataRow item in dt.Rows)
{
list.Add(RowToStudentInfo(item));
}
}
return list;
}
/// <summary>
/// 将行对象转换成学生对象
/// </summary>
/// <param name="dr"></param>
/// <returns></returns>
private Student RowToStudentInfo(DataRow dr)
{
Student student = new Student();
student.SID = dr["StudentID"].ToString();
student.SName = dr["StudentName"].ToString();
student.SGender = (Convert.ToBoolean(dr["StudentGender"])) ? "男" : "女";
student.Birthday = Convert.ToDateTime(dr["Birthday"]);
student.AdmissionTime = Convert.ToDateTime(dr["AdmissionTime"]);
//student.Departments = dr.IsNull("COName")?dr["Departments"].ToString(): dr["COName"].ToString();
// student.Class = dr.IsNull("CLName")?dr["Class"].ToString():dr["CLName"].ToString();
try
{
student.Departments = dr["COName"].ToString();
student.Class = dr["CLName"].ToString();
}
catch (Exception)
{
student.Departments = dr["Departments"].ToString();
student.Class = dr["Class"].ToString();
}
return student;
}
/// <summary>
/// 添加学生
/// </summary>
/// <param name="student">学生对象</param>
/// <returns>所影响的行数</returns>
public int AddStudentInfo(Student student)
{
string sql = "insert into Student (StudentID,PassWord,StudentName,StudentGender,Birthday,AdmissionTime,Departments,Class,Image) values (@SID,@PWD,@SName,@SGender,@Birthday,@AdmissionTime,@Departments,@Class,@Image)";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@SID", SqlDbType.VarChar,11){ Value=student.SID},
new SqlParameter("@PWD", SqlDbType.VarChar,50){ Value=student.PassWord},
new SqlParameter("@SName", SqlDbType.NVarChar,10){ Value=student.SName},
new SqlParameter("@SGender", SqlDbType.Bit){Value=student.SGender=="男"?true:false},
new SqlParameter("@Birthday", SqlDbType.DateTime){Value=student.Birthday},
new SqlParameter("@AdmissionTime", SqlDbType.DateTime){Value=student.AdmissionTime},
new SqlParameter("@Departments", SqlDbType.VarChar,11){Value=student.Departments},
new SqlParameter("@Class", SqlDbType.VarChar,11){ Value=student.Class},
new SqlParameter("@Image", SqlDbType.Image){Value=DBNull.Value}
};
try
{
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// 修改学生信息
/// </summary>
/// <param name="student">学生对象</param>
/// <returns>所影响的行数</returns>
public int UpdateStudentInfo(Student student)
{
string sql = "Update Student set StudentName=@SName,StudentGender=@SGender,Birthday=@Bir,AdmissionTime=@Admi,Departments=@Depart,Class=@Cla,Image=@Image Where StudentID=@SID";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@SID", SqlDbType.VarChar,11){ Value=student.SID},
//new SqlParameter("@PWD", SqlDbType.VarChar,50){ Value=student.PassWord},
new SqlParameter("@SName", SqlDbType.NVarChar,10){ Value=student.SName},
new SqlParameter("@SGender", SqlDbType.Bit){Value=student.SGender=="男"?true:false},
new SqlParameter("@Bir", SqlDbType.DateTime){Value=student.Birthday},
new SqlParameter("@Admi", SqlDbType.DateTime){Value=student.AdmissionTime},
new SqlParameter("@Depart", SqlDbType.VarChar,11){Value=student.Departments},
new SqlParameter("@Cla", SqlDbType.VarChar,11){ Value=student.Class},
new SqlParameter("@Image", SqlDbType.Image){Value=DBNull.Value}
};
//List<SqlParameter> list = new List<SqlParameter>();
//list.AddRange(pms);
if (student.Image!=null)
{
pms[7].Value = student.Image;
}
try
{
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
catch (Exception)
{
return 0;
}
}
/// <summary>
/// 根据学号,删除相应的学生
/// </summary>
/// <param name="sid">学号</param>
/// <returns>所影响的行数</returns>
public int DeleteStudentInfo(string sid)
{
string sql = "delete from Student Where StudentID=" + sid;
return SqlHelper.ExecuteNonquery(sql, CommandType.Text);
}
/// <summary>
/// 根据学号查询学生信息
/// </summary>
/// <param name="sid">学号</param>
/// <returns>学生对象</returns>
public Student GetStudentBySid(string sid)
{
//string sql = "select StudentID,StudentName,StudentGender,Birthday,AdmissionTime,college.COName,class.CLName from Student as student inner join Class as class on student.Class=class.CLID inner join College as college on student.Departments=college.COID Where student.StudentID="+ sid;
string sql = "select StudentID,StudentName,StudentGender,Birthday,AdmissionTime,Departments,Class from Student where StudentID = " + sid;
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
Student stu = new Student();
if (dt.Rows.Count>0)
{
stu = RowToStudentInfo(dt.Rows[0]);
}
return stu;
}
/// <summary>
/// 根据查询条件,查找相应的学生
/// </summary>
/// <param name="info">条件</param>
/// <returns>查到的学生</returns>
public List<Student> GetStudentsByInfo(string info)
{
List<Student> list = new List<Student>();
string sql = " select StudentID, StudentName, StudentGender, Birthday, AdmissionTime, college.COName,class.CLName from Student as student inner join Class as class on student.Class=class.CLID inner join College as college on student.Departments=college.COID Where student.StudentID like @SID or student.StudentName like @SName";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@SID","%"+info+"%"),
new SqlParameter("@SName","%"+info+"%")
};
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text, pms);
if (dt.Rows.Count>0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToStudentInfo(dr));
}
}
return list;
}
}
}
对课程表的数据操作层
using EAM.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
namespace EAM.DAL
{
public class CourseDAL
{
/// <summary>
/// 根据教师ID,查询教师开的课程
/// </summary>
/// <param name="TID">教师ID</param>
/// <returns></returns>
public List<Course> GetCourses(string TID)
{
string sql = "select * from Course where Teacher=" + TID;
List<Course> list = new List<Course>();
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
if (dt.Rows.Count>0)
{
foreach (DataRow dr in dt.Rows)
{
list.Add(RowToCourse(dr));
}
}
return list;
}
private Course RowToCourse(DataRow dr)
{
Course course = new Course();
course.CID = dr["CID"].ToString();
course.CName = dr["CName"].ToString();
course.Departments = dr["Departments"].ToString();
course.CTeacher = dr["Teacher"].ToString();
return course;
}
/// <summary>
/// 根据课程号,删除课程
/// </summary>
/// <param name="CID">课程号</param>
/// <returns></returns>
public int DeleteCourse(string CID)
{
string sql = "delete from Course Where CID=" + CID;
return SqlHelper.ExecuteNonquery(sql, CommandType.Text);
}
/// <summary>
/// 添加课程
/// </summary>
/// <param name="course">课程对象</param>
/// <returns></returns>
public int AddCourse(Course course)
{
string sql = "insert into Course values(@CID,@CName,@Dep,@Teac)";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@CID", course.CID),
new SqlParameter("@CName",course.CName),
new SqlParameter("@Dep",course.Departments),
new SqlParameter("@Teac",course.CTeacher)
};
try
{
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
catch (Exception)
{
return 0;
}
}
}
}
目前还需要对安全性做一些巩固,虽然在数据库的表中,对属性都增加了约束条件,同时预防了用户sql注入攻击,但这还远远不够,现在主要要对用户的输入做一些限制。