001 学习笔记--Access 常用操作

Access数据库——设计试图，可进行表字段设计

Access数据库——双击表，可维护数据

常用CRUD帮助方法如下所示：

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

namespace DBHelper
{
    public static class AccessHelper
    {
        //private static string connString = ConfigurationManager.ConnectionStrings["dbConnstr"].ConnectionString;

        public static OleDbConnection GetConnection(string connString)
        {
            OleDbConnection conn = new OleDbConnection(connString);
            return conn;
        }

        /// <summary>
        /// 获取数据总条数
        /// </summary>
        /// <returns></returns>
        public static int GetCount(string connString, string sql)
        {
            return (int)ExecuteScalar(connString, sql);
        }

        /// <summary>
        /// 删除制定ID行数据
        /// </summary>
        /// <param name="ID"></param>
        public static int DeleteByField(string connString, string tableName, string fieldName, string fieldValue)
        {
            //ExecuteNonQuery(connString,"delete from T_Person where ID=@ID", new OleDbParameter("@ID", fieldValue));
            string sql = "delete from " + tableName + " where " + fieldName + "=@" + fieldName;
            return ExecuteNonQuery(connString, sql, new OleDbParameter("@" + fieldName, fieldValue));
        }

        /// <summary>
        /// 执行 Transact-SQL 语句并返回受影响的行数
        /// </summary>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="parameters">sql语句中的参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string connString, 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();
                }
            }
        }

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集
        /// </summary>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="parameters">sql语句中的参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string connString, 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();
                }
            }
        }

        /// <summary>
        /// 只用来执行查询结果比较少的sql
        /// </summary>
        /// <param name="sql">执行的sql语句</param>
        /// <param name="parameters">sql语句中的参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string connString, 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];           //可以查询很多表，默认第一个
                }
            }
        }

    }
}

应用调用示例：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AccessDemo
{
    public class UserInfo
    {
        public string UserId { get; set; }
        public string UserName { get; set; }
        public string PassWord { get; set; }
        public string NickName { get; set; }
    }
}

窗体：

 

using DBHelper;
using System;
using System.Windows.Forms;

namespace AccessDemo
{
    public partial class Form1 : Form
    {
        //office 版本不同对应连接串不同
        private static string ConnString = "Provider= Microsoft.ACE.OLEDB.12.0;Jet OLEDB:DataBase Password = ''; Data Source = "
            + Application.StartupPath + "\\DB\\RFDB.accdb";

        //string strExePath = Application.StartupPath + @"MedViewer.mdb";   //mdb";
        //ConnString = @"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" + strExePath;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.dgvList.AutoGenerateColumns = false;
            string sql = "select * from UserInfo";
            //List<UserInfo> list = (List<UserInfo>)AccessHelper.ExecuteScalar(ConnString, sql);

            var list = AccessHelper.ExecuteDataTable(ConnString, sql);
            this.dgvList.DataSource = list;
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            try
            {
                string sql = @"insert into [UserInfo] ([UserId],[UserName],[PassWord],[NickName]) values ("
                    + "'" + this.txtUserId.Text + "',"
                    + "'" + this.txtUserName.Text + "',"
                    + "'" + this.txtPwd.Text + "',"
                    + "'" + this.txtNickName.Text + "')";

                int count = AccessHelper.ExecuteNonQuery(ConnString, sql);
                MessageBox.Show("新增成功：" + count);
                //UserId,UserName,PassWord,NickName
            }
            catch (Exception ex)
            {
            }
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            int count = AccessHelper.DeleteByField(ConnString, "UserInfo", "UserId", this.txtUserId.Text);

            MessageBox.Show("删除成功:" + count);
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            string sql = string.Format(@"update [UserInfo] set [PassWord] = '001' where [PassWord] ='{0}'", 1);

            int count = AccessHelper.ExecuteNonQuery(ConnString, sql);
            MessageBox.Show("修改成功：" + count);
        }

        private void btnQuery_Click(object sender, EventArgs e)
        {
            string sql = "select * from UserInfo";

            var list = AccessHelper.ExecuteDataTable(ConnString, sql);
            this.dgvList.DataSource = list;
        }
    }
}