Using Access databases in C#?

Question

How would I use a Microsoft Access (.accdb) database in C# (console application, not web asp.net)? From what I've read, I'll need to use ADO.NET, but I'm really at a loss as to how to do this in a C# console application. In PHP with MySQL, I'd be looking for mysqli_construct Could anyone point me towards a tutorial or documentation that would help me with that? I'm trying to use it as a way to store and access data for my non-web, non ASP.NET application, if that changes anything.

Thanks!

Solution 1

See this walkthrough for using ADO.NET to edit an Access database.

Now while that example is using a web application, don't worry, the classes being used are still applicable for a console application as well.

The main classes you will need to use are:

• OleDbConnection
• OleDbCommand
• OleDbDataReader

The documentation of the classes above all have code samples that are console app examples.

Below is a code snippet for use in a console app (remember to add the: using System.Data.OleDb;

string connectionString = 
    @"Provider=Microsoft.Jet.OLEDB.4.0;" +
    @"Data Source=C:\path\to\your\database.mdb;" +
    @"User Id=;Password=;";

string queryString = "SELECT Foo FROM Bar";

using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = new OleDbCommand(queryString, connection))
{
    try
    {
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine(reader[0].ToString());
        }
        reader.Close();
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Also, just noticed you explicitly said an .accdb database. For this grab the Microsoft Access Database Engine 2010 Redistributable. The provider in the connection string would then need to be changed to: Microsoft.ACE.OLEDB.12.0 (see notes at that link for more info).

Solution 2

Just use System.OleDb. Here is a small class that I use

First of all, place the database fle inside App_Data

web.config

<connectionStrings>
    <add name="MyConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DatabaseName.accdb" providerName="System.Data.OleDb"/>
</connectionStrings>

App_Code/DataAccess.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

public class DataAccess
{


    #region "Public Methods"

    public static DataTable GetTableFromQuery(string query, Dictionary<string, object> parameters, CommandType commandType)
    {
        DataTable dataTable = new DataTable();
        using (OleDbConnection conn = GetConnection()) {
            using (OleDbCommand cmd = new OleDbCommand(query, conn)) {
                cmd.CommandType = commandType;
                if (parameters != null) {
                    foreach (KeyValuePair<string, object> parameter in parameters) {
                        cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd)) {
                    adapter.Fill(dataTable);
                }
            }
        }
        return dataTable;
    }

    public static object GetSingleObjectFromQuery(string query, Dictionary<string, object> parameters, CommandType commandType)
    {
        object value = null;
        using (OleDbConnection conn = GetConnection()) {
            using (OleDbCommand cmd = new OleDbCommand(query, conn)) {
                cmd.CommandType = commandType;
                if (parameters != null) {
                    foreach (KeyValuePair<string, object> parameter in parameters) {
                        cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }
                conn.Open();
                using (OleDbDataReader reader = cmd.ExecuteReader()) {
                    while (reader.Read()) {
                        value = reader.GetValue(0);
                    }
                }
            }
        }
        return value;
    }

    public static int ExecuteNonQuery(string query, Dictionary<string, object> parameters, CommandType commandType)
    {
        int value = 1;
        using (OleDbConnection conn = GetConnection()) {
            using (OleDbCommand cmd = new OleDbCommand(query, conn)) {
                cmd.CommandType = commandType;
                if (parameters != null) {
                    foreach (KeyValuePair<string, object> parameter in parameters) {
                        cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
                    }
                }
                cmd.Connection.Open();
                value = cmd.ExecuteNonQuery();
            }
        }
        return value;
    }

    #endregion

    #region "Private Methods"

    private static OleDbConnection GetConnection()
    {
        string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString;
        return new OleDbConnection(ConnectionString);
    }

    #endregion

}

---

var myTable = DataAccess.GetTableFromQuery("SELECT * FROM TableName", null, CommandType.Text);

Solution 3

Take a look at the OLEDB Namespace there are some simple examples here and here.

Comments

[Roman]

FYI, you don't need the set of braces on that first using statement.