写了个保存数据的方法,一共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();
}
}
希望遇到相同问题的园友可以看见这篇随笔。