studentdb.cs

学生学籍管理

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using SchoolMIS.DataAccess.Common.Entity;

namespace SchoolMIS.DataAccess.Student.SQLServer
{
    public class StudentDB
    {
        /// <summary>
        /// 验证学生登陆信息
        /// </summary>
        /// <param name="stuID">学号</param>
        /// <param name="stuPsw">密码</param>
        /// <returns>学生姓名</returns>
        public string Login(string stuID, string stuPsw)
        {                
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.StuLogin;
            cmd.CommandType = CommandType.Text;

            //添加参数给数据命令
            SqlParameter prmStuNum = new SqlParameter("@stuID", stuID);
            SqlParameter prmStuPassword = new SqlParameter("@stuPassword", stuPsw);
            cmd.Parameters.Add(prmStuNum);
            cmd.Parameters.Add(prmStuPassword);

            try
            {
                conn.Open();
                object stuName = cmd.ExecuteScalar();

                if (stuName != null)
                {
                    return stuName.ToString();
                }
                else
                {
                    return String.Empty;
                }

            }
            catch (SqlException ex)
            {
                return String.Empty;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 添加学生信息
        /// </summary>
        /// <param name="student">学生信息实体</param>
        /// <returns>操作结果，成功为true，否则为false</returns>
        public bool AddStudent(EStudent student)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.StuInsertInfo;
            cmd.CommandType = CommandType.Text;

            SqlParameter paramStuPassword = new SqlParameter("@stuPassword", student.StuPassowrd);
            cmd.Parameters.Add(paramStuPassword);
            SqlParameter paramStuName = new SqlParameter("@stuName", student.StuName);
            cmd.Parameters.Add(paramStuName);
            SqlParameter paramStuEnrollmentDate = new SqlParameter("@stuEnrollmentDate", student.StuEnrolDate);
            cmd.Parameters.Add(paramStuEnrollmentDate);
            SqlParameter paramStuGraduatedDate = new SqlParameter("@stuGraduatedDate", student.StuGraduDate);
            cmd.Parameters.Add(paramStuGraduatedDate);
            SqlParameter paramStuMajor = new SqlParameter("@stuMajor", student.StuMajor);
            cmd.Parameters.Add(paramStuMajor);
            SqlParameter paramStuSex = new SqlParameter("@stuSex", student.StuSex);
            cmd.Parameters.Add(paramStuSex);
            SqlParameter paramStuBirthday = new SqlParameter("@stuBirthday", student.StuBirthday);
            cmd.Parameters.Add(paramStuBirthday);
            SqlParameter paramStuHome = new SqlParameter("@stuHome", student.StuHome);
            cmd.Parameters.Add(paramStuHome);
            SqlParameter paramStuRace = new SqlParameter("@stuRace", student.StuRace);
            cmd.Parameters.Add(paramStuRace);
            SqlParameter paramStuDesc = new SqlParameter("@stuDesc", student.StuDesc);
            cmd.Parameters.Add(paramStuDesc);
            SqlParameter paramStuID = new SqlParameter("@stuID", student.StuNum);
            cmd.Parameters.Add(paramStuID);
            SqlParameter paramStuParty = new SqlParameter("@stuParty", student.StuParty);
            cmd.Parameters.Add(paramStuParty);
            SqlParameter paramStuPhone = new SqlParameter("@stuPhone", student.StuPhone);
            cmd.Parameters.Add(paramStuPhone);

            try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();
                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 依据学生学号获取学生具体信息
        /// </summary>
        /// <param name="stuID">学生学号</param>
        /// <returns>学生信息实体</returns>
        public EStudent GetStudentByStuID(string stuID)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.GetStuInfoByStuID;
            cmd.CommandType = CommandType.Text;

            try
            {
                SqlParameter paramStuID = new SqlParameter("@stuID", stuID);
                cmd.Parameters.Add(paramStuID);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                reader.Read();

                EStudent stuInfo = new EStudent();
                stuInfo.StuBirthday = DateTime.Parse(reader["stuBirthday"].ToString());
                stuInfo.StuDesc = reader["stuDesc"].ToString();
                stuInfo.StuEnrolDate = DateTime.Parse(reader["stuEnrollmentDate"].ToString());
                stuInfo.StuGraduDate = DateTime.Parse(reader["stuGraduatedDate"].ToString());
                stuInfo.StuHome = reader["stuHome"].ToString();
                stuInfo.StuMajor = reader["stuMajor"].ToString();
                stuInfo.StuName = reader["stuName"].ToString();
                stuInfo.StuNum = reader["stuID"].ToString();
                stuInfo.StuParty = reader["stuParty"].ToString();
                stuInfo.StuPassowrd = reader["stuPassword"].ToString();
                stuInfo.StuPhone = reader["stuPhone"].ToString();
                stuInfo.StuRace = reader["stuRace"].ToString();
                stuInfo.StuSex = reader["stuSex"].ToString();

                //关闭数据阅读器和数据连接
                reader.Close();
                conn.Close();

                return stuInfo;
            }
            catch (SqlException ex)
            {
                return null;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// 更新学生信息
        /// </summary>
        /// <param name="student">学生信息实体</param>
        /// <returns>更新成功与否</returns>
        public bool UpdateStudent(EStudent student)
        {
            //建立数据库连接对象
            SqlConnection conn = new SqlConnection(Constants.ConnString);
            //建立数据库命令对象
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = Constants.StuUpdateInfo;
            cmd.CommandType = CommandType.Text;

            SqlParameter paramStuPassword = new SqlParameter("@stuPassword", student.StuPassowrd);
            cmd.Parameters.Add(paramStuPassword);
            SqlParameter paramStuName = new SqlParameter("@stuName", student.StuName);
            cmd.Parameters.Add(paramStuName);
            SqlParameter paramStuEnrollmentDate = new SqlParameter("@stuEnrollmentDate", student.StuEnrolDate);
            cmd.Parameters.Add(paramStuEnrollmentDate);
            SqlParameter paramStuGraduatedDate = new SqlParameter("@stuGraduatedDate", student.StuGraduDate);
            cmd.Parameters.Add(paramStuGraduatedDate);
            SqlParameter paramStuMajor = new SqlParameter("@stuMajor", student.StuMajor);
            cmd.Parameters.Add(paramStuMajor);
            SqlParameter paramStuSex = new SqlParameter("@stuSex", student.StuSex);
            cmd.Parameters.Add(paramStuSex);
            SqlParameter paramStuBirthday = new SqlParameter("@stuBirthday", student.StuBirthday);
            cmd.Parameters.Add(paramStuBirthday);
            SqlParameter paramStuHome = new SqlParameter("@stuHome", student.StuHome);
            cmd.Parameters.Add(paramStuHome);
            SqlParameter paramStuRace = new SqlParameter("@stuRace", student.StuRace);
            cmd.Parameters.Add(paramStuRace);
            SqlParameter paramStuDesc = new SqlParameter("@stuDesc", student.StuDesc);
            cmd.Parameters.Add(paramStuDesc);
            SqlParameter paramStuID = new SqlParameter("@stuID", student.StuNum);
            cmd.Parameters.Add(paramStuID);
            SqlParameter paramStuParty = new SqlParameter("@stuParty", student.StuParty);
            cmd.Parameters.Add(paramStuParty);
            SqlParameter paramStuPhone = new SqlParameter("@stuPhone", student.StuPhone);
            cmd.Parameters.Add(paramStuPhone);

            try
            {
                conn.Open();
                string temp = cmd.CommandText;
                int i = cmd.ExecuteNonQuery();
                conn.Close();

                return true;
            }
            catch (SqlException ex)
            {
                return false;
            }
            finally
            {
                //确保数据库连接被关闭
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
        }
    }
}