学员管理系统(C#源码)

use test go -------------插入班级-------------- if exists(select * from sysobjects where name='pro_insertClass') drop procedure pro_insertClass go create procedure pro_insertClass ( @classid int,@className varchar(50),@entranceDate datetime,@remark varchar(256) ) as insert into class values(@classid,@className,@entranceDate,@remark) go -------------更新班级-------------- if exists(select * from sysobjects where name='pro_updateClass') drop procedure pro_updateClass go create procedure pro_updateClass ( @classid int,@className varchar(50),@entranceDate datetime,@remark varchar(256) ) as update class set className=@className,entranceDate=@entranceDate,remark=@remark where classid=@classid go ----------------删除班级----------- if exists(select * from sysobjects where name='pro_deleteClass') drop procedure pro_deleteClass go create procedure pro_deleteClass ( @classid int ) as declare @stuid int delete from score where courseId in(select courseId from course where classid=@classid) delete from course where classid=@classid delete from studentinfo where studentid in (select studentid from studentinfo where classid=@classid) delete from class where classid=@classid go exec pro_deleteClass 1 ------------插入学生信息------------- if exists(select * from sysobjects where name='pro_insertStudent') drop procedure pro_insertStudent go create procedure pro_insertStudent ( @StudentNo varchar(50),@StudentName varchar(50),@Gender varchar(4),@Birthday datetime, @classid int,@status int,@remark varchar(256) ) as insert into studentinfo values(@StudentNo,@StudentName,@Gender,@Birthday,@classid,@status,@remark) go ------------更新学生信息------------- if exists(select * from sysobjects where name='pro_updateStudent') drop procedure pro_updateStudent go create procedure pro_updateStudent ( @studentid int,@StudentNo varchar(50),@StudentName varchar(50),@Gender varchar(4),@Birthday datetime, @classid int,@remark varchar(256) ) as update studentinfo set studentNo=@StudentNo,studentName=@StudentName,Gender=@Gender,Birthday=@Birthday,classid=@classid,remark=@remark where studentid=@studentid go exec pro_insertStudent '006','发生的','男','2007-1-2',3,0,'sadf' -----查询学员信息 if exists(select * from sysobjects where name='pro_selectStudent') drop procedure pro_selectStudent go create procedure pro_selectStudent as --select * from studentinfo select studentName,gender,Birthday,className,studentinfo.remark,studentid,studentNo from studentinfo inner join class on studentinfo.classid=class.classid go exec pro_selectStudent ---------------删除学员信息---------- if exists(select * from sysobjects where name='pro_deleteStudent') drop procedure pro_deleteStudent go create procedure pro_deleteStudent ( @studentid int ) as delete from score where studentid=@studentid delete from studentinfo where studentid=@studentid go -----查询班级信息 if exists(select * from sysobjects where name='pro_selectClass') drop procedure pro_selectClass go create procedure pro_selectClass as select * from class go -----查询科目信息 if exists(select * from sysobjects where name='pro_selectSub') drop procedure pro_selectSub go create procedure pro_selectSub as select * from subject go select * from studentinfo inner join class on studentinfo.classid=class.classid exec pro_selectStudentByClass 'S2t15班' ---查询成绩 if exists(select * from sysobjects where name='pro_selectScore') drop procedure pro_selectScore go create procedure pro_selectScore as select studentName,className,subjectName,score from studentinfo ,subject,score,class,course where studentinfo.classid=class.classid and score.courseid=course.courseid and course.subjectid=subject.subjectid and studentinfo.studentid=score.studentid GO exec pro_selectScore -----根据科目查询成绩 if exists(select * from sysobjects where name='pro_selectScoreBySubjectName') drop procedure pro_selectScoreBySubjectName go create procedure pro_selectScoreBySubjectName @subjectName varchar(20) as select studentName,className,subjectName,score from studentinfo ,subject,score,class,course where studentinfo.classid=class.classid and score.courseid=course.courseid and course.subjectid=subject.subjectid and studentinfo.studentid=score.studentid and subject.subjectName=@subjectName go exec pro_selectScoreBySubjectName '语文' -------建立课程 if exists(select * from sysobjects where name='pro_insertCourse') drop procedure pro_insertCourse go create procedure pro_insertCourse ( @courID int,@classID int,@subID int,@beginDate datetime,@finishiDate datetime,@remark varchar(50) ) as insert into course values(@courId,@classID,@subID,@beginDate,@finishiDate,@remark) go -------更新课程 if exists(select * from sysobjects where name='pro_updateCourse') drop procedure pro_updateCourse go create procedure pro_updateCourse ( @courID int,@classID int,@subID int,@beginDate datetime,@finishiDate datetime,@remark varchar(50) ) as update course set classid=@classID,subjectid=@subID,beginDate=@beginDate,finishDate=@finishiDate,remark=@remark where courseId=@courID go -------删除课程 if exists(select * from sysobjects where name='pro_deleteCourse') drop procedure pro_deleteCourse go create procedure pro_deleteCourse ( @courID int ) as delete from score where courseid=@courID delete from course where courseid=@courID go -------建立科目 if exists(select * from sysobjects where name='pro_insertSubject') drop procedure pro_insertSubject go create procedure pro_insertSubject ( @subID int,@subName varchar(20),@remark varchar(50) ) as insert into subject values(@subID,@subName,@remark) go -------删除科目 if exists(select * from sysobjects where name='pro_deleteSubject') drop procedure pro_deleteSubject go create procedure pro_deleteSubject ( @subID int ) as delete from score where courseid in (select courseid from course where subjectid=@subID) delete from course where subjectid=@subID delete from subject where subjectid=@subID go -------更新科目 if exists(select * from sysobjects where name='pro_updateSubject') drop procedure pro_updateSubject go create procedure pro_updateSubject ( @subID int,@subName varchar(20),@remark varchar(50) ) as update subject set subjectName=@subName,remark=@remark where subjectID=@subID go --------查询课程编号------- if exists(select * from sysobjects where name='pro_selectCourseID') drop procedure pro_selectCourseID go create procedure pro_selectCourseID as select courseid from course go --------根据科目名称查询课程编号------- if exists(select * from sysobjects where name='pro_selectCourseIDBySubjectNameAndClassName') drop procedure pro_selectCourseIDBySubjectNameAndClassName go create procedure pro_selectCourseIDBySubjectNameAndClassName @subjectName varchar(20) as declare @Sid int,@Classid int select @Sid=subjectid from subject where subjectName=@subjectName --select @Classid=classid from class where className=@className select courseid,subjectid,classid from course where subjectid=@Sid go exec pro_selectCourseIDBySubjectNameAndClassName 'C#/.Net' ---------建立成绩信息 if exists(select * from sysobjects where name='pro_insertScore') drop procedure pro_insertScore go create procedure pro_insertScore ( @ScoID int,@courID int,@studentid int,@score float ) as insert into score values(@ScoID,@courID,@studentid,@score) go use test go select * from score select * from studentinfo exec pro_insertScore 3,1,11,89 ---------删除成绩信息 if exists(select * from sysobjects where name='pro_deleteScore') drop procedure pro_deleteScore go create procedure pro_deleteScore ( @courID int,@studentid int ) as declare @id int select @id=scoreid from score where courseid=@courID and studentid=@studentid delete f