Access创建表结构及DataTable与数据库同步的问题

    今天因为项目需要，要做个小工具，把txt里的内容保存到access表中去，折腾了半天，历经曲折艰辛，终于把问题解决了，现在把代码写下，留作后用。

    一、创建表结构

        /// <summary>
        /// 创建mdb数据库，数据表及字段
        /// </summary>
        /// <param name="strAccessFileName">mdb文件名</param>
        /// <param name="strTableName">表名</param>
        /// <param name="listFieldName">字段列表</param>
        private void CreateAccessDBAndTable(string strAccessFileName,string   strTableName,List<string> listFieldName)
        {
            CatalogClass cat = new CatalogClass();
            cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;" +
              "Data Source="+strAccessFileName+";Jet OLEDB:Engine Type=5");

            TableClass tableClass = new TableClass();
            tableClass.ParentCatalog = cat;
            tableClass.Name = strTableName;
            //添加字段列表
            if(listFieldName != null && listFieldName.Count > 0)
            {
                foreach(string strFieldName in listFieldName)
                {
                    ColumnClass column = new ColumnClass();
                    column.ParentCatalog = cat;
                    column.Name = strFieldName;
                    //column.Properties["Jet OLEDB:Allow Zero Length"].Value = false;
                    column.Attributes = ColumnAttributesEnum.adColNullable;//字段值允许空值
                    tableClass.Columns.Append(column, DataTypeEnum.adVarChar, 50);
                }
            }
            //创建表
            cat.Tables.Append(tableClass);
            tableClass = null;
            cat = null;
        }
 

 二、DataTable与数据库同步操作

//获取数据库连接

IDbConnection dbConnection = this.GetDbConnection(strMdbFileName);
                if(dbConnection == null)
                {
                    return;
                }
                dbConnection.Open();//打开数据库连接
                string strCommandText = "select * from " + strTableName;//构造command语句
                DbDataAdapter dbAdapter = new OleDbDataAdapter(strCommandText,dbConnection as OleDbConnection);     

 

//必须激活OleDbCommandBuilder对象           
                DbCommandBuilder dbCmdBuilder = new OleDbCommandBuilder(dbAdapter as OleDbDataAdapter);

 

//发现DbCommandBuilder 两个很关键的属性：QuotePrefix，QuoteSuffix；仔细想想，OleDb可以访问的数据类型非常多啊，所以关键字段的前缀，后缀的处理方法肯定不尽相同，比如访问Excel的时候表明应该写成[sheet1$的方式]，所以提供这样一种方式是相当灵活的。用于解决表里的字段名使用了access系统的保留字而出现：”Insert into 语句的语法错误“的问题
                dbCmdBuilder.QuotePrefix = "[";
                dbCmdBuilder.QuoteSuffix = "]";
                DataSet oDataset = new DataSet();
               
                dbAdapter.Fill(oDataset,strTableName);
                DataTable dtDatatable = oDataset.Tables[strTableName];//获取数据表

               

               //遍历text文件,一个文件的内容作为表的一条记录

                foreach(string strFileName in strFileNames)
                {
                    DataRow dtDatarow = dtDatatable.NewRow();//添加一条记录行

                    StreamReader srReader = new StreamReader(strFileName, System.Text.Encoding.Default);
                    string strReadLine = null;
                    while ((strReadLine = srReader.ReadLine()) != null)
                    {
                        Regex regex = new Regex(@"/s+");
                        strReadLine = regex.Replace(strReadLine, " ");//多个空格用一个空格替换
                        strReadLine = strReadLine.Trim();//去除字符串开始和结束的空格
                        string[] strStrings = strReadLine.Split(new char[] { ' ' });
                        if(strStrings != null && strStrings.Length >= 2)
                        {
                            try
                            {
                                int.Parse(strStrings[0]);
                            }
                            catch
                            {
                                continue;
                            }
                            //如果表中有该字段
                            string strFieldName = strStrings[1].Trim().ToUpper();
                            if(dtDatatable.Columns.Contains(strFieldName))
                            {
                                //若数组个数为2，则字段值赋值为空字符串
                                if(strStrings.Length == 2)
                                {
                                    dtDatarow[strFieldName] = "";
                                }
                                else
                                {
                                    dtDatarow[strFieldName] = strStrings[2];
                                }
                            }
                        }
                    }
                    srReader.Close();
                    dtDatatable.Rows.Add(dtDatarow);//添加该条记录行
                }
                //下面两个方法调用顺序不能反了
                dbAdapter.Update(oDataset, strTableName);
                dtDatatable.AcceptChanges();
                dbConnection.Close();//关闭数据库连接

 

 

总结注意事项：

注意：
1、必须激活OleDbCommandBuilder对象，该对象是OleDb产生adapter.insertCommand,adapter.updateCommand,adapter.deleteCommand的关键
2、dtDatatable.AcceptChanges();方法必须写在dbAdapter.Update(oDataset, strTableName)的后面，否则状态位都接受了改变，就不能更新数据库了。

3、注意DbCommandBuilder 两个很关键的属性：QuotePrefix，QuoteSuffix；仔细想想，OleDb可以访问的数据类型非常多啊，所以关键字段的前缀，后缀的处理方法肯定不尽相同，比如访问Excel的时候表明应该写成[sheet1$的方式]，所以提供这样一种方式是相当灵活的。用于解决表里的字段名使用了access系统的保留字而出现：”Insert into 语句的语法错误“的问题