Better option for getting data from an MS Access file?

[sonnichjensen]

Hi

I have this:

OleDbConnection con = new OleDbConnection();

string database = "C:\\private\\tech\\sonich.mdb";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + database + ";";
// this is the part I dont like - I use a very specific driver, which can cause problems - what would you suggest to use?
// I am still somewhat new to C#, so.....

con.ConnectionString = connectionString;
con.Open();

// get data from DB
string sCommand = "SELECT * from workinghours";
//sCommand += " order by record_time";
OleDbCommand com2 = new OleDbCommand(sCommand, con);
OleDbDataReader dr2 = com2.ExecuteReader();

 

[Arne Vajhøj]

I have this:

OleDbConnection con = new OleDbConnection();

string database = "C:\\private\\tech\\sonich.mdb";
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + database + ";";
// this is the part I dont like - I use a very specific driver, which can cause problems - what would you suggest to use?
// I am still somewhat new to C#, so.....

con.ConnectionString = connectionString;
con.Open();

// get data from DB
string sCommand = "SELECT * from workinghours";
//sCommand += " order by record_time";
OleDbCommand com2 = new OleDbCommand(sCommand, con);
OleDbDataReader dr2 = com2.ExecuteReader();

You can not use an Access database without using some code that
is specific to Access databases.

As far as I know there are only two pieces of software that
can be used:
* Microsoft ODBC driver for Access
* Microsoft OLE DB provider for Access

You are using the last through the .NET OLE DB
wrapper.

I think that is fine (OLE DB is better than ODBC).

What is not so good is that your code is:
A) tied to the specific OLE DB provider via connection
string
B) tied to OLE DB via the classes

#A can be fixed by moving the connection string to
app.config.

#B can be fixed by using the Db/IDb classes/interfaces
instead of the OLE DB specific classes.

Code snippet:

DbProviderFactory dbf = DbProviderFactories.GetFactory(provname);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sqlstr;
IDataReader rdr = cmd.ExecuteReader();

provname is a String with the value "System.Data.OleDb"

constr is the same connection string as you are currently using

Both should be retrieved from app.config.

If you move up from plain ADO.NET provider usage to ORM, then
both EF and NHibernate can do something similar (except that
support for Access may be lacking compared to SQLServer,
Oracle, DB2, MySQL etc.).

Arne

 

[Tim Sprout]

You can not use an Access database without using some code that
is specific to Access databases.

As far as I know there are only two pieces of software that
can be used:
* Microsoft ODBC driver for Access
* Microsoft OLE DB provider for Access

You are using the last through the .NET OLE DB
wrapper.

I think that is fine (OLE DB is better than ODBC).

What is not so good is that your code is:
A) tied to the specific OLE DB provider via connection
string
B) tied to OLE DB via the classes

#A can be fixed by moving the connection string to
app.config.

#B can be fixed by using the Db/IDb classes/interfaces
instead of the OLE DB specific classes.

Code snippet:

DbProviderFactory dbf = DbProviderFactories.GetFactory(provname);
IDbConnection con = dbf.CreateConnection();
con.ConnectionString = constr;
con.Open();
IDbCommand cmd = con.CreateCommand();
cmd.CommandText = sqlstr;
IDataReader rdr = cmd.ExecuteReader();

provname is a String with the value "System.Data.OleDb"

constr is the same connection string as you are currently using

Both should be retrieved from app.config.

If you move up from plain ADO.NET provider usage to ORM, then
both EF and NHibernate can do something similar (except that
support for Access may be lacking compared to SQLServer,
Oracle, DB2, MySQL etc.).

Arne

What is the advantage of using the Db/IDb classes/interfaces rather than
the OLE DB specific classes?

--Tim Sprout

 

[Arne Vajhøj]

What is the advantage of using the Db/IDb classes/interfaces rather than
the OLE DB specific classes?

That you can change database software by just changing the
the provider name and connection string.

Arne

 

[Arne Vajhøj]

That you can change database software by just changing the
the provider name and connection string.

Two additional comments may be relevant here:
1) The ability to change database software obviously also
requires that one use only standard SQL not vendor
specific SQL.
2) Both ODBC and OLE DB also provide ability to change
database software, but you really want to use the
real ADO.NET provider for databases with such like
the mentioned SQLServer, Oracle, DB2, MySQL etc.

Arne

 

[Phil Hunt]

if you are using stored procedure, sequence and things lik that, they are
not interchangeble among vendors.

 

[Arne Vajhøj]

if you are using stored procedure, sequence and things lik that, they are
not interchangeble among vendors.

True.

One has to keep within the standard. And actually within a pretty
old version of the standard to be portable.

But one will never succeed if one is not trying.

And I will still consider 100 database interactions
using Db/IDb where 3 of them has a comment that
explains that the SQL is no portable, why the choice
to do was made and what workarounds may be possible
than just 100 cases of Xxx classes (and maybe even
consequent usage of [] or `` to ensure that not a
single SQL statement is portable).

Arne