/// <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;
}
/// <inheritdoc />
public void Commit()
{
if (Connection == null)
{
throw new MySqlException("Connection was closed");
}
using (var cmd = new MySQLCommand("COMMIT", Connection))
{
cmd.ExecuteNonQuery();
}
}
/// <inheritdoc />
public void Rollback()
{
if (Connection == null)
{
throw new MySqlException("Connection was closed");
}
using (var cmd = new MySQLCommand("ROLLBACK", Connection))
{
cmd.ExecuteNonQuery();
}
}
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","<>",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;
}
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;
}
/// <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;
}
