/// <summary> /// Construct and Run a simple delete command. /// </summary> /// <param name="conn">Database connection</param> /// <param name="Table">Table name</param> /// <param name="WhereParamFields">Tripplets of parameters, operand and its values to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=",myvalue},{"myfield2","<>",myvalue2}} /// /// new object[,]{{"myfield","=",100}} /// /// new object[,]{{"myfield","=",myvalue}} /// /// null /// </code> /// </param> /// <param name="WhereNoparamFields">Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=","myfield2"}} /// /// null /// </code> /// </param> /// <example>Example:<code> /// using MySQLDriverCS; /// ... /// MySQLConnection DBConn; /// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString ); /// DBConn.Open(); /// ... /// new MySQLDeleteCommand( /// DBConn, /// "Trash", /// new object[,]{{"myfield","=",myvalue}}, /// new object[,]{{"myfield","=","myfield2"}} /// ); /// ... /// new MySQLDeleteCommand( /// DBConn, /// "Trash", /// new object[,]{{"myfield2","=",myvalue2},{"myfield3","<>",myvalue3}}, /// null /// ); /// ... /// DBConn.Close(); /// </code></example> public MySQLDeleteCommand( MySQLConnection conn, string Table, object[,] WhereParamFields, string[,] WhereNoparamFields ) { string query = "delete from " + conn.Database + "." + Table + " "; query += MySQLSelectCommand.WhereQueryPart(WhereParamFields, WhereNoparamFields, "WHERE", conn) + " "; MySQLCommand command = new MySQLCommand(query, conn); command.Prepare(); // Execute query -> try { command.ExecuteNonQuery(); } catch (Exception e) { throw new MySqlException(e.Message + " in query '" + query + "'"); } // <- Execute query command.Dispose(); Query = query; bSuccess = true; }
/// <summary> /// A shortcut to make a simple update command with where part. (where would be null) /// </summary> /// <param name="conn">Database connection</param> /// <param name="FieldsAndValues">Pairs of Fields and values as an object array. Examples: /// <code> /// new object[,]{{"SettingID",SettingID},{"SettingValue",Value}} /// /// new object[,]{{"SettingID","times"},{"SettingValue",100}} /// /// null /// </code> /// </param> /// <param name="Table">Table name</param> /// <param name="WhereParamFields">Tripplets of parameters, operand and its values to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=",myvalue},{"myfield2","<>",myvalue2}} /// /// new object[,]{{"myfield","=",100}} /// /// new object[,]{{"myfield","=",myvalue}} /// /// null /// </code> /// </param> /// <param name="WhereNoparamFields">Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing). For example: /// <code> /// new object[,]{{"myfield","=","myfield2"}} /// /// null /// </code> /// </param> /// <example>Example updating SettingValue by SettingID in Settings table<code> /// using MySQLDriverCS; /// ... /// MySQLConnection DBConn; /// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString ); /// DBConn.Open(); /// ... /// new MySQLUpdateCommand(DBConn, /// new object[,] {{"SettingValue",Value}}, /// "Settings", /// new object[,] {{"SettingID","=",SettingID}}, /// null /// ); /// ... /// DBConn.Close(); /// </code></example> /// Modified by Claudia Murialdo (07/24/04) in order to support time /// component of the datetime values. public MySQLUpdateCommand( MySQLConnection conn, // Database connection object[,] FieldsAndValues, // Pairs of Fields and values string Table, // Table object[,] WhereParamFields, // Tripplets of parameters, operand and its values to match (null - nothing) string[,] WhereNoparamFields // Tripplets of parameters, operand and expressions (or other fields for joining) to match (null - nothing) ) { if (FieldsAndValues == null) { throw new MySqlException("FieldsAndValues is null."); } if (FieldsAndValues.GetLength(0) == 0) { throw new MySqlException("FieldsAndValues is empty."); } string query = "update " + conn.Database + "." + Table + " set "; ArrayList NewValues = new ArrayList(); for (int m = 0; m < FieldsAndValues.GetLength(0); m++) { string Field = FieldsAndValues[m, 0] as string; object Value = FieldsAndValues[m, 1]; if (Value == null) { query += " " + Field + "=NULL "; } else if (Value.GetType() == typeof(DateTime)) { DateTime dt = (DateTime)Value; query += " " + Field + "=\"" + dt.Year.ToString("D4") + "-" + dt.Month.ToString("D2") + "-" + dt.Day.ToString("D2") + " " + dt.Hour + ":" + dt.Minute + ":" + dt.Second + ((dt.Millisecond > 0) ? "." + dt.Millisecond.ToString("D3") : "") + "\" "; } else if (Value.GetType() == typeof(bool)) { bool bValue = (bool)Value; string str = (bValue)? "1" : "0"; query += " " + Field + "=" + str + " "; } else if (Value.GetType() == typeof(string)) { string str = Value as string; query += " " + Field + "=" + MySQLUtils.Escape(str, conn) + " "; } else if (Value.GetType() == typeof(int)) { query += " " + Field + "=" + ((int)Value).ToString() + " "; } if (m != (FieldsAndValues.GetLength(0) - 1)) { query += ", "; } } query += MySQLSelectCommand.WhereQueryPart(WhereParamFields, WhereNoparamFields, "WHERE", conn) + " "; MySQLCommand command = new MySQLCommand(query, conn); command.Prepare(); // Execute query -> try { command.ExecuteNonQuery(); } catch (Exception e) { throw new MySqlException(e.Message + " in query '" + query + "'"); } // <- Execute query command.Dispose(); Query = query; bSuccess = true; }