How to make async and await method in asp.net MVC with Web api2

Question

I’m newly to the ASP.NET MVC with Web API 2 and I created async and await method by using asp.net MVC with Web API 2 which is going to be store into the SQL DB. When I try to call my repository method in the API Controller I got an error cannot await ”system.collections.generic.list”. If anybody has idea about it kindly let me know.
Note:- I don’t want to use entity framework instead I want to store data through stored procedure.

Model:

namespace AsyncMVCWEBAPI.Models
{
    public class Product
    {
        public string Name { get; set; }
        public double Price { get; set; }
        public string Category { get; set; }
    }
}

API Controller:

public static async Task<Product> GetProduct()
{
    return await GetAllProduct(); // Here i'm getting an error can not await "system.collections.generic.list"
}

Repository:

public static IEnumerable<Product> GetAllProduct()
{           
    using (SqlConnection con = new SqlConnection(connectionString))
    {
        if (con.State == ConnectionState.Closed)
            con.Open();
        List<Product> students = new List<Product>();
        SqlCommand cmd = new SqlCommand("spGetAllStudentDetails", con);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlDataReader rdr = cmd.ExecuteReader();
        while (rdr.Read())
        {
            Product product = new Product();
            product.Price = Convert.ToInt32(rdr["Price"]);
            product.Name = rdr["Name"].ToString();
            product.Category = rdr["Category"].ToString();

            students.Add(product);

        }
        return students;
    }           
}

hutchonoid · Accepted Answer · 2016-05-04 08:27:12Z

3

The main problem is that you can't await a method that is not async. You should rewrite your GetAllProduct method to make it async with the following signature:

public static async Task<IEnumerable<Product>> GetAllProduct()

Also don't forget to use Async methods to get your data from database:

    ...
    await con.OpenAsync();
    ...
    SqlDataReader rdr = await cmd.ExecuteReaderAsync();
    while (await rdr.ReadAsync())
    {
        ...
        students.Add(product);
    }
    return students;

edited May 4, 2016 at 8:27

hutchonoid

33.4k15 gold badges102 silver badges106 bronze badges

answered May 4, 2016 at 7:18

Ivan Yuriev

4889 silver badges14 bronze badges

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

3 Comments

Parthiban Over a year ago

Thanks for your wonderfull support Ivan Yuriev, It'll working fine. I have another doubt which is what is the difference between await con.OpenAsync() and con.Open(), rdr.ReadAsync() and rdr.Read(), await cmd.ExecuteReaderAsync() and await cmd.ExecuteReader(). Please kindly let me know?

Ivan Yuriev Over a year ago

These methods help you use async\await feature of .net 4.5 The main idea is to simplify writing a async code (async is not multithreading by the way). So, your main working thread is free while you're waiting for smth: web request, database call, etc. And you utilize a resources more efficiently, You'd better read about it in official manual: msdn.microsoft.com/en-us/library/hh191443.aspx

Parthiban Over a year ago

Thanks Ivan Yuriev. That's very useful.

peco · Accepted Answer · 2016-05-04 10:07:03Z

0

As the previous answer said you have to change your signature to:

public static async Task<IEnumerable<Product>> GetAllProduct()

If you want to skip the ADO.NET boilerplate stuff, you can instead use Dapper (https://github.com/StackExchange/dapper-dot-net) to simplify the code:

public static async Task<IEnumerable<Product>> GetAllProduct()
{
    using (var con = new SqlConnection(connectionString))
    {
        await con.OpenAsync();
        return await con.QueryAsync<Product>("spGetAllStudentDetails", commandType: CommandType.StoredProcedure);
    }
}

answered May 4, 2016 at 10:07

peco

4,0001 gold badge23 silver badges27 bronze badges

Collectives™ on Stack Overflow

How to make async and await method in asp.net MVC with Web api2

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related