C# MySQL Helper Class

C# 에서 사용할 수 있는 MySQL Helper Class

using System;
using System.Data;
using MySql.Data.MySqlClient;
 
namespace Sentbe.Database
{
    public class MySqlHelper : IDisposable
    {
        private MySqlConnection connection = null;
        private String connectionString = "dbConnection";
        private MySqlDataAdapter sqlData = new MySqlDataAdapter();
        public MySqlCommand sqlCmd = new MySqlCommand();
 
        // {0} : IP, {1} : Port, {2} : DB Name, {3} : userID, {4} : userPW
        private String connectionFormat = "Server={0};Database={1};Uid={2};Pwd={3};";
 
        public String ConnectionString
        {
            set { connectionString = value; }
        }
 
        #region Constructors
 
        public MySqlHelper()
        {
            connectionString = AppConfigHelper.getConnectionString("conn");
        }
 
        public MySqlHelper(string pConnectionString)
        {
            if (!string.IsNullOrEmpty(pConnectionString.Trim()))
            {
                connectionString = pConnectionString;
            }
        }
 
        public MySqlHelper(string pIP, string pDbName, string pUserID, string pUserPW)
        {
            connectionString = string.Format(this.connectionFormat, pIP, pDbName, pUserID, pUserPW);
 
        }
        #endregion
 
 
        /// <summary>
        /// Sql Script / Stored Procedure Name
        /// </summary>
        public string CmdText
        {
            set { sqlCmd.CommandText = value; }
        }
 
        /// <summary>
        /// CommandType = CommandType....
        /// </summary>
        public CommandType cmdType
        {
 
            set { sqlCmd.CommandType = value; }
        }
 
        /// <summary>
        /// AddParameter(iSql, "return_value", SqlDbType.Int , "", ParameterDirection.ReturnValue , 5); ---- return문
        /// AddParameter(iSql, "@MSG_TEXT", SqlDbType.VarChar, "", ParameterDirection.Output, 100);
        /// </summary>
        /// <param name="Param"></param>
        /// <param name="Db_Type"></param>
        /// <param name="values"></param>
        /// <param name="Param_io"></param>
        /// <param name="nSize"></param>
        public void AddParameter(string Param, MySqlDbType Db_Type, object values, ParameterDirection Param_io, int nSize)
        {
            MySqlParameter param1 = new MySqlParameter(Param, Db_Type);
            if (Param_io == ParameterDirection.Input || Param_io == ParameterDirection.InputOutput)
                param1.Value = values;
            param1.Direction = Param_io;
            param1.Size = nSize;
            sqlCmd.Parameters.Add(param1);
        }
 
 
        #region Add Parameter TO Query
 
        private void AddParameter(MySqlCommand command, string parameterName, MySqlDbType dbType, int size, ParameterDirection direction, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value)
        {
            MySqlParameter p = new MySqlParameter(parameterName, dbType, size, direction, true, precision, scale, sourceColumn, sourceVersion, value);
            command.Parameters.Add(p);
        }
 
        public void AddParameter(MySqlCommand command, string parameterName, MySqlDbType dbType, int size, ParameterDirection direction, object value)
        {
            AddParameter(command, parameterName, dbType, size, direction, 0, 0, null, DataRowVersion.Current, value);
        }
 
        public void AddInParameter(MySqlCommand command, string parameterName, MySqlDbType dbType, object value)
        {
            AddParameter(command, parameterName, dbType, 0, ParameterDirection.Input, value);
        }
 
        public void AddOutParameter(MySqlCommand command, string parameterName, MySqlDbType dbType, int size)
        {
            AddParameter(command, parameterName, dbType, size, ParameterDirection.Output, null);
        }
 
        /// <summary>
        /// Store_Param(iSql, "@prc", SqlDbType.VarChar,"C");
        /// </summary>
        /// <param name="Param"></param>
        /// <param name="Db_Type"></param>
        /// <param name="values"></param>
        public void AddParameter(string Param, MySqlDbType Db_Type, object values)
        {
            MySqlParameter param1 = new MySqlParameter(Param, Db_Type);
            param1.Value = values;
            sqlCmd.Parameters.Add(param1);
        }
 
        /// <summary>
        /// Store_Param(iSql, "@MSG_CD", SqlDbType.VarChar, "", ParameterDirection.Output);
        /// </summary>
        /// <param name="Param"></param>
        /// <param name="Db_Type"></param>
        /// <param name="values"></param>
        /// <param name="Param_io"></param>
        public void AddParameter(string Param, MySqlDbType Db_Type, object values, ParameterDirection Param_io)
        {
            MySqlParameter param1 = new MySqlParameter(Param, Db_Type);
            if (Param_io == ParameterDirection.Input || Param_io == ParameterDirection.InputOutput)
                param1.Value = values;
            param1.Direction = Param_io;
            sqlCmd.Parameters.Add(param1);
        }
 
        public object GetParameterValue(MySqlCommand command, string parameterName)
        {
            return command.Parameters[parameterName].Value;
        }
 
        /// <summary>
        /// sp의 OUTPUT 파라미터 값 읽기
        /// int(string) return = (int/string)Return_Param("@return");
        /// </summary>
        /// <param name="param">"@param"</param>
        /// <returns>object</returns>
        public object GetParameter(string param)
        {
            return sqlCmd.Parameters[param].Value;
        }
        #endregion
 
        #region Generating SqlCommand
 
        private MySqlCommand PrepareCommand(CommandType commandType, string commandText)
        {
            if (connection == null)
            {
                connection = new MySqlConnection(this.connectionString);
            }
            if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
            {
                connection.Open();
            }
            MySqlCommand command = new MySqlCommand(commandText, connection);
            command.CommandType = commandType;
            return command;
        }
 
        public MySqlCommand GetStoreProcedureCommand(string spname)
        {
            return PrepareCommand(CommandType.StoredProcedure, spname);
        }
 
        public MySqlCommand GetSqlQueryCommand(string query)
        {
            return PrepareCommand(CommandType.Text, query);
        }
        #endregion
 
        #region Direct Quer
 
        public int DirectNonQuery(string query)
        {
            MySqlCommand sc = GetSqlQueryCommand(query);
            int iResult = ExecuteNonQuery(sc);
            return iResult;
        }
 
        /// <summary>
        /// 직접 쿼리결과를 가져오는 함수
        /// </summary>
        /// <param name="sQuery"></param>
        /// <returns></returns>
        public DataTable stringQuery(string sQuery)
        {
            return new MySqlHelper().DirectQuery(sQuery);
        }
 
        public DataTable DirectQuery(string query)
        {
            MySqlCommand sc = GetSqlQueryCommand(query);
            return LoadDataTable(sc, string.Empty);
        }
 
        public DataTable DirectQuery(string query, string tableName)
        {
            MySqlCommand sc = GetSqlQueryCommand(query);
            return LoadDataTable(sc, string.Empty);
        }
 
        /// <summary>
        /// 쿼리명령어(Update/Insert/Delete)등 실행
        /// </summary>
        /// <param name="sQuery"></param>
        /// <returns>Return 1(Success) / 0(Fail)</returns>
        public int StringNonQuery(string sQuery)
        {
            return new MySqlHelper().DirectNonQuery(sQuery);
        }
        #endregion
 
        #region Database Related Command
 
        public int ExecuteNonQuery(MySqlCommand command)
        {
            return command.ExecuteNonQuery();
        }
 
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public int ExcuteNonQuery()
        {
            return sqlExcuteNonQuery(0);
        }
 
        /// <summary>
        /// Sql Excute Command
        /// Transaction: 1 begin transaction
        /// Transaction: 0 No transaction
        /// </summary>
        /// <param name="Transaction">Transaction= 1 Or 0</param>
        /// <returns>Return Effective Rows</returns>
        public int sqlExcuteNonQuery(int Transaction)
        {
            try
            {
                sqlData.SelectCommand = sqlCmd;
                sqlCmd.Connection = new MySqlConnection(this.connectionString);
                sqlCmd.Connection.Open();
                if (Transaction > 0)
                    sqlCmd.Transaction = sqlCmd.Connection.BeginTransaction(IsolationLevel.ReadCommitted);
 
                int rtn = sqlCmd.ExecuteNonQuery();
 
                if (Transaction > 0)
                    sqlCmd.Transaction.Commit();
                sqlCmd.Connection.Close();
 
                return rtn;
            }
            catch
            {
                if (Transaction > 0)
                    sqlCmd.Transaction.Rollback();
 
                return -1;
            }
            finally
            {
            }
        }
 
        public object ExecuteScalar(MySqlCommand command)
        {
            return command.ExecuteScalar();
        }
 
        public MySqlDataReader ExecuteReader(MySqlCommand command)
        {
            return command.ExecuteReader(CommandBehavior.CloseConnection);
        }
 
        public MySqlDataReader ExecuteReader(MySqlCommand command, CommandBehavior commandBehavior)
        {
            return command.ExecuteReader(commandBehavior);
        }
 
        public DataTable LoadDataTable(MySqlCommand command, string tableName)
        {
            using (MySqlDataAdapter da = new MySqlDataAdapter(command))
            {
                using (DataTable dt = new DataTable(tableName))
                {
                    da.Fill(dt);
                    return dt;
                }
            }
        }
 
        public DataSet LoadDataSet(MySqlCommand command, string[] tableNames)
        {
            using (MySqlDataAdapter da = new MySqlDataAdapter(command))
            {
                using (DataSet ds = new DataSet())
                {
                    da.Fill(ds);
                    if (tableNames != null)
                    {
                        for (int i = 0; i < ds.Tables.Count; i++)
                        {
                            try
                            {
                                ds.Tables[i].TableName = tableNames[i];
                            }
                            catch
                            {
                            }
                        }
                    }
 
                    return ds;
                }
            }
        }
 
        /// <summary>
        /// DataSet 요청
        /// </summary>
        /// <returns>DataSet</returns>
        public DataSet LoadDataSet()
        {
            //
            DataSet ds = new DataSet();
            try
            {
                sqlData.SelectCommand = sqlCmd;
                sqlCmd.Connection = new MySqlConnection(this.connectionString);
 
                sqlData.Fill(ds);
 
                return ds;
            }
            catch
            {
                return null;
            }
            finally
            {
            }
        }
 
        private MySqlTransaction PrepareTransaction(IsolationLevel isolationLevel)
        {
            if (connection == null)
            {
                connection = new MySqlConnection(this.connectionString);
            }
            if (connection.State == ConnectionState.Closed || connection.State == ConnectionState.Broken)
            {
                connection.Open();
            }
            return connection.BeginTransaction(isolationLevel);
        }
 
        public MySqlTransaction BeginTransaction()
        {
            return PrepareTransaction(IsolationLevel.ReadCommitted);
        }
 
        public MySqlTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            return PrepareTransaction(isolationLevel);
        }
 
        public void Commit(MySqlTransaction transaction)
        {
            if (transaction != null)
                transaction.Commit();
        }
 
        public void RollBack(MySqlTransaction transaction)
        {
            if (transaction != null)
                transaction.Rollback();
        }
        #endregion
 
        #region IDisposable Members
 
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }
        #endregion
 
        #region Destructor
 
        ~MySqlHelper()
        {
            Dispose();
        }
        #endregion
 
        void IDisposable.Dispose()
        {
            if (connection != null)
            {
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }
 
    }   // end of class
 
}   // end of namespace