Reader Level:
ARTICLE

ADO.NET Application using MS Access 2007 database

Posted by Puran Mehra Articles | Learn .NET July 24, 2009
In this article I will explain you how to use ADO.NET technology to connect .NET console application and MS Access 2007 database.
  • 0
  • 0
  • 41032

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.

Article Extensions
Contents added by Unnikrishnan C on Sep 26, 2010
Dear Friend

Using your code, data can be read and processed from a single table. I am a person just trying to lear C#. How to access data from 2 tables with the following fields.

Table1
Column Name Column Name Column Name
ID PrimaryKey, Name, Age
Table2
Column Name, ColumnName, Column Name
ID ForeignKey, DateOfPromotion, Designation

The ID in Table1 is unique for each row of data.
Its relational data resides in Table2.

How to get both data for further processing?

Thanks in Advance.
Unnikrishnan

COMMENT USING

Trending up