一、创建Access数据文件

1、新建Access数据库文件

2、创建数据表

在菜单栏中->创建->表

ID、Age为长整型，Height为单精度

ID、Name为必须、其它为非必须即可不填

表命名为T_Person

二、创建C# Winform工程

1、配置文件

在App.Config文件中设置Access连接路径字符串

<connectionStrings>
    <add name="dbConnStr" connectionString="Provider= Microsoft.ACE.OLEDB.12.0;Jet OLEDB:DataBase Password='huobaby60945';Data Source=C:\Users\Administrator\Desktop\C# Access Test\AccessTest.accdb"/>
  </connectionStrings>

2、创建三层架构

分别设置数据连接层、业务处理层、UI层、Model，对应每个封装的类

（1）Model模型

PersonModel

class PersonModel
    {
        public long ID { get; set; }

        public string Name { get; set; }

        public int? Age { get; set; }

        public string AiHao { get; set; }

        public float? Height { get; set; }

        public DateTime? BirthDay { get; set; }
    }

（2）数据连接层

SqlAccesHelp

读取App.Config中的连接字符串

private static string connString = ConfigurationManager.ConnectionStrings["dbConnstr"].ConnectionString;

封装ExecuteNonQuery

        /// <summary>
        /// 执行 Transact-SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="parameters">sql语句中的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, params OleDbParameter[] parameters)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);        //AddRange添加的是数组
                    return cmd.ExecuteNonQuery();
                }
            }
        }

封装ExecuteScalar

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集
        /// </summary>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="parameters">sql语句中的参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params OleDbParameter[] parameters)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    //方法2
                    cmd.Parameters.AddRange(parameters);        //AddRange添加的是数组
                    return cmd.ExecuteScalar();
                }
            }
        }

封装ExecuteDataTable

        /// <summary>
        /// 只用来执行查询结果比较少的sql
        /// </summary>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="parameters">sql语句中的参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params OleDbParameter[] parameters)
        {
            using (OleDbConnection conn = new OleDbConnection(connString))
            {
                conn.Open();
                using (OleDbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    //方法2
                    cmd.Parameters.AddRange(parameters);        //AddRange添加的是数组
                    OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                    DataSet dataset = new DataSet();
                    adapter.Fill(dataset);
                    return dataset.Tables[0];           //可以查询很多表，默认第一个
                }
            }
        }

（3）业务处理层

PersonDAL

获取数据总条数

        /// <summary>
        /// 获取数据总条数
        /// </summary>
        /// <returns></returns>
        public static int GetCount()
        {
            return (int)SqlAccesHelp.ExecuteScalar("select count(*) from T_Person");
        }

删除制定ID行数据

        /// <summary>
        /// 删除制定ID行数据
        /// </summary>
        /// <param name="ID"></param>
        public static void DeleteByID(long ID)
        {
            SqlAccesHelp.ExecuteNonQuery("delete from T_Person where ID=@ID",
                        new OleDbParameter("@ID", ID));
        }

插入数据

        /// <summary>
        /// 插入数据。
        /// </summary>
        /// <param name="person"></param>
        public static void Insert(PersonModel person)
        {
            SqlAccesHelp.ExecuteNonQuery("insert into T_Person(ID,Name,Age,AiHao,Height,BirthDay) values (@_ID,@_Name,@_Age,@_AiHao,@_Height,@_BirthDay)",
                                    new OleDbParameter("@_ID", person.ID),
                                    new OleDbParameter("@_Name", person.Name),
                                    new OleDbParameter("@_Age", ToDBValue(person.Age)),
                                    new OleDbParameter("@_AiHao", ToDBValue(person.AiHao)),
                                    new OleDbParameter("@_Height", ToDBValue(person.Height)),
                                    new OleDbParameter("@_BirthDay", ToDBValue(person.BirthDay))
                                    );
        }

读取数据表中指定ID的数据

        /// <summary>
        /// 读取数据表中指定ID的数据
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static PersonModel GetByID(long id)
        {
            DataTable table = SqlAccesHelp.ExecuteDataTable("select * from T_Person where ID=@ID",
                new OleDbParameter("@ID", id));
            if (table.Rows.Count <= 0)
            {
                return null;    //不存在
            }
            else if (table.Rows.Count > 1)
            {
                //主键重复
                throw new Exception("ID存在重复");
            }
            else
            {
                //DAL中不要返回DataTable、DataRow等ADO.Net的类
                DataRow row = table.Rows[0];
                PersonModel person = new PersonModel();
                person.ID = (int)row["ID"];
                person.Name = (string)row["Name"];
                person.Age = (int)FromDBValue(row["Age"]);
                person.AiHao = (string)FromDBValue(row["AiHao"]);               //string本身就是可空类型
                person.Height = (float?)FromDBValue(row["Height"]);            //可空类型
                person.BirthDay = (DateTime?)FromDBValue(row["BirthDay"]);       //可空类型

                return person;
            }
        }