Issues querying Access '07 database in C#
- by Kye
I'm doing a .NET unit as part of my studies. I've only just started, with a lecturer that as kinda failed to give me the most solid foundation with .NET, so excuse the noobishness.
I'm making a pretty simple and generic database-driven application. I'm using C# and I'm accessing a Microsoft Access 2007 database.
I've put the database-ish stuff in its own class with the methods just spitting out OleDbDataAdapters that I use for committing. I feed any methods which preform a query a DataSet object from the main program, which is where I'm keeping the data (multiple tables in the db).
I've made a very generic private method that I use to perform SQL SELECT queries and have some public methods wrapping that method to get products, orders.etc (it's a generic retail database).
The generic method uses a separate Connect method to actually make the connection, and it is as follows:
private static OleDbConnection Connect()
{
OleDbConnection conn = new OleDbConnection(
@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Temp\db.accdb");
return conn;
}
The generic method is as follows:
private static OleDbDataAdapter GenericSelectQuery(
DataSet ds, string namedTable, String selectString)
{
OleDbCommand oleCommand = new OleDbCommand();
OleDbConnection conn = Connect();
oleCommand.CommandText = selectString;
oleCommand.Connection = conn;
oleCommand.CommandType = CommandType.Text;
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = oleCommand;
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.Fill(ds, namedTable);
return adapter;
}
The wrapper methods just pass along the DataSet that they received from the main program, the namedtable string is the name of the table in the dataset, and you pass in the query you wish to make.
It doesn't matter which query I give it (even something simple like SELECT * FROM TableName) I still get thrown an OleDbException, stating that there was en error with the FROM clause of the query. I've just resorted to building the queries with Access, but there's still no use. Obviously there's something wrong with my code, which wouldn't actually surprise me.
Here are some wrapper methods I'm using.
public static OleDbDataAdapter GetOrderLines(DataSet ds)
{
OleDbDataAdapter adapter = GenericSelectQuery(
ds, "orderlines", "SELECT OrderLine.* FROM OrderLine;");
return adapter;
}
They all look the same, it's just the SQL that changes.
