private void LogImageHashToDatabase(ListingImageDetails imageDetails) { try { var originalImageUrl = Regex.Replace(imageDetails.ImageUrl, "(.*)(s)(.jpg)$", "$1O$3"); imageDetails.ImageUrl = originalImageUrl; imageDetails.ImageHash = GetHashFromUrl(originalImageUrl); //_dataDestRepo.UpsertListingImageDetails(3, "[MasterPropertyRecord].[dbo].[zzz_hackathon_0115_image_hashes]", imageDetails); _dataDestRepo.UpsertListingImageDetails(3, "[MasterPropertyRecord].[dbo].[zzz_hackathon_0115_image_hashes_try2]", imageDetails); } catch (Exception ex) { Console.WriteLine("Error: failed to get image hash for listing id {0}. Error message: {1}", imageDetails.ListingId, ex.Message); } }
public void UpsertListingImageDetails(int partitionId, string tableName, ListingImageDetails image) { const string updateSqlTemplate = @"BEGIN TRAN UPDATE {0} SET [mpr_id] = @mpr_id, [master_listing_id] = @master_listing_id, [listing_id] = @listing_id, [image_url] = @image_url, [image_hash] = @image_hash, [address_line] = @address_line, [street_number] = @street_number, [street_direction] = @street_direction, [street_name] = @street_name, [street_suffix] = @street_suffix, [street_post_direction] = @street_post_direction, [unit_value] = @unit_value, [city] = @city, [state] = @state, [zip] = @zip WHERE listing_id = @listing_id AND state = @state AND image_url = @image_url IF @@ROWCOUNT = 0 BEGIN INSERT INTO {0} ( [mpr_id] , [master_listing_id], [listing_id] , [image_url] , [image_hash] , [address_line] , [street_number] , [street_direction] , [street_name] , [street_suffix] , [street_post_direction] , [unit_value] , [city] , [state] , [zip] ) VALUES ( @mpr_id, @master_listing_id, @listing_id, @image_url, @image_hash, @address_line, @street_number, @street_direction, @street_name, @street_suffix, @street_post_direction, @unit_value, @city, @state, @zip ) END COMMIT TRAN"; try { string connectionString = _mprRedirect.GetConnectionStringByPartitionId(partitionId, "MasterPropertyRecord"); using (var dbConnection = new SqlConnection(connectionString)) { dbConnection.Open(); string sql = string.Format(updateSqlTemplate, tableName); SqlCommand cmd = new SqlCommand(sql, dbConnection); cmd.Parameters.AddWithValue("mpr_id", image.MprId); cmd.Parameters.AddWithValue("master_listing_id", image.MlId); cmd.Parameters.AddWithValue("listing_id", image.ListingId); cmd.Parameters.AddWithValue("image_url", ((object)image.ImageUrl) ?? DBNull.Value); cmd.Parameters.AddWithValue("image_hash", (decimal)image.ImageHash); cmd.Parameters.AddWithValue("address_line", ((object)image.AddressLine) ?? DBNull.Value); cmd.Parameters.AddWithValue("street_number", ((object)image.StreetNumber) ?? DBNull.Value); cmd.Parameters.AddWithValue("street_direction", ((object)image.StreetDirection) ?? DBNull.Value); cmd.Parameters.AddWithValue("street_name", ((object)image.StreetName) ?? DBNull.Value); cmd.Parameters.AddWithValue("street_suffix", ((object)image.StreetSuffix) ?? DBNull.Value); cmd.Parameters.AddWithValue("street_post_direction", ((object)image.StreetPostDirection) ?? DBNull.Value); cmd.Parameters.AddWithValue("unit_value", ((object)image.UnitValue) ?? DBNull.Value); cmd.Parameters.AddWithValue("city", ((object)image.City) ?? DBNull.Value); cmd.Parameters.AddWithValue("state", ((object)image.State) ?? DBNull.Value); cmd.Parameters.AddWithValue("zip", ((object)image.Zip) ?? DBNull.Value); cmd.ExecuteNonQuery(); } } catch (Exception ex) { throw; } }