public async Task<bool> DeleteXXX(int regattaId, int id, int userId) { var result = false; using (var conn = GetMySqlConnection(regattaId)) { if (conn.State == ConnectionState.Closed) { await conn.OpenAsync(); } using (var transaction = conn.BeginTransaction()) { try { const string sqlDelClub = @"delete from 表名 where 字段1=@clubId; delete from 表名2 where 字段2=@clubId; delete from 表名3 where 字段3=@userId and clubinfo_id=@clubId;"; await conn.QueryAsync(sqlDelClub, new { clubId = id, userId = userId, }, commandType: CommandType.Text); transaction.Commit(); result = true; } catch (Exception e) { Console.WriteLine(e); transaction.Rollback(); result = false; throw; } } return result; } }
这样，用Transaction将执行代码块包起来，如果出现异常，在catch中 进行Rollback(回滚事务)，就可以保证了数据的一致性。如果是高并发场景，可能还会需要用到锁，这里暂时不做延伸讨论。
如果是返回集合，也很容易处理：
public async Task<List<实体>> GetClubsByUserId(int regattaId, int userId) { using (var conn = GetMySqlConnection(regattaId)) { if (conn.State == ConnectionState.Closed) { await conn.OpenAsync(); } const string sql = @"select b.club_id as id,c.name,c.image as ImageData,c.year,c.address,c.creator,c.description,b.contact ,b.phone,b.isvalid from 表1 a left join 表2 b on a.clubinfo_id=b.club_id left join 表3 c on b.clubbase_id=c.club_id where a.authorize_userid=@user_Id"; List<实体> clubDetailList = (await conn.QueryAsync<实体>(sql, new { user_Id = userId }, commandType: CommandType.Text)) .ToList(); return clubDetailList; } }