public async Task UpdateReverseDns(List <IpAddressDetailsUpdateDto> entriesToUpdate)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
string command = "UPDATE public.ip_address_details SET reverse_dns_data = @reverse_dns_data, reverse_dns_updated = @reverse_dns_updated WHERE ip_address = @ip_address AND date = @date";
DateTime now = DateTime.UtcNow;
foreach (IpAddressDetailsUpdateDto entryToUpdate in entriesToUpdate)
{
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter($"@date", NpgsqlDbType.Date)
{
Value = new NpgsqlDate(entryToUpdate.Date)
});
parameters.Add(new NpgsqlParameter($"@ip_address", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(entryToUpdate.IpAddress)
});
parameters.Add(new NpgsqlParameter($"@reverse_dns_data", NpgsqlDbType.Json)
{
Value = JsonConvert.SerializeObject(entryToUpdate.ReverseDnsResponses)
});
parameters.Add(new NpgsqlParameter($"@reverse_dns_updated", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(now)
});
await PostgreSqlHelper.ExecuteNonQueryAsync(connectionString, command, parameters.ToArray());
}
}
public async Task UpdateAsnInfo(List <AsInfo> requests)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
string command = "UPDATE public.ip_address_details SET as_number = @as_number, description = @description, country_code = @country_code, asn_updated = @asn_updated WHERE ip_address = @ip_address";
DateTime now = DateTime.UtcNow;
foreach (var asInfo in requests)
{
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
parameters.Add(new NpgsqlParameter($"@ip_address", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(asInfo.IpAddress)
});
parameters.Add(new NpgsqlParameter($"@as_number", NpgsqlDbType.Numeric)
{
Value = asInfo.AsNumber
});
parameters.Add(new NpgsqlParameter($"@description", NpgsqlDbType.Varchar, 250)
{
Value = asInfo.Description
});
parameters.Add(new NpgsqlParameter($"@country_code", NpgsqlDbType.Char, 2)
{
Value = asInfo.CountryCode
});
parameters.Add(new NpgsqlParameter($"@asn_updated", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(now)
});
await PostgreSqlHelper.ExecuteNonQueryAsync(connectionString, command, parameters.ToArray());
}
}
public async Task SaveIpAddressDetails(List <IpAddressDetails> ipAddressDetailsResponse)
{
string connectionString = await _connectionInfo.GetConnectionStringAsync();
List <string> values = new List <string>();
List <NpgsqlParameter> parameters = new List <NpgsqlParameter>();
for (int i = 0; i < ipAddressDetailsResponse.Count; i++)
{
parameters.Add(new NpgsqlParameter($"@date_{i}", NpgsqlDbType.Date)
{
Value = new NpgsqlDate(ipAddressDetailsResponse[i].Date)
});
parameters.Add(new NpgsqlParameter($"@ip_address_{i}", NpgsqlDbType.Inet)
{
Value = IPAddress.Parse(ipAddressDetailsResponse[i].IpAddress)
});
parameters.Add(ipAddressDetailsResponse[i].AsNumber.HasValue
? new NpgsqlParameter($"@as_number_{i}", NpgsqlDbType.Integer)
{
Value = ipAddressDetailsResponse[i].AsNumber.Value
}
: new NpgsqlParameter($"@as_number_{i}", NpgsqlDbType.Integer)
{
Value = DBNull.Value
});
parameters.Add(!string.IsNullOrEmpty(ipAddressDetailsResponse[i].Description)
? new NpgsqlParameter($"@description_{i}", NpgsqlDbType.Varchar, 250)
{
Value = ipAddressDetailsResponse[i].Description
}
: new NpgsqlParameter($"@description_{i}", NpgsqlDbType.Varchar)
{
Value = DBNull.Value
});
parameters.Add(!string.IsNullOrEmpty(ipAddressDetailsResponse[i].CountryCode)
? new NpgsqlParameter($"@country_code_{i}", NpgsqlDbType.Char, 2)
{
Value = ipAddressDetailsResponse[i].CountryCode
}
: new NpgsqlParameter($"@country_code_{i}", NpgsqlDbType.Char, 2)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].ReverseDnsResponses != null && ipAddressDetailsResponse[i].ReverseDnsResponses.Count > 0
? new NpgsqlParameter($"@reverse_dns_data_{i}", NpgsqlDbType.Json)
{
Value = JsonConvert.SerializeObject(ipAddressDetailsResponse[i].ReverseDnsResponses)
}
: new NpgsqlParameter($"@reverse_dns_data_{i}", NpgsqlDbType.Json)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].BlockListOccurrences != null && ipAddressDetailsResponse[i].BlockListOccurrences.Count > 0
? new NpgsqlParameter($"@blocklist_data_{i}", NpgsqlDbType.Json)
{
Value = JsonConvert.SerializeObject(ipAddressDetailsResponse[i].BlockListOccurrences)
}
: new NpgsqlParameter($"@blocklist_data_{i}", NpgsqlDbType.Json)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].AsnLookupTimestamp.HasValue
? new NpgsqlParameter($"@asn_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(ipAddressDetailsResponse[i].AsnLookupTimestamp.Value)
}
: new NpgsqlParameter($"@asn_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].BlocklistLookupTimestamp.HasValue
? new NpgsqlParameter($"@blocklist_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(ipAddressDetailsResponse[i].BlocklistLookupTimestamp.Value)
}
: new NpgsqlParameter($"@blocklist_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = DBNull.Value
});
parameters.Add(ipAddressDetailsResponse[i].ReverseDnsLookupTimestamp.HasValue
? new NpgsqlParameter($"@reverse_dns_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = new NpgsqlDateTime(ipAddressDetailsResponse[i].ReverseDnsLookupTimestamp.Value)
}
: new NpgsqlParameter($"@reverse_dns_updated_{i}", NpgsqlDbType.Timestamp)
{
Value = DBNull.Value
});
values.Add($"(@date_{i}, @ip_address_{i}, @as_number_{i}, @description_{i}, @country_code_{i}, @reverse_dns_data_{i}, @blocklist_data_{i}, @asn_updated_{i}, @blocklist_updated_{i}, @reverse_dns_updated_{i})");
}
string command = string.Format(IpAddressIntelligenceDaoResources.InsertIpAddressDetails, string.Join(",", values));
await PostgreSqlHelper.ExecuteNonQueryAsync(connectionString, command, parameters.ToArray());
}