public async Task <int> InsertAsync(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, string tableName, string columnList, string parameterList, IEnumerable <PropertyInfo> keyProperties, object entityToInsert) { var cmd = $"INSERT INTO {tableName} ({columnList}) VALUES ({parameterList})"; await connection.ExecuteAsync(cmd, entityToInsert, transaction, commandTimeout).ConfigureAwait(false); var r = await connection.QueryAsync <dynamic>("SELECT LAST_INSERT_ID() id", transaction : transaction, commandTimeout : commandTimeout).ConfigureAwait(false); var id = r.First().id; if (id == null) { return(0); } var pi = keyProperties as PropertyInfo[] ?? keyProperties.ToArray(); if (!pi.Any()) { return(Convert.ToInt32(id)); } var idp = pi.First(); idp.SetValue(entityToInsert, Convert.ChangeType(id, idp.PropertyType), null); return(Convert.ToInt32(id)); }
public async Task <int> InsertAsync(IDbConnection connection, IDbTransaction transaction, int?commandTimeout, string tableName, string columnList, string parameterList, IEnumerable <PropertyInfo> keyProperties, object entityToInsert) { var sb = new StringBuilder(); sb.AppendFormat("INSERT INTO {0} ({1}) VALUES ({2})", tableName, columnList, parameterList); // If no primary key then safe to assume a join table with not too much data to return var propertyInfos = keyProperties as PropertyInfo[] ?? keyProperties.ToArray(); if (!propertyInfos.Any()) { sb.Append(" RETURNING *"); } else { sb.Append(" RETURNING "); bool first = true; foreach (var property in propertyInfos) { if (!first) { sb.Append(", "); } first = false; sb.Append(property.Name); } } var results = await connection.QueryAsync(sb.ToString(), entityToInsert, transaction, commandTimeout).ConfigureAwait(false); // Return the key by assinging the corresponding property in the object - by product is that it supports compound primary keys var id = 0; var values = results.First(); foreach (var p in propertyInfos) { var value = values[p.Name.ToLower()]; p.SetValue(entityToInsert, value, null); if (id == 0) { id = Convert.ToInt32(value); } } return(id); }
private async Task UpdateUserSocialsAsync(DbConnection context, DbTransaction transaction, User user) { var userSocials = await context.QueryAsync<UserSocial>("SELECT * FROM UserSocials WHERE Username = @Username", new { user.Username }, transaction); await context.ExecuteAsync("DELETE FROM UserSocials WHERE Username = @Username and SocialId = @SocialId;", userSocials.Where( u => user.Socials.All(s => u.SocialId != s.SocialId)), transaction); await context.ExecuteAsync( "UPDATE UserSocials set Site = @Site, Url = @Url WHERE Username = @Username and SocialId = @SocialId;", user.Socials, transaction); await context.ExecuteAsync("INSERT UserSocials(Username,Site,Url) VALUES(@Username,@Site,@Url);", user.Socials.Where( s => userSocials.All(u => s.Username != u.Username && u.SocialId != s.SocialId)).Select(s => new { user.Username, s.Site, s.Url }), transaction); }
private async Task UpdateBreweryMembersAsync(DbConnection context, DbTransaction transaction, Brewery brewery) { var breweryMembers = await context.QueryAsync<BreweryMember>( "SELECT * FROM BreweryMembers WHERE BreweryId = @BreweryId", new { brewery.BreweryId }, transaction); await context.ExecuteAsync("DELETE FROM BreweryMembers WHERE BreweryId = @BreweryId and MemberUsername = @MemberUsername;", breweryMembers.Where(bm => brewery.Members.All(m => bm.MemberUsername != m.MemberUsername)).Select(bm => new { brewery.BreweryId, bm.MemberUsername }), transaction); await context.ExecuteAsync( "UPDATE BreweryMembers set Role = @Role, Confirmed = @Confirmed WHERE BreweryId = @BreweryId and MemberUsername = @MemberUsername;", brewery.Members.Where(m => breweryMembers.Any(bm => m.MemberUsername == bm.MemberUsername)), transaction); await context.ExecuteAsync("INSERT BreweryMembers(BreweryId,MemberUsername,Role,Confirmed) VALUES(@BreweryId,@MemberUsername,@Role,@Confirmed);", brewery.Members.Where(m => breweryMembers.All(bm => m.MemberUsername != bm.MemberUsername)).Select(bm => new { brewery.BreweryId, bm.MemberUsername,bm.Role,bm.Confirmed }), transaction); }
private async Task UpdateBrewerySocialsAsync(DbConnection context, DbTransaction transaction, Brewery brewery) { var brewerySocials = await context.QueryAsync<BrewerySocial>("SELECT * FROM BrewerySocials WHERE BreweryId = @BreweryId", new { brewery.BreweryId }, transaction); await context.ExecuteAsync("DELETE FROM BrewerySocials WHERE BreweryId = @BreweryId and SocialId = @SocialId;", brewerySocials.Where( u => brewery.Socials.All(s => u.SocialId != s.SocialId)), transaction); await context.ExecuteAsync( "UPDATE BrewerySocials set Site = @Site, Url = @Url WHERE BreweryId = @BreweryId and SocialId = @SocialId;", brewery.Socials, transaction); await context.ExecuteAsync("INSERT BrewerySocials(BreweryId,Site,Url) VALUES(@BreweryId,@Site,@Url);", brewery.Socials.Where( s => brewerySocials.All(u => u.SocialId != s.SocialId)).Select(s => new { brewery.BreweryId, s.Site, s.Url }), transaction); }
public async Task<List<Tuple<string, IPAddress>>> GetNodeIPMap(DbConnection conn) { var ipList = await conn.QueryAsync<string, string, Tuple<string, string>>( @"Select Cast(NodeID as varchar(50)) NodeID, IPAddress From NodeIPAddresses", commandTimeout: QueryTimeoutMs, map: Tuple.Create, splitOn: "IPAddress"); var result = new List<Tuple<string, IPAddress>>(); foreach (var entry in ipList) { IPAddress addr; if (!IPAddress.TryParse(entry.Item2, out addr)) continue; result.Add(Tuple.Create(entry.Item1, addr)); } return result; }
private async Task UpdateAromaWheelAsync(DbConnection context, SqlTransaction transaction, Hop hop) { var aromaWheels = (await context.QueryAsync<HopFlavour>(@"SELECT * FROM HopAromaWheels WHERE HopId = @HopId", new { hop.HopId }, transaction)).ToList(); var toDelete = aromaWheels.Where(h => hop.AromaWheel.All(f => f.FlavourId != h.FlavourId)); await context.ExecuteAsync("DELETE FROM HopAromaWheels WHERE HopId = @HopId and FlavourId = @FlavourId;", toDelete.Select(h => new { h.HopId, h.FlavourId }), transaction); var toAdd = hop.AromaWheel.Where(h => aromaWheels.All(f => f.FlavourId != h.FlavourId)); await context.ExecuteAsync(@"INSERT HopAromaWheels(FlavourId, HopId) VALUES(@FlavourId,@HopId);", toAdd.Select(h => new { h.HopId, h.FlavourId }), transaction); }
private async Task UpdateHopSubstituteAsync(DbConnection context, SqlTransaction transaction, Hop hop) { var hopSubstitutes = (await context.QueryAsync<Substitute>(@"SELECT * FROM Substitute WHERE HopId = @HopId", new { hop.HopId }, transaction)).ToList(); var toDelete = hopSubstitutes.Where(h => hop.Substituts.All(s => s.HopId != h.HopId && h.SubstituteId != s.HopId)); await context.ExecuteAsync("DELETE FROM Substitute WHERE HopId = @HopId", toDelete, transaction); var toAdd = hop.Substituts.Where(h => hopSubstitutes.All(s => s.HopId != h.HopId && h.HopId != s.SubstituteId)).Select(c => new Substitute { HopId = hop.HopId, SubstituteId = c.HopId }); await context.ExecuteAsync(@"INSERT Substitute(SubstituteId, HopId) VALUES(@SubstituteId,@HopId);", toAdd, transaction); }