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


namespace qminoa.DA
{
	/// <summary>
	/// AccessDB.cs 定義數(shù)據(jù)庫(kù)訪問(wèn)的各種方法
	/// 
	/// Author: Sun Qiang
	/// </summary>
	public class AccessDb
	{
		//各種數(shù)據(jù)類(lèi)型、對(duì)象聲明
		protected SqlDataAdapter sqlDataAdapter;
		protected SqlCommand sqlCommand;
		protected SqlConnection sqlConnection;
		protected SqlDataReader sqlDataReader;
		protected DataSet dataSet;
		protected DataView dataView;

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

	
        
		public AccessDb()
		{			
			//構(gòu)造函數(shù)　功能：打開(kāi)數(shù)據(jù)庫(kù)連接
			sqlDataAdapter = new SqlDataAdapter();
			sqlDataAdapter.SelectCommand = new SqlCommand();
			sqlDataAdapter.SelectCommand.Connection = new SqlConnection(connString);
			sqlDataAdapter.SelectCommand.Connection.Open();

		}//End 構(gòu)造函數(shù)
		
		//簡(jiǎn)單的登錄數(shù)據(jù)庫(kù)處理過(guò)程　只需要輸入用戶(hù)名即可
		public DataSet Login(string loginUsername, string loginPassword)
		{
			//執(zhí)行存儲(chǔ)過(guò)程 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函數(shù)

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

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

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

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

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

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

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

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

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


		//返回DataSet類(lèi)型對(duì)象
		public DataSet ReturnDataSetResult()
		{
			dataSet = new DataSet();

			sqlDataAdapter.Fill(dataSet);
			return dataSet;
		}

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

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

			dataView = dataSet.Tables[0].DefaultView;

			return dataView;
		}

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

			return sqlDataReader;
		}

		//執(zhí)行SQL語(yǔ)句并返回SqlDataReader類(lèi)型對(duì)象
		public SqlDataReader ExecuteSql(string sql)
		{
			sqlCommand = new SqlCommand();
			sqlCommand.Connection = new SqlConnection(connString);
			sqlCommand.CommandText = sql;
			sqlDataReader = sqlCommand.ExecuteReader();
			return sqlDataReader;
		}

		//關(guān)閉數(shù)據(jù)庫(kù)連接
		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);
		//		}
	
	}
}