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

public interface IUser
{
    SqlDataReader GetUserLoginBySQL(string sUserName, string sPassword);
	/// <summary>
	/// 使用存儲過程實現用戶登錄
	/// </summary>
	/// <param name="sUserName"></param>
	/// <param name="sPassword"></param>
	/// <returns></returns>
	SqlDataReader GetUserLogin(string sUserName,string sPassword);

	/// <summary>
	/// 獲取所有用戶信息
	/// </summary>
	/// <returns></returns>
	SqlDataReader GetUsers();

	/// <summary>
	/// 獲取單個用戶信息
	/// </summary>
	/// <param name="nUserID"></param>
	/// <returns></returns>
	SqlDataReader GetSingleUser(int nUserID);

	/// <summary>
	/// 注冊一個新用戶
	/// </summary>
	/// <param name="sUserName"></param>
	/// <param name="sPassword"></param>
	/// <param name="sEmail"></param>
	/// <returns></returns>
	int AddUser(string sUserName,string sPassword,string sEmail);

	/// <summary>
	/// 修改用戶的信息
	/// </summary>
	/// <param name="nUserID"></param>
	/// <param name="sEmail"></param>
	/// <returns></returns>
	int UpdateUser(int nUserID,string sEmail);

	/// <summary>
	/// 修改用戶密碼
	/// </summary>
	/// <param name="nUserID"></param>
	/// <param name="sPassword"></param>
	/// <returns></returns>
	int UpdateUserPwd(int nUserID,string sPassword);
    /// <summary>
    /// 設置用戶的管理員權限
    /// </summary>
    /// <param name="nUserID"></param>
    /// <param name="bIsAdmin"></param>
    /// <returns></returns>
    int UpdateUserAdmin(int nUserID, bool bIsAdmin);

	/// <summary>
	/// 刪除用戶
	/// </summary>
	/// <param name="nUserID"></param>
	/// <returns></returns>
	int DeleteUser(int nUserID);
}

/// <summary>
/// User 的摘要說明
/// </summary>
public class User : IUser
{
	private static readonly string GETUSERS = "SELECT * FROM Users";
	private static readonly string GETSINGLEUSER = "SELECT * FROM Users WHERE UserID=";
	private static readonly string ADDUSER = "INSERT INTO Users(UserName,Password,Email,IsAdmin)VALUES";
	private static readonly string UPDATEUSER = "UPDATE Users SET Email=";
	private static readonly string UPDATEUSERADMIN = "UPDATE Users SET IsAdmin=";
	private static readonly string UPDATEUSERPASSWORD = "UPDATE Users SET Password=";
	private static readonly string DELETEUSER = "DELETE Users WHERE UserID=";
    private static readonly string GETUSERLOGINBYSQL = "SELECT UserID FROM Users WHERE UserName =";

	
	public User()
	{
		///
	}
    public SqlDataReader GetUserLoginBySQL(string sUserName, string sPassword)
    {
        ///創建鏈接
        SqlConnection myConnection = new SqlConnection(
            ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

        ///定義SQL語句
        string cmdText = GETUSERLOGINBYSQL
            + "'" + sUserName.ToString() + "'"
            + " AND Password ="
            + "'" + sPassword.ToString() + "'";
        ///創建Command
        SqlCommand myCommand = new SqlCommand(cmdText, myConnection);

        ///定義DataReader
        SqlDataReader dr = null;
        try
        {
            ///打開鏈接
            myConnection.Open();
            ///讀取數據
            dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (SqlException ex)
        {
            ///拋出異常
            throw new Exception(ex.Message, ex);
        }
        ///返回DataReader
        return dr;
    }

	public SqlDataReader GetUserLogin(string sUserName,string sPassword)
    {
       

		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///創建Command
		SqlCommand myCommand = new SqlCommand("Pr_GetUserLogin",myConnection);
		///設置為執行存儲過程
		myCommand.CommandType = CommandType.StoredProcedure;

		///添加存儲過程的參數
		SqlParameter pUserName = new SqlParameter("@UserName",SqlDbType.VarChar,32);
		pUserName.Value = sUserName;
		myCommand.Parameters.Add(pUserName);

		SqlParameter pPassword = new SqlParameter("@Password",SqlDbType.VarChar,255);
		pPassword.Value = sPassword;
		myCommand.Parameters.Add(pPassword);

		///定義DataReader
		SqlDataReader dr = null;
		try
		{
			///打開鏈接
			myConnection.Open();
			///讀取數據
			dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		///返回DataReader
		return dr;
	}

	public SqlDataReader GetUsers()
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
		///創建Command
		SqlCommand myCommand = new SqlCommand(GETUSERS,myConnection);

		///定義DataReader
		SqlDataReader dr = null;
		try
		{
			///打開鏈接
			myConnection.Open();
			///讀取數據
			dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		///返回DataReader
		return dr;
	}

	public SqlDataReader GetSingleUser(int nUserID)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);
		
		///定義SQL語句
		string cmdText = GETSINGLEUSER + "'" + nUserID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義DataReader
		SqlDataReader dr = null;
		try
		{
			///打開鏈接
			myConnection.Open();
			///讀取數據
			dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		///返回DataReader
		return dr;
	}

	public int AddUser(string sUserName,string sPassword,string sEmail)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
        string cmdText = ADDUSER + "(" 
			+ "'" + sUserName + "',"
			+ "'" + sPassword + "',"
		    + "'" + sEmail + "',"
			+ "'0'"
			+ ")";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開鏈接
			myConnection.Open();
			///執行SQL語句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	public int UpdateUser(int nUserID,string sEmail)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = UPDATEUSER
			+ "'" + sEmail + "'"
			+ " WHERE UserID=" + "'"
			+ nUserID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開鏈接
			myConnection.Open();
			///執行SQL語句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	public int UpdateUserPwd(int nUserID,string sPassword)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = UPDATEUSERPASSWORD
			+ "'" + sPassword + "'"
			+ " WHERE UserID=" + "'"
			+ nUserID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開鏈接
			myConnection.Open();
			///執行SQL語句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	public int UpdateUserAdmin(int nUserID,bool bIsAdmin)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = UPDATEUSERADMIN
			+ "'" + (bIsAdmin ? 1 : 0).ToString() + "'"
			+ " WHERE UserID=" + "'"
			+ nUserID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開鏈接
			myConnection.Open();
			///執行SQL語句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}

	public int DeleteUser(int nUserID)
	{
		///創建鏈接
		SqlConnection myConnection = new SqlConnection(
			ConfigurationManager.ConnectionStrings["SQLCONNECTIONSTRING"].ConnectionString);

		///定義SQL語句
		string cmdText = DELETEUSER
			+ "'" + nUserID.ToString() + "'";
		///創建Command
		SqlCommand myCommand = new SqlCommand(cmdText,myConnection);

		///定義返回值
		int nResult = -1;

		try
		{
			///打開鏈接
			myConnection.Open();
			///執行SQL語句
			nResult = myCommand.ExecuteNonQuery();
		}
		catch(SqlException ex)
		{
			///拋出異常
			throw new Exception(ex.Message,ex);
		}
		finally
		{   ///關閉鏈接
			myConnection.Close();
		}
		///返回nResult
		return nResult;
	}
}