ADO.NET Application using MS Access 2007 database
In this article I will explain you how to use ADO.NET technology to connect .NET console application and MS Access 2007 database.
Step 1: Create a Console Application in your .NET Framework. Select File -> New Project as shown in figure.
![Console_MSAccess.gif]()
Step 2: .NET Data Provider
The application uses OleDb data providers to work with Microsoft Access database.
Step 3: Adding Namespace References
The second step is to add reference to the assembly and include the namespaces in your project. Select Project -> Add Reference option. The below figure shows how to add a reference to the System.Data.dll assembly.
![Console_MSAccess.gif]()
Step 4: Include namespaces
After adding a reference to the assembly you need to include namespaces to the project by using the using namespace as below:
using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
Step 5: Establishing the connection
You have to create a connection using the data provider Connection class. I have used Ms Access 2007 database. Below is the code to make the connection:
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Testing.accdb";
Step 6: Creating a command Object
Next step is to create a Command object. OleDBCommand class is used for it.
The OleDbCommand constructor takes two parameters. The first is a SQL query and the second is the Connection object.
I have created a SELECT SQL query from the Test_table in the Testing.accdb database of MS Access 2007.
OleDbConnection conn = new OleDbConnection(connectionString);
string sql = "select Name, Address, Salary from Test_table";
OleDbCommand cmd = new OleDbCommand(sql, conn);
Step 7: Filling DataReader Object
The next step is to open connection by calling Open method of the Connection object and than reading data from the Command object.
The ExecuteReader method, OleDbCommand, returns data in an OleDataReader object. The DataReader object reads fast and forward only cached data.
conn.Open();
OleDbDataReader reader;
reader = cmd.ExecuteReader();
Step 8: Displaying Data
The Read method of OleDbDataReader reads the data. The DataReader class has Getxxx methods, which return different types of data. The Getxxx methods takes and index of the field you want to read data of.
while (reader.Read())
{
Console.Write(reader.GetString(0).ToString() + "\t \t");
Console.Write(reader.GetString(1).ToString() + "\t \t ");
Console.WriteLine(reader.GetDecimal(2));
}
Step 9: Closing the Connection and releasing the resources
Here we close the reader and connection objects by calling their Close methods.
reader.Close();
conn.Close();
The complete listing of the above steps in ADO.NET Application using MS Access 2007 database
using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
namespace ADO_MSAccess_test
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\\Testing.accdb";
OleDbConnection conn = new OleDbConnection(connectionString);
string sql = "select Name, Address, Salary from Test_table";
OleDbCommand cmd = new OleDbCommand(sql, conn);
conn.Open();
OleDbDataReader reader;
reader = cmd.ExecuteReader();
Console.WriteLine("Person Name \tAddress\t\t Salary");
Console.WriteLine("==============================================");
while (reader.Read())
{
Console.Write(reader.GetString(0).ToString() + "\t \t");
Console.Write(reader.GetString(1).ToString() + "\t \t ");
Console.WriteLine(reader.GetDecimal(2));
}
Console.ReadLine();
reader.Close();
conn.Close();
}
}
}
Output of the above program
![output_access.gif]()
Conclusion
Hope the article would have helped you in using ADO.NET connectivity in your .NET application with MS Access 2007.
Your feedback and constructive contributions are welcome. Please feel free to contact me for feedback or comments you may have about this article.