博客

本文主要提供ACCESS数据库操作方法，包括连接、读取、插入、更新、删除等操作，以及基本指令格式；仅供学习！

以下为程序源码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.OleDb; // <- for database methods
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

    //========================================================================================
    //==================//               ACCESS数据库操作                 //==================//                          
    //========================================================================================
    public class AccessDbObj
    {
        public OleDbConnection m_DbConnect;

        //string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=moviedb.mdb";
        //必须使用86平台（64有错误，打不开数据库）
        public void OpenDataBase(string path)
        {
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path;
            try
            {
                m_DbConnect = new OleDbConnection(connectionString);
                m_DbConnect.Open();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return;
            }
        }

        //关闭数据库
        public void CloseDataBase()
        {
            m_DbConnect.Close();
        }

        static public string ToDbString(string strData)
        {
            return "'" + strData + "'";
        }

        //获取数据库全部数据
        public DataTable GetDbDataTable(string sqlQueryString)
        {
            DataTable data = new DataTable();
            try
            {
                OleDbCommand SQLQuery = new OleDbCommand();
                SQLQuery.CommandText = sqlQueryString;
                SQLQuery.Connection = m_DbConnect;
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(SQLQuery);               
                dataAdapter.Fill(data);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message);
                data = null;
            }
            return data;
        }


        //如果更新失败，则添加新项目
        public bool AddIfUpdataFailed(string SQLUpdateString, string SQLInsertString)
        {
            try
            {
                OleDbCommand SQLCommand1 = new OleDbCommand();
                SQLCommand1.CommandText = SQLUpdateString;
                SQLCommand1.Connection = m_DbConnect;
                int response1 = SQLCommand1.ExecuteNonQuery();
                if (response1 >= 1)
                {
                    return true;
                }
                else
                {
                    OleDbCommand SQLCommand = new OleDbCommand();
                    SQLCommand.CommandText = SQLInsertString;
                    SQLCommand.Connection = m_DbConnect;
                    int response = -1;
                    response = SQLCommand.ExecuteNonQuery();
                    if (response >= 1)
                        return true;
                    else
                        return false;
                }             
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //插入指令（格式化插入） //"INSERT INTO 表名(字段1，字段2，...,字段n) VALUES(内容1，内容2，...，内容n)" //注意 字符串要加单引号''
        public bool InsertCommand(string SQLInsertString)
        {
            try
            {              
                OleDbCommand SQLCommand = new OleDbCommand();
                SQLCommand.CommandText = SQLInsertString;
                SQLCommand.Connection = m_DbConnect;
                int response = -1;
                response = SQLCommand.ExecuteNonQuery();
                if (response >= 1)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //删除指令 "DELETE FROM 表名 where 字段 = " + "内容" + "";  //注意 字符串要加单引号''
        public void DeleteCommand(string SQLDeleteString)
        {
            try
            {               
                OleDbCommand sqlDelete = new OleDbCommand();
                sqlDelete.CommandText = SQLDeleteString;
                sqlDelete.Connection = m_DbConnect;
                sqlDelete.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        //更新指令 "UPDATE 表名 SET 字段1 =内容1, 字段2=内容2,...,字段n=内容n WHERE 查找字段=查找内容"  //注意 字符串要加单引号''
        public bool UpdataCommand(string SQLUpdateString)
        {
            try
            {
                OleDbCommand SQLCommand = new OleDbCommand();
                SQLCommand.CommandText = SQLUpdateString;
                SQLCommand.Connection = m_DbConnect;
                int response = SQLCommand.ExecuteNonQuery();
                if (response >= 1)
                {
                    //MessageBox.Show("Update successful!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
                else
                {
                    //MessageBox.Show("更新失败，没有该项目");
                    return false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return false;
            }
        }

        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movieType WHERE movietype.typeID = movie.typeID";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.title LIKE '" + title + "%'";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.typeID = " + type + "";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND movie.MovieYear BETWEEN " + yr1 + " AND " + yr2 + "";
        //"SELECT movieID, Title, Publisher, Previewed, MovieYear, Type FROM movie,movietype WHERE movietype.typeID = movie.typeID AND Previewed ='" + previewed + "'";
        //选择指令
        public int SelectCommand(string SQLSelectString)
        {
            OleDbCommand SQLQuery = new OleDbCommand();    
            SQLQuery.CommandText = SQLSelectString;
            SQLQuery.Connection = m_DbConnect; 
            DataTable data = new DataTable();
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(SQLQuery);
            dataAdapter.Fill(data);
            return data.Rows.Count;

            //dataGridView1.DataSource = null;
            //dataGridView1.Columns.Clear(); // <-- clear columns
            //---------------------------------
            //dataGridView1.DataSource = data;
            //dataGridView1.AllowUserToAddRows = false; // remove the null line
            //dataGridView1.ReadOnly = true;
        }

        public int GetRowCount( string fieldName)
        {
            string SQLCmdText = "   select max(" + fieldName + "）from table";
            return 1;
        }

        public void AlterTable(string TableName)
        {           
            //string SQLAlterString = "Alter TABLE [表名] Alter COLUMN 自动编号字段名 COUNTER (你要的初始值, 1)";
        }
    }