/// <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","&lt;&gt;",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","&lt;&gt;",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;
        }
Example #2
0
 /// <inheritdoc />
 public void Commit()
 {
     if (Connection == null)
     {
         throw new MySqlException("Connection was closed");
     }
     using (var cmd = new MySQLCommand("COMMIT", Connection))
     {
         cmd.ExecuteNonQuery();
     }
 }
Example #3
0
 /// <inheritdoc />
 public void Rollback()
 {
     if (Connection == null)
     {
         throw new MySqlException("Connection was closed");
     }
     using (var cmd = new MySQLCommand("ROLLBACK", Connection))
     {
         cmd.ExecuteNonQuery();
     }
 }
Example #4
0
        internal MySQLTransaction(MySQLConnection connection, IsolationLevel isolationLevel)
        {
            Connection = connection;
            var sql = "";

            switch (isolationLevel)
            {
            case IsolationLevel.ReadCommitted:
                sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED";

                break;

            case IsolationLevel.ReadUncommitted:
                sql = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";

                break;

            case IsolationLevel.RepeatableRead:
                sql = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ";

                break;

            case IsolationLevel.Serializable:
                sql = "SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE";

                break;

            case IsolationLevel.Chaos:
                throw new MySqlException("MySQLDriverCS Error: Chaos isolation level is not implemented in MySQL.");
            }

            IsolationLevel = isolationLevel;
            using (var cmd = new MySQLCommand(sql, connection))
            {
                cmd.ExecuteNonQuery();
            }
            using (var cmd = new MySQLCommand("BEGIN", connection))
            {
                cmd.ExecuteNonQuery();
            }
        }
        /// <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","&lt;&gt;",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;
        }
Example #6
0
        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>
 /// Constructor from MySQLCommand and MySQLConnection
 /// </summary>
 /// <param name="strCommand"></param>
 /// <param name="conn"></param>
 public MySQLDataAdapter(String strCommand, MySQLConnection conn)
 {
     m_selectCommand = new MySQLCommand(strCommand, conn);
 }
 /// <summary>
 /// Constructor from MySQLCommand
 /// </summary>
 /// <param name="cmd"></param>
 public MySQLDataAdapter(MySQLCommand cmd)
 {
     m_selectCommand = cmd;
 }
Example #9
0
        /// <summary>
        /// This sample uses insert, update and delete. Assumes that you have the default database 'test'.
        /// </summary>
        public static void Program()
        {
            MySQLConnection conn = new MySQLConnection(new MySQLConnectionString("localhost", "test", "root", "").AsString);

            MessageBox.Show("Connecting to database");
            conn.Open();
            MySQLCommand cmd;

            cmd = new MySQLDriverCS.MySQLCommand("DROP TABLE IF EXISTS test.mysqldrivercs_test", conn);
            MessageBox.Show(cmd.CommandText);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            cmd = new MySQLDriverCS.MySQLCommand("CREATE TABLE test.mysqldrivercs_test(" +
                                                 "SettingID tinyint(3) unsigned NOT NULL auto_increment," +
                                                 "SettingValue text, " +
                                                 "PRIMARY KEY  (SettingID), UNIQUE KEY SettingID(SettingID),  KEY SettingID_2 (SettingID))" +
                                                 " TYPE=MyISAM COMMENT='MySQL test table'", conn);
            MessageBox.Show(cmd.CommandText);
            cmd.ExecuteNonQuery();
            cmd.Dispose();

            string Value     = "Value";
            int    SettingID = 1;

            MessageBox.Show("Inserting 1,\"Value\"");
            new MySQLInsertCommand(conn,
                                   new object[, ] {
                { "SettingID", SettingID }, { "SettingValue", Value }
            },
                                   "mysqldrivercs_test"
                                   );

            MessageBox.Show("Updating 1,\"Value2\"");
            Value = "Value2";
            new MySQLUpdateCommand(conn,
                                   new object[, ] {
                { "SettingValue", Value }
            },
                                   "mysqldrivercs_test",
                                   new object[, ] {
                { "SettingID", "=", SettingID }
            },
                                   null
                                   );

            MessageBox.Show("Retrieving 1");
            DataTable dt = new MySQLSelectCommand(conn,
                                                  new string[] { "SettingID", "SettingValue" },
                                                  new string[] { "mysqldrivercs_test" },
                                                  new object[, ] {
                { "SettingID", "=", SettingID }
            },
                                                  null,
                                                  null
                                                  ).Table;
            string storedValue = dt.Rows[0]["SettingValue"].ToString();

            MessageBox.Show("1 -> \"" + storedValue + "\"");

            MessageBox.Show("Deleting 1");
            new MySQLDeleteCommand(conn, "mysqldrivercs_test", new object[, ] {
                { "SettingID", "=", SettingID }
            }, null);

            MessageBox.Show("Closing database");
            conn.Close();
            MessageBox.Show("Program finished successfuly!");
        }
        /// <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;
        }