/// <summary>
/// Get all national parks from DB
/// </summary>
/// <returns>List of national parks from DB</returns>
public IList <NationalParkItem> GetAllNationalParks()
{
List <NationalParkItem> parks = new List <NationalParkItem>();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM park", connection);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
NationalParkItem park = GetNationalParkFromReader(reader);
parks.Add(park);
}
}
return(parks);
}
/// <summary>
/// Select a National Park by Park Code.
/// </summary>
/// <param name="id"></param>
/// <returns>NationalParkItem filtered by Park Code</returns>
public NationalParkItem GetNationalPark(string id)
{
NationalParkItem nationalPark = new NationalParkItem();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("SELECT * FROM park WHERE parkCode = @parkCode;", connection);
command.Parameters.AddWithValue("@parkCode", id);
SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
nationalPark = GetNationalParkFromReader(reader);
}
}
return(nationalPark);
}
/// <summary>
/// Get park object from sql data reader
/// </summary>
/// <param name="reader"></param>
/// <returns>Park object built from DB</returns>
private NationalParkItem GetNationalParkFromReader(SqlDataReader reader)
{
NationalParkItem park = new NationalParkItem();
park.ParkCode = Convert.ToString(reader["parkCode"]);
park.Name = Convert.ToString(reader["parkName"]);
park.State = Convert.ToString(reader["state"]);
park.Acreage = Convert.ToInt32(reader["acreage"]);
park.ElevationInFt = Convert.ToInt32(reader["elevationInFeet"]);
park.MilesOfTrail = Convert.ToDouble(reader["milesOfTrail"]);
park.NumberOfCampsites = Convert.ToInt16(reader["numberOfCampsites"]);
park.Climate = Convert.ToString(reader["climate"]);
park.YearFounded = Convert.ToInt16(reader["yearFounded"]);
park.AnnualVisitorCt = Convert.ToInt32(reader["annualVisitorCount"]);
park.InspirationalQuote = Convert.ToString(reader["inspirationalQuote"]);
park.InspirationalQuoteSrc = Convert.ToString(reader["inspirationalQuoteSource"]);
park.ParkDescription = Convert.ToString(reader["parkDescription"]);
park.EntryFee = Convert.ToInt16(reader["entryFee"]);
park.NumberAnimalSpecies = Convert.ToInt16(reader["numberOfAnimalSpecies"]);
return(park);
}
public void ParkRankings()
{
SurveyViewModel testSurveyModel = new SurveyViewModel()
{
ParkCode = "TEST",
StateOfResidence = "OH",
UserActivityLevel = "Inactive",
Email = "*****@*****.**"
};
NationalParkItem secondPark = new NationalParkItem()
{
ParkCode = "QWER",
Name = "Test park two",
State = "Garry",
Acreage = 203955,
ElevationInFt = 5302,
MilesOfTrail = 508.34,
NumberOfCampsites = 25,
Climate = "Rainforest",
YearFounded = 1928,
AnnualVisitorCt = 20890567,
InspirationalQuote = "Who likes the cherry ones?!",
InspirationalQuoteSrc = "Garry Le",
ParkDescription = "This is Garry's favorite place to be.",
EntryFee = 9,
NumberAnimalSpecies = 48
};
// must first populate DB w/national parks due to pK / fK relationships
var isParkOneAdded = _db.AddNewNationalPark(_nationalParkItem);
Assert.IsTrue(isParkOneAdded);
var isParkTwoAdded = _db.AddNewNationalPark(secondPark);
Assert.IsTrue(isParkTwoAdded);
// save new posts to database; post votes for park Codes
// 3x "TEST", 1x for "QWER"
var isPostAdded = _db.SaveNewPost(testSurveyModel);
Assert.IsTrue(isPostAdded);
testSurveyModel.Email = "*****@*****.**";
isPostAdded = _db.SaveNewPost(testSurveyModel);
Assert.IsTrue(isPostAdded);
testSurveyModel.Email = "*****@*****.**";
isPostAdded = _db.SaveNewPost(testSurveyModel);
Assert.IsTrue(isPostAdded);
testSurveyModel.ParkCode = secondPark.ParkCode;
isPostAdded = _db.SaveNewPost(testSurveyModel);
Assert.IsTrue(isPostAdded);
// pull DESC list of park rankings from DB
var listParkRankings = _db.ParkRankings();
int parkOneCount = -1;
int parkTwoCount = -1;
foreach (var surveyResult in listParkRankings)
{
if (_nationalParkItem.ParkCode.Equals(surveyResult.ParkCode))
{
parkOneCount = surveyResult.count;
}
if (secondPark.ParkCode.Equals(surveyResult.ParkCode))
{
parkTwoCount = surveyResult.count;
}
}
// Ensure both parks were found
Assert.IsTrue(parkOneCount != -1 && parkTwoCount != -1);
// ensure the 3x added _nationalParkItem is greater than 1x added secondPark
Assert.IsTrue(parkOneCount > parkTwoCount);
}
/// <summary>
/// Add a new national park to the DB
/// </summary>
/// <param name="park"></param>
/// <returns>bool result - true if success, false if failed</returns>
public bool AddNewNationalPark(NationalParkItem park)
{
bool result = false;
// Create a connection
using (SqlConnection conn = new SqlConnection(_connectionString))
{
// Open the connection
conn.Open();
// Create the command
SqlCommand cmd = new SqlCommand("INSERT INTO park (parkCode, " +
"parkName, " +
"state, " +
"acreage, " +
"elevationInFeet, " +
"milesOfTrail, " +
"numberOfCampsites, " +
"climate, " +
"yearFounded, " +
"annualVisitorCount, " +
"inspirationalQuote, " +
"inspirationalQuoteSource, " +
"parkDescription, " +
"entryFee, " +
"numberOfAnimalSpecies) " +
"VALUES (@parkCode, " +
"@parkName, " +
"@state, " +
"@acreage, " +
"@elevationInFeet, " +
"@milesOfTrail, " +
"@numberOfCampsites, " +
"@climate, " +
"@yearFounded, " +
"@annualVisitorCount, " +
"@inspirationalQuote, " +
"@inspirationalQuoteSource, " +
"@parkDescription, " +
"@entryFee, " +
"@numberOfAnimalSpecies);", conn);
cmd.Parameters.AddWithValue("@parkCode", park.ParkCode);
cmd.Parameters.AddWithValue("@parkName", park.Name);
cmd.Parameters.AddWithValue("@state", park.State);
cmd.Parameters.AddWithValue("@acreage", park.Acreage);
cmd.Parameters.AddWithValue("@elevationInFeet", park.ElevationInFt);
cmd.Parameters.AddWithValue("@milesOfTrail", park.MilesOfTrail);
cmd.Parameters.AddWithValue("@numberOfCampsites", park.NumberOfCampsites);
cmd.Parameters.AddWithValue("@climate", park.Climate);
cmd.Parameters.AddWithValue("@yearFounded", park.YearFounded);
cmd.Parameters.AddWithValue("@annualVisitorCount", park.AnnualVisitorCt);
cmd.Parameters.AddWithValue("@inspirationalQuote", park.InspirationalQuote);
cmd.Parameters.AddWithValue("@inspirationalQuoteSource", park.InspirationalQuoteSrc);
cmd.Parameters.AddWithValue("@parkDescription", park.ParkDescription);
cmd.Parameters.AddWithValue("@entryFee", park.EntryFee);
cmd.Parameters.AddWithValue("@numberOfAnimalSpecies", park.NumberAnimalSpecies);
// Execute the command
int rowsAffected = cmd.ExecuteNonQuery();
result = rowsAffected == 1;
}
return(result);
}
