C# OLEDBConnection

richard smith

I want to use C# OLEDBConnection to open an access database and build a query in that database and then save the query. So for example, I want to build a query of SELECT * From tblCustInfo then after that I want to SELECT ZIP from tbl_City so the query would hold all of the info from tbl_custInfo and then the zips from tbl_City.

I know how to open a database via OLEDBConnection, I just don't know how to select data from tables (or 2 tables for that matter!)or how to save the query once I have it built out.

OleDbConnection olecon = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseNamae);

olecon.Open();

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted answer
If you mean that you want to save the query itself rather than the results it produces, then the only way I can think of doing it would be to record an Access macro which creates and saves the query. You could then run the macro from C#.

If you want to try that, there's a Microsoft Support article here which shows how to run an Office macro using C#:

http://support.microsoft.com/kb/306683
Vulpes13y ago
The ObjectContext class is in fact part of the Entity Framework (EF) which is an object-relational mapping (ORM) for querying databases using LINQ. The LINQ queries are translated by EF into native SQL queries which the database understands.

Personally, I've always regarded EF (and it's close relative LINQ to SQL) as more trouble than they're worth and preferred to stick with native SQL queries and the standard ADO.NET classes.

However, none of these technologies will help to save the SQL queries themselves within Access.

To do that you'd need to know which system table or tables are used internally by Access for this purpose and the format in which the queries are saved. Even if you knew all this, there would probably be additional security hurdles to overcome.

So, I still think that a macro is the best answer here as this will enable Access to do the heavy lifting.
richard smith13y ago
I actually just stumbled across this article

http://msdn.microsoft.com/en-us/library/bb339670.aspx

Could that be modified to create the query in access or am I reading how this works incorrectly?
richard smith13y ago
That is what I was after...saving the query itself rather than the results. I will follow the link you provided for running the access macro via C#.
richard smith13y ago
What if I just wanted to use C# to build the query in access and save it? So I would build a query in access that does SELECT * from tableCustomer then save the query as qry_Customer.

Something similar to this terrible pseudocode should work (or at least I think)
sql = "SELECT fieldNames from table1 ";

string fieldNames;
While db_reader.Read()
fieldNames &= db_reader.Item(db_reader.GetOrdinal("fieldNames)).ToString() & ",";
End while

fieldNames = fieldNames.Substring(0, fieldNames.Length - 1

sql = "SELECT ID, phone, email, " + fieldNames + " from table2 ";

//Now how to save this as a query from within access?

But how to actually save that SELECT statement as a query in access?
Vulpes13y ago
Well, as you don't normally know how many records there are to be read, you can read them in a loop like this:

OleDbDataReader aReader = aCommand.ExecuteReader();

int recNum = 1;

while(aReader.Read())
{
Console.WriteLine("Details for record number {0}", recNum);
Console.WriteLine(" First field = {0}", aReader[0]);
Console.WriteLine(" Second field = {1}", aReader[1]);
recNum++;
}

aReader.Close();

As you'll gather, I've assumed that there are at least two columns in the table.

Notice that the DataReader indexer always returns an object and so, if you want to do any calculations or otherwise manipulate the column values, you need to cast them to their actual type first. For example if the third column were an integer column, you could do:

int val = (int)aReader[2];

The DataReader also has some 'strongly typed' methods which enable you to get the values without casting. For example:

int val = aReader.GetInt32(2);

If you want to save the records for future processing rather than doing something with them immediately, you could also consider using a Dataset which is a collection of DataTables.

However, you'd need to use an OleDbDataAdapter for this rather than an OleDbDataReader.

As the second query seems to be independent of the first, I'd just create a separate OleDbCommand for that and process it separately.

The following MSDN links may be helpful:

http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader(v=vs.100).aspx

http://msdn.microsoft.com/en-GB/library/system.data.oledb.oledbdataadapter(v=vs.100).aspx
richard smith13y ago
I just found this after even more googling...

OleDbCommand aCommand = new OleDbCommand("select * from emp_test", aConnection);
//create the datareader object to connect to table
OleDbDataReader aReader = aCommand.ExecuteReader();
Console.WriteLine("This is the returned data from emp_test table");

Now I am still stuck on 2 questions...how to save the query...and how to append the results of another query to the end of this query?