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

using System.Collections;

namespace Teach_Manager.Dao
{
	/// <summary>
	/// CommonSql 的摘要說明。
	/// </summary>
	public class CommonSql:CommonInterface
	{
		private SqlConnection conn = null;
		private SqlCommand cmd = null;
		private SqlTransaction trans = null;
		private String connstr = null;

		//構造函數
		public CommonSql(String ConnStr_Param)
		{
			connstr = ConnStr_Param;
			Initial();

		}

		
		/// <summary>
		/// 初始化
		/// </summary>
		private void Initial()
		{
			try
			{
				if (connstr == null)
				{
				}
				this.conn = new SqlConnection(connstr);
				this.cmd = new SqlCommand();
				cmd.Connection = this.conn;
				this.conn.Open();
			}

			catch (Exception e)
			{
				throw e;

			}

		}

		
		/// 開始一個事務
		/// 
		public void BeginTrans()
		{
			trans = conn.BeginTransaction();
			cmd.Transaction = trans;
		}

		/// 提交一個事務
		/// 
		public void CommitTrans()
		{
			trans.Commit();
		}

		/// 回滾一個事務
		/// 
		public void RollbackTrans()
		{
			trans.Rollback();
		}

		/// 執行SQL語句
		/// 
		public void Execute(String sql)
		{
			try
			{
				cmd.CommandText = sql;
				cmd.ExecuteNonQuery();
			}

			catch (Exception e)
			{
				throw e;
			}
		}
		//執行sql  返回第一行,第一列
		public string return_str(string str_sql)
		{
			string str_return="";
			try
			{
				cmd.CommandText=str_sql;
				str_return=cmd.ExecuteScalar().ToString();				
			}
			catch (Exception e)
			{
				throw e;
			}
			return str_return;
		}
		/// 執行SQL語句，填充到指定的DataTable中，返回DataSet
		/// 
		public DataSet ExeForDst(String QueryString, String strTable)
		{
			DataSet ds = new DataSet();
			SqlDataAdapter ad = new SqlDataAdapter();
			cmd.CommandText = QueryString;

			try
			{
				ad.SelectCommand = cmd;
				ad.Fill(ds, strTable);

			}
			catch (Exception e)
			{
				throw e;
			}

			return ds;
		}

		/// 執行一段SQL語句，返回DataSet結果集
		/// 
		public DataSet ExeForDst(String QueryString)
		{
			DataSet ds = new DataSet();
			SqlDataAdapter ad = new SqlDataAdapter();
			cmd.CommandText = QueryString;

			try
			{
				ad.SelectCommand = cmd;
				ad.Fill(ds);

			}
			catch (Exception e)
			{
				throw e;
			}

			return ds;
		}

		
		/// 執行SQL語句，返回DataTable
		/// 
		public DataTable ExeForDtl(String QueryString, String TableName)
		{
			try
			{
				DataSet ds;
				DataTable dt;
				ds = ExeForDst(QueryString, TableName);
				dt = ds.Tables[TableName];
				ds = null;

				return dt;
			}
			catch
			{
				throw;
			}
			finally
			{
			}
		}

		/// 執行SQL語句，返回默認DataTable
		/// 
		public DataTable ExeForDtl(String QueryString)
		{
			try
			{
				DataSet ds;
				DataTable dt;
				ds = ExeForDst(QueryString);
				dt = ds.Tables[0];
				ds = null;

				return dt;
			}
			catch (Exception ee)
			{
				throw new Exception(ee.Message);
			}
			finally
			{
			}
		}

		/// 執行SQL語句，返回IDataReader接口
		/// 
		public IDataReader ExeForDtr(String QueryString)
		{
			try
			{
				cmd.CommandText = QueryString;
				return cmd.ExecuteReader();
			}
			catch
			{
				throw;
			}
		}

		/// 返回IDbCommand接口
		/// 
		public IDbCommand GetCommand()
		{
			try
			{
				return this.cmd;
			}
			catch (Exception e)
			{
				throw e;
			}
		}

		
		/// 返回IDbConnection接口
		/// 
		public IDbConnection GetConn()
		{
			return this.conn;
		}

		/// 關閉數據庫連接
		/// 
		public void Close()
		{
			if (conn.State.ToString().ToUpper() == "OPEN")
			{
				this.conn.Close();
			}
		}

		/// 用來執行帶有參數的SQL語句（不是存儲過程）
		/// 
		public void ExecuteNonQuery(string SQLText, string[] Parameters, string[] ParametersValue)
		{
			try
			{
				this.cmd.CommandText = SQLText;
				for (int i = 0; i < Parameters.Length; i++)
				{
					this.cmd.Parameters.Add("@" + Parameters[i].ToString(), ParametersValue[i].ToString());
				}

				this.cmd.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				throw e;
			}
		}

		/// 執行存儲過程
		/// 
		public void ExecuteSP(string StoredProcedureName, string[] Parameters, string[] ParametersValue, string[] ParametersType)
		{
			try
			{
				this.cmd.CommandText = StoredProcedureName;
				this.cmd.CommandType = CommandType.StoredProcedure;

				for (int i = 0; i < Parameters.Length; i++)
				{
					SqlParameter myParm = this.cmd.Parameters.Add("@" + Parameters[i], ParametersType[i].ToString());
					myParm.Value = ParametersValue[i];

				}

				this.cmd.ExecuteNonQuery();
			}
			catch (Exception e)
			{
				throw e;
			}
		}

		/// 執行存儲過程,得到結果集DataSet
		/// 
		public DataSet ExcuteSp(string sqname, string[,] array)
		{
			try
			{
				DataSet dset = new DataSet();
				SqlDataAdapter dp = new SqlDataAdapter();

				SqlCommand cmmd = new SqlCommand();
				dp.SelectCommand = cmmd;

				dp.SelectCommand.Connection = this.conn;
				dp.SelectCommand.CommandType = CommandType.StoredProcedure;
				dp.SelectCommand.CommandText = sqname;
				for (int i = 0; i <= array.GetUpperBound(0); i++)
				{
					if (array[i, 0] != null)
					{
						SqlParameter Parm = dp.SelectCommand.Parameters.Add(array[i, 0].ToString(), SqlDbType.NVarChar);
						Parm.Value = array[i, 1].ToString();
					}
				}
				dp.Fill(dset, "Default");
				return dset;
			}
			catch (Exception e)
			{
				throw e;
			}
		}

	

		public object ExecuteScalar(string QueryString)
		{
			cmd.CommandText = QueryString;

			try
			{
				return cmd.ExecuteScalar();
			}
			catch (Exception e)
			{
				throw e;
			}

			//			return null;;
		}

		public IDataAdapter getDataAdapter(string sql)
		{
			return new SqlDataAdapter(sql, this.conn);
		}

	}
}