using System;
using System.Collections.Generic;
using System.Text;
using System.IO;

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


namespace IDbAccess
{
    //MS SQL SERVER 數據庫訪問類
    class SqlDbAccess : IDbAccess
    {
        private SqlConnection conn; //數據庫連接對象

        private SqlTransaction trans;   //事務處理對象

        private bool inTransaction = false; //true表示當前正處于事務中
        

        public IDbConnection Connection
        {
            get
            {
                return this.conn;
            }
        }

        public SqlDbAccess(string strConnection)  
        {
            this.conn = new SqlConnection(strConnection);
        }    
        
        public void Open()
        {
            if (conn.State != ConnectionState.Open)
            {
                this.conn.Open();
            }
        }
        
        public void Close()
        {
            if (conn.State == ConnectionState.Open)
            {
                this.conn.Close();
            }
        }
        
        public void BeginTrans()
        {
            trans = conn.BeginTransaction();
            inTransaction = true;
        }

        public void CommitTrans()
        {
            trans.Commit();
            inTransaction = false;
        }

        public void RollbackTrans()
        {
            trans.Rollback();
            inTransaction = false;
        }

        public void ExecSQLNoDataSet(string strSql, string[] strParams, object[] strValues)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTransaction)
            {
                cmd.Transaction = trans;
            }
            if ((strParams != null) && (strParams.Length != strValues.Length))
            {
                throw new Exception("查詢參數和值不對應!");
            }
            cmd.CommandText = strSql;
            if (strParams != null)
            {
                for (int i = 0; i < strParams.Length; i++)
                {
                    cmd.Parameters.Add(strValues[i]);
                }
            }
            cmd.ExecuteNonQuery();
        }
        public void ExecSQLNoDataSet(string strSql)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTransaction)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandText = strSql;
            cmd.ExecuteNonQuery();
        }

        public DataSet ExecSQLGetDataSet(string QueryString)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = this.conn;
            if (inTransaction)
            {
                cmd.Transaction = trans;
            }
            DataSet ds = new DataSet();
            SqlDataAdapter ad = new SqlDataAdapter();
            cmd.CommandText = QueryString;
            ad.SelectCommand = cmd;
            ad.Fill(ds);
            return ds;
        }

    } 

}