?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>
/// StuSQL 的摘要說明
/// </summary>
   
public class StuSQL
{   
    //連接數據庫的字符串
    private string stuConnStr;
    //數據庫連接對象
    private SqlConnection stuConn;
    public StuSQL()
	{
		//
		// TODO: 在此處添加構造函數邏輯
		//
	}
    public void Open()
    {
        stuConnStr = "Data Source=.;Initial Catalog=selectCourses;Integrated Security=True";
        //創建數據庫連接對象
        stuConn = new SqlConnection(stuConnStr);
        //打開數據庫連接
        stuConn.Open();
    }

    public void Close()
    {
        //關閉數據庫連接
        stuConn.Close();
    }

    
    public int ExecuteSQL(string str)
    {
        SqlCommand cmd = new SqlCommand(str, stuConn);
        //返回數據庫操作影響的行數
        int aff = cmd.ExecuteNonQuery();
        return aff;
    }


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

    //調用存儲過程實現學生選課功能
    public int ElectCourse(string stuid,string cursid)
    {
        //創建數據庫操作命令的對象
        SqlCommand cmd=new SqlCommand("ElectCourse",stuConn);
        //指明Sql命令的操作類型是使用存儲過程
        cmd.CommandType=CommandType.StoredProcedure;

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

         SqlParameter  curs=new SqlParameter("@CourseID",SqlDbType.NVarChar,50);
         curs.Value=cursid;
         cmd.Parameters.Add(curs);

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

        //執行存儲過程
        cmd.ExecuteNonQuery();
        //取得存儲過程OUTPUT參數的值作為函數的返回值
        if(aff.Value.ToString()!="")
            return (int)aff.Value;
        else
            return 0;
    }
    //調用存儲過程實現學生退選課程功能
    public int CancelCourse(string stuid, string cursid)
    {
        SqlCommand cmd=new SqlCommand("CancelCourse",stuConn);
        cmd.CommandType=CommandType.StoredProcedure;

        //給存儲過程添加參數
        SqlParameter stu=new SqlParameter("@StudentID",SqlDbType.NVarChar,50);
        stu.Value=stuid;
        cmd.Parameters.Add(stu);
        
        SqlParameter  curs=new SqlParameter("@CourseID",SqlDbType.NVarChar,50);
        curs.Value=cursid;
        cmd.Parameters.Add(curs);

        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; 
    }

    //調用存儲過程判斷學生是否選擇了某門課程，函數返回零說明該學生沒有選擇這門課程，
    //返回值大于零說明該學生選擇了這門課
    public int CourseSelected(string stuid, string cursid)
    {
        SqlCommand cmd = new SqlCommand("CourseSelected", stuConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

        SqlParameter curs = new SqlParameter("@CourseID", SqlDbType.NVarChar, 50);
        curs.Value = cursid;
        cmd.Parameters.Add(curs);

        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; 
        
    }

    //調用存儲過程判斷學生用戶輸入的學號和選課密碼和數據庫的是否一樣。函數返回零說明輸入的學號和選課密碼與數據庫
    //不一樣，返回值大于零說明輸入的學號和選課密碼合法。
    public int StudentLogin(string stuid, string pwd)
    {
        SqlCommand cmd = new SqlCommand("StudentLogin", stuConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

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

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

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

    }


    //調用存儲過程判斷學生是否已經登錄。
    public int StudentIfLogin(string stuid)
    {
        SqlCommand cmd = new SqlCommand("StudentIfLogin", stuConn);
        cmd.CommandType = CommandType.StoredProcedure;

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

        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;
    }


     //調用存儲過程判斷學生是否在允許的時期內進行選課
    public bool IsValidPeriod()
    {
        SqlCommand cmd = new SqlCommand("IsValidPeriod", stuConn);
        cmd.CommandType = CommandType.StoredProcedure;

        //給存儲過程添加參數
        SqlParameter paraPeriodID = new SqlParameter("@PeriodID", SqlDbType.Int,4);
        paraPeriodID.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(paraPeriodID);

        cmd.ExecuteNonQuery();
        if (paraPeriodID.Value.ToString() != "")
        {
            if((int)paraPeriodID.Value>0)
                return true;
            else
                return false;
        }
        else
        {
            return false;
        }
    }

}