public async Task <List <PrimaryKey> > GetPrimaryKeysAsync(string tableName)
{
if (_tableKeyDic.ContainsKey(tableName))
{
return(_tableKeyDic[tableName].ToList());
}
var sqlConn = new MySqlConnection(_conStr);
var talbeInfo = SqlDbUtil.GetTableInfo(tableName, sqlConn);
var priKeys = SqlDbUtil.GetPrimaryKeys(talbeInfo);
_tableKeyDic[tableName] = priKeys;
return(priKeys.ToList());
}
public async Task <Result> SaveAsync(IEnumerable <TableStatus> tables)
{
var result = await Task.Run(() =>
{
var sqlConn = new MySqlConnection(_conStr);
MySqlTransaction trans = null;
var tsList = tables as IList <TableStatus> ?? tables.ToList();
try
{
sqlConn.Open();
var tuples = new List <Tuple <TableInfo, DataTable, DataTable> >();
foreach (var ts in tsList)
{
DataTable insertTable, otherTable;
if (!SqlDbUtil.GetChangedData(ts, sqlConn, out insertTable, out otherTable))
{
continue;
}
var tableInfo = SqlDbUtil.GetTableInfo(ts.TableName, sqlConn);
tuples.Add(Tuple.Create(tableInfo, insertTable, otherTable));
}
trans = sqlConn.BeginTransaction();
for (var i = 0; i < tuples.Count; i++)
{
var ti = tuples[i].Item1;
var insertTable = tuples[i].Item2;
var otherTable = tuples[i].Item3;
if (otherTable.Rows.Count > 0 || insertTable.Rows.Count > 0)
{
//采用SqlDataAdapter批量更新及删除数据
var cmd = sqlConn.CreateCommand();
cmd.CommandText = string.Format("select * from {0} where 0=1", ti.TableName);
cmd.Transaction = trans;
var sda = new MySqlDataAdapter(cmd);
if (otherTable.Rows.Count > 0)
{
//var sb1 = new MySqlCommandBuilder(sda);
var updateCmd = SqlDbUtil.GetUpdateCommand(ti, sqlConn);
var deleteCmd = SqlDbUtil.GetDeleteCommand(ti, sqlConn);
updateCmd.Transaction = trans;
deleteCmd.Transaction = trans;
sda.UpdateCommand = updateCmd;
sda.DeleteCommand = deleteCmd;
}
if (insertTable.Rows.Count > 0)
{
var insertCmd = SqlDbUtil.GetInsertCommand(ti, sqlConn);
insertCmd.Transaction = trans;
sda.InsertCommand = insertCmd;
}
try
{
if (otherTable.Rows.Count > 0)
{
sda.Update(otherTable);
}
if (insertTable.Rows.Count > 0)
{
sda.Update(insertTable);
}
}
catch (DBConcurrencyException)
{
}
}
}
trans.Commit();
return(Result.Ok);
}
catch (Exception e)
{
trans.Rollback();
return(new Result(e));
}
finally
{
sqlConn.Close();
if (trans != null)
{
trans.Dispose();
}
}
});
return(result);
}