NpgsqlCommand over NpgsqlDataAdapter

Question

Why would you use NpgsqlCommand over NpgsqlDataAdapter?

NpgsqlCommand usage, using reader

var _command = new NpgsqlCommand();
var _reader = _command.ExecuteReader();
...
while (_reader.Read())
{
...

NpgsqlDataAdapter usage, using Datasets

IDbDataAdapter dbDataAdapter = new NpgsqlDataAdapter();
dbDataAdapter.SelectCommand = selectCommand;
...
dbDataAdapter.Fill (dataSet);

Basic usage does not mention DataAdapter. Is NpgsqlCommand and reader more efficient and should be preferred choice?

Answer 1

NpgsqlCommand is the more low-level API - you're responsible for providing the raw SQL, and also for reading the resultset via NpgsqlDataReader. DataAdapter/DataTable are a layer above that; it has some shortcomings (e.g. resultsets are buffered in memory) and in general is considered somewhat old/legacy. If what you want is to send SQL and read the results back, consider using NpgsqlCommand unless there's a good reason to use NpgsqlAdapter.