C# MySqlCommand.ExecuteNonQueryAsync方法代码示例

        public override async Task<bool> updateAsync(object table)
        {
            var cliente = this.getCastCliente(table);
            using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
            {
                MySqlCommand miComando = new MySqlCommand()
                {
                    Connection = MidbConexion,
                    CommandText = @"UPDATE hotelcolonia.cliente SET  numpersonas=?numpersonas,nombre=?nombre,
                                   lugar_procedencia=?lugar_procedencia,correo=?correo,numtarjeta=?numtarjeta,telefono=?telefono
                                   WHERE id_cliente=?id_cliente"

                };
                MidbConexion.Open();
               
                miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
                miComando.Parameters.AddWithValue("?numpersonas", cliente.acompaniantes);
                miComando.Parameters.AddWithValue("?nombre", cliente.nombre);
                miComando.Parameters.AddWithValue("?lugar_procedencia", cliente.origen);
                miComando.Parameters.AddWithValue("?correo", cliente.email);
                miComando.Parameters.AddWithValue("?numtarjeta", cliente.tarjeta);
                miComando.Parameters.AddWithValue("?telefono", cliente.telefono);
                await miComando.ExecuteNonQueryAsync();


                await new CrudHabitacion().updateAsync(cliente.selectedHabitacion.numero, true);

            }
            return true;
        }

        public override async Task<bool> addAsync(object table)
        {
            var cliente = this.getCastCliente(table);
            using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
            {
                MySqlCommand miComando = new MySqlCommand()
                {
                    Connection = MidbConexion,
                    CommandText = @"INSERT INTO cliente(id_cliente,numpersonas,nombre,lugar_procedencia,correo,numtarjeta,telefono) 
                                    VALUES (?id_cliente,?numpersonas,?nombre,?lugar_procedencia,?correo,?numtarjeta,?telefono)"
                };
                MidbConexion.Open();
                miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
                miComando.Parameters.AddWithValue("?numpersonas", cliente.acompaniantes);
                miComando.Parameters.AddWithValue("?nombre", cliente.nombre);
                miComando.Parameters.AddWithValue("?telefono", cliente.telefono);
                miComando.Parameters.AddWithValue("?lugar_procedencia", cliente.origen);
                miComando.Parameters.AddWithValue("?correo", cliente.email);
                miComando.Parameters.AddWithValue("?numtarjeta", cliente.tarjeta);
                await miComando.ExecuteNonQueryAsync();
                miComando.Parameters.Clear();
                miComando.CommandText = @"INSERT INTO control_reservacion(clv_reservacion,dia_entrada,dia_salida,hora_entrada,hora_salida,id_cliente) 
                                    VALUES (?clv_reservacion,?dia_entrada,?dia_salida,?hora_entrada,?hora_salida,?id_cliente)";

                int claveReservacion = await this.getMaxAsync("control_reservacion", "clv_reservacion");
                miComando.Parameters.AddWithValue("?clv_reservacion", claveReservacion);
                miComando.Parameters.AddWithValue("?dia_entrada", cliente.dateEntrada);
                miComando.Parameters.AddWithValue("?dia_salida", cliente.dateSalida);
                miComando.Parameters.AddWithValue("?hora_entrada", cliente.dateEntrada.ToShortTimeString());
                miComando.Parameters.AddWithValue("?hora_salida", cliente.dateSalida.ToShortTimeString());
                miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
                await miComando.ExecuteNonQueryAsync();

                miComando.Parameters.Clear();

                miComando.CommandText = @"INSERT INTO detalle_reservacion(clv_reservacion,num_habitacion) 
                                        VALUES(?clv_reservacion,?num_habitacion)";

                miComando.Parameters.AddWithValue("?clv_reservacion", claveReservacion);
                miComando.Parameters.AddWithValue("?num_habitacion", cliente.selectedHabitacion.numero);

                await miComando.ExecuteNonQueryAsync();
                miComando.Parameters.Clear();
    
                int claveGasto = await this.getMaxAsync("gastos","clv_gasto");
                miComando.CommandText = "INSERT INTO gastos(clv_gasto,id_cliente) VALUES (?clv_gasto,?id_cliente)";
                miComando.Parameters.AddWithValue("clv_gasto", claveGasto);
                miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
                await miComando.ExecuteNonQueryAsync();

                await new CrudHabitacion().updateAsync(cliente.selectedHabitacion.numero, true);

                return true;

            }
        }

        public override async Task<bool> addAsync(object table)
        {
            var orden = this.getTable(table);
            using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
            {
                MidbConexion.Open();
                MySqlCommand miComando = new MySqlCommand()
                {
                    Connection = MidbConexion,
                    CommandText = "INSERT INTO orden(clv_orden,nummesa,fecha,id_cliente) VALUES (?clv_orden,?nummesa,?fecha,?id_cliente)"
                };
                miComando.Parameters.AddWithValue("?clv_orden", orden.claveOrden);
                miComando.Parameters.AddWithValue("?nummesa", orden.numMesa);
                miComando.Parameters.AddWithValue("?fecha", orden.fechaOrden);
                miComando.Parameters.AddWithValue("?id_cliente", orden.idCliente);
                await miComando.ExecuteNonQueryAsync();
                miComando.Parameters.Clear();
                foreach (var producto in listProducto)
                {
                    for (int i = producto.cantidad;i>0;i--)
                    {
                        miComando.CommandText = "INSERT INTO detalle_orden(clv_orden,clv_producto) VALUES(?clv_orden,?clv_producto)";
                        miComando.Parameters.AddWithValue("?clv_orden", orden.claveOrden);
                        miComando.Parameters.AddWithValue("?clv_producto", producto.clvProducto);
                        await miComando.ExecuteNonQueryAsync();
                        miComando.Parameters.Clear();
                    }
                   
                }
                
                miComando.CommandText = "INSERT INTO detalle_gasto(clv_gasto,clv_orden) VALUES(?clv_gasto,?clv_orden)";
                miComando.Parameters.AddWithValue("?clv_gasto",await new CrudCliente().getClienteGasto(orden.idCliente));
                miComando.Parameters.AddWithValue("?clv_orden", orden.claveOrden);
                await miComando.ExecuteNonQueryAsync();
                return true;

            }
        }

 public override async Task<bool> addAsync(object table)
 {
     var usuario = getCastUsuario(table);
     using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
     {
         MidbConexion.Open();//Abrimos la conexion para realizar los siguientes pasos
         // 'Declaro comando para poder realizar mis instrucciones sql
         MySqlCommand micomando = new MySqlCommand { Connection = MidbConexion, CommandText = "INSERT INTO usuario(nombre,password) VALUES (?Nom,?Pass)" };
         micomando.Parameters.AddWithValue("?Nom", usuario.nombre);
         micomando.Parameters.AddWithValue("?Pass", usuario.password);
         await micomando.ExecuteNonQueryAsync();
         return true;
     }
 }

        public async void add_app_to_store(string title, string description, double cost, int dev_id, bool in_app, string package_loc)
        {
            int retryCount = 3;
            bool success = false;
            MySqlCommand cmd = new MySqlCommand();
            while (retryCount > 0 && !success)
            {
                try
                {
                    conn.Open();

                    //->insert relevant data
                    cmd.Connection = conn;
                    cmd.CommandText = "COMMIT; ";
                    cmd.CommandText += "INSERT INTO software(title, description, dev_id, cost, in_app_purchases, package_location)" +
                        "VALUES(@title, @description, @dev_id, @cost, @in_app_purchases, @package_location);";
                    cmd.CommandText += "COMMIT; ";
                    cmd.Prepare();
                    //->add parameters to query
                    cmd.Parameters.AddWithValue("@title", title);
                    cmd.Parameters.AddWithValue("@description", description);
                    cmd.Parameters.AddWithValue("@dev_id", dev_id);       //->Datetime now in current timezone
                    cmd.Parameters.AddWithValue("@cost", cost);       //-> we are not actually using a bank, so its always a valid payment
                    cmd.Parameters.AddWithValue("@in_app_purchases", in_app);
                    cmd.Parameters.AddWithValue("@package_location", package_loc);

                    await cmd.ExecuteNonQueryAsync();
                    success = true;

                    if (conn != null)
                        conn.Close();
                    return;
                }
                catch (MySqlException ex)
                {
                    rollback(cmd);
                    Thread.Sleep(500);
                    if (ex.Number != 1205)
                    {
                        // a sql exception that is not a deadlock 
                        throw;
                    }
                    // Add delay here if you wish. 
                    retryCount--;
                    if (retryCount == 0) throw;
                }
            }
        }

 public override async Task<bool> deleteAsync(object table)
 {
     var producto = getCastProducto(table);
     using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
     {
         MidbConexion.Open();
         MySqlCommand miComando = new MySqlCommand()
         {
             Connection = MidbConexion,
             CommandText = @"DELETE FROM PRODUCTO WHERE clv_producto=?clv_producto"
         };
         miComando.Parameters.AddWithValue("?clv_producto", producto.clvProducto);
         await miComando.ExecuteNonQueryAsync();
         return true;
     }
 }

 public override async Task<bool> deleteAsync(object table)
 {
     var cliente = this.getCastCliente(table);
     using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
     {
         //http://blog.openalfa.com/como-trabajar-con-restricciones-de-clave-externa-en-mysql
         MySqlCommand miComando = new MySqlCommand()
         {
             Connection = MidbConexion,
             CommandText = "DELETE FROM cliente WHERE id_cliente =?id_cliente"
         };
         MidbConexion.Open();
         miComando.Parameters.AddWithValue("?id_cliente", cliente.id);
         await miComando.ExecuteNonQueryAsync();
     }
     return true;
 }

        public override async Task<bool> updateAsync(object table)
        {
            var habitacion = getCastHabitacion(table);
            using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
            {
                MidbConexion.Open();
                MySqlCommand miComando = new MySqlCommand()
                {
                    Connection = MidbConexion,
                    CommandText = "UPDATE habitacion SET estado=?estado,tamanio=?tamanio,precio=?precio,telefono=?telefono WHERE num_habitacion=?habitacion"
                };
                miComando.Parameters.AddWithValue("?estado", habitacion.estado == true ? "Ocupado" : "Disponible");
                miComando.Parameters.AddWithValue("?tamanio", habitacion.tamanio);
                miComando.Parameters.AddWithValue("?precio", habitacion.precio);
                miComando.Parameters.AddWithValue("?telefono", habitacion.telefono);
                miComando.Parameters.AddWithValue("?habitacion", habitacion.numero);
                await miComando.ExecuteNonQueryAsync();
                return true;

            }
        }

        public override async Task<bool> addAsync(object table)
        {
            var habitacion = getCastHabitacion(table);
            using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
            {
                MidbConexion.Open();
                MySqlCommand miComando = new MySqlCommand()
                {
                    Connection = MidbConexion,
                    CommandText = "INSERT INTO habitacion(num_habitacion,estado,tamanio,precio,telefono) VALUES (?habitacion,?estado,?tamanio,?precio,?telefono)"
                };
                miComando.Parameters.AddWithValue("?estado", habitacion.estado == true ? "Ocupado" : "Disponible");
                miComando.Parameters.AddWithValue("?tamanio", habitacion.tamanio);
                miComando.Parameters.AddWithValue("?precio", Convert.ToDouble(habitacion.precio));
                miComando.Parameters.AddWithValue("?telefono", habitacion.telefono);
                miComando.Parameters.AddWithValue("?habitacion", habitacion.numero);
                await miComando.ExecuteNonQueryAsync();
                return true;

            }
        }

 public override async Task<bool> updateAsync(object table)
 {
     var producto = getCastProducto(table);
     using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
     {
         MidbConexion.Open();
         MySqlCommand miComando = new MySqlCommand()
         {
             Connection = MidbConexion,
             CommandText = @"UPDATE PRODUCTO  SET descripcion=?descripcion, precio=?precio, cantidad=?cantidad, img_producto=?img_producto,categoria=?categoria  
                            WHERE clv_producto=?clv_producto"
         };
         miComando.Parameters.AddWithValue("?clv_producto", producto.clvProducto);
         miComando.Parameters.AddWithValue("?descripcion", producto.descripcion);
         miComando.Parameters.AddWithValue("?precio", producto.precio);
         miComando.Parameters.AddWithValue("?cantidad", producto.cantidad);
         miComando.Parameters.AddWithValue("?categoria", producto.categoria);
         miComando.Parameters.AddWithValue("?img_producto", producto.imgProducto);
         await miComando.ExecuteNonQueryAsync();
         return true;
     }
 }

        public async void process_transaction(int sid, int uid, string p_method)
        {       //->record users transaction details upon app purchase
            int retryCount = 3;
            bool success = false;
            MySqlCommand cmd = new MySqlCommand();
            while (retryCount > 0 && !success)
            {
                try
                {
                    // your sql here
                    success = true;
                    conn.Open();

                    //->insert relevant data
                    cmd.Connection = conn;
                    cmd.CommandText = "START TRANSACTION; ";
                    cmd.CommandText += "INSERT INTO transaction(sid, uid, payment_method, date, confirmed)" +
                        "VALUES(@sid, @uid, @payment_method, @date, @confirmed);";
                    
                    cmd.Prepare();
                    //->add parameters to query
                    cmd.Parameters.AddWithValue("@sid", sid);
                    cmd.Parameters.AddWithValue("@uid", uid);
                    cmd.Parameters.AddWithValue("@payment_method", p_method);       //-> Paypal, direct deposit, etc.
                    cmd.Parameters.AddWithValue("@date", DateTime.UtcNow.ToString());       //->Datetime now in current timezone
                    cmd.Parameters.AddWithValue("@confirmed", 1);       //-> we are not actually using a bank, so its always a valid payment
                    await cmd.ExecuteNonQueryAsync();

                    

                        string stm = "SELECT payment_method from transaction where trans_id = last_insert_id();";
                        stm += "COMMIT;";
                        cmd = new MySqlCommand(stm, conn);
                        MySqlDataReader rdr = cmd.ExecuteReader();
                        rdr.Read();
                        if (rdr.GetString(0) == "error")
                            Console.WriteLine("trigger activated upon bad value");
                        else
                            Console.WriteLine("succesful commit, trigger not activated");
                     
                    Console.WriteLine("rolling back test");
                        rollback(cmd);
                        

                    if (conn != null)
                        conn.Close();
                }
                catch (MySqlException ex) 
                {
                    rollback(cmd);
                    if (ex.Number != 1205)
                    {
                        // a sql exception that is not a deadlock 
                        Console.WriteLine("generic error");
                        throw;
                    }
                    // Add delay here if you wish. 
                    Console.WriteLine("deadlock detected");
                    Thread.Sleep(500);
                    retryCount--;
                    if (retryCount == 0) throw;
                }
            }
        }

        public bool set_user_account_details(int uid, string email, string password, string name, DateTime dob, string phone)
        {       //-> update user account details
             int retryCount = 3;
            bool success = false;
            MySqlCommand cmd = new MySqlCommand();
            while (retryCount > 0 && !success)
            {
                try
                {
                    if (login(email.ToString(), password.ToString()))
                    {       //-> check login
                        cmd.Connection = conn;
                        cmd.CommandText = "START TRANSACTION; ";
                        cmd.CommandText += "UPDATE users SET user_name = @user_name, password = @password," +
                            "name = @name, dob = @dob, phone= @phone WHERE user_id = " + uid + ";";
                        cmd.CommandText += "COMMIT; ";
                        conn.Open();
                        cmd.Prepare();
                        //->add parameters to query
                        cmd.Parameters.AddWithValue("@user_name", email);
                        cmd.Parameters.AddWithValue("@password", md5_encrypt(password.ToString()));
                        cmd.Parameters.AddWithValue("@name", name);       //-> Paypal, direct deposit, etc.
                        cmd.Parameters.AddWithValue("@dob", dob);       //->Datetime now in current timezone
                        cmd.Parameters.AddWithValue("@phone", phone);       //-> we are not actually using a bank, so its always a valid payment
                        cmd.ExecuteNonQueryAsync();

                        success = true;
                        if (conn != null)
                            conn.Close();

                        return true;
                    }
                }
                catch (MySqlException ex)
                {
                    rollback(cmd);
                    if (ex.Number != 1205)
                    {
                        if (ex.Number != 1205)
                        {
                            // a sql exception that is not a deadlock 
                            return false;
                        }
                        // Add delay here if you wish.
                        Thread.Sleep(500);
                        retryCount--;
                        if (retryCount == 0) throw;

                    }
                    else
                    {
                        return false;
                    }
                }
            }
             return false;
            }

 public async void create_account(string email, string password, DateTime dob, char gender, string phone, string name, int dev)
 {       //-> insert user account into database
     int retryCount = 3;
     bool success = false;
     MySqlCommand cmd = new MySqlCommand();
     while (retryCount > 0 && !success)
     {
         try
         {
             conn.Open();
             
             
             cmd.Connection = conn;
             cmd.CommandText = "START TRANSACTION; ";
             cmd.CommandText += "INSERT INTO users(user_name, password, name, dob, gender, phone, dev_id)" +
                 "VALUES(@user_name, @password, @name, @dob, @gender, @phone, @dev_id);";
             cmd.CommandText += "COMMIT; ";
             cmd.Prepare();
             //-> add parameters of this function to insert statement
             cmd.Parameters.AddWithValue("@user_name", email);
             cmd.Parameters.AddWithValue("@password", md5_encrypt(password));
             cmd.Parameters.AddWithValue("@name", name);
             cmd.Parameters.AddWithValue("@dob", dob);
             cmd.Parameters.AddWithValue("@gender", gender);
             cmd.Parameters.AddWithValue("@phone", phone);
             cmd.Parameters.AddWithValue("@dev_id", dev);
             
             await cmd.ExecuteNonQueryAsync();
             success = true;
         }
         catch (MySqlException ex)
         {       //-> error handline
             rollback(cmd);
             if (ex.Number != 1205)
             {
                 // a sql exception that is not a deadlock 
                 throw;
             }
             // Add delay here if you wish. 
             Thread.Sleep(500);
             retryCount--;
             if (retryCount == 0) throw;
         }
         finally
         {
             if (conn != null)
                 conn.Close();
         }
     }
 }

 public override async Task<bool>deleteAsync(object table)
 {
     var habitacion = getCastHabitacion(table);
     using (MySqlConnection MidbConexion = new MySqlConnection(this.dbPath))
     {
         MidbConexion.Open();
         MySqlCommand miComando = new MySqlCommand()
         {
             Connection = MidbConexion,
             CommandText = "DELETE FROM habitacion WHERE num_habitacion=?habitacion"
         };
         miComando.Parameters.AddWithValue("?habitacion", habitacion.numero);
         await miComando.ExecuteNonQueryAsync();
         return true;
     }
 }

 public async void update_software_tuple(int index, int dev_id, string tuple, string value)
 {
     int retryCount = 3;
     bool success = false;
     MySqlCommand cmd = new MySqlCommand();
     while (retryCount > 0 && !success)
     {
         try
         {
             // your sql here
             success = true;
             conn.Open();
             //->insert relevant data
             
             cmd.Connection = conn;
             cmd.CommandText = "START TRANSACTION; "; 
             cmd.CommandText += "Update software SET "+tuple+" = @value "+
                 "WHERE software_id = @index and dev_id = @dev_id;";
             cmd.CommandText += "COMMIT; ";
             cmd.Prepare();
             //->add parameters to query
             cmd.Parameters.AddWithValue("@dev_id", dev_id);
             cmd.Parameters.AddWithValue("@index", index);
             cmd.Parameters.AddWithValue("@value", value);
             await cmd.ExecuteNonQueryAsync();
             if (conn != null)
                 conn.Close();
         }
         catch (MySqlException ex)
         {
             rollback(cmd);
             if (ex.Number != 1205)
             {
                 // a sql exception that is not a deadlock 
                 throw;
             }
             // Add delay here if you wish. 
             Thread.Sleep(500);
             retryCount--;
             if (retryCount == 0) throw;
         }
     }
 }