ASP.NET MVC5 - Asynchronous Methods on the Controller

Question

ASP.NET MVC5 - Asynchronous Methods on the Controller

Navigation

#1 by (3 votes)
#2 by (2 votes)
#3 by (1 votes)

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 work out a homemade solution.

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

[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(... runs 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 CPU-bound work on a background thread.

How do I change the method for execution to be asynchronous?

EDIT

@dcastro asked for the Selecionar() method, 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 you need to add more codes, just ask via comments.

c# oracle asp.net-mvc-5 async

asked by anonymous 26.02.2014 / 20:14

3 answers

3

To see why new threads should not be created or use ThreadPool (with Task.Run for example), see this question: ASP.NET async operations . (I found this explanation too complex, so I asked myself a question).

Problems

Issue # 1: System.Data.OracleClient of Microsoft has been deprecated - a Microsoft recommends using 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 the future release. Microsoft recommends that you use a third-party Oracle provider.

As an alternative, Oracle itself provides the ODP.NET library (Oracle Data Provider). And here 's where the ...

Issue # 2: The ODP.NET library does not support asynchronous calls! (see discussion: link ).

Solutions

In this case, you have 3 possible solutions:

(Not recommended) Continue to use OracleClient of Microsoft, although not advised by Microsoft.

Migrate to ODP.NET and use synchronous calls.

Migrate to Sql Server, and use upgraded libraries and support for truly asynchronous calls.

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

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

In addition, the benefits of making asynchronous calls to the database are very tempting. This is a critical aspect of achieving a scalable, high-performance 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, not await), but at least you'll use updated and better supported libraries. The migration should be relatively easy, the API is very similar (or equal) to the OracleClient API.

ODP.NET API: link

28.02.2014 / 14:50

2

If your method does not actually do any asynchronous operations, it does not make sense to have KeyWord async.

And asynchronous methods must be preceded by the keyword await to be executed and waited, releasing the current thread in the meantime.

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 that the Select method is asynchronous (which only makes sense if internally it 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);
}

NOTE:

Direct direct access to a relational database is not recommended asynchronously.

26.02.2014 / 20:28

Continue to execute the loop even if a passage gives a problem How to sum values from a grouped field by date?

score 1 · Accepted Answer

I think 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 should expect the response of some action at some point. But the compiler will not know where to wait on its own, it will wait when it finds await . And, to "convert" a synchronous execution to asynchronous, you use a Task .