csharp-access database

While we can use OLE DB if we’re using the .NET Framework, Microsoft has stated that OLE DB won’t be supported for .NET Core.

.net core

使用 ODBC 来实现。

.net framework

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SPCAssist
{
    /*
     * Oledb.4.0在64位系统上不支持，需要使用 x86 目标平台
     */
    public class AccessHelper
    {
        OleDbConnection conn;
        DataTable dt;

        /*
         * dbPath 完整路径 d:/ddd/dd.mdb
         */
        public int Open(string dbPath)
        {
            if (conn==null)
            {
                conn = new OleDbConnection();
            }
            conn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; Data Source=" + dbPath;

            return 0;
        }

        public void Close()
        {
            conn.Close();
        }

        public DataTable Query(string sql)
        {
            OleDbCommand cmd = new OleDbCommand();
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            dt = new DataTable();
            da.Fill(dt);
            
            return dt;
        }
        public int Exec(string sql)
        {
            OleDbCommand cmd = new OleDbCommand();
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = sql;
            int iRet = cmd.ExecuteNonQuery();

            return iRet;
        }

    }
}

query

sql = "select * from tblCharacteristic";
var dt = access.Query(sql);
foreach (DataRow row in dt.Rows)
{
    var ss = row["CharacteristicID"];
}

string mdfFile = @"csharpexamples.mdb";
 
using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
{
    using (OleDbCommand selectCommand = new OleDbCommand("SELECT TOP 10 * FROM TABLE1", connection))
    {
        connection.Open();
 
        DataTable table = new DataTable();
        OleDbDataAdapter adapter = new OleDbDataAdapter();
        adapter.SelectCommand = selectCommand;
        adapter.Fill(table);
 
        foreach (DataRow row in table.Rows)
        {
            object nameValue = row["NAME"];
            object surnameValue = row["SURNAME"];
        }
    }
}

insert

string mdfFile = @"csharpexamples.mdb";
 
using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
{
    using (OleDbCommand insertCommand = new OleDbCommand("INSERT INTO TABLE1 ([NAME],[SURNAME]) VALUES (?,?)", connection))
    {
        connection.Open();
 
        insertCommand.Parameters.AddWithValue("@NAME", "Brad");
        insertCommand.Parameters.AddWithValue("@SURNAME", "Pitt");
 
        insertCommand.ExecuteNonQuery();
    }
}

update

string mdfFile = @"csharpexamples.mdb";
 
using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
{
    using (OleDbCommand updateCommand = new OleDbCommand("UPDATE TABLE1 SET [NAME] = ?, [SURNAME] = ? WHERE [ID] = ?", connection))
    {
        connection.Open();
 
        updateCommand.Parameters.AddWithValue("@NAME", "Brad2");
        updateCommand.Parameters.AddWithValue("@SURNAME", "Pitt2");
        updateCommand.Parameters.AddWithValue("@ID", 2);
 
        updateCommand.ExecuteNonQuery();
    }
}      

delete

string mdfFile = @"csharpexamples.mdb";
 
using (OleDbConnection connection = new OleDbConnection(string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}", mdfFile)))
{
    using (OleDbCommand deleteCommand = new OleDbCommand("DELETE FROM TABLE1 WHERE [ID] = ?", connection))
    {
        connection.Open();
 
        deleteCommand.Parameters.AddWithValue("@ID", 2);
 
        deleteCommand.ExecuteNonQuery();
    }
}