Database adapters

I'm writing adapters for some MS Access database because I really didn't like that automatically generated code that Visual Studio was providing. Right now I just need to get data, not updating anything over there, but that will come later.

Here's some code that I'm using (all the adapters look the same, so that is what bothers me if I can fix them up somehow). I'm wondering about moving most of the methods to the base class and just passing kind of Filter class as an argument to form an actual query, but I'd like to see some different approaches on that problem.

```
public abstract class AccessDatabaseAdapter
{
private readonly OleDbConnection _connection;
private OleDbDataAdapter _adapter;

public OleDbDataAdapter Adapter
{
get
{
if (_adapter == null)
{
_adapter= new OleDbDataAdapter(String.Empty, _connection);
}
return _adapter;
}
}

protected AccessDatabaseAdapter()
{
var parser = new MSAccessConnectionStringParser();
_connection = new OleDbConnection(parser.GetConnectionString());
}

private void SetSelectCommand(string command)
{
Adapter.SelectCommand = new OleDbCommand(command);
Adapter.SelectCommand.Connection = _connection;
}

protected DataTable GetTable(string command)
{
var dataTable = new DataTable();
SetSelectCommand(command);
Adapter.Fill(dataTable);

return dataTable;
}

protected abstract bool RowIsInvalid(DataRow row);
}

public interface ITableRetrieveAdapter
{
T GetWithId(int id);
IList GetAll();
}

public class MsAccessComponentRetrieveAdapter : AccessDatabaseAdapter, ITableRetrieveAdapter
{
protected override bool RowIsInvalid(DataRow row)
{
return row == null || row[0] is DBNull || row[1] is DBNull || row[2] is DBNull;
}

public Component GetWithId(int id)
{
var selectCommmand = string.Fo

Don't do SELECT *, especially not when you later rely on specific fields to be returned in a specific order: (int)row[0], (string)row[1], (string)row[2].


There are really three major reasons:

• Inefficiency in moving data to the consumer.

• Indexing issues.

• Binding Problems.

I never see you close your OleDbConnection anywhere in your code. That worries me. IMHO you should look at something like this to fill a DataTable:

DataSet ds = new DataSet();

using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = new OleDbCommand(query, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
    adapter.Fill(ds);
}

return ds;

Granted, that example is for a DataSet, but judging from your code it should be much the same for a DataTable.

But I would really urge you to use an ORM like Entity Framework or NHibernate instead of data adapters etc.

StackExchange Code Review Q#93955, answer score: 7