?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>
/// TeacherSQL 的摘要說明
/// </summary>
public class TeaSQL
{
    //連接數(shù)據(jù)庫的字符串
    private string teaConnStr;
    //數(shù)據(jù)庫連接對象
    private SqlConnection teaConn;
	public TeaSQL()
	{
		//
		// TODO: 在此處添加構(gòu)造函數(shù)邏輯
		//
	}

    public void Open()
    {
        teaConnStr = "Data Source=.;Initial Catalog=selectCourses;Integrated Security=True";
        //創(chuàng)建數(shù)據(jù)庫連接對象
        teaConn = new SqlConnection(teaConnStr);
        //打開數(shù)據(jù)庫連接
        teaConn.Open();
    }

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


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

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

    //調(diào)用存儲(chǔ)過程判斷老師用戶輸入的學(xué)號(hào)和選課密碼和數(shù)據(jù)庫的是否一樣。函數(shù)返回零說明輸入的學(xué)號(hào)和選課密碼與數(shù)據(jù)庫
    //不一樣，返回值大于零說明輸入的學(xué)號(hào)和選課密碼合法。
    public int TeacherLogin(string teaid, string pwd)
    {
        //創(chuàng)建數(shù)據(jù)庫操作命令的對象
        SqlCommand cmd = new SqlCommand("TeacherLogin", teaConn);
        //指明Sql命令的操作類型是使用存儲(chǔ)過程
        cmd.CommandType = CommandType.StoredProcedure;

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

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

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

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

    }


    //調(diào)用存儲(chǔ)過程判斷老師是否已經(jīng)登錄。
    public int TeacherIfLogin(string teaid)
    {
        SqlCommand cmd = new SqlCommand("TeacherIfLogin", teaConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

        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ǔ)過程實(shí)現(xiàn)往數(shù)據(jù)庫中添加一條新的課程記錄
    public bool AddCourse(string CourseID, string CourseName, int CourseHours, int CourseScore, String TeacherID, int UpperLimitStudentNumber)
    {
        SqlCommand cmd = new SqlCommand("AddCourse", teaConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

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

        SqlParameter cursHours = new SqlParameter("@CourseHours", SqlDbType.Int,4);
        cursHours.Value = CourseHours;
        cmd.Parameters.Add(cursHours);

        SqlParameter cursScore = new SqlParameter("@CourseScore", SqlDbType.Int, 4);
        cursScore.Value = CourseScore;
        cmd.Parameters.Add(cursScore);

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

        SqlParameter stunum = new SqlParameter("@UpperLimitStudentNumber", SqlDbType.Int, 4);
        stunum.Value = UpperLimitStudentNumber;
        cmd.Parameters.Add(stunum);

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

    //調(diào)用存儲(chǔ)過程實(shí)現(xiàn)退選課程功能
    public bool RemoveCourse(string cursid)
    {
        SqlCommand cmd = new SqlCommand("RemoveCourse", teaConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

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

    //調(diào)用存儲(chǔ)過程實(shí)現(xiàn)修改課程信息功能
    public bool ModifyCourse(string CourseID, string CourseName, int CourseHours, int CourseScore, int UpperLimitStudentNumber)
    {
        SqlCommand cmd = new SqlCommand("ModifyCourse", teaConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

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

        SqlParameter cursHours = new SqlParameter("@CourseHours", SqlDbType.Int, 4);
        cursHours.Value = CourseHours;
        cmd.Parameters.Add(cursHours);

        SqlParameter cursScore = new SqlParameter("@CourseScore", SqlDbType.Int, 4);
        cursScore.Value = CourseScore;
        cmd.Parameters.Add(cursScore);

        SqlParameter stunum = new SqlParameter("@UpperLimitStudentNumber", SqlDbType.Int, 4);
        stunum.Value = UpperLimitStudentNumber;
        cmd.Parameters.Add(stunum);

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

}