Source

UserControl / UserControl / DBConnect.cs

using System;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace UserControl
{
    /// <summary>
    /// データベースへ接続する為のSingletonクラスです。
    /// インスタンスを取得するには<see cref="GetInstance"/>を呼び出してください。
    /// Pageから直接使わず、代理classを立てる事を推奨します。
    /// </summary>
    public class DBConnect
    {
        private static object GetInstance_LockObj = new object();
        private static DBConnect Instance;
        private static string ConnectionString = string.Format("server={0};user id={1};password={2};database={3};charset=utf8;",
                "localhost", "root", "", "wp22");

        private DBConnect() { }

        /// <summary>
        /// このSingleton classの唯一のinstanceを返します。
        /// </summary>
        /// <returns></returns>
        public static DBConnect GetInstance()
        {
            if (Instance == null)
            {
                lock (GetInstance_LockObj)
                {
                    if (Instance == null) Instance = new DBConnect();
                }
            }
            return Instance;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns>このコネクションは、using等を利用して自力で閉じてください。</returns>
        public MySqlConnection GetConnection()
        {
            return new MySqlConnection(ConnectionString);
        }

        /// <summary>
        /// コネクションからトランザクションを開始します。
        /// ex.)
        /// 
        /// var db = DBConnect.GetInstance();
        /// using (var connection = db.GetConnection())
        /// {
        ///     connection.Open();
        ///     var transaction = db.GetTransaction(connection);
        ///     try
        ///     {
        ///         db.InsertWithTransaction(connection, transaction, ...);
        ///         db.UpdateWithTransaction(connection, transaction, ...);
        ///         db.DeleteWithTransaction(connection, transaction, ...);
        ///     }
        ///     catch (Expression)
        ///     {
        ///         try
        ///         {
        ///             transaction.Rollback();
        ///         }
        ///         catch (MySqlExpression)
        ///         {
        ///             throw;
        ///         }
        ///     }
        /// }
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public MySqlTransaction GetTransaction(MySqlConnection connection)
        {
            return connection.BeginTransaction();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <returns></returns>
        public int Count(string table)
        {
            string commandText = string.Format("Select Count(*) From {0};", table);
            using (var connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereClause">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereParameters"></param>
        /// <returns></returns>
        public int Count(string table, string whereClause, Dictionary<string, object> whereParameters)
        {
            string commandText = string.Format("Select Count(*) From {0} Where {1};", table, whereClause);
            using (var connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                foreach (var entry in whereParameters)
                    command.Parameters.AddWithValue("?" + entry.Key, entry.Value);
                return Convert.ToInt32(command.ExecuteScalar());
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
        /// <param name="searchWord"></param>
        /// <returns></returns>
        public int CountWithSearch(string table, string[] columns, string[] searchWords)
        {
            string concatColumn = string.Empty;
            foreach (string column in columns)
            {
                if (concatColumn != string.Empty) concatColumn += ",";
                concatColumn += column + ",' '";
            }
            concatColumn = string.Format("Lower(Concat({0}))", concatColumn);

            string whereClause = string.Empty;
            var whereParametere = new Dictionary<string, object>();
            for (int i = 0; i < searchWords.Length; ++i)
            {
                if (whereClause != string.Empty) whereClause += " And ";
                whereClause += string.Format("{0} Like ?SEARCH{1} ", concatColumn, i);
                whereParametere[string.Format("SEARCH{0}", i)] = string.Format("%{0}%", searchWords[i]);
            }
            return Count(table, whereClause, whereParametere);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public MySqlDataReader Select(MySqlConnection connection, string table, string[] columns, uint start, uint length)
        {
            string commandText = string.Format("Select {0} From {1} Limit ?START, ?LENGTH;",
                string.Join(",", columns), table);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            command.Parameters.AddWithValue("?START", start);
            command.Parameters.AddWithValue("?LENGTH", length);
            return command.ExecuteReader();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereClause">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereParameters"></param>
        /// <param name="order">ここにIO入力値を利用してはなりません。</param>
        /// <param name="direction">ここにIO入力値を利用してはなりません。</param>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public MySqlDataReader Select(MySqlConnection connection,  string table, string[] columns, string whereClause, Dictionary<string, object> whereParameters,
            string order, string direction, uint start, uint length)
        {
            string commandText = string.Format("Select {0} From {1} Where {2} Order By {3} {4} Limit ?START, ?LENGTH;",
                string.Join(",", columns),
                table,
                whereClause,
                order,
                direction);
            MySqlCommand command = connection.CreateCommand();
            command.CommandText = commandText;
            foreach (var entry in whereParameters)
                command.Parameters.AddWithValue("?" + entry.Key, entry.Value);
            command.Parameters.AddWithValue("?START", start);
            command.Parameters.AddWithValue("?LENGTH", length);
            return command.ExecuteReader();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
        /// <param name="searchWord"></param>
        /// <param name="order">ここにIO入力値を利用してはなりません。</param>
        /// <param name="direction">ここにIO入力値を利用してはなりません。</param>
        /// <param name="start"></param>
        /// <param name="length"></param>
        /// <returns></returns>
        public MySqlDataReader SelectWithSearch(MySqlConnection connection, string table, string[] columns, string[] searchWords,
            string order, string direction, uint start, uint length)
        {
            string concatColumn = string.Empty;
            var columnList = new List<string>();
            foreach (string column in columns)
            {
                if (concatColumn != string.Empty) concatColumn += ",";
                concatColumn += column + ",' '";
                columnList.Add(column);
            }
            concatColumn = string.Format("Lower(Concat({0}))", concatColumn);
            columnList.Add(concatColumn);

            string whereClause = string.Empty;
            var whereParametere = new Dictionary<string, object>();
            for (int i = 0; i < searchWords.Length; ++i)
            {
                if (whereClause != string.Empty) whereClause += " And ";
                whereClause += string.Format("{0} Like ?SEARCH{1} ", concatColumn, i);
                whereParametere[string.Format("SEARCH{0}", i)] = string.Format("%{0}%", searchWords[i]);
            }
            return Select(connection, table, columnList.ToArray(), whereClause, whereParametere, order, direction, start, length);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
        /// <param name="values"></param>
        public void Insert(string table, string[] columns, object[] values)
        {
            string valuesParameters = string.Empty;
            foreach (var column in columns)
            {
                if (valuesParameters != string.Empty) valuesParameters += ", ";
                valuesParameters += "?" + column;
            }
            string commandText = string.Format("Insert {0} ({1}) Values ({2});",
                table, string.Join(",", columns), valuesParameters);
            using (var connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                for (int i = 0; i < values.Length; ++i)
                    command.Parameters.AddWithValue("?" + columns[i], values[i]);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="values"></param>
        public void InsertWithTransaction(MySqlConnection connection, MySqlTransaction transaction,
            string table, string[] columns, object[] values)
        {
            string valuesParameters = string.Empty;
            foreach (var column in columns)
            {
                if (valuesParameters != string.Empty) valuesParameters += ", ";
                valuesParameters += "?" + column;
            }
            string commandText = string.Format("Insert {0} ({1}) Values ({2});",
                table, string.Join(",", columns), valuesParameters);
            MySqlCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = commandText;
            for (int i = 0; i < values.Length; ++i)
                command.Parameters.AddWithValue("?" + columns[i], values[i]);
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="columns">ここにIO入力値を利用してはなりません。</param>
        /// <param name="values"></param>
        /// <param name="whereClause">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereParameters"></param>
        public void Update(string table, string[] columns, object[] values,
            string whereClause, Dictionary<string, object> whereParameters)
        {
            using (var connection = new MySqlConnection(ConnectionString))
            {
                string valuesParameters = string.Empty;
                foreach (var column in columns)
                {
                    if (valuesParameters != string.Empty) valuesParameters += ", ";
                    valuesParameters += string.Format("{0} = ?{0}", column);
                }
                string commandText = string.Format("Update {0} Set {1} Where {2}", table, valuesParameters, whereClause);
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                for (int i = 0; i < values.Length; ++i)
                    command.Parameters.AddWithValue("?" + columns[i], values[i]);
                foreach (var entry in whereParameters)
                    command.Parameters.AddWithValue("?" + entry.Key, entry.Value);
                command.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <param name="transaction"></param>
        /// <param name="table"></param>
        /// <param name="columns"></param>
        /// <param name="values"></param>
        /// <param name="whereClause"></param>
        /// <param name="whereParameters"></param>
        public void UpdateWithTransaction(MySqlConnection connection, MySqlTransaction transaction,
            string table, string[] columns, object[] values,
            string whereClause, Dictionary<string, object> whereParameters)
        {
            string valuesParameters = string.Empty;
            foreach (var column in columns)
            {
                if (valuesParameters != string.Empty) valuesParameters += ", ";
                valuesParameters += string.Format("{0} = ?{0}", column);
            }
            string commandText = string.Format("Update {0} Set {1} Where {2}", table, valuesParameters, whereClause);
            MySqlCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = commandText;
            for (int i = 0; i < values.Length; ++i)
                command.Parameters.AddWithValue("?" + columns[i], values[i]);
            foreach (var entry in whereParameters)
                command.Parameters.AddWithValue("?" + entry.Key, entry.Value);
            command.ExecuteNonQuery();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="table">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereClause">ここにIO入力値を利用してはなりません。</param>
        /// <param name="whereParameters"></param>
        public void Delete(string table, string whereClause, Dictionary<string, object> whereParameters)
        {
            string commandText = string.Format("Delete From {0} Where {1}", table, whereClause);
            using (var connection = new MySqlConnection(ConnectionString))
            {
                connection.Open();
                MySqlCommand command = connection.CreateCommand();
                command.CommandText = commandText;
                foreach (var entry in whereParameters)
                    command.Parameters.AddWithValue("?" + entry.Key, entry.Value);
                command.ExecuteNonQuery();
            }
        }

        public void DeleteWithTransaction(MySqlConnection connection, MySqlTransaction transaction,
            string table, string whereClause, Dictionary<string, object> whereParameters)
        {
            string commandText = string.Format("Delete From {0} Where {1}", table, whereClause);
            connection.Open();
            MySqlCommand command = connection.CreateCommand();
            command.Transaction = transaction;
            command.CommandText = commandText;
            foreach (var entry in whereParameters)
                command.Parameters.AddWithValue("?" + entry.Key, entry.Value);
            command.ExecuteNonQuery();
        }
    }
}
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.