constants.cs

学生学籍管理

using System;
using System.Collections.Generic;
using System.Text;

namespace SchoolMIS.DataAccess.Student.SQLServer
{
    class Constants
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static readonly string ConnString =
            @"Data Source=(local);Initial Catalog=student";

        /// <summary>
        /// 验证学生登陆
        /// </summary>
        public static readonly string StuLogin =
            @"select stuName from student where stuID = @stuID and stuPassword = @stuPassword";
        
        /// <summary>
        /// 修改学生信息
        /// </summary>
        public static readonly string StuUpdateInfo =
            @"UPDATE student SET
                [stuPassword]=@stuPassword,
                [stuName]=@stuName,
                [stuEnrollmentDate]=@stuEnrollmentDate,
                [stuGraduatedDate]=@stuGraduatedDate,
                [stuMajor]=@stuMajor,
                [stuSex]=@stuSex,
                [stuBirthday]=@stuBirthday,
                [stuHome]=@stuHome,
                [stuRace]=@stuRace,
                [stuDesc]=@stuDesc,
                [stuParty]=@stuParty,
                [stuPhone]=@stuPhone
            WHERE
                [stuID]=@stuID";

        /// <summary>
        /// 添加学生信息
        /// </summary>
        public static readonly string StuInsertInfo =
            @"INSERT INTO student(
                [stuPassword],[stuName],
                [stuEnrollmentDate],[stuGraduatedDate],
                [stuMajor],[stuSex],[stuBirthday],
                [stuHome],[stuRace],[stuDesc],[stuID],[stuParty],[stuPhone])
            VALUES(
                @stuPassword,@stuName,
                @stuEnrollmentDate,@stuGraduatedDate,
                @stuMajor,@stuSex,@stuBirthday,
                @stuHome,@stuRace,@stuDesc,@stuID,@stuParty,@stuPhone)";

        /// <summary>
        /// 依据学生学号获取学生信息
        /// </summary>
        public static readonly string GetStuInfoByStuID =
            @"SELECT * FROM [student] WHERE stuID = @stuID";

        /// <summary>
        /// 获取所有的课程信息
        /// </summary>
        public static readonly string GetAllCourse =
            @"SELECT courseID AS 编号,
                     courseName AS 名称,
                     courseTime AS 上课时间,
                     courseAddress AS 上课地点,
                     courseStartTime AS 开课时间,
                     courseEndTime AS 结课时间,
                     courseCredit AS 学分,
                     courseTeacher AS 教师,
                     courseDesc AS 其他信息   
              FROM [course]";

        /// <summary>
        /// 获取已经选择的课程信息
        /// </summary>
        public static readonly string GetSelectedCourse =
            @"SELECT [stuCourse].scID AS 选课编号,
                     courseName AS 名称,
                     courseTime AS 上课时间,
                     courseAddress AS 上课地点,
                     courseStartTime AS 开课时间,
                     courseEndTime AS 结课时间,
                     courseCredit AS 学分,
                     courseTeacher AS 教师,
                     courseDesc AS 其他信息   
              FROM   [course],[stuCourse]
              WHERE  [stuCourse].stuID = @stuNum 
              AND    [course].courseID = [stuCourse].courseID";

        /// <summary>
        /// 四六级报考历史
        /// </summary>
        public static readonly string GetCetHistory =
            @"SELECT cetID AS 编号,
                     cetClass AS 级别,
                     stuCardID AS 身份证号码,
                     cetTestDate AS 考试时间,
                     cetTestPlace AS 考试地点,
                     cetTestScore AS 分数
               FROM  cet
               WHERE stuID = @stuID";

        /// <summary>
        /// 计算机等级考试报考历史
        /// </summary>
        public static readonly string GetComputerHistory =
            @"SELECT comID AS 编号,
                     comType AS 级别,
                     stuCardID AS 身份证号码,
                     comTestDate AS 考试时间,
                     comTestPlace AS 考试地点,
                     comScore AS 分数
               FROM  computer
               WHERE stuID = @stuID";

        /// <summary>
        /// 添加学生选课信息
        /// </summary>
        public static readonly string AddSelectedCourse =
            @"INSERT INTO stuCourse(stuID,courseID) VALUES (@stuNum,@courseID)";

        /// <summary>
        /// 删除学生选课信息
        /// </summary>
        public static readonly string DeleteSelectedCourse =
            @"DELETE FROM stuCourse where scID = @scID";

        /// <summary>
        /// 判断学生是否已经选择该课程
        /// </summary>
        public static readonly string IsSelectedCourse =
            @"SELECT COUNT(scID) FROM stuCourse 
              WHERE stuID = @stuNum and courseID = @courseID";

        /// <summary>
        /// 四六级报考
        /// </summary>
        public static readonly string AddCetTest =
            @"INSERT INTO [cet] (stuID,cetClass,stuCardID)
              VALUES (@stuID,@cetClass,@stuCardID)";

        /// <summary>
        /// 计算机等级考试报考
        /// </summary>
        public static readonly string AddComTest =
            @"INSERT INTO [computer] (stuID,comType,stuCardID)
              VALUES (@stuID,@comType,@stuCardID)";
    }
}