Dapper command like DataAdapter

Question

Please, i can convert this method to uses Dapper?

"BuscarUltimasLeituras" is a procedure
"@Last" is a param of procedure (int)

public DataTable GetLeituras ( int ultimas )
    {
        DataTable listaLeiturasRomaneio = new DataTable();
        try
        {
            _sqlConnection.Open();

            _sqlCommand = new SqlCommand();
            _sqlCommand.Connection = _sqlConnection;
            _sqlCommand.CommandText = "BuscarUltimasLeituras";
            _sqlCommand.CommandType = System.Data.CommandType.StoredProcedure;
            _sqlCommand.Parameters.Add("@Last", SqlDbType.Int).Value = ultimas;
            _sqlAdapter = new SqlDataAdapter(_sqlCommand);
            _sqlAdapter.Fill(listaLeiturasRomaneio);
        }
        catch ( SqlException )
        {
            listaLeiturasRomaneio = null;
        }
        finally
        {
            _sqlConnection.Close();
        }
        return listaLeiturasRomaneio;
    }

When you say "convert to use Dapper", what exactly do you mean? Do you still want to return a DataTable? If so then why do you want to do anything with it at all? — Lasse V. Karlsen, Dec 12 '17 at 12:24
Yes, it's possible to execute a stored procedure with a parameter in Dapper and return results. What did you try and where did you get stuck? — David, Dec 12 '17 at 12:25
Possible duplicate of [Is there a way to call a stored procedure with Dapper?](https://stackoverflow.com/questions/5962117/is-there-a-way-to-call-a-stored-procedure-with-dapper) — mjwills, Dec 12 '17 at 12:36

score 9 · Accepted Answer · answered Dec 12 '17 at 12:29

If you still want a DataTable, you could try:

var listaLeiturasRomaneio = new DataTable();
using (var reader = _sqlConnection.ExecuteReader(
    "BuscarUltimasLeituras", new { Last = ultimas },
    commandType: CommandType.StoredProcedure))
{
    listaLeiturasRomaneio.Load(reader);
}

However, the more typical usage would be to create a class to match your data, then:

var listaLeiturasRomaneio = _sqlConnection.Query<YourType>(
    "BuscarUltimasLeituras", new { Last = ultimas },
    commandType: CommandType.StoredProcedure).AsList();

Note that dapper also supports dynamic usage, but this is usually for casual usage:

var listaLeiturasRomaneio = _sqlConnection.Query(
    "BuscarUltimasLeituras", new { Last = ultimas },
    commandType: CommandType.StoredProcedure).AsList();

Dapper command like DataAdapter

1 Answers1