In this article, you’ll learn how to connect and read Microsoft Access 2003 or earlier versions (.mdb).
Note: Originally, this article was published on Jan 01, 2000. This is an updated article.
The code snippet in this article is a simple console application that connects with an Access 2000 database, reads data from a table, and displays it on the console. The database has a table named, Developer with two columns, Name and Address.
The .NET framework has two common approaches, ADO.NET and LINQ to read databases. ADO.NET uses the OLE-DB data provider to read a Microsoft Access database. The OLE-DB classes are defined in the System.Data.OleDb namespace. We must import the System.Data.OleDb namespace in our code using the following definition.
The following code is the complete list of the C# console app that connects with an Access database, opens a connection, reads data, and displays it on the system console.
The following code snippet adds a new row in the table. The INSERT SQL query is used to add a new row.
- using System;
- using System.Data.OleDb;
- namespace AccessDBSample {
- class Program {
- static void Main(string[] args) {
-
- string connectionString = @ "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Mahesh\Data\Dev.mdb";
- string strSQL = "SELECT * FROM Developer";
-
- using(OleDbConnection connection = new OleDbConnection(connectionString)) {
-
- OleDbCommand command = new OleDbCommand(strSQL, connection);
-
- try {
-
- connection.Open();
-
- using(OleDbDataReader reader = command.ExecuteReader()) {
- Console.WriteLine("------------Original data----------------");
- while (reader.Read()) {
- Console.WriteLine("{0} {1}", reader["Name"].ToString(), reader["Address"].ToString());
- }
- }
- } catch (Exception ex) {
- Console.WriteLine(ex.Message);
- }
-
- }
- Console.ReadKey();
- }
- }
- }
-
- strSQL = "INSERT INTO Developer(Name, Address) VALUES ('New Developer', 'New Address')";
- command = new OleDbCommand(strSQL, connection);
-
- command.ExecuteReader();
The following code snippet updates rows that match with the WHERE condition in the query.
-
- strSQL = "UPDATE Developer SET Name = 'Updated Name' WHERE Name = 'New Developer'";
- command = new OleDbCommand(strSQL, connection);
- command.ExecuteReader();
The following code snippet deletes rows that match with the WHERE condition.
-
- strSQL = "DELETE FROM Developer WHERE Name = 'Updated Name'";
- command = new OleDbCommand(strSQL, connection);
- command.ExecuteReader();