Open connection more than once in C#

Question

I am developing a service in c#.

1. calls a function to check if it has active records.
2. if you have any record call another function to select the data referring to the first query.

When I run my service it gives already open connection error.

So before I call each function I make one reader.Close(); and I no longer open the connection in the other functions. However I do not know if this is correct and if it would be the right way.

Step 1

public int VerifyStatus30(List<Class.ReturnTableName> tableInfo)
    {
        try
        {
            var countDis = 0;

            using (MySqlConnection conn = DB.DatabaseConnection.getHSDBConnection())
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand("SELECT DataObj, RecId FROM HS_REGISTRIES WHERE ErrorCode = 0 AND HandleStatus = 30 ORDER BY UpdateDate30 ASC LIMIT 1", conn);

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        countDis = 1;
                        while (reader.Read())
                        {
                            int dataObj = reader.GetInt32(0);
                            int recId = reader.GetInt32(1);
                            reader.Close();

                            ClassTable.ReturnSapId returnSapId = new ClassTable.ReturnSapId();
                            returnSapId.GetSapId(recId, dataObj, tableInfo);
                        }
                    }
                }
            }

            return countDis; // retorna a variável quantidade
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

Step 2

public void GetSapId(int recId, int dataObj, List<Class.ReturnTableName> tableInfo)
    {
        try
        {
            Class.ReturnTableName result = tableInfo.Find(x => x.IdIntegraHardness == dataObj);

            using (MySqlConnection conn = DB.DatabaseConnection.getHSDBConnection())
            {
                //conn.Open();
                MySqlCommand command = new MySqlCommand("SELECT AbsEntry,u_D005_id FROM HS501_ONCM WHERE RecId = @recId", conn);
                command.Parameters.AddWithValue("@tableName", result.TableIntegraHardness.Replace("'", ""));
                command.Parameters.AddWithValue("@recId", recId);

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            int idSAP = reader.GetInt32(0);//ID SAP
                            int idHardness = reader.GetInt32(1);//ID HARDNESS

                            Class.UpdateStatus updateStatus = new Class.UpdateStatus();
                            reader.Close();
                            updateStatus.Update40(recId, 1);
                            SaveIdSH(idSAP,idHardness, tableInfo, dataObj);
                            updateStatus.Update50(recId, 1);
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }

Answer 1

Why capture an exception to launch it again? What advantage do you think you have in this? There is only disadvantage! Why close something that will be closed more robust and correct at the end of the using? And contrary to what was said in the other answer need not use finally. If the connection is open, pass it to the other method instead of opening it again. I’m going to write better code, but actually all this architecture is already bad, but it’s not the focus of the question solving this, and it’s not even worth it because almost everyone does it wrong. See how the code gets cleaner:

public int VerifyStatus30(List<Class.ReturnTableName> tableInfo) {
    var countDis = 0;
    using (var conn = MySqlConnection(Ambiente.ConnectionString)) { //isto estaria em uma classe estática que tem a string, mas pode por na mão
    conn.Open();
    var command = new MySqlCommand("SELECT DataObj, RecId FROM HS_REGISTRIES WHERE ErrorCode = 0 AND HandleStatus = 30 ORDER BY UpdateDate30 ASC LIMIT 1", conn);
    using (var reader = command.ExecuteReader()) {
        if (reader.HasRows) {
            countDis = 1;
            while (reader.Read()) {
                int dataObj = reader.GetInt32(0);
                int recId = reader.GetInt32(1);
                ClassTable.ReturnSapId returnSapId = new ClassTable.ReturnSapId();
                returnSapId.GetSapId(recId, dataObj, tableInfo, conn);
            }
        }
    }
    return countDis;
}

public void GetSapId(int recId, int dataObj, List<Class.ReturnTableName> tableInfo, MySqlConnection conn) {
    Class.ReturnTableName result = tableInfo.Find(x => x.IdIntegraHardness == dataObj);
    var command = new MySqlCommand("SELECT AbsEntry,u_D005_id FROM HS501_ONCM WHERE RecId = @recId", conn);
    command.Parameters.AddWithValue("@tableName", result.TableIntegraHardness.Replace("'", ""));
    command.Parameters.AddWithValue("@recId", recId);
    using (var  reader = command.ExecuteReader()) {
        if (reader.HasRows) {
            while (reader.Read()) {
                int idSAP = reader.GetInt32(0);//ID SAP
                int idHardness = reader.GetInt32(1);//ID HARDNESS
                Class.UpdateStatus updateStatus = new Class.UpdateStatus();
                updateStatus.Update40(recId, 1);
                SaveIdSH(idSAP,idHardness, tableInfo, dataObj);
                updateStatus.Update50(recId, 1);
            }
        }
    }
}

I put in the Github for future reference.

Answer 2

at the end of each function, you can terminate the connection to the database regardless of error or not, use the Finally block{}:

public int VerifyStatus30(List<Class.ReturnTableName> tableInfo)
    {
        try
        {
            var countDis = 0;

            using (MySqlConnection conn = DB.DatabaseConnection.getHSDBConnection())
            {
                conn.Open();
                MySqlCommand command = new MySqlCommand("SELECT DataObj, RecId FROM HS_REGISTRIES WHERE ErrorCode = 0 AND HandleStatus = 30 ORDER BY UpdateDate30 ASC LIMIT 1", conn);

                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        countDis = 1;
                        while (reader.Read())
                        {
                            int dataObj = reader.GetInt32(0);
                            int recId = reader.GetInt32(1);
                            reader.Close();

                            ClassTable.ReturnSapId returnSapId = new ClassTable.ReturnSapId();
                            returnSapId.GetSapId(recId, dataObj, tableInfo);
                        }
                    }
                }
            }

            return countDis; // retorna a variável quantidade
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
       finally
       {
          conn.Close();
       }
    }