在 C# 中使用 Access 数据库？

Using Access databases in C#?

提问by MatthewSot

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_constructCould 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.NETapplication, if that changes anything.

我将如何.accdb在 C#（控制台应用程序，而不是 web asp.net）中使用 Microsoft Access ( ) 数据库？从我读过的内容来看，我需要使用ADO.NET，但我真的不知道如何在 C# 控制台应用程序中执行此操作。在带有 的 PHP 中MySQL，我会寻找mysqli_construct可以帮助我解决此问题的教程或文档吗？我试图将它用作存储和访问我的非 Web、非 ASP.NET应用程序数据的一种方式，如果这有什么改变的话。

Thanks!

谢谢！

采纳答案by Robert Groves

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

有关使用 ADO.NET 编辑 Access 数据库的信息，请参阅此演练。

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.

现在，虽然该示例使用的是 Web 应用程序，但请不要担心，所使用的类仍然适用于控制台应用程序。

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;

下面是在控制台应用程序中使用的代码片段（记得添加： 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).

另外，刚刚注意到您明确提到了 .accdb 数据库。为此，请使用Microsoft Access Database Engine 2010 Redistributable。然后需要将连接字符串中的提供程序更改为：（Microsoft.ACE.OLEDB.12.0有关更多信息，请参阅该链接中的注释）。

回答by nmaait

Take a look at the OLEDB Namespacethere are some simple examples hereand here.

看看OLEDB 命名空间，这里和这里有一些简单的例子。

回答by naveen

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

只需使用System.OleDb. 这是我使用的一个小班级

First of all, place the database fle inside App_Data

首先，将数据库文件放在 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

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);