Access数据访问层（DAL）

最新推荐文章于 2023-02-25 20:23:07 发布

原创最新推荐文章于 2023-02-25 20:23:07 发布 · 547 阅读

0 ·

CC 4.0 BY-SA版权

C# 同时被 3 个专栏收录

6 篇文章

订阅专栏

Access

3 篇文章

订阅专栏

ADO.NET

3 篇文章

订阅专栏

本文详细探讨了Access作为数据访问层（DAL）的角色，介绍如何利用Access连接和操作数据库，包括创建连接字符串、执行SQL查询以及事务处理。通过实例展示了在应用程序中集成Access DAL的方法，帮助开发者理解如何高效地管理和访问数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using DTcms.DBUtility;

namespace example
{
    public class AdminAccess
    {
        #region 是否存在该记录
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Admin");
            strSql.Append(" where admin_id=@admin_id ");
            OleDbParameter[] parameters = {
					new OleDbParameter("@admin_id", OleDbType.Integer,4)};
            parameters[0].Value = id;
            return DbHelperOleDb.Exists(strSql.ToString(), parameters);
        }
        #endregion
        #region 增加
        /// <summary>
        /// 增加一条数据
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public bool Insert(AdminInfo admin)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Admin(");
            strSql.Append("admin_name,admin_password,admin_type)");
            strSql.Append(" values (");
            strSql.Append("@admin_name,@admin_password,@admin_type)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@admin_name", OleDbType.VarChar,50),
					new OleDbParameter("@admin_password", OleDbType.VarChar,50),
					new OleDbParameter("@admin_type", OleDbType.Integer,4)};
            parameters[0].Value = admin.admin_name;
            parameters[1].Value = admin.admin_password;
            parameters[2].Value = admin.admin_type;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #region 修改
        /// <summary>
        /// 修改一列数据
        /// </summary>
        /// <param name="id"></param>
        /// <param name="strValue"></param>
        public void Update(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Admin set " + strValue);
            strSql.Append(" where admin_id=" + id);
            DbHelperOleDb.ExecuteSql(strSql.ToString());
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        /// <param name="client"></param>
        /// <returns></returns>
        public bool Update(AdminInfo admin)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Admin set ");
            strSql.Append("admin_name=@admin_name,admin_password=@admin_password,admin_type=@admin_type)");
            strSql.Append(" where admin_id=@admin_id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@admin_name", OleDbType.VarChar,50),
					new OleDbParameter("@admin_password", OleDbType.VarChar,50),
					new OleDbParameter("@admin_type", OleDbType.Integer,4),
                    new OleDbParameter("@admin_id", OleDbType.Integer,4)};
            parameters[0].Value = admin.admin_name;
            parameters[1].Value = admin.admin_password;
            parameters[2].Value = admin.admin_type;
            parameters[3].Value = admin.admin_id;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #region 删除
        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Admin ");
            strSql.Append(" where admin_id=@admin_id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@admin_id", OleDbType.Integer,4)};
            parameters[0].Value = id;
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #region 查询
        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="strWhere"></param>
        /// <returns></returns>
        public IList<AdminInfo> Select(string strWhere)
        {
            IList<AdminInfo> list = new List<AdminInfo>();
            string strSql = "select * from Admin " + strWhere;

            using (IDataReader rd = DbHelperOleDb.ExecuteReader(strSql))
            {
                while (rd.Read())
                {
                    AdminInfo client = Select(rd);
                    list.Add(client);
                }
            }
            return list;

        }
        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <param name="rd"></param>
        /// <returns></returns>
        public AdminInfo Select(IDataReader rd)
        {
            AdminInfo admin = new AdminInfo();
            admin.admin_id = (int)rd["admin_id"];
            admin.admin_name = rd["admin_name"].ToString();
            admin.admin_password = rd["admin_password"].ToString();
            admin.admin_type = (int)rd["admin_type"];
            return admin;
        }
        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public AdminInfo Select(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 admin_id,admin_name,admin_password,admin_type from Admin where admin_id=@admin_id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@admin_id", OleDbType.Integer,4)};
            parameters[0].Value = id;
            DataSet ds = DbHelperOleDb.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
                return Select(ds.Tables[0].Rows[0]);
            return null;
        }
        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public AdminInfo Select(DataRow row)
        {
            AdminInfo admin = new AdminInfo();
            admin.admin_id = (int)row["admin_id"];
            admin.admin_name = row["admin_name"].ToString();
            admin.admin_password = row["admin_password"].ToString();
            admin.admin_type = (int)row["admin_type"];
            return admin;
        }
        #endregion
    }
}