Tag Archives: SQL

Connecting C# and Access2007

If you encounter a situation where you have to use an Access2007 (.accdb) database, but you're like me and you'd rather use one of the .net programming languages,
you might want to use that familiar language to query over the database instead.

So I've set up a sample database called SouthStorm (Get It?) located on my C:\  drive which contains a list of bands, their albums and the songs inside the album.



















Inside the tblSong, there is a list of songs stored in my application.


And I could go into the form designer, create a form and at all the buttons to eventually do a query on the recordsource to find the songs I want.
But instead I'm going to use C# to find all the songs in my database.

So make a console application and to start with add using System.Data.OleDb;  to the top of your project.

Once that is added you can start adding .accdb connection to your program.
After you've added the database you want to connect to in OleDbConnection, you also want to get something out of this database,
the query you want to run on this data is stored inside OleDbCommand.



Now you have set up the connection, and the query you want to run on this pool of data. now you have to open the connection.

Under OleDbCommand you'll try to open the connection by writing.


This is a quite important step, if you have to open the connection trough a command, you can safely imply that you will have to close the connection as well,
but we haven't quite reached that step yet.

Now that your application has a standing connection with your Access2007 database you have to read the data that is gained from your query.

What happens here is rather simple, you have executed your query and you put the reader in loop, which means that he will add every record that comes out of your query, one by one.

and a very important thing to notice is the GetString arguement, because I'm working with string characters for the names in the database,
I have to use a GetString. If you're working with different value's, for example number or ID's of your table, you'll have to use GetInt32(0).ToString().

Now your console application can read the data, but as mentioned before you'll have to close your connection with the database in the end.

Notice we have added a Console.ReadKey(); so that the console doesn't immediately disappear after retrieving your data.

The last thing we're going to add to this project is an exception handler.

And voila, we are ready to run the application.

So now you are all set to use C# to access Access.

In the end your code should look like this: