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);
}
}
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;
}
}
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;
}
}
