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

namespace Hugo.BookShop.DbBase
{
	/// <summary>
	/// base.
	/// </summary>
	public abstract class Base
	{

		#region "Fields of base calss"

		/// <summary>
		/// connecting to Database
		/// </summary>
		protected static string strConn = ConfigurationSettings.AppSettings["strConnection"];

		/// <summary>
		/// SQL command
		/// </summary>
		protected static string strSQL;

		#endregion
		

		#region "Properties of base class"
		private int m_ID;
		private string m_Name;

		/// <summary>
		/// Property：ID
		/// </summary>
		public int ID
		{
			get
			{
				return m_ID;
			}
			set
			{
				m_ID = value;
			}
		}


		/// <summary>
		/// name
		/// </summary>
		public string Name
		{
			get
			{
				return m_Name;
			}
			set
			{
				m_Name = value;
			}
		}

		#endregion


		#region "Functions of base class"
		public Base()
		{
			//
			// TODO: Add constructor logic here
			//
		}

		/// <summary>
		/// executing SQL commands
		/// </summary>
		/// <param name="strSQL">string</param>
		/// <returns>return int</returns>
		protected static int ExecuteSql(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(strConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				myCmd.ExecuteNonQuery();
				return 0;
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		///executing SQL commands
		/// </summary>
		/// <param name="strSQL">要執行的SQL語句,為字符串類型string</param>
		/// <returns>返回執行情況,整形int</returns>
		protected static int ExecuteSqlEx(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(strConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			
			try
			{
				myCn.Open();				
				SqlDataReader myReader = myCmd.ExecuteReader();
				if(myReader.Read())
				{
					return 0;
				}	
				else
				{
					throw new Exception("Value Unavailable!");
				}
			}
			catch(System.Data.SqlClient.SqlException e)
			{								
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		/// get dataset
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(DataSet)</returns>
		protected static DataSet ExecuteSql4Ds(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(strConn);			
			try
			{
				myCn.Open();
				SqlDataAdapter sda = new SqlDataAdapter(strSQL,myCn);
				DataSet ds = new DataSet("ds");
				sda.Fill(ds);
				return ds;
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCn.Close();
			}
		}


		/// <summary>
		/// get single value
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(int)</returns>
		protected static int ExecuteSql4Value(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(strConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				object r = myCmd.ExecuteScalar();
				if(Object.Equals(r,null))
				{
					throw new Exception("value unavailable！");
				}
				else
				{
					return (int)r;
				}				
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}		


		/// <summary>
		/// get object
		/// </summary>
		/// <param name="strSQL">(string)</param>
		/// <returns>(object)</returns>
		protected static object ExecuteSql4ValueEx(string strSQL)
		{
			SqlConnection myCn = new SqlConnection(strConn);			
			SqlCommand myCmd = new SqlCommand(strSQL,myCn);
			try
			{
				myCn.Open();
				object r = myCmd.ExecuteScalar();
				if(Object.Equals(r,null))
				{
					throw new Exception("object unavailable!");
				}
				else
				{
					return r;
				}				
			}
			catch(System.Data.SqlClient.SqlException e)
			{				
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}


		/// <summary>
		/// execute multipul SQL commands 
		/// </summary>
		/// <param name="strSQLs">string</param>
		/// <returns>int</returns>
		protected static int ExecuteSqls(string[] strSQLs)
		{
			SqlConnection myCn = new SqlConnection(strConn);			
			SqlCommand myCmd = new SqlCommand();			
			int j=strSQLs.Length;

			try
			{
				myCn.Open();				
			}
			catch(System.Data.SqlClient.SqlException e)
			{
				throw new Exception(e.Message);
			}
			SqlTransaction myTrans = myCn.BeginTransaction();

			try
			{											
				myCmd.Connection = myCn;				
				myCmd.Transaction = myTrans;

				foreach(string str in strSQLs)
				{
					myCmd.CommandText = str;
					myCmd.ExecuteNonQuery();
				}
				myTrans.Commit();
				return 0;
			}
			catch(System.Data.SqlClient.SqlException e)
			{			
				myTrans.Rollback();
				throw new Exception(e.Message);
			}
			finally
			{
				myCmd.Dispose();
				myCn.Close();
			}
		}

		#endregion
	}
}