/// <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; }
private void Exec(string query, MySQLConnection conn, string[] Fields) { MySQLCommand command = new MySQLCommand(query, conn); //GDBUtils.WhereCommandPart(ref command,WhereParamFields); command.Prepare(); DataTable table; BindVarQueryStatement reader = null; // Execute query -> try { reader = (BindVarQueryStatement)command.ExecuteReader(/*CommandBehavior.SequentialAccess*/); // Get results -> table = new DataTable(null); for (int n = 0; n < reader.FieldCount; n++) { System.Data.DataColumn dc = new DataColumn(reader.GetName(n)); String s = dc.ToString(); System.Data.DataColumn dcNew = new System.Data.DataColumn(s); table.Columns.Add(dcNew); } while (reader.Read()) { if (reader.IsClosed) { throw new MySqlException("Reader is closed."); } DataRow row = table.NewRow(); // Bugfixed by Yann Sénécheau 2002-10-28 -> // for(n=0;n<Fields.Length;n++) for (int n = 0; n < reader.FieldCount; n++) // <- Bugfixed by Yann Sénécheau 2002-10-28 { if (n < reader.FieldCount) { if (reader.GetValue(n) == null) { row[n] = null; } else if (reader.GetFieldType(n) == typeof(string)) { row[n] = reader.GetString(n).Clone(); } else { row[n] = reader.GetValue(n); } } else { break; } } table.Rows.Add(row); } // <- Get results } catch (Exception e) { throw new MySqlException(e.Message + " in query '" + query + "'"); } finally { if (reader != null) { reader.Close(); } command.Dispose(); } // <- Execute query Table = table; }
/// <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; }
/// <summary> /// Construct and Run a simple insert command. /// </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> /// <example>Example: /// <code> /// using MySQLDriverCS; /// ... /// MySQLConnection DBConn; /// DBConn = new MySQLConnection( new MySQLConnectionString("myhost","mydatabase","mylogin","mypassword").AsString ); /// DBConn.Open(); /// ... /// new MySQLInsertCommand( /// DBConn, /// new object[,] { /// {"SettingID",SettingID}, /// {"SettingValue",Value} /// }, /// "Settings" /// ); /// ... /// DBConn.Close(); /// </code></example> /// Modified by Claudia Murialdo (07/24/04) in order to support time /// component of the datetime values and culture-independent /// format of numeric values in a stmt. public MySQLInsertCommand( MySQLConnection conn, object[,] FieldsAndValues, string Table ) { if (FieldsAndValues == null) { throw new MySqlException("FieldsAndValues is null."); } if (FieldsAndValues.GetLength(0) == 0) { throw new MySqlException("FieldsAndValues is empty."); } string query = "insert into " + Table + " ( "; int m; for (m = 0; m < FieldsAndValues.GetLength(0); m++) { string Field = FieldsAndValues[m, 0] as string; query += " " + Field.ToString() + " "; if (m != (FieldsAndValues.GetLength(0) - 1)) { query += " , "; } } query += " ) VALUES ( "; for (m = 0; m < FieldsAndValues.GetLength(0); m++) { string Field = FieldsAndValues[m, 0] as string; object Value = FieldsAndValues[m, 1]; if (Value == null) { query += " NULL "; } else if ((Value.GetType() == typeof(int)) || (Value.GetType() == typeof(long)) || (Value.GetType() == typeof(short)) || (Value.GetType() == typeof(decimal)) || (Value.GetType() == typeof(float)) || (Value.GetType() == typeof(double))) { query += " " + Convert.ToString(Value, CultureInfo.InvariantCulture.NumberFormat) + " "; } else if (Value.GetType() == typeof(bool)) { bool bValue = (bool)Value; string str = (bValue)? "1" : "0"; query += " " + str + " "; } else if (Value.GetType() == typeof(DateTime)) { DateTime dt = (DateTime)Value; query += " \"" + 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 { query += " " + MySQLUtils.Escape(Value.ToString(), conn) + " "; } if (m != (FieldsAndValues.GetLength(0) - 1)) { query += " , "; } } query += ") ;"; 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; }