How to make two queries using the same Mysqlconnection?

Question

I have the following code snippet:

using(MySqlConnection conn = new MySqlConnection(conexao)){
    try
    {
        string consulta = "SELECT * FROM tb_Cliente WHERE ID_Cliente IN ("+listaClientes+")";
        MySqlCommand cmd = new MySqlCommand(consulta,conn);
        conn.Open();
        MySqlDataReader reader = cmd.ExecuteReader();
        while(reader.Read())
        {
            using(MySqlConnection conn2 = new MySqlConnection(conexao))
            {
                try
                {
                    consulta = "INSERT INTO tb_Teste (ID_Cliente,Nome_Cliente,Idade_Cliente,Data_Insert) VALUES (@ID_Cli,@Nome_Cli,@Idade_Cli,@Data_insert)";
                    MySqlCommand cmd2 = new MySqlCommand(consulta,conn2);
                    cmd2.Parameters.AddWithValue("@ID_Cli",reader["ID_Cliente"]);
                    cmd2.Parameters.AddWithValue("@Nome_Cli",reader["Nome_Cliente"]);
                    cmd2.Parameters.AddWithValue("@ID_Cli",reader["ID_Cliente"]);
                    cmd2.Parameters.AddWithValue("@Idade_Cli",reader["Idade_Cliente"]);
                    cmd2.Parameters.AddWithValue("@Data_insert",DateTime.Now);
                    conn2.Open();
                    cmd2.ExecuteNonQuery();
                    conn2.Close();
                }
                catch(MySqlException ex)
                {
                    EscreverLog("ERRO: " + ex.Message);
                }
            }
        }
    }
    catch(MySqlException ex)
    {
        EscreverLog("ERRO: " + ex.Message);
    }
    finally
    {
        conn.Close();
    }
}

In it I do a SELECT searching all clients in the range specified by the string listClientes and for each result I do an INSERT in another table, with other information more.

Running the code everything works normal but I think it’s wrong to open several Mysqlconnection and Mysqlcommands. How can I change the code to make it less "messy"? For example using the same connection or even the same Command.

Answer 1

You should use the same connection, it is not a good business to keep opening several connections to the same situation, so a minimal example, but, with rescues:

• What would be: listaClientes, exemplify?
• Pay attention to the part about adding vestments always putting one clear!
• Beware of reader already opened, after using give a Close() or a Dispose() (the second is the best way, to soon release this resource when it does not make sense to exist).

A basic code, serving as a minimum example:

First create the template to receive the values from the other table and use the DataReader and use the method Dispose (using already solves as shown in the example, the same method is called at the end of this key of the using). With this data prepared use the structure foreach and pass the value to the new table, example:

---

public class Ex 
{
    public int Id {get;set;}
    public string Nome {get;set;}
    public int Idade {get;set;}
    public DateTime DataAdd {get;set;} = DateTime.Now;
}
static void Example()
{
    string conexao = "";
    string listaClientes = "";
    using (MySqlConnection conn = new MySqlConnection(conexao))
    {
        conn.Open();
        try
        {
            string consulta = " SELECT * FROM tb_Cliente WHERE ID_Cliente IN  ";
            consulta += " (" + listaClientes + ") ";
            List<Ex> listEx = neew List<Ex>();
            using (MySqlCommand cmd = new MySqlCommand(consulta, conn))
            {
                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    consulta = " INSERT INTO tb_Teste (ID_Cliente,Nome_Cliente,Idade_Cliente,Data_Insert) ";
                    consulta += " VALUES (@ID_Cli,@Nome_Cli,@Idade_Cli,@Data_insert)";
                    while (reader.Read())
                    {
                        try
                        {
                            listEx.add(new Ex {
                                Id = reader.GetInt32(0),
                                Nome = reader.getString(1),
                                Idade = reader.getInt32(2)                              
                            });
                        }
                        catch (MySqlException ex)
                        {
                            //EscreverLog("ERRO: " + ex.Message);
                        }
                    }                   
                }
            }
            
            using (MySqlCommand cmd2 = new MySqlCommand(consulta, conn))
            {
                foreach(var e in listEx)
                {
                    try
                    {
                        cmd2.Parameters.Clear();                                        
                        cmd2.Parameters.AddWithValue("@ID_Cli", e.Id);
                        cmd2.Parameters.AddWithValue("@Nome_Cli", e.Nome);
                        cmd2.Parameters.AddWithValue("@ID_Cli", e.Id);
                        cmd2.Parameters.AddWithValue("@Idade_Cli", e.Idade);
                        cmd2.Parameters.AddWithValue("@Data_insert", e.DataAdd);
                        cmd2.ExecuteNonQuery();
                    }
                    catch (MySqlException ex)
                    {
                        //EscreverLog("ERRO: " + ex.Message);
                    }
                }
            }
        }
        catch (MySqlException ex)
        {
            //EscreverLog("ERRO: " + ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }
}

There is a way to transfer information from one table to another without having to for, loop, create structures, that is, directly in SQL

static void SQL()
{
  StringBuilder str = new StringBuilder();
  str.Append(" INSERT INTO tb_Teste (ID_Cliente,Nome_Cliente,Idade_Cliente,Data_Insert) ");
  str.Append(" SELECT ID_Cliente,Nome_Cliente,Idade_Cliente,Data_Insert FROM tb_Cliente ");
  str.Append(" WHERE ID_Cliente IN(" + listaClientes + ")");
  using (MySqlConnection conn = new MySqlConnection(conexaoStr))
  using (MySqlCommand command1 = new MySqlCommand(str.ToString(), conn))
  {
    conn.Open();
    command1.ExecuteNonQuery();
  }
}

would be a practical way to pass values between tables.

Answer 2

You do not need to use other Connections, just use the same, what you need is to change the command or create another, and here there is no problem, because each Command is a different operation:

using(MySqlConnection conn = new MySqlConnection(conexao)){
    try
    {
        string consulta = "SELECT * FROM tb_Cliente WHERE ID_Cliente IN ("+listaClientes+")";
        MySqlCommand cmd = new MySqlCommand(consulta,conn);
        conn.Open();
        MySqlDataReader reader = cmd.ExecuteReader();
        MySqlCommand cmd2 = new MySqlCommand(consulta,conn);

        while(reader.Read())
        {
            consulta = "INSERT INTO tb_Teste (ID_Cliente,Nome_Cliente,Idade_Cliente,Data_Insert) VALUES (@ID_Cli,@Nome_Cli,@Idade_Cli,@Data_insert)";
            cmd2.Parameters.Clear();
            cmd2.Parameters.AddWithValue("@ID_Cli",reader["ID_Cliente"]);
            cmd2.Parameters.AddWithValue("@Nome_Cli",reader["Nome_Cliente"]);
            cmd2.Parameters.AddWithValue("@ID_Cli",reader["ID_Cliente"]);
            cmd2.Parameters.AddWithValue("@Idade_Cli",reader["Idade_Cliente"]);
            cmd2.Parameters.AddWithValue("@Data_insert",DateTime.Now);
            cmd2.ExecuteNonQuery();
        }
    }
    catch(MySqlException ex)
    {
        EscreverLog("ERRO: " + ex.Message);
    }
}

Note that, you can share the same Connection, which is closed when leaving the block using.

That’s personal, but I’d rather put the using within the try:

 try
 {
    using(MySqlConnection conn = new MySqlConnection(conexao)){
    }
 }

This is because it will also treat an error when creating Connection, in your example only treats errors after opening Connection.

Still on Connections, a good idea is to create a Pool of connectivions that remain open for a certain time, then ai use the new MySqlConnection(conexao) a connection that already exists and open will be used, of course if it is the same credentials.

You can read more about it here: https://dev.mysql.com/doc/connector-net/en/connector-net-connections-string.html