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

using Model;

namespace DAL
{
    /*学生信息业务逻辑层实现*/
    public class dalStudent_QQ287307421
    {
        /*待执行的sql语句*/
        public static string sql = "";

        /*添加学生信息实现*/
        public static bool AddStudent_QQ287307421(Student_QQ287307421 student_QQ287307421)
        {
            string sql = "insert into Student_QQ287307421(studentNumber,studentName,studentPassword,studentSex,studentClassNumber,studentBirthday,studentState,studentPhoto,studentTelephone,studentEmail,studentQQ,studentAddress,studentMemo) values(@studentNumber,@studentName,@studentPassword,@studentSex,@studentClassNumber,@studentBirthday,@studentState,@studentPhoto,@studentTelephone,@studentEmail,@studentQQ,@studentAddress,@studentMemo)";
            /*构建sql参数*/
            SqlParameter[] parm = new SqlParameter[] {
             new SqlParameter("@studentNumber",SqlDbType.VarChar),
             new SqlParameter("@studentName",SqlDbType.VarChar),
             new SqlParameter("@studentPassword",SqlDbType.VarChar),
             new SqlParameter("@studentSex",SqlDbType.VarChar),
             new SqlParameter("@studentClassNumber",SqlDbType.VarChar),
             new SqlParameter("@studentBirthday",SqlDbType.DateTime),
             new SqlParameter("@studentState",SqlDbType.VarChar),
             new SqlParameter("@studentPhoto",SqlDbType.Image),
             new SqlParameter("@studentTelephone",SqlDbType.VarChar),
             new SqlParameter("@studentEmail",SqlDbType.VarChar),
             new SqlParameter("@studentQQ",SqlDbType.VarChar),
             new SqlParameter("@studentAddress",SqlDbType.VarChar),
             new SqlParameter("@studentMemo",SqlDbType.VarChar)
            };
            /*给参数赋值*/
            parm[0].Value = student_QQ287307421.studentNumber; //学号
            parm[1].Value = student_QQ287307421.studentName; //姓名
            parm[2].Value = student_QQ287307421.studentPassword; //登录密码
            parm[3].Value = student_QQ287307421.studentSex; //性别
            parm[4].Value = student_QQ287307421.studentClassNumber; //所在班级
            parm[5].Value = student_QQ287307421.studentBirthday; //出生日期
            parm[6].Value = student_QQ287307421.studentState; //政治面貌
            parm[7].Value = student_QQ287307421.studentPhoto; //学生照片
            parm[8].Value = student_QQ287307421.studentTelephone; //联系电话
            parm[9].Value = student_QQ287307421.studentEmail; //学生邮箱
            parm[10].Value = student_QQ287307421.studentQQ; //联系qq
            parm[11].Value = student_QQ287307421.studentAddress; //家庭地址
            parm[12].Value = student_QQ287307421.studentMemo; //附加信息

            /*执行sql进行添加*/
            return (DBHelp.ExecuteNonQuery(sql, parm) > 0) ? true : false;
        }

        /*根据studentNumber获取某条学生信息记录*/
        public static Student_QQ287307421 getSomeStudent_QQ287307421(string studentNumber)
        {
            /*构建查询sql*/
            string sql = "select * from Student_QQ287307421 where studentNumber='" + studentNumber + "'"; 
            SqlDataReader DataRead = DBHelp.ExecuteReader(sql, null);
            Student_QQ287307421 student_QQ287307421 = new Student_QQ287307421();
            /*如果查询存在记录，就包装到对象中返回*/
            if (DataRead.Read())
            {
                student_QQ287307421.studentNumber = DataRead["studentNumber"].ToString();
                student_QQ287307421.studentName = DataRead["studentName"].ToString();
                student_QQ287307421.studentPassword = DataRead["studentPassword"].ToString();
                student_QQ287307421.studentSex = DataRead["studentSex"].ToString();
                student_QQ287307421.studentClassNumber = DataRead["studentClassNumber"].ToString();
                student_QQ287307421.studentBirthday = Convert.ToDateTime(DataRead["studentBirthday"].ToString());
                student_QQ287307421.studentState = DataRead["studentState"].ToString();
                student_QQ287307421.studentPhoto = (byte[])DataRead["studentPhoto"];
                student_QQ287307421.studentTelephone = DataRead["studentTelephone"].ToString();
                student_QQ287307421.studentEmail = DataRead["studentEmail"].ToString();
                student_QQ287307421.studentQQ = DataRead["studentQQ"].ToString();
                student_QQ287307421.studentAddress = DataRead["studentAddress"].ToString();
                student_QQ287307421.studentMemo = DataRead["studentMemo"].ToString();
            }
            DataRead.Close();
            return student_QQ287307421;
        }

        /*更新学生信息实现*/
        public static bool EditStudent_QQ287307421(Student_QQ287307421 student_QQ287307421)
        {
            string sql = "update Student_QQ287307421 set studentName=@studentName,studentPassword=@studentPassword,studentSex=@studentSex,studentClassNumber=@studentClassNumber,studentBirthday=@studentBirthday,studentState=@studentState,studentPhoto=@studentPhoto,studentTelephone=@studentTelephone,studentEmail=@studentEmail,studentQQ=@studentQQ,studentAddress=@studentAddress,studentMemo=@studentMemo where studentNumber=@studentNumber";
            /*构建sql参数信息*/
            SqlParameter[] parm = new SqlParameter[] {
             new SqlParameter("@studentName",SqlDbType.VarChar),
             new SqlParameter("@studentPassword",SqlDbType.VarChar),
             new SqlParameter("@studentSex",SqlDbType.VarChar),
             new SqlParameter("@studentClassNumber",SqlDbType.VarChar),
             new SqlParameter("@studentBirthday",SqlDbType.DateTime),
             new SqlParameter("@studentState",SqlDbType.VarChar),
             new SqlParameter("@studentPhoto",SqlDbType.Image),
             new SqlParameter("@studentTelephone",SqlDbType.VarChar),
             new SqlParameter("@studentEmail",SqlDbType.VarChar),
             new SqlParameter("@studentQQ",SqlDbType.VarChar),
             new SqlParameter("@studentAddress",SqlDbType.VarChar),
             new SqlParameter("@studentMemo",SqlDbType.VarChar),
             new SqlParameter("@studentNumber",SqlDbType.VarChar)
            };
            /*为参数赋值*/
            parm[0].Value = student_QQ287307421.studentName;
            parm[1].Value = student_QQ287307421.studentPassword;
            parm[2].Value = student_QQ287307421.studentSex;
            parm[3].Value = student_QQ287307421.studentClassNumber;
            parm[4].Value = student_QQ287307421.studentBirthday;
            parm[5].Value = student_QQ287307421.studentState;
            parm[6].Value = student_QQ287307421.studentPhoto;
            parm[7].Value = student_QQ287307421.studentTelephone;
            parm[8].Value = student_QQ287307421.studentEmail;
            parm[9].Value = student_QQ287307421.studentQQ;
            parm[10].Value = student_QQ287307421.studentAddress;
            parm[11].Value = student_QQ287307421.studentMemo;
            parm[12].Value = student_QQ287307421.studentNumber;
            /*执行更新*/
            return (DBHelp.ExecuteNonQuery(sql, parm) > 0) ? true : false;
        }


        /*删除学生信息*/
        public static bool DelStudent_QQ287307421(string p)
        {
            string sql = "";
            string[] ids = p.Split(',');
            for(int i=0;i<ids.Length;i++)
            {
                if(i != ids.Length-1)
                  sql += "'" + ids[i] + "',";
                else
                  sql += "'" + ids[i] + "'";
            }
            sql = "delete from Student_QQ287307421 where studentNumber in (" + sql + ")";
            return ((DBHelp.ExecuteNonQuery(sql, null)) > 0) ? true : false;
        }


        /*查询学生信息*/
        public static System.Data.DataTable GetStudent_QQ287307421(int PageIndex, int PageSize, out int PageCount, out int RecordCount, string strWhere)
        {
            try
            {
                string strSql = " select Student_QQ287307421.*,ClassInfo.className from Student_QQ287307421,ClassInfo where 1=1 and  Student_QQ287307421.studentClassNumber=ClassInfo.classNumber";
                string strShow = "studentNumber as 学号,studentName as 姓名,studentSex as 性别,className as 所在班级,convert(char(11),studentBirthday,20) as 出生日期,studentState as 政治面貌,studentPhoto as 学生照片,studentTelephone as 联系电话,studentEmail as 学生邮箱,studentQQ as 联系qq";
                return DAL.DBHelp.ExecutePagerWhenPrimaryIsString(PageIndex, PageSize, "studentNumber", strShow, strSql, strWhere, " studentNumber asc ", out PageCount, out RecordCount);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public static DataSet getAllStudent_QQ287307421()
        {
            try
            {
                string strSql = "select * from Student_QQ287307421";
                return DBHelp.ExecuteDataSet(strSql, null);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


    }
}
