0

I have been trying to get data from the table. The code can be found below. When I test the API I get an error "This MySqlConnection is already in use". Can anyone tell me what wrong am I doing over here? Or missing to mention some more details here?

    public async Task<object> GetFormListDetails()
    {
        try
        {
            MySqlConnection con = new MySqlConnection(@"Server=Localhost;Database=HotelManagement;Uid=root;Pwd=Onkar@123;");
            con.Open();
            MySqlCommand cmd = new MySqlCommand("select * from HotelManagement.MealType", con);

            MySqlDataReader tableData = cmd.ExecuteReader();

            var data = cmd.ExecuteNonQuery();
            var count = tableData.FieldCount;
            var myString = String.Empty;
            using (tableData)
            {
                while (tableData.Read())
                {
                    for (var i = 0; i < count; i++)
                    {
                        myString = tableData.GetValue(i).ToString();
                    }
                    tableData.Close();
                    return await Task.FromResult(new ResponseModel(ResponseCode.OK, myString, null));
                }
                con.Close();
                return await Task.FromResult(new ResponseModel(ResponseCode.Error, "", "Faild to add data"));
            }
            
        }

        catch (Exception ex)
        {
            return await Task.FromResult(new ResponseModel(ResponseCode.Error, ex.Message, null));
        }
    }

The stack trace shows the below details.

 at MySqlConnector.Core.ServerSession.StartQuerying(ICancellableCommand command) in /_/src/MySqlConnector/Core/ServerSession.cs:line 294
   at MySqlConnector.Core.CommandExecutor.<ExecuteReaderAsync>d__0.MoveNext() in /_/src/MySqlConnector/Core/CommandExecutor.cs:line 56
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at MySqlConnector.MySqlCommand.<ExecuteNonQueryAsync>d__69.MoveNext() in /_/src/MySqlConnector/MySqlCommand.cs:line 271
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at MySqlConnector.MySqlCommand.ExecuteNonQuery() in /_/src/MySqlConnector/MySqlCommand.cs:line 101
   at Hotel.Controllers.RoomController.<GetFormListDetails>d__2.MoveNext() in /Users/onkar/Projects/hm/HM/Controllers/RoomController.cs:line
Onkar Nirhali
  • 15
  • 3
  • 1
    can it be that you shortened the error text#? don't do that the interesting part is missing – nbk Nov 06 '21 at 01:14
  • 1
    you put a return before close the connection. – Leandro Bardelli Nov 06 '21 at 01:28
  • 2
    also consider wrapping your connection in a `using` statement so that it will be closed when disposed. – Nkosi Nov 06 '21 at 01:30
  • Closest thing I could find that this might be a duplicate https://stackoverflow.com/questions/53627973/this-mysqlconnection-is-already-in-use – Nkosi Nov 06 '21 at 01:38
  • The exception is thrown by `cmd.ExecuteNonQuery()` because your `tableData` is still open, blocking the connection `con`. A note: your code seems to be asynchronous but you are only using synchronous methods. So you'll not have any benefits from async this way. You should use the async methods provided by the classes in MySqlConnector, like `ExecuteReaderAsync`, `ReadAsync` and so on. You could also return `Task` instead of Task. – Steeeve Nov 06 '21 at 02:10

1 Answers1

1

Please comment out this line of code as such and try it again:

//var data = cmd.ExecuteNonQuery();

Not 100% certain without running the code myself but, I believe that is the issue. In your code, you are already calling cmd.ExecuteReader() to retrieve your data. MySql seems to have an issue with interleaving these database calls.

Glenn Kees
  • 106
  • 4