public PanoramicModel GetPanoramicById(int id)
{
PanoramicModel panoramic = new PanoramicModel();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT * FROM panoramic_images WHERE panoramic_image_id = @id;", conn);
cmd.Parameters.AddWithValue("@id", id);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
panoramic = MapRowToPanoramic(reader);
}
else
{
panoramic = null;
}
}
}
catch (SqlException)
{
throw;
}
return(panoramic);
}
public List <PanoramicModel> GetPanoramicsByTrailId(int trailId)
{
List <PanoramicModel> panoramics = new List <PanoramicModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT * FROM panoramic_images WHERE trail_id = @trailId;", conn);
cmd.Parameters.AddWithValue("@trailId", trailId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
PanoramicModel panoramic = MapRowToPanoramic(reader);
panoramics.Add(panoramic);
}
}
}
catch (SqlException)
{
throw;
}
return(panoramics);
}
public List <PanoramicModel> GetAllPanoramics()
{
List <PanoramicModel> panoramics = new List <PanoramicModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT * FROM panoramic_images;", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
PanoramicModel panoramic = MapRowToPanoramic(reader);
panoramics.Add(panoramic);
}
return(panoramics);
}
}
catch (SqlException)
{
throw;
}
}
public List <PanoramicModel> GetVisitedPanoramicsByUsername(string userName)
{
List <PanoramicModel> panoramics = new List <PanoramicModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT pi.*
FROM panoramic_images pi
JOIN user_panoramic_associative upa
ON upa.panoramic_image_id = pi.panoramic_image_id
JOIN Users u
ON u.UserId = upa.UserId
WHERE u.UserName = @userName;", conn);
cmd.Parameters.AddWithValue("@userName", userName);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
PanoramicModel panoramic = MapRowToPanoramic(reader);
panoramics.Add(panoramic);
}
}
}
catch (SqlException)
{
throw;
}
return(panoramics);
}
public List <PanoramicModel> GetPanoramicsByTrailName(string name)
{
List <PanoramicModel> panoramics = new List <PanoramicModel>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand(@"SELECT p.*
FROM panoramic_images p
INNER JOIN trails
ON p.trail_id = trails.trail_id
WHERE trails.trail_name = @trailName;", conn);
cmd.Parameters.AddWithValue("@trailName", name);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
PanoramicModel panoramic = MapRowToPanoramic(reader);
panoramics.Add(panoramic);
}
}
}
catch (SqlException)
{
throw;
}
return(panoramics);
}
public IHttpActionResult GetPanoramicById(int panoramicId)
{
PanoramicModel panoramic = panoramicDAL.GetPanoramicById(panoramicId);
panoramic.BackgroundSoundClips = panoramicDAL.GetBackgroundSoundClipsByPanoramicId(panoramicId);
return(Ok(panoramic));
}
private static int InsertFakePanoramic(PanoramicModel panoramicImage, int trailId)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO panoramic_images " +
" (trail_id, image_address, image_latitude, image_longitude, is_trail_head) " +
"VALUES " +
" (@trailId, @imageAddress, @latitude, @longitude, @isTrailHead)", conn);
cmd.Parameters.AddWithValue("@trailId", trailId);
cmd.Parameters.AddWithValue("@imageAddress", panoramicImage.ImageAddress);
cmd.Parameters.AddWithValue("@latitude", panoramicImage.Latitude);
cmd.Parameters.AddWithValue("@longitude", panoramicImage.Longitude);
cmd.Parameters.AddWithValue("@isTrailHead", true);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("SELECT panoramic_image_id FROM panoramic_images " +
"WHERE panoramic_images.image_address = @imageAddress " +
"AND panoramic_images.image_latitude = @latitude " +
"AND panoramic_images.image_longitude = @longitude " +
"AND panoramic_images.is_trail_head = @isTrailHead ", conn);
cmd.Parameters.AddWithValue("@imageAddress", panoramicImage.ImageAddress);
cmd.Parameters.AddWithValue("@latitude", panoramicImage.Latitude);
cmd.Parameters.AddWithValue("@longitude", panoramicImage.Longitude);
cmd.Parameters.AddWithValue("@isTrailHead", true);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return(result);
}
}
public void GetPanoramicById()
{
using (TransactionScope transaction = new TransactionScope())
{
int newParkId = ParkSqlDALTests.InsertFakePark(park);
int newTrailId = TrailSqlDALTests.InsertFakeTrail(trail, newParkId);
int newPanoramicId = PanoramicSqlDALTests.InsertFakePanoramic(panoramicImage, newTrailId);
PanoramicSqlDAL testClass = new PanoramicSqlDAL(connectionString);
PanoramicModel newPanoramicImages = testClass.GetPanoramicById(newPanoramicId);
Assert.AreEqual(newPanoramicImages.PanoramicId, newPanoramicId);
}
}
public void Initialize()
{
park = new ParkModel()
{
Name = "testPark",
Description = "testDescription",
Latitude = 41,
Longitude = 100,
Zoom = 200
};
trail = new TrailModel()
{
Name = "testTrail",
Description = "testTrailDescription"
};
panoramicImage = new PanoramicModel()
{
ImageAddress = "address",
Latitude = 100,
Longitude = 42,
};
}
