在评论区里说代码垃圾之前，我先说了：这代码写的挺垃圾的。在即将完成一半的时候意识到了，代码冗余太多，基本没有遵循DRY原则，还能再降低一些代码的耦合性。高内聚低耦合，刚听别人讲这个概念时没怎么听懂，只是跟着把代码示例写了一下，现在想想，那个例子的代码确实挺好的。
在CSDN上放全部代码不是很方便，所以挑了一些比较集中的代码在这里做了展示，完整的源码放GitHub上了https://github.com/Cheng-xiaozhen/Educational-Administration-Management
首先是数据库方面的代码
采用生成脚本的方式，生成的代码。
USE [master]
GO
/****** Object: Database [EAMDatabase] Script Date: 2020/5/29 16:03:59 ******/
CREATE DATABASE [EAMDatabase]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'EAMDatabase', FILENAME = N'D:\C# for vs\Educational Administration Management\EAMDatabase.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
LOG ON
( NAME = N'EAMDatabase_log', FILENAME = N'D:\C# for vs\Educational Administration Management\EAMDatabase_log.ldf' , SIZE = 73728KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [EAMDatabase] SET COMPATIBILITY_LEVEL = 140
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [EAMDatabase].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [EAMDatabase] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [EAMDatabase] SET ANSI_NULLS OFF
GO
ALTER DATABASE [EAMDatabase] SET ANSI_PADDING OFF
GO
ALTER DATABASE [EAMDatabase] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [EAMDatabase] SET ARITHABORT OFF
GO
ALTER DATABASE [EAMDatabase] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [EAMDatabase] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [EAMDatabase] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [EAMDatabase] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [EAMDatabase] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [EAMDatabase] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [EAMDatabase] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [EAMDatabase] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [EAMDatabase] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [EAMDatabase] SET DISABLE_BROKER
GO
ALTER DATABASE [EAMDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [EAMDatabase] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [EAMDatabase] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [EAMDatabase] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [EAMDatabase] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [EAMDatabase] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [EAMDatabase] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [EAMDatabase] SET RECOVERY FULL
GO
ALTER DATABASE [EAMDatabase] SET MULTI_USER
GO
ALTER DATABASE [EAMDatabase] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [EAMDatabase] SET DB_CHAINING OFF
GO
ALTER DATABASE [EAMDatabase] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [EAMDatabase] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [EAMDatabase] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'EAMDatabase', N'ON'
GO
ALTER DATABASE [EAMDatabase] SET QUERY_STORE = OFF
GO
USE [EAMDatabase]
GO
/****** Object: Table [dbo].[Class] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
[CLID] [varchar](11) NOT NULL,
[CLName] [nvarchar](10) NOT NULL,
CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED
(
[CLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_Class_CLID] UNIQUE NONCLUSTERED
(
[CLID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[College] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[College](
[COID] [varchar](11) NOT NULL,
[COName] [nvarchar](12) NOT NULL,
CONSTRAINT [PK_College] PRIMARY KEY CLUSTERED
(
[COID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_College_COID] UNIQUE NONCLUSTERED
(
[COID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Course] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Course](
[CID] [varchar](11) NOT NULL,
[CName] [nvarchar](10) NOT NULL,
[Departments] [varchar](11) NOT NULL,
[Teacher] [varchar](11) NOT NULL,
CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_Course_CID] UNIQUE NONCLUSTERED
(
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Student] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[StudentID] [varchar](11) NOT NULL,
[PassWord] [varchar](50) NOT NULL,
[StudentName] [nvarchar](10) NOT NULL,
[StudentGender] [bit] NOT NULL,
[Birthday] [datetime] NOT NULL,
[AdmissionTime] [datetime] NOT NULL,
[Departments] [varchar](11) NOT NULL,
[Class] [varchar](11) NOT NULL,
[Image] [image] NULL,
CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_Student_SID] UNIQUE NONCLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object: Table [dbo].[Student_Grade] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student_Grade](
[AutoID] [int] IDENTITY(1,1) NOT NULL,
[SID] [varchar](11) NOT NULL,
[CID] [varchar](11) NOT NULL,
[Score] [float] NULL,
CONSTRAINT [PK_Student_Grade_1] PRIMARY KEY CLUSTERED
(
[AutoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_Student_Grade_SIDCID] UNIQUE NONCLUSTERED
(
[SID] ASC,
[CID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Teacher] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Teacher](
[TID] [varchar](11) NOT NULL,
[PassWord] [varchar](50) NOT NULL,
[TName] [nvarchar](10) NOT NULL,
[TGender] [bit] NOT NULL,
[Birthday] [datetime] NOT NULL,
[AdmissionTime] [datetime] NOT NULL,
[Departments] [varchar](11) NOT NULL,
[TSalary] [money] NOT NULL,
[Image] [image] NULL,
[Address] [nvarchar](20) NOT NULL,
CONSTRAINT [PK_Teacher] PRIMARY KEY CLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UQ_Teacher_TID] UNIQUE NONCLUSTERED
(
[TID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Student] ADD CONSTRAINT [DF_Student_StudentGender] DEFAULT ((1)) FOR [StudentGender]
GO
ALTER TABLE [dbo].[Teacher] ADD CONSTRAINT [DF_Teacher_TGender] DEFAULT ((1)) FOR [TGender]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_College] FOREIGN KEY([Departments])
REFERENCES [dbo].[College] ([COID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_College]
GO
ALTER TABLE [dbo].[Course] WITH CHECK ADD CONSTRAINT [FK_Course_Teacher] FOREIGN KEY([Teacher])
REFERENCES [dbo].[Teacher] ([TID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Course] CHECK CONSTRAINT [FK_Course_Teacher]
GO
ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_Class] FOREIGN KEY([Class])
REFERENCES [dbo].[Class] ([CLID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Class]
GO
ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [FK_Student_College] FOREIGN KEY([Departments])
REFERENCES [dbo].[College] ([COID])
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_College]
GO
ALTER TABLE [dbo].[Student_Grade] WITH CHECK ADD CONSTRAINT [FK_Student_Grade_Course] FOREIGN KEY([CID])
REFERENCES [dbo].[Course] ([CID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Student_Grade] CHECK CONSTRAINT [FK_Student_Grade_Course]
GO
ALTER TABLE [dbo].[Student_Grade] WITH CHECK ADD CONSTRAINT [FK_Student_Grade_Student] FOREIGN KEY([SID])
REFERENCES [dbo].[Student] ([StudentID])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Student_Grade] CHECK CONSTRAINT [FK_Student_Grade_Student]
GO
ALTER TABLE [dbo].[Teacher] WITH CHECK ADD CONSTRAINT [FK_Teacher_College] FOREIGN KEY([Departments])
REFERENCES [dbo].[College] ([COID])
GO
ALTER TABLE [dbo].[Teacher] CHECK CONSTRAINT [FK_Teacher_College]
GO
ALTER TABLE [dbo].[Student] WITH CHECK ADD CONSTRAINT [CK_Student_Gender] CHECK (([StudentGender]=(0) OR [StudentGender]=(1)))
GO
ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [CK_Student_Gender]
GO
ALTER TABLE [dbo].[Teacher] WITH CHECK ADD CONSTRAINT [CK_Teacher_Salary] CHECK (([TSalary]>(0)))
GO
ALTER TABLE [dbo].[Teacher] CHECK CONSTRAINT [CK_Teacher_Salary]
GO
ALTER TABLE [dbo].[Teacher] WITH CHECK ADD CONSTRAINT [CK_Teacher_TGender] CHECK (([TGender]=(0) OR [TGender]=(1)))
GO
ALTER TABLE [dbo].[Teacher] CHECK CONSTRAINT [CK_Teacher_TGender]
GO
/****** Object: StoredProcedure [dbo].[usp_getStudentScoreByPage] Script Date: 2020/5/29 16:04:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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+1 and @pageSize*@pageIndex
--计算总的记录条数
set @recordCount=(select count(*) from Student_Grade)
--计算总页数
set @pageCount=CEILING(@recordCount*1.0/@pageSize)
end
GO
USE [master]
GO
ALTER DATABASE [EAMDatabase] SET READ_WRITE
GO
将访问数据库的方法抽象成一个类
因为在数据库里对数据加了一些约束，违反约束时会报错，所以对两个常用的方法多加了一次异常捕捉
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace EAM.DAL
{
public static class SqlHelper
{
//从配置文件中读取数据库连接字符串
private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssql"].ConnectionString;
public static int ExecuteNonquery(string sql, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandType = cmdType;
if (pms != null)
{
com.Parameters.AddRange(pms);
}
con.Open();
try
{
return com.ExecuteNonQuery();
}
catch (Exception)
{
return 0;
}
}
}
}
public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandType = cmdType;
if (pms != null)
{
com.Parameters.AddRange(pms);
}
con.Open();
return com.ExecuteScalar();
}
}
}
public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand com = new SqlCommand(sql, con))
{
com.CommandType = cmdType;
if (pms != null)
{
com.Parameters.AddRange(pms);
}
try
{
con.Open();
return com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
}
public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
try
{
adapter.Fill(dt);
}
catch (Exception)
{
throw;
}
}
return dt;
}
}
}
登录功能的实现
学生的
数据访问层
/// <summary>
/// 登录时，根据用户名查询相应的对象信息
/// </summary>
/// <param name="sid">用户名</param>
/// <returns></returns>
public Student getStudentInfoBySid(string sid)
{
string sql = "select * from Student where StudentID=@sid";
SqlParameter pms = new SqlParameter("@sid", SqlDbType.VarChar, 11) { Value = sid };
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text, pms);
Student student = null;
if (dt.Rows.Count > 0)
{
//RowToStudent是一个方法，将一行数据转换为一个学生对象
student = RowToStudent(dt.Rows[0]);
}
return student;
}
private Student RowToStudent(DataRow dr)
{
Student student = new Student();
student.SID = dr["StudentID"].ToString();
student.PassWord = dr["PassWord"].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["Departments"].ToString();
student.Class = dr["Class"].ToString();
//这里考虑了数据库中是否有学生的图像，有的话，显示的就是学生的图像，没有的话，就显示默认图像
if (!dr.IsNull("Image"))
{
byte[] imageByte = (byte[])dr["Image"];
MemoryStream stream = new MemoryStream(imageByte);
student.Image = new Bitmap(stream);
}
return student;
}
业务逻辑层
/// <summary>
/// 登录时，根据用户名查询相应的对象信息
/// </summary>
/// <param name="sid">用户名</param>
/// <param name="pwd">密码</param>
/// <param name="msg">提示信息</param>
/// <param name="stu">学生对象</param>
/// <returns>成功与否</returns>
public bool getStudentInfoBySid(string sid, string pwd, out string msg,out Student stu)
{
stu = dal.getStudentInfoBySid(sid);
if (stu != null)
{
if (stu.PassWord == MD5Encode.GetMd5(pwd))
{
msg = "登录成功";
return true;
}
else
{
msg = "密码错误";
return false;
}
}
else
{
msg = "该用户不存在";
return false;
}
}
教师的
学生和教师的登录方面的代码几乎一样
数据访问层
/// <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="id">账号</param>
/// <param name="pwd">密码</param>
/// <param name="msg">提示信息</param>
/// <returns>成功与否</returns>
public bool GetTeacherInfo(string id,string pwd,out string msg)
{
Teacher teacher = dal.GetTeacherInfo(id);
if (teacher!=null)
{
if (teacher.PassWord==MD5Encode.GetMd5(pwd))
{
msg = "登录成功";
return true;
}
else
{
msg = "密码错误";
return false;
}
}
else
{
msg = "该用户不存在";
return false;
}
}
用户界面层
/// <summary>
/// 登录按钮，根据用户输入的用户名和密码，与数据库中的数据进行比对
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void button1_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(textBox1.Text) ||string.IsNullOrEmpty(textBox2.Text))
{
return;
}
string msg;
string id = textBox1.Text.Trim();
string pwd = textBox2.Text.Trim();
if (ucSwitch1.Checked)
{
TeacherBLL bll = new TeacherBLL();
Boolean b = bll.GetTeacherInfo(id, pwd, out msg);
if (b)
{
//FrmTips.ShowTipsSuccess(this, msg);
//fea.Obj = bll.GetTeacher(id);
//FrmTeacherMain frmTeacher = new FrmTeacherMain();
//this.evtFt += new EventHandler(frmTeacher.SetTxt);
//if (this.evtFt!=null)
//{
// this.evtFt(this, fea);
// frmTeacher.ShowDialog();
//}
FrmTips.ShowTipsSuccess(this, msg);
this.Tag = bll.GetTeacher(id);
this.DialogResult = DialogResult.OK;
}
else
{
FrmTips.ShowTipsError(this, msg);
}
}
else
{
StudentBLL bll = new StudentBLL();
Student stu = new Student();
bool b = bll.getStudentInfoBySid(id, pwd, out msg,out stu);
if (b)
{
this.Tag = stu;
this.DialogResult = DialogResult.Yes;
}
else
{
FrmTips.ShowTipsError(this, msg);
}
}
}
全部的功能基本都是按着这三步走的，从数据访问层获取数据，再经过业务逻辑层，最后呈现到用户界面层
这个教师和学生登录功能， 完全可以合并，根据用户的选项是教师还是学生，加一个判断标识，从而调用方法。尤其是登录业务逻辑层的代码，一摸一样，完全可以写为一个方法。
登录成功后，个人信息的展示
主函数里的代码
using EAM.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Educational_Administration_Management
{
public static class Program
{
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Form1 frm = new Form1();
if (frm.ShowDialog() == DialogResult.OK)
{
Teacher t = frm.Tag as Teacher;
Application.Run(new FrmTeacherMain(ref t));
}
else if (frm.DialogResult == DialogResult.Yes)
{
Student s = frm.Tag as Student;
Application.Run(new FrmStudentMain(s));
}
// Application.Run(new Form1());
}
}
}
在上面的代码里，看到了传参时，一个加了ref关键字，另一个没加。在这里其实不用加：如果你只想在方法中改变引用参数的内容，没有必要使用Ref来修饰引用参数。如果你希望在方法中改变引用对象参数的引用(调用方法外的)，如重新初始化对象，则需要使用Ref关键字来修饰参数。在将教师方面的功能做完后，发现不用加ref，但在生成教师主窗体时传递的参数也仍保留了ref。
就这样，在数据库中可以查询到相应数据，可以登录时，在生成对应的主窗体时，将包含个人信息的对象作为参数传递过去，之后再一一赋值。
private void UCT_Home_Load(object sender, EventArgs e)
{
lblAddress.Text = teacher.Address;
lblBir.Text = teacher.Birthday.ToString("yyy/MM/dd");
lblAdm.Text = teacher.AdmissionTime.ToString("yyy/MM/dd");
lblGender.Text = teacher.TGender;
lblID.Text = teacher.TID;
lblMoney.Text = teacher.TSalary.ToString();
lblName.Text = teacher.TName;
lblDep.Text = teacher.Departments.ToString();
CollegeBLL bll = new CollegeBLL();
List<College> list = bll.GetColleges();
foreach (var item in list)
{
if (item.COID==lblDep.Text)
{
lblDep.Text = item.COName;
break;
}
}
if (teacher.Image!=null)
{
UserImage.Image = teacher.Image;
}
}
private void UCS_Home_Load(object sender, EventArgs e)
{
lblID.Text = student.SID;
lblName.Text = student.SName;
lblGender.Text = student.SGender;
lblBir.Text = student.Birthday.ToString("yyy/MM/dd");
lblAdm.Text = student.AdmissionTime.ToString("yyy/MM/dd");
lblDep.Text = student.Departments;
lblClass.Text = student.Class;
lblAge.Text = (DateTime.Now.Year - student.Birthday.Year).ToString();
List<College> colList = new CollegeBLL().GetColleges();
foreach (var item in colList)
{
if (item.COID == lblDep.Text)
{
lblDep.Text = item.COName;
break;
}
}
List<Class> claList = new ClassBLL().GetClasses();
foreach (var item in claList)
{
if (item.CLID == lblClass.Text)
{
lblClass.Text = item.CLName;
break;
}
}
if (student.Image != null)
{
UserImage.Image = student.Image;
}
}
教师的学生管理
加载该页面时的数据访问层
/// <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>
/// <returns></returns>
public List<Student> GetAllStudents()
{
return dal.GetAllStudents();
}
加载该页面时的用户界面层
private void UCT_StudentManagement_Load(object sender, EventArgs e)
{
LoadStudentInfo();
}
StudentBLL bll = new StudentBLL();
private void LoadStudentInfo()
{
dataGridView1.DataSource = bll.GetAllStudents();
dataGridView1.AutoGenerateColumns = false;
if (dataGridView1.Rows.Count>0)
{
dataGridView1.Rows[0].Selected = false;
}
for (int i = 8; i < dataGridView1.Columns.Count; i++)
{
dataGridView1.Columns[i].Visible = false;
}
dataGridView1.Columns[1].Visible = false;
}
增加学生的数据访问层
/// <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 bool AddStudentInfo(Student student)
{
return dal.AddStudentInfo(student)>0;
}
增加学生的用户界面层
//添加学生
private void btnAdd_Click(object sender, EventArgs e)
{
FrmAddStudent frmAdd = new FrmAddStudent();
frmAdd.FormClosed += new FormClosedEventHandler(frmAdd_FormClosed);
frmAdd.ShowDialog();
}
private void frmAdd_FormClosed(object sender, FormClosedEventArgs e)
{
LoadStudentInfo();
}
删除学生的数据访问层
/// <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 bool DeleteStudentInfo(string sid)
{
return dal.DeleteStudentInfo(sid) > 0;
}
删除学生的用户界面层
//删除学生
private void btnDelete_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count>0)
{
string sid = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
StudentBLL bll = new StudentBLL();
string msg = bll.DeleteStudentInfo(sid)?"删除成功":"删除失败";
MessageBox.Show(msg);
LoadStudentInfo();
}
else
{
MessageBox.Show("请选择要删除的行");
}
}
修改学生的数据访问层
/// <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="student">学生对象</param>
/// <returns>成功与否</returns>
public bool UpdateStudentInfo(Student student)
{
return dal.UpdateStudentInfo(student) > 0;
}
修改学生的用户界面层
//修改学生
public event EventHandler evtFes;
FrmEventArgs fea = new FrmEventArgs();
private void btnEdit_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count>0)
{
string sid = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
StudentBLL bll = new StudentBLL();
fea.Obj = bll.GetStudentBySid(sid);
FrmEditStudent frmEdit = new FrmEditStudent();
this.evtFes += new EventHandler(frmEdit.SetTxt);
if (this.evtFes!=null)
{
this.evtFes(this, fea);
frmEdit.FormClosed += new FormClosedEventHandler(frmAdd_FormClosed);
frmEdit.ShowDialog();
}
}
else
{
MessageBox.Show("请选中要修改的学生");
}
}
查询学生的数据访问层
/// <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;
}
查询学生的业务逻辑层
/// <summary>
/// 根据查询条件，查找相应的学生
/// </summary>
/// <param name="info">条件</param>
/// <returns>查到的学生</returns>
public List<Student> GetStudentsByInfo(string info)
{
return dal.GetStudentsByInfo(info);
}
查询学生的用户界面层
//查询学生
private void btnSearch_Click(object sender, EventArgs e)
{
string info = textBox1.Text;
StudentBLL bll = new StudentBLL();
List<Student> list = bll.GetStudentsByInfo(info);
if (list.Count>0)
{
dataGridView1.DataSource = list;
}
else
{
MessageBox.Show("没有查询到相应的学生");
}
}
教师的课程管理
加载自己开设课程的数据访问层
/// <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;
}
加载自己开设课程的业务逻辑层
/// <summary>
/// 根据教师ID，查询教师开的课程
/// </summary>
/// <param name="TID">教师ID</param>
/// <returns></returns>
public List<Course> GetCourses(string TID)
{
return dal.GetCourses(TID);
}
加载自己开设课程的用户界面层
//加载课程信息
private void LoadCourseInfo()
{
CourseBLL bll = new CourseBLL();
dataGridView1.DataSource = bll.GetCourses(teacher.TID);
}
增加课程的数据访问层
/// <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;
}
}
增加课程的业务逻辑层
/// <summary>
/// 添加课程
/// </summary>
/// <param name="course">课程对象</param>
/// <returns></returns>
public bool AddCourse(Course course)
{
return dal.AddCourse(course) > 0;
}
增加课程的用户界面层
//添加课程
private void ucBtnAdd_BtnClick(object sender, EventArgs e)
{
if (!Check.isCourse(textBox1.Text))
{
MessageBox.Show("课程号错误");
return;
}
string cid = textBox1.Text;
string cname = textBox2.Text;
Course course = new Course()
{
CID=cid,
CName=cname,
Departments=teacher.Departments,
CTeacher=teacher.TID
};
CourseBLL bll = new CourseBLL();
if (bll.AddCourse(course))
{
MessageBox.Show("添加成功");
LoadCourseInfo();
textBox1.Text = "";
textBox2.Text = "";
}
else
{
MessageBox.Show("添加失败");
}
}
删除课程的数据访问层
/// <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="CID">课程号</param>
/// <returns></returns>
public bool DeleteCourse(string CID)
{
return dal.DeleteCourse(CID) > 0;
}
删除课程的用户界面层
//删除课程
private void ucBtnDelete_BtnClick(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count>0)
{
string sid = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
CourseBLL bll = new CourseBLL();
string msg = bll.DeleteCourse(sid) ? "删除成功" : "删除失败";
MessageBox.Show(msg);
LoadCourseInfo();
}
else
{
MessageBox.Show("请选中要删除的行");
}
}
加载选修自己课程的学生信息的数据访问层
/// <summary>
/// 获得教师所教授的学生成绩表的信息
/// </summary>
/// <param name="tid">教师ID</param>
/// <param name="pageCount"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetScores(string tid,out string pageCount,out string recordCount,int pageIndex,int pageSize)
{
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@TID", SqlDbType.VarChar,11){Value=tid},
new SqlParameter("@pageIndex", SqlDbType.Int){Value=pageIndex},
new SqlParameter("@pageSize", SqlDbType.Int){Value=pageSize},
new SqlParameter("@pageCount", SqlDbType.Int){Direction= ParameterDirection.Output},
new SqlParameter("@recordCount", SqlDbType.Int){Direction= ParameterDirection.Output}
};
DataTable dt = SqlHelper.ExecuteDataTable("usp_getStudentScoreByPage", CommandType.StoredProcedure,pms);
pageCount = pms[3].Value.ToString();
recordCount = pms[4].Value.ToString();
return dt;
}
加载选修自己课程的学生信息的业务逻辑层
/// <summary>
/// 获得教师所教授的学生的成绩
/// </summary>
/// <param name="tid"></param>
/// <param name="pageCount"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetScores(string tid, out string pageCount, out string recordCount,int pageIndex,int pageSize)
{
return dal.GetScores(tid, out pageCount, out recordCount,pageIndex,pageSize);
}
加载选修自己课程的学生信息的用户界面层
int pageIndex = 1, pageSize = 16;
string pageCount;
string recordCount;
//加载学生成绩信息
private void LoadScoreInfo()
{
ScoreBLL bll = new ScoreBLL();
dataGridView2.DataSource = bll.GetScores(teacher.TID,out pageCount,out recordCount,pageIndex,pageSize);
label1.Text = "共有" + pageCount + "页，共有" + recordCount + "条记录";
}
查看成绩的上一页、下一页
//上一页
private void ucBtnExt1_BtnClick(object sender, EventArgs e)
{
pageIndex--;
if (pageIndex<1)
{
pageIndex = 1;
}
label2.Text = "当前为第" + pageIndex + "页";
LoadScoreInfo();
}
//下一页
private void ucBtnExt2_BtnClick(object sender, EventArgs e)
{
pageIndex++;
if (pageIndex>Convert.ToInt32(pageCount))
{
pageIndex = Convert.ToInt32(pageCount);
}
label2.Text = "当前为第" + pageIndex + "页";
LoadScoreInfo();
}
修改成绩的数据访问层
/// <summary>
/// 更新学生成绩
/// </summary>
/// <param name="score">成绩</param>
/// <param name="sid">学生id</param>
/// <returns></returns>
public int UpdateScore(int score,string sid,string cid)
{
string sql = "update Student_Grade set Score=@score where SID=" + sid+" and CID ="+cid;
SqlParameter pms = new SqlParameter("@score", score);
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
修改成绩的业务逻辑层
/// <summary>
/// 更新学生成绩
/// </summary>
/// <param name="score">成绩</param>
/// <param name="sid">学生id</param>
/// <returns></returns>
public bool UpdateScore(int score, string sid,string cid)
{
return dal.UpdateScore(score, sid,cid) > 0;
}
修改成绩的用户界面层
private void dataGridView2_DataError(object sender, DataGridViewDataErrorEventArgs e)
{
MessageBox.Show("格式不正确,请输入整数");
dataGridView2.CancelEdit();
}
private void dataGridView2_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
string sid="";
string cid="";
int score=0;
try
{
sid = dataGridView2.Rows[e.RowIndex].Cells[0].Value.ToString();
cid = dataGridView2.Rows[e.RowIndex].Cells[4].Value.ToString();
score = Convert.ToInt32(dataGridView2.Rows[e.RowIndex].Cells[3].Value);
}
catch (Exception)
{
MessageBox.Show("录入失败");
return;
}
//这个判断看起来，有些智障，不过 就先这样吧
if (score < 0 || score > 100)
{
MessageBox.Show("输入错误");
return;
}
ScoreBLL bll = new ScoreBLL();
if (!bll.UpdateScore(score,sid,cid))
{
MessageBox.Show("录入失败");
}
}
修改个人信息
修改个人信息的数据访问层
/// <summary>
/// 修改教师信息
/// </summary>
/// <param name="t"></param>
/// <returns></returns>
public int EditTeacher(Teacher t)
{
string sql = "update Teacher set TName=@name,TGender=@gender,Birthday=@bir,AdmissionTime=@adm,Departments=@depart,TSalary=@salary,Image=@image,Address=@add where TID=@ID";
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="t"></param>
/// <returns></returns>
public bool EditTeacher(Teacher t)
{
return dal.EditTeacher(t)>0;
}
修改信息的用户界面层
//确定修改
private void ucBtnSave_BtnClick(object sender, EventArgs e)
{
if (!Check.isTeacherID(txtTID.Text))
{
MessageBox.Show("工号不正确");
return;
}
try
{
teacher.TID = txtTID.Text;
teacher.TName = txtName.Text;
teacher.Address = txtAddress.Text;
teacher.TSalary = Convert.ToDecimal(txtTSalary.Text);
teacher.TGender = cmbGender.Text;
teacher.Departments = cmbDepart.SelectedValue.ToString();
teacher.Birthday = Convert.ToDateTime(dtpBir.Value.ToString("yyy/MM/dd"));
teacher.AdmissionTime = Convert.ToDateTime(dtpAdmission.Value.ToString("yyy/MM/dd"));
teacher.Image = (Bitmap)pictureBox1.Image;
TeacherBLL bll = new TeacherBLL();
string msg = bll.EditTeacher(teacher) ? "修改成功" : "修改失败";
MessageBox.Show(msg);
LoadTeacherInfo();
}
catch (Exception)
{
MessageBox.Show("修改失败");
}
}
修改密码的数据访问层
/// <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);
}
修改密码的业务逻辑层
/// <summary>
/// 修改密码
/// </summary>
/// <param name="pwd">新密码</param>
/// <param name="id">教师ID</param>
/// <returns></returns>
public bool EditPwd(string pwd, string id)
{
return dal.EditPwd(pwd, id)>0;
}
修改密码的用户界面层
private void ucBtnChangePwd_BtnClick(object sender, EventArgs e)
{
FrmInputs frm = new FrmInputs("修改密码",
new string[] { "旧密码", "新密码", "确定密码" },
new Dictionary<string, HZH_Controls.TextInputType>() { },
new Dictionary<string, string>() { },
new Dictionary<string, KeyBoardType>() { { "旧密码", KeyBoardType.UCKeyBorderAll_EN }, { "新密码", KeyBoardType.UCKeyBorderAll_EN } },
new List<string>() { "旧密码", "新密码", "确定密码" });
frm.ShowDialog(this);
if (frm.DialogResult== DialogResult.OK)
{
string oldPwd = frm.Values[0];
string newPwd = frm.Values[1];
string comPwd = frm.Values[2];
if (newPwd!=comPwd)
{
MessageBox.Show("两次密码不一致");
return;
}
if (!Check.isPwd(newPwd))
{
MessageBox.Show("密码最少6位，包括至少1个大写字母，1个小写字母，1个数字，1个特殊字符");
return;
}
if (MD5Encode.GetMd5(oldPwd)==teacher.PassWord)
{
TeacherBLL bll = new TeacherBLL();
string msg = bll.EditPwd(MD5Encode.GetMd5(newPwd), teacher.TID)?"修改成功":"修改失败";
MessageBox.Show(msg);
}
else
{
MessageBox.Show("原密码错误");
}
}
}
学生的课程管理
加载学院开设课程，学生已选课程，学生选课、退课的数据访问层
/// <summary>
/// 学生选课时，获得所有课程
/// </summary>
/// <returns></returns>
public List<Course> getCourse()
{
string sql = "select * from Course";
List<Course> list = new List<Course>();
DataTable dt = SqlHelper.ExecuteDataTable(sql, CommandType.Text);
if (dt.Rows.Count>0)
{
foreach (DataRow item in dt.Rows)
{
list.Add(RowToCourse(item));
}
}
return list;
}
/// <summary>
/// 根据学生学号，查询学生的课程
/// </summary>
/// <param name="sid"></param>
/// <returns></returns>
public List<Course> getMyCourse(string sid)
{
string sql = "select c.CID,c.CName from Student_Grade as s inner join Course as c on s.CID = c.CID where s.SID = " + sid;
List<Course> list = new List<Course>();
using(SqlDataReader reader=SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if(reader.HasRows)
{
while(reader.Read())
{
Course course = new Course();
course.CID = reader.GetString(0);
course.CName = reader.GetString(1);
list.Add(course);
}
}
}
return list;
}
/// <summary>
/// 学生选课
/// </summary>
/// <param name="sid"></param>
/// <param name="cid"></param>
/// <returns></returns>
public int AddStuCourse(string sid,string cid)
{
string sql = "insert into Student_Grade (SID,CID) values (@sid,@cid)";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@sid",sid),
new SqlParameter("@cid",cid)
};
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
/// <summary>
/// 学生删除课程
/// </summary>
/// <param name="sid"></param>
/// <param name="cid"></param>
/// <returns></returns>
public int DeleteStuCourse(string sid,string cid)
{
string sql = "delete from Student_Grade where SID=@sid and CID=@cid";
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@sid",sid),
new SqlParameter("@cid",cid)
};
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
加载学院开设课程，学生已选课程，学生选课、退课的业务逻辑层
/// <summary>
/// 学生选课时，获得所有课程
/// </summary>
/// <returns></returns>
public List<Course> getCourse()
{
return dal.getCourse();
}
/// <summary>
/// 根据学生学号，查询学生的课程
/// </summary>
/// <param name="sid"></param>
/// <returns></returns>
public List<Course> getMyCourse(string sid)
{
return dal.getMyCourse(sid);
}
/// <summary>
/// 学生选课
/// </summary>
/// <param name="sid"></param>
/// <param name="cid"></param>
/// <returns></returns>
public bool AddStuCourse(string sid, string cid)
{
return dal.AddStuCourse(sid, cid) > 0;
}
/// <summary>
/// 学生删除课程
/// </summary>
/// <param name="sid"></param>
/// <param name="cid"></param>
/// <returns></returns>
public bool DeleteStuCourse(string sid, string cid)
{
return dal.DeleteStuCourse(sid, cid) > 0;
}
加载学院开设课程，学生已选课程，学生选课、退课的用户界面层
private void LoadMyCourse()
{
CourseBLL bll = new CourseBLL();
List<Course> list = bll.getMyCourse(sid);
dataGridView2.DataSource = list;
}
private void LoadCourse()
{
CourseBLL bll = new CourseBLL();
List<Course> list = bll.getCourse();
dataGridView1.DataSource = list;
}
private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if(true)
{
string cid = dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString();
CourseBLL bll = new CourseBLL();
bool b = bll.AddStuCourse(sid, cid);
if (b)
{
MessageBox.Show("选课成功");
LoadMyCourse();
}
else
{
MessageBox.Show("选课失败");
}
}
}
private void dataGridView2_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (true)
{
string cid = dataGridView2.Rows[e.RowIndex].Cells[0].Value.ToString();
CourseBLL bll = new CourseBLL();
bool b = bll.DeleteStuCourse(sid, cid);
if (b)
{
MessageBox.Show("退课成功");
LoadMyCourse();
}
else
{
MessageBox.Show("退课失败");
}
}
}
学生成绩查询
查询成绩的数据访问层
/// <summary>
/// 学生查看自己的成绩
/// </summary>
/// <param name="sid">学生学号</param>
/// <returns></returns>
public List<Score> getScores(string sid)
{
string sql = "select CName,Score,TName from Student_Grade inner join Course on Course.CID = Student_Grade.CID inner join Teacher on Course.Teacher = Teacher.TID where SID = "+sid;
List<Score> list = new List<Score>();
using(SqlDataReader reader=SqlHelper.ExecuteReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
while(reader.Read())
{
Score score = new Score();
score.CID = reader.GetString(0);
score.Grade = reader.IsDBNull(1) ? null :(double?) reader.GetDouble(1);
score.SID = reader.GetString(2);
list.Add(score);
}
}
}
return list;
}
查询成绩的业务逻辑层
/// <summary>
/// 学生查看自己的成绩
/// </summary>
/// <param name="sid">学生学号</param>
/// <returns></returns>
public List<Score> getScores(string sid)
{
return dal.getScores(sid);
}
查询成绩的用户界面层
private void LoadChart()
{
ScoreBLL bll = new ScoreBLL();
List<Score> list = bll.getScores(sid);
List<string> courseList = new List<string>();
List<double> scoreList = new List<double>();
foreach (var item in list)
{
if (item.Grade!=null)
{
scoreList.Add(Convert.ToDouble(item.Grade));
courseList.Add(item.CID);
}
}
chart1.ChartAreas[0].AxisX.Title = "课程";
chart1.ChartAreas[0].AxisY.Title = "成绩";
chart1.ChartAreas[0].AxisX.LabelStyle.Font = new Font("微软雅黑", 10, FontStyle.Bold);
chart1.ChartAreas[0].AxisY.LabelStyle.Font = new Font("微软雅黑", 10, FontStyle.Bold);
chart1.Series[0].Points.DataBindXY(courseList, scoreList);
}
private void LoadScore()
{
List<DataGridViewColumnEntity> lstCulumns = new List<DataGridViewColumnEntity>();
lstCulumns.Add(new DataGridViewColumnEntity() { DataField = "CID", HeadText = "课程名", Width = 70, WidthType = SizeType.Percent });
lstCulumns.Add(new DataGridViewColumnEntity() { DataField = "Grade", HeadText = "分数", Width = 70, WidthType = SizeType.Percent, Format = (a) => { return ((double?)a) == null ? "暂无成绩" : a.ToString(); } });
lstCulumns.Add(new DataGridViewColumnEntity() { DataField = "SID", HeadText = "教师", Width = 50, WidthType = SizeType.Percent });
this.ucDataGridView1.Columns = lstCulumns;
this.ucDataGridView1.IsShowCheckBox = true;
this.ucDataGridView1.DataSource = new ScoreBLL().getScores(sid);
this.ucDataGridView1.First();
}
学生修改个人信息
修改个人信息与密码的数据访问层
/// <summary>
/// 学生修改个人信息
/// </summary>
/// <param name="student"></param>
/// <returns></returns>
public int EditStudent(Student student)
{
string sql = "update Student set StudentName=@name,StudentGender=@gender,Birthday=@bir,AdmissionTime=@adm,Departments=@depart,Class=@class,Image=@image where StudentID=@ID";
byte[] buffer;
using (MemoryStream ms = new MemoryStream())
{
student.Image.Save(ms, System.Drawing.Imaging.ImageFormat.Bmp);
buffer = ms.GetBuffer();
}
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@ID",student.SID),
new SqlParameter("@name",student.SName),
new SqlParameter("@gender", SqlDbType.Bit){ Value=student.SGender=="男"?true:false},
new SqlParameter("@bir",student.Birthday),
new SqlParameter("@adm", student.AdmissionTime),
new SqlParameter("@depart",student.Departments),
new SqlParameter("@class",student.Class),
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"></param>
/// <returns></returns>
public int EditPwd(string pwd,string id)
{
string sql = "update Student set PassWord=@pwd where StudentID=" + id;
SqlParameter pms = new SqlParameter("@pwd", pwd);
return SqlHelper.ExecuteNonquery(sql, CommandType.Text, pms);
}
学生修改个人信息的业务逻辑层
/// <summary>
/// 学生修改个人信息
/// </summary>
/// <param name="student"></param>
/// <returns></returns>
public bool EditStudent(Student student)
{
return dal.EditStudent(student) > 0;
}
/// <summary>
/// 学生修改密码
/// </summary>
/// <param name="pwd"></param>
/// <param name="id"></param>
/// <returns></returns>
public bool EditPwd(string pwd, string id)
{
return dal.EditPwd(pwd, id) > 0;
}
修改个人信息的用户界面层
private void ucBtnSave_BtnClick(object sender, EventArgs e)
{
if (!Check.isStudentID(txtTID.Text))
{
MessageBox.Show("学号不正确");
return;
}
try
{
student.SID = txtTID.Text;
student.SName = txtName.Text;
student.SGender = cmbGender.Text;
student.Departments = cmbDepart.SelectedValue.ToString();
student.Class = cmbClass.SelectedValue.ToString();
student.Birthday = Convert.ToDateTime(dtpBir.Value.ToString("yyy/MM/dd"));
student.AdmissionTime = Convert.ToDateTime(dtpAdmission.Value.ToString("yyy/MM/dd"));
student.Image = (Bitmap)pictureBox1.Image;
StudentBLL bll = new StudentBLL();
string msg = bll.EditStudent(student) ? "修改成功" : "修改失败";
MessageBox.Show(msg);
LoadStudentInfo();
}
catch (Exception)
{
MessageBox.Show("修改失败");
}
}
private void ucBtnChangePwd_BtnClick(object sender, EventArgs e)
{
FrmInputs frm = new FrmInputs("修改密码",
new string[] { "旧密码", "新密码", "确定密码" },
new Dictionary<string, HZH_Controls.TextInputType>() { },
new Dictionary<string, string>() { },
new Dictionary<string, KeyBoardType>() { { "旧密码", KeyBoardType.UCKeyBorderAll_EN }, { "新密码", KeyBoardType.UCKeyBorderAll_EN } },
new List<string>() { "旧密码", "新密码", "确定密码" });
frm.ShowDialog(this);
if (frm.DialogResult == DialogResult.OK)
{
string oldPwd = frm.Values[0];
string newPwd = frm.Values[1];
string comPwd = frm.Values[2];
if (newPwd != comPwd)
{
MessageBox.Show("两次密码不一致");
return;
}
if (!Check.isPwd(newPwd))
{
MessageBox.Show("密码最少6位，包括至少1个大写字母，1个小写字母，1个数字，1个特殊字符");
return;
}
if (MD5Encode.GetMd5(oldPwd) == student.PassWord)
{
StudentBLL bll = new StudentBLL();
string msg = bll.EditPwd(MD5Encode.GetMd5(newPwd), student.SID) ? "修改成功" : "修改失败";
MessageBox.Show(msg);
}
else
{
MessageBox.Show("原密码错误");
}
}
}
截止到现在，B站上的作业演示视频播放量超过了5000，收藏人数过百，点赞即将过百。这个之前根本就没有想到过。现在的网友太热情了，为了回馈热情的网友，在期末考试安全通过后，把未做完的那个B/S结构的给安排上。