public void SetBreweryOwner(AddBreweryModel brewery, string userName)
{
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT Users.UserId FROM Users
WHERE userName = @userName", conn);
cmd.Parameters.AddWithValue("@userName", userName);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
brewery.UserId = Convert.ToString(reader["UserId"]);
}
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
}
/// <summary>
/// Helper Method that uses Google GeoCode API that gets and sets lat and long from an address
/// - JV
/// </summary>
/// <param name="brewery">the brewery model being added</param>
public void SetBreweryCoords(AddBreweryModel brewery)
{
string address = brewery.BreweryAddress + ", " + brewery.BreweryCity + ", " + brewery.BreweryDistrict + "," + brewery.BreweryPostalCode;
var geoCoder = new GoogleLocationService(locationServiceApiKey);
var breweryLocation = geoCoder.GetLatLongFromAddress(address);
brewery.BreweryLatitude = breweryLocation.Latitude;
brewery.BreweryLongitude = breweryLocation.Longitude;
}
public void AddBrewery_Test()
{
// Arrange
BreweryDAL breweryDAL = new BreweryDAL(connectionString);
string name;
using (TransactionScope transact = new TransactionScope())
{
AddBreweryModel brewery = new AddBreweryModel
{
BreweryName = "Jimmy's Place",
BreweryAddress = "776 County Road 30A",
BreweryCity = "Ashland",
BreweryCountry = "USA",
BreweryDistrict = "OH",
BreweryPostalCode = "44805",
History = "Making Home-Brews since 2016",
YearFounded = 2016,
Email = "*****@*****.**",
Phone = "419-631-6637",
HoursOfOperation = "From Dusk til' Dawn",
BreweryProfileImg = "none",
BreweryBackgroundImg = "none",
BreweryHeaderImage = "none"
};
breweryDAL.SetBreweryCoords(brewery);
breweryDAL.AddBrewery(brewery);
name = brewery.BreweryName;
// Act
List <string> results = breweryDAL.GetAllBreweryNames();
// Assert
Assert.IsTrue(results.Contains(name));
}
}
public void AddBrewery(AddBreweryModel brewery)
{
SetBreweryCoords(brewery);
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"INSERT INTO Brewery (UserId, BreweryName, BreweryAddress, BreweryCity, BreweryDistrict, BreweryCountry, BreweryPostalCode, History, YearFounded, HoursOfOperation, BreweryProfileImg, BreweryBackgroundImg, BreweryHeaderImg, Email, Phone, BreweryLatitude, BreweryLongitude)
VALUES (@userId, @breweryName, @breweryAddress, @breweryCity, @breweryDistrict, @breweryCountry, @breweryPostalCode, @history, @yearFounded, @hoursOfOperation, @breweryProfileImg, @breweryBackgroundImg, @breweryHeaderImg, @email, @phone, @latitude, @longitude)", conn);
//MERGE INTO dbo.Brewery WITH(HOLDLOCK) AS target
//USING(SELECT * FROM dbo.Brewery WHERE Brewery.BreweryId = 10) AS source
// ON target.BreweryId = source.BreweryId
//WHEN MATCHED THEN
// UPDATE SET target.BreweryName = 'Hi i work'
//WHEN NOT MATCHED BY TARGET THEN
// INSERT(BreweryName)
// VALUES('this is when it doesnt match');
//--Update the row if it exists.
// UPDATE Brewery
//SET BreweryName = 'timmy'
//WHERE BreweryId = 16
//-- Insert the row if the UPDATE statement failed.
//IF(@@ROWCOUNT = 0)
//BEGIN
// INSERT INTO Brewery(BreweryName, BreweryAddress)
// VALUES('not timmy', '111 E Balls St')
// END
//SqlCommand cmd = new SqlCommand(@"UPDATE Brewery
// SET target.UserId = @userId
// SET target.BreweryName = @breweryName
// SET target.BreweryAddress = @breweryAddress
// SET target.BreweryCity = @breweryCity
// SET target.BreweryDistrict = @breweryDistrict
// SET target.BreweryCountry = @breweryCountry
// SET target.BreweryPostalCode = @breweryPostalCode
// SET target.History = @history
// SET target.YearFounded = @yearFounded
// SET target.HoursOfOperation, @hoursOfOperation
// SET target.BreweryProfileImg = @breweryProfileImg
// SET target.BreweryBackgroundImg = @breweryBackgroundImg
// SET target.BreweryHeaderImg = @breweryHeaderImage
// SET target.Email = @email
// SET target.Phone = @phone
// SET target.Latitude = @latitude
// SET target.Longitude = @longitude
// WHERE Brewery.BreweryName = @breweryName
// @IF(@@ROWCOUNT = 0)
// INSERT INTO Brewery (UserId, BreweryName, BreweryAddress, BreweryCity, BreweryDistrict, BreweryCountry, BreweryPostalCode, History, YearFounded, HoursOfOperation, BreweryProfileImg, BreweryBackgroundImg, BreweryHeaderImg, Email, Phone, BreweryLatitude, BreweryLongitude)
// VALUES (@userId, @breweryName, @breweryAddress, @breweryCity, @breweryDistrict, @breweryCountry, @breweryPostalCode, @history, @yearFounded, @hoursOfOperation, @breweryProfileImg, @breweryBackgroundImg, @breweryHeaderImg, @email, @phone, @latitude, @longitude)", conn);
//cmd.Parameters.AddWithValue("@userId", brewery.UserId);
//cmd.Parameters.AddWithValue("@breweryName", brewery.BreweryName);
//cmd.Parameters.AddWithValue("@breweryAddress", brewery.BreweryAddress);
//cmd.Parameters.AddWithValue("@breweryCity", brewery.BreweryCity);
//cmd.Parameters.AddWithValue("@breweryDistrict", brewery.BreweryDistrict);
//cmd.Parameters.AddWithValue("@breweryCountry", brewery.BreweryCountry);
//cmd.Parameters.AddWithValue("@breweryPostalCode", brewery.BreweryPostalCode);
//cmd.Parameters.AddWithValue("@history", brewery.History);
//cmd.Parameters.AddWithValue("@yearFounded", brewery.YearFounded);
//cmd.Parameters.AddWithValue("@hoursOfOperation", brewery.HoursOfOperation);
//cmd.Parameters.AddWithValue("@breweryProfileImg", brewery.BreweryProfileImg);
//cmd.Parameters.AddWithValue("@breweryBackgroundImg", brewery.BreweryBackgroundImg);
//cmd.Parameters.AddWithValue("@breweryHeaderImg", brewery.BreweryHeaderImage);
//cmd.Parameters.AddWithValue("@email", brewery.Email);
//cmd.Parameters.AddWithValue("@phone", brewery.Phone);
//cmd.Parameters.AddWithValue("@latitude", brewery.BreweryLatitude);
//cmd.Parameters.AddWithValue("@longitude", brewery.BreweryLongitude);
cmd.ExecuteNonQuery();
}
}
catch (SqlException ex)
{
Console.WriteLine(ex);
}
}
