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

namespace sc
{
	/// <summary>
	/// 數據庫連接操作類
	/// </summary>
	public class Db
	{
        /// <summary>
        /// 數據庫連接字符串
        /// </summary>
        protected static string connString = ConfigurationSettings.AppSettings["ConnectionString"];

		public Db()
		{
		}

        /// <summary>
        /// 執行select類型的sql語句,返回select得到的數據集
        /// </summary>
        /// <param name="sqlSelect">select語句</param>
        /// <returns>返回select得到的數據集</returns>
        public static DataSet ExecuteSelectSql( string sqlSelect )
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlDataAdapter sda = new SqlDataAdapter(sqlSelect,conn);
            DataSet ds = new DataSet();
            try
            {
                sda.Fill(ds);
            }
            catch(SqlException e)
            {
                throw new Exception(e.Message);
            }
            return ds;
        }

        /// <summary>
        /// 執行insert,update,delete等語句,改變的行數
        /// </summary>
        /// <param name="sql">sql語句</param>
        /// <returns>改變的行數</returns>
        public static int ExecuteSql( string sql )
        {
            int rows = -1;
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand(sql,conn);
            try
            {
                conn.Open();
                rows = cmd.ExecuteNonQuery();
            }
            catch(SqlException e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }    
            
            return rows;
        }

        /// <summary>
        /// 執行需要返回剛插入記錄的identity的insert語句,返回改變行數
        /// </summary>
        /// <param name="sqlInsert">insert語句</param>
        /// <param name="identity">identity</param>
        /// <returns>返回改變行數</returns>
        public static int ExecuteInsertSql( string sqlInsert, ref int identity )
        {
            int rows = -1;
            SqlConnection conn = new SqlConnection(connString);			
            SqlCommand cmd = new SqlCommand();			

            try
            {
                conn.Open();				
            }
            catch(SqlException e)
            {
                throw new Exception(e.Message);
            }

            SqlTransaction trans = conn.BeginTransaction();
            try
            {											
                cmd.Connection = conn;				
                cmd.Transaction = trans;
                //執行insert
                cmd.CommandText = sqlInsert;
                rows = cmd.ExecuteNonQuery();
                //select出identity
                cmd.CommandText = "select @@identity";
                SqlDataReader sdr = cmd.ExecuteReader();
                if(sdr.Read())
                {
                    identity = sdr.GetInt32(0);
                }

                trans.Commit();             
            }
            catch(System.Data.SqlClient.SqlException e)
            {			
                trans.Rollback();
                throw new Exception(e.Message);
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }

            return rows;
        }

	}
}