Pages

Tuesday, 22 April 2008

Connecting to an Access Database using C#

This may seem like a common task, and it is, however I found it ridiculously difficult to learn how to do it. The MSDN website has articles on each class involved but nowhere does it explain how to put everything together. After reading this blog you should be able to:

Open a connection to an existing MS Access (.mdb) database

Perform SELECT queries

Extract the results from queries

Modify/Add row of a table

Update the changes to the Database

Close the connection

Figure out for yourself how to do other operations

Opening a connectionI will assume that there already exists an MS Access database with at least one table already created. This is fairly easy to do using MS Access and there are plenty of tutorials showing you how to do this. The DB should be in the same folder as the executable file (/bin/debug). To configure a connection to a database:

OleDbConnection is a special class which deals with creating a connection or 'handle' to the database. The first part of the string describes the database type, the last part is the name/location of the database file. To actually open a connection use:

conn.Open();

You should enclose this in a try/catch statement and test for OleDbExceptions. The connection is now open. To close the connection:

conn.Close();

Adapters.NET provides support for accessing many different database types. Programmers generally don't want to worry about what particular database they are using, they just want the data. This is where Adapters come in. Adapters (Specific to each database type) present the data structures as a generic 'DataSet' object. Theoretically the database type could be changed without affecting all the code.

OleDbDataAdapter adapter = new OleDbDataAdapter();

The above statement creates a new Adapter object. OleDb is just the name of the adapter specific to Access DBs. The next step is to attach a command to the Adapter for it to execute on the DB.

OleDbCommand command = new OleDbCommand("SELECT * from roads", conn);

This statement creates a command object which will return all the rows from the table 'roads' using the connection we made earlier. Lets attach this command to the Adapter we created:

Whats actually going on here is a little more complicated. By defining the SelecCommand property of the Adapter, the OleDbCommandBuilder() function will automatically build the SQL statements necessary to update, delete and add to the database. This will come in useful later.

The "roads" argument is optional and just gives the DataSet a name. Now we must associate our new DataSet with the results from our query:

adapter.Fill(ds, "roads");

The Fill() method will make cause the adapter to execute the SELECT command, get the results and store them in the DataSet 'ds'. 'ds' now contains a local copy of part of our database. The structure of the DataSet is identical to the structure of the DB. We can now access the data easily. DataSets contain Tables, which contain Rows which contain Columns. To access a table:

DataTable dt = ds.Tables[0];

A numerical index or the table name (string) can be used as the array index. We could iterate the rows of a table as follows:

Notice the hierarchy: DataSet.DataTable.DataRow['columnName']. Using a local DataSet rather than referring to the DB all the time frees up the DB, allowing multiple connections to be made to it.Adding Rows to the databaseAs mentioned before DataSets are generic, they have no default structure, no column names, no associated data types. They inherit their structure from the Data Base thanks to the OleDbAdapter. With this in mind, to add a row we therefore need to create a new object 'newRoadsRow' say, which is of the same format as the DB Table where it will end up:

The above scripts give only a very basic grasp on DBs. There are many more features than I have described here but you should now have the knowledge to research these yourself. The full code is reproduced here, please reference this website if you use it in any projects:

13 comments:

pao
said...

just wanted to ask, i tried to see the output of ur sample, but eery time i try running it, i get a pop-up asking for a debugging. i have to the option to choose which program to use to debug it, then it points out to the following statements:

For the example to work, you need to have an access database file called tax.mdb which is in the same folder as the executable file (exe). Secondly within that table there must be a table called 'roads' with at least one row of data. Also the column names must be 'name' and 'toll'. If you have a table with a different name or collumn names you need to change the code to match. The tax.mdb file that i used is uploaded to here if you want to test your code: http://users.ecs.soton.ac.uk/cc1206/docs/doc1209976452.zip

hei, i'm here again,.. well, i tried a different approach, and got it to connect to the database and select and display the data(atlast hehe),.. just a little info on my project, its just a simple database, using c# as my front and msaccess as my back,. would like to find out the general syntax/command for adding+saving data from my program to the database >.< oh and if u would know any certain site which gives a good tutorial on c#,.. not to complicated for a newbie to understand hopefully ^^

Hey. I found that C# Station tutorials were good. Although the concepts may seem a little abstract if you stick with it C# will make a lot more sense and you should be able to pick up the rest quite easily.

http://www.csharp-station.com/Tutorial.aspx

As for adding/saving data, you'll need to get your head around SQL. SQL statements (Queries) are pretty much standard across all database types (Access, MySQL etc) but take a little bit of time to get your head around. Personally i found the W3Schools tutorials to be excellent.

lmao, this is funny xD every time i make a post, a few mins after, i suddenly get the job done XD anwei, little update, ive done my database ^^ it was a breeze,. now my next task is retrieving it and then PRINTING it,. this is my real prob,. i dont have any background in printing programs 0.o any idea? scenario is basically button for retrieving certain data, then, with the data retrieved, i need to print those data with a certain template/format much like excel probably,. any idea? >.<