Sqlite批量插入速度慢的解决方法小计

写了个保存数据的方法，一共500多条数据，用了一分钟还多，代码如下：

 

　　　/// <summary>
        /// 保存DataTable
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <returns>影响的行数</returns>
        public int SaveDataTable(DataTable dt)
        {
            try
            {
                DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter();
                string sql = string.Format(@"select * from {0} where 1=2", dt.TableName);
                DbCommand cmd = _dbProviderFactory.CreateCommand();
                DbCommandBuilder comBuilder = _dbProviderFactory.CreateCommandBuilder();

                comBuilder.DataAdapter = adapter;
                cmd.CommandText = sql;
                cmd.Connection = _con;
                adapter.SelectCommand = cmd;
                adapter.InsertCommand = comBuilder.GetInsertCommand();
                adapter.UpdateCommand = comBuilder.GetUpdateCommand();
                adapter.DeleteCommand = comBuilder.GetDeleteCommand();

                _con.Open();
                 int result = adapter.Update(dt);
                 return result;
            }
            catch (Exception ex)
            {
                  throw new Exception("保存DataTable出错：" + ex.Message);
            }
            finally
            {
                _con.Close();
            }

后来在一篇博客（http://zhiwei.li/text/2010/08/sqlite%E6%8F%92%E5%85%A5%E6%80%A7%E8%83%BD%E4%BC%98%E5%8C%96/）里发现了问题的解决方法，原来sqlite在每执行一次insert操作时都开启一次事务，在sqlite官网上的解释如下：Because it does not have a central server to coordinate access, SQLite must close and reopen the database file, and thus invalidate its cache, for each transaction. In this test, each SQL statement is a separate transaction so the database file must be opened and closed and the cache must be .......

 

　　后来将代码修改如下就OK了：

 

  　　 /// <summary>
        /// 保存DataTable
        /// </summary>
        /// <param name="dt">datatable</param>
        /// <returns>影响的行数</returns>
        public int SaveDataTable(DataTable dt)
        {
            try
            {
                DbDataAdapter adapter = _dbProviderFactory.CreateDataAdapter();
                string sql = string.Format(@"select * from {0} where 1=2", dt.TableName);
                DbCommand cmd = _dbProviderFactory.CreateCommand();
                DbCommandBuilder comBuilder = _dbProviderFactory.CreateCommandBuilder();

                comBuilder.DataAdapter = adapter;
                cmd.CommandText = sql;
                cmd.Connection = _con;
                adapter.SelectCommand = cmd;
                adapter.InsertCommand = comBuilder.GetInsertCommand();
                adapter.UpdateCommand = comBuilder.GetUpdateCommand();
                adapter.DeleteCommand = comBuilder.GetDeleteCommand();

                _con.Open();
                _tran = _con.BeginTransaction();//transaction begin 传说中sqlite每执行一条insert语句都开启一个事务，死慢
                cmd.Transaction = _tran;
                int result = adapter.Update(dt);
                _tran.Commit();//transaction end
                return result;
            }
            catch (Exception ex)
            {
                _tran.Rollback();
                throw new Exception("保存DataTable出错：" + ex.Message);
            }
            finally
            {
                _con.Close();
            }
        }

希望遇到相同问题的园友可以看见这篇随笔。