sql helper

namespace Saga.LiveChain.SA.DataAccess
{

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

    // using Saga.LivePortal.SystemFramework.LogManager;
    // using Saga.LivePortal.SystemFramework.ExceptionManager;
    /// <summary>
    /// Structure of Connection parameter
    /// </summary>
    public struct SConnParameterSql
    {
        /// <summary>
        /// Set up DataSet.
        /// </summary>
        public DataSet ds;
        /// <summary>
        /// Table name in this dataset.
        /// </summary>
        public string MappingTableName;
        /// <summary>
        /// Primary key for this table.
        /// </summary>
        public DataColumn[] primarykey;
        /// <summary>
        /// Command Text for the Connection.
        /// </summary>
        public string commandtext;
        /// <summary>
        /// Command Typefor the Connection.
        /// </summary>
        public CommandType commandtype;
        /// <summary>
        /// Parameter the Connection
        /// </summary>
        public SqlParameter[] parameters;
    }

    /// <summary>
    /// General method of connect database
    /// </summary>
    public class DBCommonSQL
    {
        private SqlConnection connect = new SqlConnection();
        private SqlCommand command = new SqlCommand();
        private SqlDataAdapter da = new SqlDataAdapter();
        private SqlTransaction transaction = null;

        /// <summary>
        /// Initialize the connetion
        /// </summary>
        public DBCommonSQL()
        {
            connect = new SqlConnection(this.ConnectionString);

            command.Connection = (SqlConnection)connect;

            da = new SqlDataAdapter(command);

        }

        /*
         * 第二个连接串，
         * 没有参数是构造第一个
         * 有参数时构造第二个
        public DBCommonSQL(string DB)
        {
            if (DB=="BBS")
            {
                connect = new SqlConnection(this.ConnectionStringBBS);

                command.Connection = (SqlConnection)connect;

                da = new SqlDataAdapter(command);
            }

        }
        */
        /// <summary>
        /// Get the strong-type dataset
        /// </summary>
        /// <param name="connParameter"></param>
        /// <returns>DataSet</returns>
        internal DataSet GetStrongTypeDataSet(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;
                // connParameter.ds.Tables[connParameter.MappingTableName].PrimaryKey = connParameter.primarykey;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                da.SelectCommand = command;
                //da.FillSchema(connParameter.ds, SchemaType.Mapped, connParameter.MappingTableName);
                da.Fill(connParameter.ds, connParameter.MappingTableName);

                connParameter.ds.RemotingFormat = SerializationFormat.Binary;
                return connParameter.ds;
            }
            catch (Exception e)
            {
                CatchException(e);
                return null;
            }
            finally
            {
                command.Dispose();
                da.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary>
        /// Get the DataTable
        /// </summary>
        /// <param name="connParameter"></param>
        /// <returns>DataSet</returns>
        internal DataTable GetDataTable(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                da.SelectCommand = command;

                da.Fill(connParameter.ds);

                return connParameter.ds.Tables[0];
            }
            catch (Exception e)
            {
                CatchException(e);
                return null;
            }
            finally
            {
                command.Dispose();
                da.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary>
        /// Get DataReader
        /// </summary>
        /// <param name="connParameter"></param>
        /// <returns>IDataReader</returns>
        internal IDataReader GetDataReader(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }

                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;
                SqlDataReader dr = null;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                GetOutParameters(connParameter.parameters, command);
                return dr;
            }
            catch (Exception e)
            {
                CatchException(e);
                return null;
            }
            finally
            {
                command.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary>
        /// operate the database by  StoredProcedure with update,deleteal,add
        /// </summary>
        /// <param name="connParameter"></param>
        internal void EditTableWithReturnValue(SConnParameterSql connParameter)
        {
            EditTable(connParameter, true);
        }
        /// <summary>
        /// operate the database by  StoredProcedure with update,deleteal,add
        /// </summary>
        /// <param name="connParameter"></param>
        internal void EditTableNoReturnValue(SConnParameterSql connParameter)
        {
            EditTable(connParameter, false);
        }
        /// <summary>
        /// operate the database by  StoredProcedure with update,deleteal,add
        /// </summary>
        /// <param name="connParameter"></param>
        /// <param name="isReturnValue"></param>
        private void EditTable(SConnParameterSql connParameter, bool isReturnValue)
        {
            try
            {
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;

                //Add parameters values
                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                //if the provided connection is not open, we will open it
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                //Commit Value
                command.ExecuteNonQuery();

                //Get return parameters va
                if (isReturnValue)
                    GetOutParameters(connParameter.parameters, command);
            }
            catch (Exception e)
            {
                CatchException(e);
            }
            finally
            {
                command.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        /// <summary>
        /// catch exception
        /// </summary>
        /// <param name="ex"></param>
        internal void CatchException(Exception ex)
        {
            //throw ex;
            //System.Diagnostics.Debug.Write(ex);
            try
            {
                // AppService.ProcessException(ex);
            }
            catch
            {
                //LogManager lm = new LogManager();
                // lm.FillLogEntry(1000, 5, ex.Message, LogManager.Category.DataAccessEvents, TraceEventType.Error);
                // lm.DoLogging();

                // throw ex2;
            }
        }
        internal int ExecuteScalar(SConnParameterSql connParameter)
        {
            //if the provided connection is not open, we will open it
            try
            {
                if (connect.State != ConnectionState.Open)
                {
                    connect.Open();
                }
                command.CommandText = connParameter.commandtext;
                command.CommandType = connParameter.commandtype;

                command.Parameters.Clear();
                if (connParameter.parameters != null)
                {
                    foreach (SqlParameter parameter in connParameter.parameters)
                    {
                        command.Parameters.Add(parameter);
                    }
                }
                da.SelectCommand = command;
               
                return (int)command.ExecuteScalar();
               
            }
            catch (Exception e)
            {
                CatchException(e);
                return 0;
            }
            finally
            {
                command.Dispose();
                da.Dispose();
                if (connect.State == ConnectionState.Open)
                {
                    connect.Close();
                }
            }
        }

        #region Get output parameters values
        /// <summary>
        /// Get output parameters values
        /// </summary>
        /// <param name="parameters"></param>
        /// <param name="cmd"></param>
        private void GetOutParameters(SqlParameter[] parameters, SqlCommand cmd)
        {
            foreach (SqlParameter p in parameters)
            {
                if (p.Direction == ParameterDirection.Output ||
                    p.Direction == ParameterDirection.InputOutput ||
                    p.Direction == ParameterDirection.ReturnValue)
                {
                    p.Value = cmd.Parameters[p.ParameterName].Value;
                }
            }
        }
        #endregion

        #region Connection String
        /// <summary>
        /// Get the Connection String
        /// </summary>
        public string ConnectionString
        {
            get
            {
                // return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                return ConfigurationSettings.AppSettings["ConnectionString"];
            }
        }

        public string ConnectionStringBBS
        {
            get
            {
                // return System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                return ConfigurationSettings.AppSettings["ConnectionStringBBS"];
            }
        }

        #endregion

        #region Begin, Commit, Rollback Transactions, Close Conn

        /// <summary>
        /// Begin transaction
        /// </summary>
        public void BeginTransaction()
        {
            if (transaction != null)
            {
                throw new System.NotSupportedException("Does not support nested transaction.");
            }
            if (connect.State != ConnectionState.Open)
            {
                connect.Open();
            }
            transaction = connect.BeginTransaction();
        }

        /// <summary>
        /// Commit Transaction
        /// </summary>
        public void CommitTransaction()
        {
            if (null == transaction)
            {
                //throw new DBAccess.TransactionObjectInvalidException();
            }
            transaction.Commit();
            transaction.Dispose();
            transaction = null;
            connect.Close();
        }

        /// <summary>
        /// Rollback Transaction
        /// </summary>
        public void RollbackTransaction()
        {
            if (null == transaction)
            {
                //throw new DBAccess.TransactionObjectInvalidException();
            }
            transaction.Rollback();
            transaction.Dispose();
            transaction = null;
            connect.Close();
        }
        /// <summary>
        /// Close connection when transaction had been finnaly
        /// </summary>
        public void CloseConnForTransactionFinnaly()
        {
            if (connect.State != ConnectionState.Closed)
            {
                connect.Close();
            }
        }
        #endregion

        #region IDisposable members

        /// <summary>
        /// Dispose transaction
        /// </summary>
        public void Dispose()
        {
            if (transaction != null)
            {
                transaction.Dispose();
            }
        }

        #endregion
    }

}