using System;
using System.Data;
using System.Data.SqlClient;


namespace qminoa.DA
{
	/// <summary>
	/// AccessDB.cs 定義數據庫訪問的各種方法
	/// 
	/// Author: Sun Qiang
	/// </summary>
	public class AccessDb
	{
		//各種數據類型、對象聲明
		protected SqlDataAdapter sqlDataAdapter;
		protected SqlCommand sqlCommand;
		protected SqlConnection sqlConnection;
		protected SqlDataReader sqlDataReader;
		protected DataSet dataSet;
		protected DataView dataView;

		//數據庫連接字符串
		string connString = System.Configuration.ConfigurationSettings.AppSettings.Get("ConnectionString");

	
        
		public AccessDb()
		{			
			//構造函數　功能：打開數據庫連接
			sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.SelectCommand = new SqlCommand();
			sqlDataAdapter.SelectCommand.Connection = new SqlConnection(connString);
			sqlDataAdapter.SelectCommand.Connection.Open();

		}//End 構造函數
		
		//簡單的登錄數據庫處理過程　只需要輸入用戶名即可
		public DataSet Login(string loginUsername, string loginPassword)
		{
			//執行存儲過程 cpSempleLogin
			sqlDataAdapter.SelectCommand.CommandText = "cpSempleLogin";
			sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

			sqlDataAdapter.SelectCommand.Parameters.Add("@LoginID",SqlDbType.VarChar,16);
			sqlDataAdapter.SelectCommand.Parameters["@LoginID"].Value = loginUsername;

			dataSet = new DataSet();
			
			sqlDataAdapter.Fill(dataSet);

			sqlDataAdapter.SelectCommand.Connection.Close();

			return dataSet;
		}//End Login函數

		//接收存儲過程名
		public void SendStoredProcedure(string storedProcedureName)
		{
			sqlDataAdapter.SelectCommand.Parameters.Clear();
			sqlDataAdapter.SelectCommand.CommandText = storedProcedureName;
			sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
		}

		//接收存儲過程參數名、參數名類型、參數值 參數值類型重載(int paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, int paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存儲過程參數名、參數名類型、參數值 參數值類型重載(string paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, string paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存儲過程參數名、參數名類型、參數值 參數值類型重載(bool paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, bool paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存儲過程參數名、參數名類型、參數值 參數值類型重載(byte paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, byte paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存儲過程參數名、參數名類型、參數值 參數值類型重載(char paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, char paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//接收存儲過程參數名、參數名類型、參數值 參數值類型重載(object paraValue)
		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, object paraValue)
		{
			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType);
			sqlDataAdapter.SelectCommand.Parameters[paraName].Value = paraValue;
		}

		//執行Sql語句或者存儲過程，但不需返回值
		public void ExecuteNoReturn()
		{
			sqlDataAdapter.SelectCommand.ExecuteNonQuery();
		}

		public void ExecuteReader()
		{
			sqlDataAdapter.SelectCommand.ExecuteReader();
			//sqlDataAdapter.UpdateCommand = "";
			//sqlDataAdapter.UpdateCommand.
		}


		//返回DataSet類型對象
		public DataSet ReturnDataSetResult()
		{
			dataSet = new DataSet();

			sqlDataAdapter.Fill(dataSet);
			return dataSet;
		}

		//重載ReturnDataSetResult()
		//返回DataSet類型對象到指定表中
		public DataSet ReturnDataSetResult(string tableName)
		{
			sqlDataAdapter.Fill(dataSet, tableName);
			return dataSet;
		}

		//返回DataView類型引用
		public DataView ReturnDataViewResult()
		{
			ReturnDataSetResult();

			dataView = dataSet.Tables[0].DefaultView;

			return dataView;
		}

		//返回SqlDataReader類型引用
		public SqlDataReader ReturnSqlDataReaderResult()
		{
			//sqlDataReader = new SqlDataReader();
            sqlDataReader = sqlDataAdapter.SelectCommand.ExecuteReader();

			return sqlDataReader;
		}

		//執行SQL語句并返回SqlDataReader類型對象
		public SqlDataReader ExecuteSql(string sql)
		{
			sqlCommand = new SqlCommand();
			sqlCommand.Connection = new SqlConnection(connString);
			sqlCommand.CommandText = sql;
			sqlDataReader = sqlCommand.ExecuteReader();
			return sqlDataReader;
		}

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


		public void ClearParameters()
		{
			sqlDataAdapter.SelectCommand.Parameters.Clear();
		}

		//		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, int paraSize)
		//		{
		//			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType, paraSize);
		//		}
		//
		//		public void SendStoredProcedureParameters(string paraName, SqlDbType paraType, int paraSize, string paraSourceColumn)
		//		{
		//			sqlDataAdapter.SelectCommand.Parameters.Add(paraName, paraType, paraSize, paraSourceColumn);
		//		}
	
	}
}