How to connect with database and return values using Async/await task in Web Api controller c#

Question

Hello i am trying to connect with database and return values from a web api controller.I want to do it all the way asynchronous with new task to have better performance if it is possible.here is an example of what i have done.Am i doing it the correct way?Am i using correct the using statements?is the await correct in Open,Close connection and sda.FillAsync ?Thanks!

 public async Task<HttpResponseMessage> Get()
 {
   return await Task.Run(() => GetAllCustomers());
 }

    private async Task <HttpResponseMessage> GetAllCustomers()
    {
        DataTable Customers= new DataTable();

        using (MySqlConnection con = new MySqlConnection(""))
        using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM Customers", con))
        {
            try
            {
                if (con.State == ConnectionState.Closed)
                {
                    await con.OpenAsync();
                    cmd.CommandType = CommandType.Text;
                    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
                    await sda.FillAsync(Customers);
                }
            }

            catch (MySqlException ex)
            {
                ex.Message.ToString();
            }
            finally
            {
                await con.CloseAsync();
            }
        }
        return ControllerContext.Request
       .CreateResponse(HttpStatusCode.OK, new { Customers });
    }

public async Task<HttpResponseMessage> Get() AFAIK, it is recommend to call that Method GetAsync then. — Fildor
– Fildor, Commented Sep 1, 2017 at 11:03
I have a feeling that calling Task.Run explicitly is not needed in Get() — michauzo
– michauzo, Commented Sep 1, 2017 at 11:04
catch (MySqlException ex) { ex.Message.ToString(); - this is completely useless. Log it at least. But better return an Error Response. — Fildor
– Fildor, Commented Sep 1, 2017 at 11:05
I'm voting to close this question as off-topic because it is a request for code review. — Fildor
– Fildor, Commented Sep 1, 2017 at 11:08

score 1 · Accepted Answer · 2017-09-01 22:20:43Z

1

Replace

return await Task.Run(() => GetAllCustomers());

with

return await GetAllCustomers();

You don't need to run the service task explicitly (the controller method is already marked as async).

Rename GetAllCustomers to GetAllCustomersAsync (best-practice naming convention)

I prefer to code WebApis this way:

public async Task<IHttpActionResult> Get() 
{
    try 
    {
         var result = await GetAllCustomersAsync();
         return Ok(result);
    }
    catch(Exception ex) 
    {
        return InternalServerError(ex);
    }
}

UPDATE: An update to show how your database-operation can be simplified:

private async Task<Customers> GetAllCustomersAsync()
{
   var customers = new DataTable();
   using (var con = new MySqlConnection(""))
   using (var cmd = new MySqlCommand("SELECT * FROM Customers", con))
   {
       await con.OpenAsync();
       cmd.CommandType = CommandType.Text;
       var sda = new MySqlDataAdapter(cmd);
       await sda.FillAsync(customers);
   }
   return customers;
}

edited Sep 1, 2017 at 22:20

answered Sep 1, 2017 at 11:14

user725844

Sign up to request clarification or add additional context in comments.

2 Comments

srv8 Over a year ago

Thanks! do you know if the await in Open,Close connection and sda.FillAsync is correct ?

user725844 Over a year ago

I looks right (haven't tested). But I would remove the try-catch-finally block and the if-statement inside the try block. When you create a new connection it will start in a closed state. It shouldn't be necessary to close the connection. The connection is created inside a using-block, and will be disposed automatically (dispose = automatic close)

Collectives™ on Stack Overflow

How to connect with database and return values using Async/await task in Web Api controller c#

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related