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 connection
I 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 conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=tax.mdb");
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:

adapter.SelectCommand = command;
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

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.

DataSet -> DataTable -> DataRow
As mentioned earlier, C# uses the generic 'DataSet' to store results from DB queries. Therefore we need to create a DataSet object to store the results of our query.

ds = new DataSet("roads");

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:

foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0} Costs {1} per mile", dr["name"].ToString(), dr["toll"].ToString());
}

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 database
As 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:

DataRow newRoadsRow = ds.Tables["roads"].NewRow();

We can now enter data into the new row, addressing it as follows:

newRoadsRow["road"] = 5664;
newRoadsRow["name"] = "St Chris";
newRoadsRow["toll"] = 7;

As before, the indexes are the column names as defined in the DB. Now our data is stored, we must add this row to our local DataSet.

ds.Tables["roads"].Rows.Add(newRoadsRow);

Finally our changes must be saved to the database. Again, we need to use our Adapter:

adapter.Update(dt);

We are now in a position to close the DB connection.

More Info
For more info on these objects/classes visit the MSDN Site:

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:


//Simple DB Operations
//Requires existing DB called tax.mdb in /debug/bin folder
//
using System;
using System.Data;
using System.Data.OleDb;
using System.Xml.Serialization;

public class TaxDB
{
private OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.JET.OLEDB.4.0;" + @"data source=tax.mdb");
private OleDbDataAdapter adapter = new OleDbDataAdapter();
private DataSet ds;

private bool open()
{
// create and open the connection
try
{
conn.Open();
}
catch (OleDbException ex)
{
Console.WriteLine("OleDB Error: {0}", ex.Message);
return false;
}

return true;
}//end db open

public bool close()
{
try
{
conn.Close();
Console.WriteLine("Connection Closed");
}
catch(OleDbException ex)
{
Console.WriteLine("OleDB Error: {0}", ex.Message);
return false;
}
return true;
}//end close

public DataTable getTable(string tableName)
{
// create the adapter and fill the DataSet
OleDbCommand command = new OleDbCommand("SELECT * from roads", conn);
adapter.SelectCommand = command;
OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
open();
ds = new DataSet("roads");
adapter.Fill(ds, "roads");
DataTable dt = ds.Tables[0];
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("{0} Costs {1} per mile", dr["name"].ToString(), dr["toll"].ToString());
}

DataRow newRoadsRow = ds.Tables["roads"].NewRow();
newRoadsRow["road"] = 5664;
newRoadsRow["name"] = "St Chris";
newRoadsRow["toll"] = 7;
ds.Tables["roads"].Rows.Add(newRoadsRow);
adapter.Update(dt);
close();

return dt;
}//end getTable
}
public class Program
{
static void Main()
{
TaxDB myTax = new TaxDB();
myTax.getTable("roads");
}
}

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:

{...

...adapter.Fill(ds, "roads");

...}

and

{...

...myTax.getTable("roads");

...}

why is this so? sorry, kinda newbie with programming

Chris said...

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

pao said...

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 ^^

Chris said...

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.

http://www.w3schools.com/sql/default.asp

That should be enough to get you started!

pao said...

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? >.<

Anonymous said...

hello.

I am using oledb connection for access database. I added a dataset. I want the program to read from the dataset. However, there is a problem when i tried urs.

Chris said...

I'm afraid you'll have to provide a little more information. what does and doesn't work?

Ana said...

hello, i'm using 3 tables, 2 of them have a foreing key, and when i execute the code inside "try{}" , the code generate a error, and goes to "catch". Sorry my english :sBut, i don't know what to do.

Nevin said...

Still progressing through and modifying to my own program, but I can't thank you enough....

great job and great resource!!

ben686@hotmail.com said...

Finished going through it...works perfectly!

(it took me a while to figure out that one of the problems that I was having was adding value to the column name "order"....is that possible?

Asava Samuel said...

Here is a database compatible with .NET, Silverlight, Windows Phone, Mono, Monodroid, and Monotouch:
http://www.kellermansoftware.com/p-43-ninja-net-database-pro.aspx

Anonymous said...

Have a look at here
Using Access Database with c# -- geeksprogrammings.blogspot.com
http://geeksprogrammings.blogspot.com/2013/10/connect-access-database-with-c.html

For Deletion Record
http://geeksprogrammings.blogspot.com/2013/09/delete-record-from-access-database.html

rokn elbeet said...

ما تهتم به شركة كشف تسربات المياه بالرياض هو البحث عن كيفة علاج مشاكل تسريبات المياه التي تطرأ علي المكان فجأة بواسطة اجهزة الكشف الحدية التي تستخدمها شركة كشف تسربات بالرياض والتي تسعي للوصول الي افضل النتائج المثالية القادرة علي حل هذه المشكلة بدون تدمير فالاعتماد علي الاساليب الحديثة يساعدكم في الحصول علي نتيجة مثالية في مصلحة العميل فنحن لا نكتفي بتقديم هذه الاعمال في مدينة الرياض فقط بلا لدينا الفنين المتميزة الذي يقدمون شركة كشف تسربات المياه بالدمام التي تعمل علي حل مشكلة البيت بدون الاعتماد علي ا اساليب تقليدية التي تستخدما بعض مقدمي خدمة شركة كشف تسربات بالدمام فلا تتكايل بشأن هذا العمل بالذات لانه يحل لك الكثير من المشاكل