ASP.NET MVC5 - Asynchronous Methods in Controller

Asked

Viewed 2,713 times

5

I’m developing an MVC layer for an ASP.NET system written initially in Webforms. It does not use Entityframework, so a good part of the system I had to elaborate a home solution.

In order to use the asynchronous Ajax methods with the MVC5 Controllers, I wrote as an example the following method:

[Authorize]
public async Task<JsonResult> IndexAsync()
{
    var pessoas = new Pessoas(GeneralSettings.DataBaseConnection)
        .Selecionar()
        .Select(p => new { NOME = p.Nome, CPF = p.Cpf, FONE = p.TelefoneResidencial, CELULAR = p.TelefoneCelular, DEPARTAMENTO = "Teste", EMAIL = p.Email })
        .Take(10);
    return Json(pessoas, JsonRequestBehavior.AllowGet);
}

This method is not asynchronous because return Json(... run synchronously. The following message appears:

This async method Lacks 'await' Operators and will run synchronously. Consider using the 'await' Operator to await non-blocking API calls, or 'await Task.Run(...)' to do CPU-bound work on a background thread.

How should I change the method so that the execution is asynchronous?

EDIT

The @dcastro asked for the method Selecionar(), which is reproduced below:

public override IEnumerable<Pessoa> Selecionar(IEnumerable<Operador> operadores)
{
    using (var obj = new Database())
    {
        var sSql =
            "select p.ID_PESSOA, p.NOME_COMPLETO, p.APELIDO, p.EMAIL_PESSOAL, p.NOME_PAI, p.NOME_MAE, p.SEXO, p.CPF, p.RG, p.ORGAO_RG, " +
            " p.EMISSAO_RG, p.DATA_NASC, p.LOCAL_NASC, p.ENDERECO, p.NUMERO, p.COMPLEMENTO, p.BAIRRO, p.CIDADE, p.ESTADO, p.DDD_FONE_RES, " +
            " p.FONE_RES, p.DDD_FONE_CEL, p.CELULAR, p.ID_BANCO_TALENTOS, p.ESTADO_CIVIL, p.ID_NACIONALIDADE, p.DEFICIENTE, p.TAMANHO_SAPATO, " +
            " p.TAMANHO_CAMISETA, p.ALERGIA, p.NOME_CONJUGE, p.DDD_TEL_EMERGENCIA, p.TEL_EMERGENCIA, p.DDD_CEL_EMERGENCIA, p.CEL_EMERGENCIA, " +
            " p.CONTATO_EMERGENCIA, p.ID_FORMACAO_ACADEMICA, p.PARENTESCO, p.FOTO_PESSOA, p.RI, p.TITULO_ELEITOR, p.ZONA, p.SECAO, " +
            " p.CART_TRABALHO, p.SERIE, p.PIS, p.CPF_CONJUGE, p.LINKEDIN, p.FACEBOOK, p.TWITTER, p.ID_ETNIA, p.CEP " +
            " from PESSOAS p ";

        foreach (var operador in operadores)
        {
            sSql += WhereOuAnd + " p." + operador;
        }

        var parametros = operadores.Where(o => o.GetType().IsAssignableFrom(typeof(Igual))).Select(o2 => ((Igual)o2).ParametroOracle).ToList();
        var dataTable = obj.ConsultarSQl(ConexaoBancoDados, sSql, parametros);

        foreach (DataRow linha in dataTable.Rows)
        {
            yield return new Pessoa
            {
                PessoaId = Convert.ToInt32(linha["ID_PESSOA"].ToString()),
                Nome = linha["NOME_COMPLETO"].ToString(),
                Apelido = linha["APELIDO"].ToString(),
                Email = linha["EMAIL_PESSOAL"].ToString(),
                NomePai = linha["NOME_PAI"].ToString(),
                NomeMae = linha["NOME_MAE"].ToString(),
                Sexo = linha["SEXO"].ToString(),
                Cpf = linha["CPF"].ToString(),
                Rg = linha["RG"].ToString(),
                OrgaoEmissorRg = linha["ORGAO_RG"].ToString(),
                DataEmissaoRg = (!String.IsNullOrEmpty(linha["EMISSAO_RG"].ToString())) ? Convert.ToDateTime(linha["EMISSAO_RG"].ToString()) : DateTime.MinValue,
                Nascimento = (!String.IsNullOrEmpty(linha["DATA_NASC"].ToString())) ? Convert.ToDateTime(linha["DATA_NASC"].ToString()) : DateTime.MinValue,
                LocalNascimento = linha["LOCAL_NASC"].ToString(),
                Endereco = linha["ENDERECO"].ToString(),
                Numero = linha["NUMERO"].ToString(),
                Complemento = linha["COMPLEMENTO"].ToString(),
                Bairro = linha["BAIRRO"].ToString(),
                Cidade = linha["CIDADE"].ToString(),
                Estado = linha["ESTADO"].ToString(),
                DddTelefoneResidencial = (!String.IsNullOrEmpty(linha["DDD_FONE_RES"].ToString())) ? Convert.ToInt32(linha["DDD_FONE_RES"].ToString()) : Int32.MinValue,
                TelefoneResidencial = (!String.IsNullOrEmpty(linha["FONE_RES"].ToString())) ? Convert.ToInt32(linha["FONE_RES"].ToString()) : Int32.MinValue,
                DddCelular = (!String.IsNullOrEmpty(linha["DDD_FONE_CEL"].ToString())) ? Convert.ToInt32(linha["DDD_FONE_CEL"].ToString()) : Int32.MinValue,
                TelefoneCelular = (!String.IsNullOrEmpty(linha["CELULAR"].ToString())) ? Convert.ToInt32(linha["CELULAR"].ToString()) : Int32.MinValue,
                BancoTalentosId = linha["ID_BANCO_TALENTOS"].ToString(),
                EstadoCivil = linha["ESTADO_CIVIL"].ToString(),
                NacionalidadeId = (!String.IsNullOrEmpty(linha["ID_NACIONALIDADE"].ToString())) ? Convert.ToInt32(linha["ID_NACIONALIDADE"].ToString()) : Int32.MinValue,
                Deficiente = linha["DEFICIENTE"].ToString(),
                TamanhoSapato = (!String.IsNullOrEmpty(linha["TAMANHO_SAPATO"].ToString())) ? Convert.ToInt32(linha["TAMANHO_SAPATO"].ToString()) : Int32.MinValue,
                TamanhoCamiseta = linha["TAMANHO_CAMISETA"].ToString(),
                Alergia = linha["ALERGIA"].ToString(),
                NomeConjuge = linha["NOME_CONJUGE"].ToString(),
                DddTelefoneEmergencia = (!String.IsNullOrEmpty(linha["DDD_TEL_EMERGENCIA"].ToString())) ? Convert.ToInt32(linha["DDD_TEL_EMERGENCIA"].ToString()) : Int32.MinValue,
                TelefoneEmergencia = (!String.IsNullOrEmpty(linha["TEL_EMERGENCIA"].ToString())) ? Convert.ToInt32(linha["TEL_EMERGENCIA"].ToString()) : Int32.MinValue,
                DddCelularEmergencia = (!String.IsNullOrEmpty(linha["DDD_CEL_EMERGENCIA"].ToString())) ? Convert.ToInt32(linha["DDD_CEL_EMERGENCIA"].ToString()) : Int32.MinValue,
                CelularEmergencia = (!String.IsNullOrEmpty(linha["CEL_EMERGENCIA"].ToString())) ? Convert.ToInt32(linha["CEL_EMERGENCIA"].ToString()) : Int32.MinValue,
                ContatoEmergencia = linha["CONTATO_EMERGENCIA"].ToString(),
                FormacaoAcademicaId = (!String.IsNullOrEmpty(linha["ID_FORMACAO_ACADEMICA"].ToString())) ? Convert.ToInt32(linha["ID_FORMACAO_ACADEMICA"].ToString()) : Int32.MinValue,
                Parentesco = linha["PARENTESCO"].ToString(),
                FotoPessoa = (linha["FOTO_PESSOA"].ToString() != "") ? (Byte[])linha["FOTO_PESSOA"] : new byte[0],
                RI = linha["RI"].ToString(),
                TituloEleitor = linha["TITULO_ELEITOR"].ToString(),
                Zona = linha["ZONA"].ToString(),
                Secao = linha["SECAO"].ToString(),
                CarteiraTrabalho = linha["SECAO"].ToString(),
                Serie = linha["SECAO"].ToString(),
                Pis = linha["PIS"].ToString(),
                CpfConjuge = linha["CPF_CONJUGE"].ToString(),
                Linkedin = linha["LINKEDIN"].ToString(),
                Facebook = linha["FACEBOOK"].ToString(),
                Twitter = linha["TWITTER"].ToString(),
                EtniaId = (!String.IsNullOrEmpty(linha["ID_ETNIA"].ToString())) ? Convert.ToInt32(linha["ID_ETNIA"].ToString()) : Int32.MinValue,
                Cep = linha["CEP"].ToString()
            };
        }
    }
}

Method ConsultarSql (i did not write this code. It is part of the data access scheme that already existed):

/// <summary>
/// Método utilizado para a execução de pesquisas no banco de dados com o envio de uma coleção de parametros.
/// </summary>
/// <param name="pStringConexao">String de conexão com o banco de dados.</param>
/// <param name="pSQL">Enviar o comando SQL que será executado.</param>
/// <param name="pParams">Coleção de parametros esperados no comeando SQL.</param>
/// <returns>Retorna um DataTable com o resultado da pesquisa.</returns>
public DataTable ConsultarSQl(string pStringConexao, string pSQL, List<OracleParameter> pParams)
{
    #region Abre a Conexão

    OracleConnection cn = new OracleConnection();

    try
    {
        cn = GetConnection(pStringConexao);
        cn.Open();
    }
    catch (Exception ex)
    {
        cn.Dispose();
        throw ex;
}

    #endregion

    OracleCommand dbCommand = new OracleCommand(pSQL, cn);
    dbCommand.CommandType = CommandType.Text;

    foreach (OracleParameter param in pParams)
    {
        if (param.Value != null)
        dbCommand.Parameters.Add(param);
        }

    OracleDataAdapter oAdp = new OracleDataAdapter(dbCommand);
    DataSet ds = new DataSet();

    try
    {
        oAdp.Fill(ds);
    }
    catch (Exception ex)
    {
        if (cn.State == ConnectionState.Open)
        {
            cn.Close();
        }

        dbCommand.Dispose();
        cn.Dispose();
        throw ex;
    }
    finally
    {
        if (cn.State == ConnectionState.Open)
        {
            cn.Close();
        }

        dbCommand.Dispose();
        cn.Dispose();
    }

    return ds.Tables[0];
}

If there is need to put more codes, just ask via comments.

c# oracle asp.net-mvc-5 async

  • What is the API you are using to select people from the database? You can show the line of code that makes the call?

  • This is the line: var pessoas = new Pessoas(GeneralSettings.DataBaseConnection)&#xA; .Selecionar()&#xA; .Select(p => new { NOME = p.Nome, CPF = p.Cpf, FONE = p.TelefoneResidencial, CELULAR = p.TelefoneCelular, DEPARTAMENTO = "Teste", EMAIL = p.Email })&#xA; .Take(10);. This method Selecionar makes access to the bank.

  • Yeah, but what’s the code inside .Selecionar()? This code may call other methods, and these may call other methods, etc., but I need to know which line of code actually communicates directly with the database API. And what library is being used for that.

  • @dcastro I edited the question explaining the method Selecionar().

  • This is not yet the lowest layer of the system :/ This code called ConsultarSQl() on a type object Database... Don’t you know what technology is being used? You said that not Entity Framework, then what is it? ADO.NET? Sqlite-net? I’m sorry if my question was unclear.

  • I apologize. I will edit again.

  • @dcastro See now.

  • Are you using the OracleClient, so' needed to know this :) Now I’m busy, but later I answer the question. Spoiler: It’s going to take a lot of code changing to make everything signature :/

  • No problem: I win the jewel and I change the Accept if the answer contemplates everything.

Show 4 more comments

3 answers

2

If your method doesn’t actually do any asynchronous operation, it makes no sense to have Keyword async.

And asynchronous methods should be preceded by the keyword await for them to be executed and awaited, releasing the current thread meanwhile.

Original code

[Authorize]
public async Task<JsonResult> IndexAsync()
{
    var pessoas = new Pessoas(GeneralSettings.DataBaseConnection)
        .Selecionar()
        .Select(p => new { NOME = p.Nome, CPF = p.Cpf, FONE = p.TelefoneResidencial, CELULAR = p.TelefoneCelular, DEPARTAMENTO = "Teste", EMAIL = p.Email })
        .Take(10);

    return Json(pessoas, JsonRequestBehavior.AllowGet);
}

Ideal code

Assuming the Select method is asynchronous (which only makes sense if it internally does network I/O or filesystem asynchronously as well).

[Authorize]
public async Task<JsonResult> IndexAsync()
{
    var model = new Pessoas(GeneralSettings.DataBaseConnection);
    var pessoas = await model.Selecionar();
    var result = pessoas
        .Select(p => new { NOME = p.Nome, CPF = p.Cpf, FONE = p.TelefoneResidencial, CELULAR = p.TelefoneCelular, DEPARTAMENTO = "Teste", EMAIL = p.Email })
        .Take(10);

    return Json(result, JsonRequestBehavior.AllowGet);
}

OBS.:

Access is not recommended straightforward an asynchronous relational database.

  • Within the method Selecionar() i have some additional operations, so access is not direct. Thank you for the reply.

  • This Select method is asynchronous and returns a Task<T>? If it’s not, the Task.Run placement doesn’t solve much either. If it is, the asynchronous method should receive the keyword await, otherwise you will try to call an extensionMethod of Ienumerable<T> in a Task, which makes no sense and does not work.

  • 1

    Well, now just edit the other methods so that the internal call is also asynchronous. Thanks again.

2

To understand why new threads should not be created or use Threadpool (with Task.Run for example), see this question: Async operations on ASP.NET. (I found this explanation too complex, so I created a question of my own).

Problems

Problem #1: the System.Data.OracleClient Microsoft has been deprecated - Microsoft recommends the use of other libraries.

The types in System.Data.Oracleclient are deprecated. The types remain supported in the Current version of.NET Framework but will be Removed in a Future release. Microsoft Recommends that you use a third-party Oracle Provider.

Alternatively, Oracle itself makes the library available ODP.NET (Oracle Data Provider). It is here that the...

Problem #2: ODP.NET library does not support asynchronous calls! (see discussion: https://community.oracle.com/thread/1043399).

Solutions

In this case, you have 3 possible solutions:

  1. (Not recommended) Continue using OracleClient Microsoft, although not advised by Microsoft.
  2. Migrate to ODP.NET and use synchronous calls.
  3. Migrate to Sql Server, and use libraries up-to-date and with call support truly asynchronous.

Solution 3 involves a very large change in system architecture and infrastructure in general.

In most projects, the team tries to avoid changes of this dimension. But still, consider exposing the situation to the other team members and the project manager. Sometimes it is preferable to make a big change today, than an even bigger change tomorrow ;)

In addition, the benefits of making asynchronous calls to' database are much tempters. This is a critical aspect to achieve a scalable and high-throughput web application.

In case this is not possible, I recommend at least migrating to ODP.NET (solution #2). The whole process has to be synchronous (no Tasks, no await), but at least you’ll be using up-to-date libraries with better support. The migration should be relatively easy, the API is very similar (or equal) to the API of the OracleClient.

ODP.NET API: http://docs.oracle.com/html/A96160_01/intro.htm#1007745

  • Your answer is great, but unfortunately I can’t leave the topology of Oracle. If I could, I would possibly be using direct the EntityFramework, and not this low-quality home solution. Thank you!

  • @Gypsy :)

1


I think the asynchronous execution gets more interesting in the data call part of the database:

[Authorize]
public async Task<JsonResult> IndexAsync()
{
    var pessoas = await Task.Run(() => new Pessoas(GeneralSettings.DataBaseConnection)
        .Selecionar()
        .Select(p => new { NOME = p.Nome, CPF = p.Cpf, FONE = p.TelefoneResidencial, CELULAR = p.TelefoneCelular, DEPARTAMENTO = "Teste", EMAIL = p.Email })
        .Take(10));

    return Json(pessoas, JsonRequestBehavior.AllowGet);
}

The problem is this: A method async must expect the response of some action at some point. But the compiler will not know where to wait on his own, he will wait when he finds await. And to "convert" a synchronous run to asynchronous, you use a Task.

  • Reference

  • 1

    In fact, use Task.Run in this case will not bring any benefit, will only degrade the performance. This code releases the current thread, and charges a thread from ThreadPool to execute the query. In other words - releasing a thread and occupying another thread, does not bring advantages - only adds overhead of 2 context changes.

  • @dcastro I was interested in your comment (after all, I am no expert in async and I am interested in learning). If my answer is wrong (even partially), why not put a correct version? You will have my upvote, and surely your answer will be accepted.

  • Yes, I will post a reply, but first I need the OP to respond to my account, to find out if the library he is using supports native asynchrons calls :)

  • @dcastro From what I understand, he’s carrying a legacy Web Forms code for MVC 5. I think he’s willing to convert the legacy code to asynchronous. (From what I know the OP by chat, I know he enters the OS a little late)

  • 1

    Yes, but which library does it use to access the database? Currently, almost all expose methods asyncronos, but it is better to check.. This way I can give a more informed answer.

  • @dcastro: When using await In this case, there is no simple exchange of threads. It will be the exchange of a thread from the IIS pool (threads that meet requests) for one from the . Net thread pool (generic threads).

  • @Miguelangelo But IIS uses the CLR Threadpool, which is the same Threadpool as the Task.Run uses.

  • @dcastro: In integrated mode, the source of the threads is the same, however the IIS has a thread limit that will use to meet requests... but that doesn’t matter, because it seems the standard limit is 5000 threads! =)

  • Reference: http://msdn.microsoft.com/en-us/library/ee377050.aspx

  • 2

    @Miguelangelo Read this question/answer: Async operations on ASP.NET and especially the first 2 links at the bottom of the reply.

  • 1

    @dcastro I just read... very interesting.

Show 7 more comments

Browser other questions tagged c# oracle asp.net-mvc-5 async

You are not signed in. Login or sign up in order to post.