Vendor independent version, solely relies on ADO.NET interfaces; 2 ways:
public DataTable Read1<T>(string query) where T : IDbConnection, new()
{
using (var conn = new T())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.Connection.ConnectionString = _connectionString;
cmd.Connection.Open();
var table = new DataTable();
table.Load(cmd.ExecuteReader());
return table;
}
}
}
public DataTable Read2<S, T>(string query) where S : IDbConnection, new()
where T : IDbDataAdapter, IDisposable, new()
{
using (var conn = new S())
{
using (var da = new T())
{
using (da.SelectCommand = conn.CreateCommand())
{
da.SelectCommand.CommandText = query;
da.SelectCommand.Connection.ConnectionString = _connectionString;
DataSet ds = new DataSet(); //conn is opened by dataadapter
da.Fill(ds);
return ds.Tables[0];
}
}
}
}
I did some performance testing, and the second approach always outperformed the first.
Stopwatch sw = Stopwatch.StartNew();
DataTable dt = null;
for (int i = 0; i < 100; i++)
{
dt = Read1<MySqlConnection>(query); // ~9800ms
dt = Read2<MySqlConnection, MySqlDataAdapter>(query); // ~2300ms
dt = Read1<SQLiteConnection>(query); // ~4000ms
dt = Read2<SQLiteConnection, SQLiteDataAdapter>(query); // ~2000ms
dt = Read1<SqlCeConnection>(query); // ~5700ms
dt = Read2<SqlCeConnection, SqlCeDataAdapter>(query); // ~5700ms
dt = Read1<SqlConnection>(query); // ~850ms
dt = Read2<SqlConnection, SqlDataAdapter>(query); // ~600ms
dt = Read1<VistaDBConnection>(query); // ~3900ms
dt = Read2<VistaDBConnection, VistaDBDataAdapter>(query); // ~3700ms
}
sw.Stop();
MessageBox.Show(sw.Elapsed.TotalMilliseconds.ToString());
Read1 looks better on eyes, but data adapter performs better (not to confuse that one db outperformed the other, the queries were all different). The difference between the two depended on query though. The reason could be that Load requires various constraints to be checked row by row from the documentation when adding rows (its a method on DataTable) while Fill is on DataAdapters which were designed just for that - fast creation of DataTables.