C#操作MDB数据库

最新推荐文章于 2024-08-22 17:06:37 发布
yang帆
最新推荐文章于 2024-08-22 17:06:37 发布
阅读量6.6k
点赞数 3
分类专栏： c#学习笔记文章标签： C# MDB Access 创建mdb文件读取mdb数据
本文链接：https://blog.csdn.net/Until_youyf/article/details/103363941
版权
c#学习笔记专栏收录该内容
8 篇文章 0 订阅
订阅专栏
C#操作MDB数据库，包括创建mdb文件，打开连接，读取mdb数据以及存储数据，从mdb数据库中获取表信息以及表字段、表数据信息
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;

namespace Business
{
    public class MdbHelper
    {
        private OleDbConnection myConn;
        /// <summary>
        /// 初始化连接数据库
        /// </summary>
        /// <param name="address"></param>
        public MdbHelper(string address)
        {
            try
            {
                //创建一个 OleDbConnection对象
                string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + address;
                myConn = new OleDbConnection(strCon);
                myConn.Open();
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("初始化连接数据库连接失败:{0}", ex.Message));
            }
        }

        /// <summary>
        /// 关闭数据连接
        /// </summary>
        public void CloseConnection()
        {
            myConn.Close();
        }

        /// <summary>
        ///在mdb中创建一个类型和datatable一致的空表
        /// </summary>
        /// <param name="tableName"></param>
        public void CreateTable(string tableName, DataTable dt)
        {
            try
            {
                string sql = "create table " + tableName;
                string tableAttribute = "";
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    tableAttribute = tableAttribute + dt.Columns[i].ColumnName + " " + GetType(dt.Columns[i].DataType.ToString());
                    if (i < dt.Columns.Count - 1)
                    {
                        tableAttribute = tableAttribute + ",";
                    }
                }
                sql = sql + "(" + tableAttribute + ");";
                OleDbCommand cmd = new OleDbCommand(sql, myConn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 将datatable导入对应名字的表中
        /// </summary>
        /// <param name="path"></param>
        /// <param name="name"></param>
        /// <param name="dt"></param>
        public void DatatableToMdb(string name, DataTable dt)
        {
            try
            {
                string strCom = string.Format("select * from {0}", name);
                OleDbDataAdapter da = new OleDbDataAdapter(strCom, myConn);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);//这里的CommandBuilder对象一定不要忘了,一般就是写在DataAdapter定义的后面
                cb.QuotePrefix = "[";
                cb.QuoteSuffix = "]";
                DataSet midData = new DataSet();
                da.Fill(midData, name);
                foreach (DataRow dR in dt.Rows)
                {
                    DataRow dr = midData.Tables[name].NewRow();
                    dr.ItemArray = dR.ItemArray;//行复制
                    midData.Tables[name].Rows.Add(dr);
                }
                da.Update(midData, name);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 获取创建mdb表格的属性字段类型
        /// </summary>
        /// <param name="datatype"></param>
        /// <returns></returns>
        private string GetType(string datatype)
        {
            switch (datatype)//匹配类型选择
            {
                case "System.String":
                    return "TEXT(50)";
                case "System.DateTime":
                    return "DateTime";
                case "System.Double":
                    return "Double";
                case "System.Int32":
                case "System.Int16":
                case "System.Int64":
                    return "Int";
                default:
                    return "TEXT(50)";
            }
        }
        /// <summary>
        /// 创建Access数据库
        /// </summary>
        /// <param name="path">文件和文件路径</param>
        /// <returns>真为创建成功，假为创建失败或是文件已存在</returns>
        public static bool CreateAccessDatabase(string path)
        {
            //如果文件存在反回假
            if (File.Exists(path))
            {
                MessageBox.Show("文件已存在！");
                return false;
            }

            try
            {
                //如果目录不存在，则创建目录
                string dirName = Path.GetDirectoryName(path);
                if (!Directory.Exists(dirName))
                {
                    Directory.CreateDirectory(dirName);
                }

                //创建Catalog目录类
                ADOX.CatalogClass catalog = new ADOX.CatalogClass();

                string _connectionStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path
                                         + ";";
                //根据联结字符串使用Jet数据库引擎创建数据库
                catalog.Create(_connectionStr);

                //要加上下面这两句，否则创建文件后会有*.ldb文件，一直到程序关闭后才消失 
                //原因参考   https://blog.csdn.net/baple/article/details/8131717
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog.ActiveConnection);
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(catalog);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("数据库创建失败:{0}", ex.Message));
            }
        }

        /// <summary>
        /// 获取当前连接的mdb中的所有表名
        /// </summary>
        /// <returns></returns>
        public List<string> GetTables()
        {
            DataTable dt = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
            List<string> tableNameList = new List<string>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                String tableName = dt.Rows[i]["TABLE_NAME"].ToString();
                tableNameList.Add(tableName);
            }
            return tableNameList;
        }

        /// <summary>
        /// 获取当前传入表名中的表内的所有字段信息
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<Dictionary<string, OleDbType>> GetFields(string tableName)
        {
            DataTable schemaTable;
            List<Dictionary<string, OleDbType>> Fields = new List<Dictionary<string, OleDbType>>();
            schemaTable = myConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new Object[] { null, null, tableName, null });
            for (int j = 0; j < schemaTable.Rows.Count; j++)
            {
                Dictionary<string, OleDbType> dic = new Dictionary<string, OleDbType>();
                string fieldName = schemaTable.Rows[j]["COLUMN_NAME"].ToString();
                OleDbType type = (OleDbType)schemaTable.Rows[j]["DATA_TYPE"];
                dic.Add(fieldName, type);
                Fields.Add(dic);
            }
            return Fields;
        }

        /// <summary> 
        /// 根据SQL命令返回一个DataTable
        /// </summary> 
        /// <param name="sql">sql/param> 
        /// <returns>DataTable</returns> 
        public DataTable GetDataTable(string sql)
        {
            DataTable ds = new DataTable();
            try
            {
                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, myConn);
                adapter.Fill(ds);
            }
            catch (Exception)
            {
                throw new Exception("sql语句：" + sql + " 执行失败！");
            }
            return ds;
        }

        /// <summary>
        /// 按行获取数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public IDataReader QueryReader(string sql)
        {
            lock (myConn)
            {
                bool fClose = myConn.State != ConnectionState.Open;
                try
                {
                    if (fClose)
                        myConn.Open();
                    OleDbCommand cmd = new OleDbCommand(sql, myConn);
                    return cmd.ExecuteReader();
                }
                catch (Exception)
                {
                    throw new Exception("sql语句：" + sql + " 执行失败！");
                }
            }
        }

        /// <summary>
        /// 用于查询数据条数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int QueryOneInt(string sql)
        {
            using (var dr = QueryReader(sql))
            {
                if (dr.Read())
                {
                    var o = dr.GetValue(0);
                    return SafeConvertAux.ToInt32(o);
                }
                return 0;
            }
        }
    }
}