/// Modified by Claudia Murialdo (07/24/04) in order to support
/// culture-independent format of numeric values in a stmt.
internal static string WhereQueryPart(object[,] WhereParamFields, string[,] WhereNoparamFields, string prefix, MySQLConnection conn)
{
string query = ""; int n;
bool Anything = false;
if ((WhereParamFields != null) && (WhereParamFields.Length != 0))
{
Anything = true;
n = WhereParamFields.GetLength(0);
for (int m = 0; m < WhereParamFields.GetLength(0); m++)
{
n--;
string Field = WhereParamFields[m, 0] as string;
string Operand = WhereParamFields[m, 1] as string;
object Value = WhereParamFields[m, 2];
if (Value == null)
{
query += " " + Field + " " + Operand + " 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 += " " + Field + " " + Operand + " " + Convert.ToString(Value, CultureInfo.InvariantCulture.NumberFormat) + " ";
}
else
{
query += " " + Field + " " + Operand + " " + MySQLUtils.Escape(Value.ToString(), conn) + " ";
}
if (n != 0)
{
query += " AND ";
}
}
}
if ((WhereNoparamFields != null) && (WhereNoparamFields.Length != 0))
{
if (Anything)
{
query += " AND ";
}
Anything = true;
n = WhereNoparamFields.GetLength(0);
for (int m = 0; m < WhereNoparamFields.GetLength(0); m++)
{
n--;
string Field = WhereNoparamFields[m, 0] as string;
string Operand = WhereNoparamFields[m, 1] as string;
object Value = WhereNoparamFields[m, 2];
if (Value == null)
{
query += " " + Field + " " + Operand + " NULL ";
}
else
{
query += " " + Field + " " + Operand + " " + Value.ToString() + " ";
}
if (n != 0)
{
query += " AND ";
}
}
}
if (Anything)
{
query = " " + prefix + " " + query;
}
return(query);
}
/// <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;
}