HOW TO CONNECT TO ACCESS DATABASE FROM C#

DATABASE

The database consists of the table 'Persons' with 4 columns:

In the download section you will find the zip-file with demo database demo.accdb

CONFIGURING VISUAL STUDIO PROJECT

Create a new Console App (.NET Framework)

Add connection string to app.config:

<connectionStrings>
 <add name="DEMOCONNECTION"
      providerName="System.Data.OleDb"
      connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\..\..\demo.accdb" />
</connectionStrings>

Data Source is the relative or absolute path to the Access database file.

Add System.Configuration.dll to your project:
  Add References -> Add Reference -> Assemblies -> System.Configuration

SOURCE CODE

using System;
using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;
 
namespace DemoCsharpAccessDatabase
{
    class Program
    {
        static void Main(string[] args)
        {
            ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["DEMOCONNECTION"];
            using (OleDbConnection connection = new OleDbConnection(settings.ConnectionString))
            {
                connection.Open();
                read(connection);
                insert(connection);
                read(connection);
            }
            Console.ReadLine();
        }
 
 
 
        private static void read(OleDbConnection connection)
        {
            using (OleDbCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT * FROM Persons";
                using (OleDbDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.Write($"{reader["ID"],-4}"); // left align
                        Console.Write($"{reader["Name"],-10}"); // left align
                        Console.Write($"{reader["BirthDate"],10:d}"); // righ align, short date pattern.
                        Console.WriteLine($"{reader["Weight"],10:0.00}"); // right align, max 2 decimal places
                    }
                }
            }
        }
 
 
        private static void insert(OleDbConnection connection)
        {
            using (DbTransaction transaction = connection.BeginTransaction())
            {
                using (DbCommand cmd = connection.CreateCommand())
                {
                    cmd.Transaction = transaction;
                    /*
                    The OLE DB .NET Provider does not support named parameters for passing parameters 
                    to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. 
                    In this case, the question mark (?) placeholder must be used. 
                    For example: SELECT * FROM Customers WHERE CustomerID = ?
                    Therefore, the order in which OleDbParameter objects are added to the OleDbParameterCollection 
                    must directly correspond to the position of the question mark placeholder for the parameter in the command text.
                    */
                    cmd.CommandText = $"INSERT INTO Persons (Name, BirthDate, Weight) VALUES (?, ?, ?)";
                    cmd.Parameters.Add(cmd.CreateParameter());
                    cmd.Parameters.Add(cmd.CreateParameter());
                    cmd.Parameters.Add(cmd.CreateParameter());
 
                    // you can call next statements in a loop
 
                    cmd.Parameters[0].Value = "Nik"// name
                    cmd.Parameters[1].Value = new DateTime(1979, 3, 20); // birth date
                    cmd.Parameters[2].Value = 54.3; // weight
                    int rowsInserted = cmd.ExecuteNonQuery();
                    Console.WriteLine("Rows inserted: " + rowsInserted);
 
                    cmd.Parameters[0].Value = "Jack"// name
                    cmd.Parameters[1].Value = new DateTime(1987, 11, 9); // birth date
                    cmd.Parameters[2].Value = 95.3; // weight
                    rowsInserted = cmd.ExecuteNonQuery();
                    Console.WriteLine("Rows inserted: " + rowsInserted);
 
                    // loop end
                }
                transaction.Commit();
            }
        }
 
    }
}

PROGRAM OUTPUT

1   Joe       02.07.1982     90,00
2   Mary      15.12.2000     65,10
3   Mike      27.05.1964     76,30
Rows inserted: 1
Rows inserted: 1
1   Joe       02.07.1982     90,00
2   Mary      15.12.2000     65,10
3   Mike      27.05.1964     76,30
4   Nik       20.03.1979     54,30
5   Jack      09.11.1987     95,30

DOWNLOAD

DemoCsharpAccessDatabase.zip

TIP

Query Express is a simple Query Analyzer look-alike, but being small and free it can be run where the SQL Server client tools or Access are not installed or licensed.

It connects to SQL Server, Oracle, Access and other OLE-DB compliant databases.

Connection string

Example connection string:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\PROJECTS\DEMO\DemoCsharpAccessDatabase\demo.accdb