public static bool TableSubmit(List<tableInfo> tableList)
{
bool bolResult = false;
using (SQLiteConnection connection = new SQLiteConnection(DbHelperSQL.connectionString))
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (SQLiteTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted))
{
try
{
SQLiteDataAdapter adpAdapter;
SQLiteCommand command ;
SQLiteCommandBuilder commandBuilder;
foreach (tableInfo table in tableList)
{
//获取表信息
command = new SQLiteCommand() { CommandText = string.Format("SELECT {0} FROM {1}", getTableColumn(table.dtTable), table.sName) };
command.Connection = connection;
command.Transaction = trans;
//创建commandBuilder
adpAdapter = new SQLiteDataAdapter(command);
commandBuilder = new SQLiteCommandBuilder(adpAdapter);
//设置增删改SQLCommand
adpAdapter.InsertCommand = commandBuilder.GetInsertCommand();
adpAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();
adpAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
//设置事务提交数据
adpAdapter.InsertCommand.Transaction = trans;
adpAdapter.UpdateCommand.Transaction = trans;
adpAdapter.DeleteCommand.Transaction = trans;
//尝试提交数据
adpAdapter.Update(table.dtTable);
}
trans.Commit();//提交数据
bolResult = true;
}
catch (Exception ex)
{
trans.Rollback();//回滚数据
}
finally
{
//释放资源
trans.Dispose();
connection.Close();
connection.Dispose();
}
}
}
return bolResult;
}
public struct tableInfo
{
public readonly string sName; //表名
public readonly DataTable dtTable; //数据
public tableInfo(string Name,DataTable Table)
{
this.sName = Name;
this.dtTable = Table;
}