ADO.NET Application using MS Access 2007 database

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.

 

 

 

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.

 

 

 

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

 

 

 

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.