?using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


/// <summary>
/// AdminSQL 的摘要說(shuō)明
/// </summary>
public class AdminSQL
{
    //連接數(shù)據(jù)庫(kù)的字符串
    private string adminConnStr;
    //數(shù)據(jù)庫(kù)連接對(duì)象
    private SqlConnection adminConn;

	public AdminSQL()
	{
		//
		// TODO: 在此處添加構(gòu)造函數(shù)邏輯
		//
	}
    public void Open()
    {
        adminConnStr = "Data Source=.;Initial Catalog=selectCourses;Integrated Security=True";
        //創(chuàng)建數(shù)據(jù)庫(kù)連接對(duì)象
        adminConn = new SqlConnection(adminConnStr);
        //打開數(shù)據(jù)庫(kù)連接
        adminConn.Open();
    }

    //關(guān)閉數(shù)據(jù)庫(kù)連接
    public void Close()
    {
        adminConn.Close();
    }


    public int ExecuteSQL(string str)
    {
        SqlCommand cmd = new SqlCommand(str, adminConn);
        //返回?cái)?shù)據(jù)庫(kù)操作影響的行數(shù)
        int aff = cmd.ExecuteNonQuery();
        return aff;
    }


    //讀取數(shù)據(jù)庫(kù)中的數(shù)據(jù)并存放在一個(gè)數(shù)據(jù)集Dataset里面
    public DataSet GetDataSet(string strComm, String strT)
    {
        SqlDataAdapter ad = new SqlDataAdapter(strComm, adminConn);
        //創(chuàng)建Dataset對(duì)象
        DataSet ds = new DataSet();
        //填充數(shù)據(jù)
        ad.Fill(ds, strT);
        return ds;
    }

    //調(diào)用存儲(chǔ)過(guò)程判斷管理員輸入的學(xué)號(hào)和密碼和數(shù)據(jù)庫(kù)的是否一樣。函數(shù)返回零說(shuō)明輸入的學(xué)號(hào)和密碼與數(shù)據(jù)庫(kù)
    //不一樣，返回值大于零說(shuō)明輸入的學(xué)號(hào)和密碼合法。
    public int AdminLogin(string adminid, string pwd)
    {
        //創(chuàng)建數(shù)據(jù)庫(kù)操作命令的對(duì)象
        SqlCommand cmd = new SqlCommand("AdminLogin", adminConn);
        //指明Sql命令的操作類型是使用存儲(chǔ)過(guò)程
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter admin = new SqlParameter("@AdminID", SqlDbType.NVarChar, 50);
        admin.Value = adminid;
        cmd.Parameters.Add(admin);

        SqlParameter Pwd = new SqlParameter("@AdminPwd", SqlDbType.NVarChar, 50);
        Pwd.Value = pwd;
        cmd.Parameters.Add(Pwd);

        SqlParameter aff = new SqlParameter("@returnvalue", SqlDbType.Int, 4);
        //說(shuō)明該存儲(chǔ)過(guò)程的參數(shù)是OUTPUT類型
        aff.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(aff);


        //執(zhí)行存儲(chǔ)過(guò)程
        cmd.ExecuteNonQuery();
        //取得存儲(chǔ)過(guò)程OUTPUT參數(shù)的值作為函數(shù)的返回值
        if (aff.Value.ToString() != "")
            return (int)aff.Value;
        else
            return 0;

    }


    //調(diào)用存儲(chǔ)過(guò)程判斷管理員是否已經(jīng)登錄。
    public int AdminIfLogin(string adminid)
    {
        SqlCommand cmd = new SqlCommand("AdminIfLogin", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter admin = new SqlParameter("@AdminID", SqlDbType.NVarChar, 50);
        admin.Value = adminid;
        cmd.Parameters.Add(admin);

        SqlParameter aff = new SqlParameter("@returnvalue", SqlDbType.Int, 4);
        aff.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(aff);

        cmd.ExecuteNonQuery();
        if ((int)aff.Value!= 0)
            return (int)aff.Value;
        else
            return 0;
    }


    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)修改學(xué)生記錄功能
    public bool ModifyStudent(string StudentID, string StudentName, string StudentPwd)
    {
        SqlCommand cmd = new SqlCommand("ModifyStudentInfo", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter stu = new SqlParameter("@StudentID", SqlDbType.NVarChar, 50);
        stu.Value = StudentID;
        cmd.Parameters.Add(stu);

        SqlParameter stuname = new SqlParameter("@StudentName", SqlDbType.NVarChar, 50);
        stuname.Value = StudentName;
        cmd.Parameters.Add(stuname);

        SqlParameter Pwd = new SqlParameter("@StudentPwd", SqlDbType.NVarChar, 50);
        Pwd.Value = StudentPwd;
        cmd.Parameters.Add(Pwd);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)刪除學(xué)生記錄功能
    public bool DeleteStudent(string StudentID)
    {
        SqlCommand cmd = new SqlCommand("DeleteStudent", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter stu = new SqlParameter("@StudentID", SqlDbType.NVarChar, 50);
        stu.Value = StudentID;
        cmd.Parameters.Add(stu);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)添加學(xué)生記錄功能
    public int AddStudent(string StudentID, string StudentName, string StudentPwd)
    {
        SqlCommand cmd = new SqlCommand("AddStudent", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter stu = new SqlParameter("@StudentID", SqlDbType.NVarChar, 50);
        stu.Value = StudentID;
        cmd.Parameters.Add(stu);

        SqlParameter stuname = new SqlParameter("@StudentName", SqlDbType.NVarChar, 50);
        stuname.Value = StudentName;
        cmd.Parameters.Add(stuname);

        SqlParameter Pwd = new SqlParameter("@StudentPwd", SqlDbType.NVarChar, 50);
        Pwd.Value = StudentPwd;
        cmd.Parameters.Add(Pwd);

        SqlParameter aff = new SqlParameter("@rowaffected", SqlDbType.Int, 4);
        aff.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(aff);

        cmd.ExecuteNonQuery();
        if (aff.Value.ToString() != "")
            return (int)aff.Value;
        else
            return 0;
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)修改教師記錄功能
    public bool ModifyTeacher(string TeacherID, string TeacherName, string TeacherPwd)
    {
        SqlCommand cmd = new SqlCommand("ModifyTeacherInfo", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter tea = new SqlParameter("@TeacherID", SqlDbType.NVarChar, 50);
        tea.Value = TeacherID;
        cmd.Parameters.Add(tea);

        SqlParameter teaname = new SqlParameter("@TeacherName", SqlDbType.NVarChar, 50);
        teaname.Value = TeacherName;
        cmd.Parameters.Add(teaname);

        SqlParameter Pwd = new SqlParameter("@TeacherPwd", SqlDbType.NVarChar, 50);
        Pwd.Value = TeacherPwd;
        cmd.Parameters.Add(Pwd);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)刪除教師記錄功能
    public bool DeleteTeacher(string TeacherID)
    {
        SqlCommand cmd = new SqlCommand("DeleteTeacher", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter tea = new SqlParameter("@TeacherID", SqlDbType.NVarChar, 50);
        tea.Value = TeacherID;
        cmd.Parameters.Add(tea);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)添加教師記錄功能
    public int AddTeacher(string TeacherID, string TeacherName, string TeacherPwd)
    {
        SqlCommand cmd = new SqlCommand("AddTeacher", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter tea = new SqlParameter("@TeacherID", SqlDbType.NVarChar, 50);
        tea.Value = TeacherID;
        cmd.Parameters.Add(tea);

        SqlParameter teaname = new SqlParameter("@TeacherName", SqlDbType.NVarChar, 50);
        teaname.Value = TeacherName;
        cmd.Parameters.Add(teaname);

        SqlParameter Pwd = new SqlParameter("@TeacherPwd", SqlDbType.NVarChar, 50);
        Pwd.Value = TeacherPwd;
        cmd.Parameters.Add(Pwd);

        SqlParameter aff = new SqlParameter("@rowaffected", SqlDbType.Int, 4);
        aff.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(aff);

        cmd.ExecuteNonQuery();
        if (aff.Value.ToString() != "")
            return (int)aff.Value;
        else
            return 0;
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)修改通知信息功能
    public bool ModifyNotice(string NoticeID, string NoticeTitle, string NoticeContent, DateTime NoticeDate, String NoticeObject)
    {
        SqlCommand cmd = new SqlCommand("ModifyNotice", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter ntID = new SqlParameter("@NoticeID", SqlDbType.NVarChar, 50);
        ntID.Value = NoticeID;
        cmd.Parameters.Add(ntID);

        SqlParameter ntTitle = new SqlParameter("@NoticeTitle", SqlDbType.NVarChar, 50);
        ntTitle.Value = NoticeTitle;
        cmd.Parameters.Add(ntTitle);

        SqlParameter ntContent = new SqlParameter("@NoticeContent", SqlDbType.NVarChar, 50);
        ntContent.Value = NoticeContent;
        cmd.Parameters.Add(ntContent);

        SqlParameter ntDate = new SqlParameter("@NoticeDate", SqlDbType.DateTime, 8);
        ntDate.Value = NoticeDate;
        cmd.Parameters.Add(ntDate);

        SqlParameter ntObject = new SqlParameter("@NoticeObject", SqlDbType.NVarChar, 50);
        ntObject.Value = NoticeObject;
        cmd.Parameters.Add(ntObject);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)刪除通知信息功能
    public bool DeleteNotice(string NoticeID)
    {
        SqlCommand cmd = new SqlCommand("DeleteNotice", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter ntID = new SqlParameter("@NoticeID", SqlDbType.NVarChar, 50);
        ntID.Value = NoticeID;
        cmd.Parameters.Add(ntID);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)添加通知信息功能
    public int AddNotice(string NoticeID, string NoticeTitle, string NoticeContent,DateTime NoticeDate,String NoticeObject)
    {
        SqlCommand cmd = new SqlCommand("AddNotice", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter ntID = new SqlParameter("@NoticeID", SqlDbType.NVarChar, 50);
        ntID.Value = NoticeID;
        cmd.Parameters.Add(ntID);

        SqlParameter ntTitle = new SqlParameter("@NoticeTitle", SqlDbType.NVarChar, 50);
        ntTitle.Value =NoticeTitle;
        cmd.Parameters.Add(ntTitle);

        SqlParameter ntContent = new SqlParameter("@NoticeContent", SqlDbType.NVarChar, 50);
        ntContent.Value = NoticeContent;
        cmd.Parameters.Add(ntContent);

        SqlParameter ntDate = new SqlParameter("@NoticeDate", SqlDbType.DateTime, 8);
        ntDate.Value =NoticeDate;
        cmd.Parameters.Add(ntDate);

        SqlParameter ntObject = new SqlParameter("@NoticeObject", SqlDbType.NVarChar, 50);
        ntObject.Value = NoticeObject;
        cmd.Parameters.Add(ntObject);

        SqlParameter aff = new SqlParameter("@rowaffected", SqlDbType.Int, 4);
        aff.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(aff);

        cmd.ExecuteNonQuery();
        if (aff.Value.ToString() != "")
            return (int)aff.Value;
        else
            return 0;
    }

    //調(diào)用存儲(chǔ)過(guò)程實(shí)現(xiàn)修改選課期限功能
    public bool UpdatePeriod(string BeginTime, string EndTime)
    {
        SqlCommand cmd = new SqlCommand("UpdatePeriod", adminConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲(chǔ)過(guò)程添加參數(shù)
        SqlParameter bg = new SqlParameter("@BeginTime", SqlDbType.DateTime, 8);
        bg.Value =BeginTime;
        cmd.Parameters.Add(bg);

        SqlParameter end = new SqlParameter("@EndTime", SqlDbType.DateTime, 8);
        end.Value = EndTime;
        cmd.Parameters.Add(end);

        try
        {
            cmd.ExecuteNonQuery();
            return true;
        }
        catch
        {
            return false;
        }
    }
}