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;
}
/// <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;
}