constants.cs

学生学籍管理

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

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

        /// <summary>
        /// 管理员登陆
        /// </summary>
        public static readonly string AdminLogin =
            @"SELECT COUNT(*) FROM [users]
              WHERE   UserName = @adminName 
              AND UserPassword = @adminPassword
              AND Usertype = '管理员' ";

        /// <summary>
        /// 修改密码
        /// </summary>
        public static readonly string ChangePassword =
            @"UPDATE [users] SET UserPassword = @adminPassword
              WHERE UserName = @adminName";

        /// <summary>
        /// 添加教师
        /// </summary>
        public static readonly string AddTeacher =
            @"INSERT INTO [users]
                (
                    UserName,
                    UserPassword,
                    Usertype 
                )
              VALUES
                (
                    @teaName,
                    @teaPassword,
                    '教师'
                )";

        /// <summary>
        /// 获取所有教师信息
        /// </summary>
        public static readonly string AllTeacher = 
            @"SELECT 
                UserName AS 教师姓名,
                Usertype AS 所属级别
              FROM [users] 
              WHERE Usertype = '教师'";

        /// <summary>
        /// 删除教师
        /// </summary>
        public static readonly string DeleteTeacher = 
            @"DELETE FROM [users]
              WHERE UserName = @teaName";

        /// <summary>
        /// 检查教师是否开设课程
        /// </summary>
        public static readonly string IsUselessTeacher =
            @"SELECT COUNT(*) FROM [course]
              WHERE courseTeacher = @teaName";

        /// <summary>
        /// 所有报考cet的学生信息
        /// </summary>
        public static readonly string GetAllCet =
            @"SELECT
                [cet].cetID AS 编号,
                [student].stuName AS 学生姓名,
                [cet].stuID AS 学生学号,
                [cet].stuCardID AS 身份证号,
                [cet].cetTestDate AS 考试时间,
                [cet].cetTestPlace AS 考试地点,
                [cet].cetClass AS 考试级别,
                [cet].cetTestScore AS 成绩
              FROM
                [cet],[student]
              WHERE
                [cet].stuID = [student].stuID";

        /// <summary>
        /// 所有报考computer的学生信息
        /// </summary>
        public static readonly string GetAllComputer =
            @"SELECT
                [computer].comID AS 编号,
                [student].stuName AS 学生姓名,
                [computer].stuID AS 学生学号,
                [computer].stuCardID AS 身份证号,
                [computer].comTestDate AS 考试时间,
                [computer].comTestPlace AS 考试地点,
                [computer].comType AS 考试级别,
                [computer].comScore AS 成绩
              FROM
                [computer],[student]
              WHERE
                [computer].stuID = [student].stuID";

        /// <summary>
        /// 更新四六级考试信息
        /// </summary>
        public static readonly string UpdateCet =
            @"UPDATE    [cet]
              SET
                        cetTestDate = @testDate,
                        cetTestPlace = @testPlace,
                        cetTestScore = @testScore
              Where
                        cetID = @cetID";

        /// <summary>
        /// 更新计算机等级考试信息
        /// </summary>
        public static readonly string UpdateComputer =
            @"UPDATE    [computer]
              SET
                        comTestDate = @testDate,
                        comTestPlace = @testPlace,
                        comScore = @testScore
              Where
                        comID = @comID";

        /// <summary>
        /// 删除指定编号的考试信息
        /// </summary>
        public static readonly string DeleteCet =
            @"DELETE FROM [cet]
              WHERE cetID = @cetID";

        /// <summary>
        /// 删除指定编号的考试信息
        /// </summary>
        public static readonly string DeleteComputer =
            @"DELETE FROM [computer]
              WHERE comID = @comID";
    }
}