C#操作Access数据库

首先把Access数据库文件放在项目的/bin/Debug的文件夹里面
下面是我Access文件中的两张测试表：Table1，Table2

DAL文件代码

using System;
using System.Data;
using System.Data.OleDb;

namespace access
{
    /// <summary>
    /// 操作Access数据库
    /// </summary>
    class AccessOperation
    {
        //连接字符
        //Environment.CurrentDirectory：获取或设置当前工作目录的完全限定路径
        static string connStr = @"Provider= Microsoft.Jet.OLEDB.4.0;Data Source = " + Environment.CurrentDirectory + @"\DataWarehouse.mdb";

        #region 执行查询指令，获取返回DataTable
        /// <summary>
        /// 执行查询指令，获取返回DataTable
        /// </summary>
        /// <param name="sql">查询sql语句</param>
        /// <param name="param">sql语句的参数</param>
        /// <returns></returns> 
        public static DataTable ExecuteDataTable(string sql, params OleDbParameter[] param)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    DataTable dt = new DataTable();
                    OleDbDataAdapter sda = new OleDbDataAdapter(cmd);
                    sda.Fill(dt);
                    return (dt);
                }
            }
        }

        #endregion

        #region 执行增加、删除、修改指令
        /// <summary>
        /// 执行增加、删除、修改指令
        /// </summary>
        /// <param name="sql">增加、删除、修改的sql语句</param>
        /// <param name="param">sql语句的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params OleDbParameter[] param)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    conn.Open();
                    return (cmd.ExecuteNonQuery());
                }
            }
        }

        #endregion

        #region 执行查询指令，获取返回的首行首列的值
        /// <summary>
        /// 执行查询指令，获取返回的首行首列的值
        /// </summary>
        /// <param name="sql">查询sql语句</param>
        /// <param name="param">sql语句的参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params OleDbParameter[] param)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                using (OleDbCommand cmd = new OleDbCommand(sql, conn))
                {
                    if (param != null)
                    {
                        cmd.Parameters.AddRange(param);
                    }
                    conn.Open();
                    return (cmd.ExecuteScalar());
                }
            }
        }
        #endregion

        #region 执行查询指令，获取返回的datareader
        /// <summary>
        /// 执行查询指令，获取返回的datareader
        /// </summary>
        /// <param name="sql">查询sql语句</param>
        /// <param name="param">sql语句的参数</param>
        /// <returns></returns>
        public static OleDbDataReader ExecuteReader(string sql, params OleDbParameter[] param)
        {
            OleDbConnection conn = new OleDbConnection(connStr);
            OleDbCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = CommandType.Text;
            if (param != null)
            {
                cmd.Parameters.AddRange(param);
            }
            conn.Open();
            return (cmd.ExecuteReader(CommandBehavior.CloseConnection));
        }
        #endregion
    }
}

查询代码：

 StringBuilder sb = new StringBuilder();
            //语句与SQL相同，此处不直接使用字符串拼接是因为如果查询多个字段或有多个条件使用拼接难找到你想要找的字符
            //若不直接用string 那么在此的每一个append中的值最后加上空格防止出错
            sb.Append("SELECT Table1.UID,Table1.Name,Table1.Sex,Table2.SID,Table2.Laborage,Table2.Month ");
            sb.Append("FROM   Table1 INNER JOIN Table2 ON Table1.UID = Table2.UID");
            dgvData.DataSource = AccessOperation.ExecuteDataTable(sb.ToString());

效果：

新增代码：

StringBuilder sb = new StringBuilder();
            //此处需注意，字段名需用中括号括起来，字符类型值需要用单引号括起来，时间类型值用前后加#
            sb.Append("INSERT INTO Table2([Laborage], [Month], [UID]) ");
            sb.Append("VALUES ('110+10',1,1)");
            ///int dt = Convert.ToInt32(ExecuteScalar("select @@identity as id")); 不支持。。

            int Insert = AccessOperation.ExecuteNonQuery(sb.ToString());
            if (Insert > 0)
            {
                sb.Clear();
                //存在并发问题。。
                sb.Append("SELECT SID ");
                sb.Append("FROM Table2 ");
                sb.Append("ORDER BY SID desc");
                //返回新增的ID
                                MessageBox.Show(AccessOperation.ExecuteScalar(sb.ToString()).ToString());
            }

效果：

修改代码：

//获取dgv当前选中的行
            int SID = Convert.ToInt32(dgvData.SelectedRows[0].Cells["SID"].Value.ToString());
            StringBuilder sb = new StringBuilder();
            //注意与新增一样
            sb.Append("UPDATE Table2 ");
            sb.Append("SET [Laborage] = '100.11+0.01', [Month] = 2, [UID] = 2 ");
            sb.Append("where SID = "+SID);
            //弹出受影响行数
            MessageBox.Show(AccessOperation.ExecuteNonQuery(sb.ToString()).ToString());

效果：

删除代码：

//获取dgv当前选中的行
            int SID = Convert.ToInt32(dgvData.SelectedRows[0].Cells["SID"].Value.ToString());
            
            MessageBox.Show(AccessOperation.ExecuteNonQuery("DELETE FROM Table2 WHERE SID=" + SID).ToString());          

Access数据库虽然说已经落伍了，但是技不压身，学一学，用来做一些单机的小程序还是不错的