studentservice.cs

开发框架

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Models;
using IDAL;
/************************************
 * 类名：StudentService
 * 创建日期：2007-7-13
 * 功能描述：提供学员信息数据访问
 * *********************************/
namespace DAL.SqlServer
{
    public class StudentService : IStudentService
    {
        #region Private Members
        //从配置文件中读取数据库连接字符串
        private readonly string connString = ConfigurationManager.ConnectionStrings["MySchoolConnectionString"].ToString();
        private readonly string dboOwner = ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();
        #endregion

        #region Public Methods
        /// <summary>
        /// 创建学员帐户
        /// </summary>
        /// <param name="objStudent">学员实体对象</param>
        /// <returns>生成帐户记录的ＩＤ</returns>
        public  int AddSutdent(Student objStudent)
        {
            int number;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_InsertPartStudentInfo", conn);
                objCommand.CommandType = CommandType.StoredProcedure;

                objCommand.Parameters.Add("@LoginID", SqlDbType.NVarChar, 50).Value = objStudent.LoginId;
                objCommand.Parameters.Add("@LoginPwd", SqlDbType.NVarChar, 50).Value = objStudent.LingPwd;
                objCommand.Parameters.Add("@UserStateId", SqlDbType.Int).Value = objStudent.UserStateId;
                objCommand.Parameters.Add("@ClassID", SqlDbType.Int).Value = objStudent.ClassID;
                objCommand.Parameters.Add("@StudentNO", SqlDbType.NVarChar, 255).Value = objStudent.StudentNO;
                objCommand.Parameters.Add("@StudentName", SqlDbType.NVarChar, 255).Value = objStudent.StudentName;
                objCommand.Parameters.Add("@Sex", SqlDbType.NVarChar, 255).Value = objStudent.Sex;
                conn.Open();
                number = Convert.ToInt32(objCommand.ExecuteScalar());
                conn.Close();
                conn.Dispose();
            }
            return number;
        }
        /// <summary>
        /// 根据学员ＩＤ删除帐户信息
        /// </summary>
        /// <param name="loginID"></param>
        public  void DeleteStudent(string loginID)
        {
            int studentID = GetStudentIDByLoginID(loginID);
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_DeleteStudent", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@StudentID", SqlDbType.Int).Value = studentID;
                conn.Open();
                objCommand.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
            }
        }
        /// <summary>
        /// 根据登录ＩＤ得到学员信息
        /// </summary>
        /// <param name="loginID">登录ＩＤ</param>
        /// <returns>学员信息实体</returns>
        public  Student GetStudentInfoByLoginID(string loginID)
        {
            Student studentInfo = new Student();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectStudentInfoByLoginID", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@LoginID", SqlDbType.NVarChar, 50).Value = loginID;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (objReader.Read())
                    {
                        studentInfo.LoginId = Convert.ToString(objReader["LoginId"]);
                        studentInfo.StudentNO = Convert.ToString(objReader["StudentNO"]);
                        studentInfo.StudentName = Convert.ToString(objReader["StudentName"]);
                        studentInfo.Sex = Convert.ToString(objReader["Sex"]);
                        studentInfo.StudentIDNO = Convert.ToString(objReader["StudentIDNO"]);
                        studentInfo.Phone = Convert.ToString(objReader["Phone"]);
                    }
                }
                conn.Close();
                conn.Dispose();
            }
            return studentInfo;
        }
        /// <summary>
        /// 根据学员登录ＩＤ得到学员ＩＤ
        /// </summary>
        /// <param name="loginID">登录ＩＤ</param>
        /// <returns>学员ＩＤ</returns>
        public  int GetStudentIDByLoginID(string loginID)
        {
            int studentID = 0;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectStudentIDByLoginID", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@LoginId", SqlDbType.NVarChar, 50).Value = loginID;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (objReader.Read())
                    {
                        studentID = Convert.ToInt32(objReader["StudentID"]);
                    }
                    objReader.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }
            return studentID;
        }
        /// <summary>
        /// 根据登录ＩＤ得到学员登录密码和用户状态ＩＤ
        /// </summary>
        /// <param name="loginID">登录ＩＤ</param>
        /// <returns>密码和状态ＩＤ的集合</returns>
        public  List<Student> GetStudentLoginPwdByLoginID(string loginID)
        {
            List<Student> studentlist = new List<Student>();
            string pwd = string.Empty;
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectStudentByLoginID", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@LoginId", SqlDbType.NVarChar, 50).Value = loginID;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if (objReader.Read())
                    {
                        Student student = new Student();
                        student.LingPwd = Convert.ToString(objReader["LoginPwd"]);
                        student.UserStateId = Convert.ToInt32(objReader["UserStateId"]);
                        studentlist.Add(student);
                    }
                    objReader.Dispose();
                }
                conn.Close();
                conn.Dispose();
            }

            return studentlist;
        }
        /// <summary>
        /// 更新学员信息
        /// </summary>
        /// <param name="objStudent">学员实体对象</param>
        public  void ModifyStudent(Student objStudent)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_UpdateStudentBaseInfo", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                objCommand.Parameters.Add("@LoginID", SqlDbType.NVarChar, 50).Value = objStudent.LoginId;
                objCommand.Parameters.Add("@StudentNO", SqlDbType.NVarChar, 255).Value = objStudent.StudentNO;
                objCommand.Parameters.Add("@StudentName", SqlDbType.NVarChar, 255).Value = objStudent.StudentName;
                objCommand.Parameters.Add("@Sex", SqlDbType.NVarChar, 255).Value = objStudent.Sex;
                objCommand.Parameters.Add("@StudentIDNO", SqlDbType.NVarChar, 255).Value = objStudent.StudentIDNO;
                objCommand.Parameters.Add("@Phone", SqlDbType.NVarChar, 255).Value = objStudent.Phone;
                conn.Open();
                objCommand.ExecuteNonQuery();
                conn.Close();
                conn.Dispose();
            }

        }
        /// <summary>
        /// 返回所有学员信息集合
        /// </summary>
        /// <returns>学员信息集合</returns>
        public  IList<Student> GetAllStudents()
        {
            IList<Student> objStudentList = new List<Student>();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_SelectStudentsAll", conn);
                objCommand.CommandType = CommandType.StoredProcedure;
                conn.Open();
                using (SqlDataReader objReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (objReader.Read())
                    {
                        Student objStudent = new Student();
                        objStudent.LoginId = Convert.ToString(objReader["LoginId"]);
                        objStudent.StudentNO = Convert.ToString(objReader["StudentNO"]);
                        objStudent.StudentName = Convert.ToString(objReader["StudentName"]);
                        objStudent.Sex = Convert.ToString(objReader["Sex"]);
                        objStudent.StudentIDNO = Convert.ToString(objReader["StudentIDNO"]);
                        objStudent.Phone = Convert.ToString(objReader["Phone"]);
                        objStudentList.Add(objStudent);
                    }
                }
                conn.Close();
                conn.Dispose();
            }
            return objStudentList;
        }
        #endregion
    }
}