ACCESS数据库操作

来源：互联网发布：mysql date转字符串编辑：程序博客网时间：2024/06/04 20:01
本文主要提供ACCESS数据库操作方法，包括连接、读取、插入、更新、删除等操作，以及基本指令格式；仅供学习！
以下为程序源码：
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Data.OleDb; // <- for database methodsusing 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)";        }    }
阅读全文
0 0