Commits

Sachirou Inoue committed 3d849f1

Add transaction operation. (experimental)

  • Participants
  • Parent commits ce190be

Comments (0)

Files changed (2)

File UserControl/DBConnect.cs

         /// <summary>
         /// 
         /// </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 {0} From {1} Limit ?START, ?LENGTH;",
                 string.Join(",", columns), table);
-            connection.Open();
             MySqlCommand command = connection.CreateCommand();
             command.CommandText = commandText;
             command.Parameters.AddWithValue("?START", start);
                 whereClause,
                 order,
                 direction);
-            connection.Open();
             MySqlCommand command = connection.CreateCommand();
             command.CommandText = commandText;
             foreach (var entry in whereParameters)
         /// <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>
         /// <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>
                 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();
+        }
     }
 }

File UserControl/Users.cs

             var db = DBConnect.GetInstance();
             using (var connection = db.GetConnection())
             {
+                connection.Open();
                 using(var reader = db.Select(connection, TableName, new string[] { "id", "name", "pass", "age" },
                     "id = ?ID", new Dictionary<string, object>() { { "ID", userID } }, "id", "asc", 0, 1))
                 {
             var db = DBConnect.GetInstance();
             using (var connection = db.GetConnection())
             {
+                connection.Open();
                 MySql.Data.MySqlClient.MySqlDataReader reader;
                 if (Search != null || Order != null)
                 {