mysql ,from:https://blog.csdn.net/majiakun1/article/details/46607163
public static void ExecuteNonQueryWithCommit(List<String> SQLList)
{
if (conn == null)
return;
if (conn.State != System.Data.ConnectionState.Open)
{
connect();
}
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = conn.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLList.Count; n++)
{
string strsql = SQLList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
//后来加上的
if (n > 0 && (n % 500 == 0 || n == SQLList.Count - 1))
{
tx.Commit();
tx = conn.BeginTransaction();
}
}
//tx.Commit();//原来一次性提交
}
catch (System.Data.SqlClient.SqlException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
}
sqlite版
public static void ExecuteNonQueryWithCommit(List<String> SQLList)
{
if (cn == null)
return;
if (cn.State != System.Data.ConnectionState.Open)
{
cn.Open();
}
using (SQLiteTransaction tran = cn.BeginTransaction())
{
SQLiteCommand command = new SQLiteCommand();
command.Connection = cn;
string ErrorSQL = "";
try
{
foreach (string SQL in SQLList)
{
ErrorSQL = SQL;
command.CommandText = SQL;
command.ExecuteNonQuery();
}
tran.Commit();
}
catch (Exception )
{
tran.Rollback();
string sql = string.Empty;
foreach (var temp in SQLList)
{
sql += temp;
}
}
command.Dispose();
}
}