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

namespace ST_GROUP.FlowerPrearrange
{
	/// <summary>
	/// base.
	/// </summary>
	public abstract class ST_Base
	{

		#region "Fields of base calss"

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

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

		#endregion
		

		#region "Properties of base class"
		private int ST_m_ID;
		private string ST_m_Name;

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


		/// <summary>
		/// name
		/// </summary>
		public string ST_Name
		{
			get
			{
				return ST_m_Name;
			}
			set
			{
				ST_m_Name = value;
			}
		}

		#endregion


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

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


		/// <summary>
		///executing SQL commands
		/// </summary>
		/// <param name="ST_strSQL">要執(zhí)行的SQL語(yǔ)句,為字符串類型string</param>
		/// <returns>返回執(zhí)行情況,整形int</returns>
		protected static int ST_ExecuteSqlEx(string ST_strSQL)
		{
			SqlConnection ST_myCn = new SqlConnection(ST_strConn);			
			SqlCommand ST_myCmd = new SqlCommand(ST_strSQL,ST_myCn);
			
			try
			{
				ST_myCn.Open();				
				SqlDataReader myReader = ST_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
			{
				ST_myCmd.Dispose();
				ST_myCn.Close();
			}
		}


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


		/// <summary>
		/// get single value
		/// </summary>
		/// <param name="ST_strSQL">(string)</param>
		/// <returns>(int)</returns>
		protected static int ST_ExecuteSql4Value(string ST_strSQL)
		{
			SqlConnection ST_myCn = new SqlConnection(ST_strConn);			
			SqlCommand ST_myCmd = new SqlCommand(ST_strSQL,ST_myCn);
			try
			{
				ST_myCn.Open();
				object r = ST_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
			{
				ST_myCmd.Dispose();
				ST_myCn.Close();
			}
		}		


		/// <summary>
		/// get object
		/// </summary>
		/// <param name="ST_strSQL">(string)</param>
		/// <returns>(object)</returns>
		protected static object ST_ExecuteSql4ValueEx(string ST_strSQL)
		{
			SqlConnection ST_myCn = new SqlConnection(ST_strConn);			
			SqlCommand ST_myCmd = new SqlCommand(ST_strSQL,ST_myCn);
			try
			{
				ST_myCn.Open();
				object r = ST_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
			{
				ST_myCmd.Dispose();
				ST_myCn.Close();
			}
		}


		/// <summary>
		/// execute multipul SQL commands 
		/// </summary>
		/// <param name="strSQLs">string</param>
		/// <returns>int</returns>
		protected static int ST_ExecuteSqls(string[] strSQLs)
		{
			SqlConnection ST_myCn = new SqlConnection(ST_strConn);			
			SqlCommand ST_myCmd = new SqlCommand();			
			int j=strSQLs.Length;

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

			try
			{											
				ST_myCmd.Connection = ST_myCn;				
				ST_myCmd.Transaction = ST_myTrans;

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

		#endregion
	}
}