예제 #1
0
        /// 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","&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;
        }
        /// <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;
        }