The 'await' operator can only be used within an async method [MySQL connector]

Question

This question is related 2 of my previous questions which have been posted on StackOverflow in last few days.

1st Question which was regarding a MySqlClient EndOfStreamException.

1st Question

The answer for the 1st question leads me to Switch libraries from MySQL ADO.NET to MySQL connector.

I had some issues with some MySQL keywords since moving from MySQL ADO.NET to MySQL connector. I got the answer to that question as well.

2nd Question

Now the program is running without any errors. But unfortunately, there is a catch since the data is not written to the database whatsoever.

With the intent of finding a solution to this issue I tried to add "await" keyword to ExecuteNonQueryAsync() as of the below code.

 private bool OpenConnection()
        {
            try
            {
                //connection.Open(); //line commented
                 **await connection.OpenAsync();**
                Console.WriteLine("MySQL connected.");
                return true;
            }
            catch (MySqlException ex)
            {

             }   





public void Insert()
            {
                    if (this.OpenConnection() == true)
                    {
                          using(var reader = new StreamReader(@"C:\Users\Admin\source\Bargstedt.csv"))
                    {
                    //List<string> listA = new List<string>();


                        while (!reader.EndOfStream)

                        {
                        ***await reader.OpenAsync();***

                        var line = reader.ReadLine();
                            var values = line.Split(',');
                            string querynew = "INSERT INTO jobs"
                                      + "(nJobNumber,strClientReference,datPromisedDelivery)" 
                                      + "VALUES (@jobNo, @strClientName, @strClientReference)";


                        var cmd = new MySqlCommand();

                        cmd.CommandText= querynew;




                        cmd.Parameters.AddWithValue("strClientName", "MySqlDbType.VarChar").Value =(values[1]);
                                cmd.Parameters.AddWithValue("strClientReference", "MySqlDbType.VarChar").Value = values[2];



                    ***await cmd.ExecuteNonQueryAsync();***
                        // cmd.ExecuteNonQueryAsync(); //Line commented

                    }
                    }
                    this.CloseConnection();
                }



        }

Before implemented this, I reckoned the issue was with the execution line since any of the data isn't written back to the database.

That is the reason it persuades me to include await open and execute lines to the source code likewise given in this example.

Unfortunately, in all the places I have used await keyword (Highlighted in the above code) is triggering an error:

Error CS4032 The 'await' operator can only be used within an async method. Consider marking this method with the 'async' modifier and changing its return type to 'Task'.

How to fix this issue and what do you think the reason which the database is not updating?

would it be fixed if this 'await' method implemented correctly?

Answer 1

Asynchronous code is more complicated. I would recommend that you stick with the (fully supported) synchronous methods until you get your database code working. So change await connection.OpenAsync(); back to connection.Open();, await cmd.ExecuteNonQueryAsync(); to cmd.ExecuteNonQuery(), etc.

(Don't call an Async method without using an await statement, because it will keep executing in the background while your primary method keeps running, which may cause the same connection to be used simultaneously on two separate threads, causing an error.)

The example code you gave doesn't add a parameter value for @jobNo. That will cause an exception because the parameter isn't defined.

The code cmd.Parameters.AddWithValue("strClientName", "MySqlDbType.VarChar").Value =(values[1]) is incorrectly adding a parameter with the literal string value "MySqlDbType.VarChar", then overwriting it. It would be more straightforward to write cmd.Parameters.AddWithValue("strClientName", values[1]);.

Once you get your database code working, and want to switch to async, you can use await within the method body, then redeclare the method signature with the async keyword, e.g., private async Task<bool> OpenConnection().