/// method to add a new great person
public void Insert(GreatPeople greatPeople)
{
string connString = GetConnectionString();
// build out SQL command
var sb = new StringBuilder("INSERT INTO GreatPeoples");
sb.Append(" ([ID],[Name],[KnownFor], [Occupation], [NetWorth], [BirthYear])");
sb.Append(" Values (");
sb.Append("'").Append(greatPeople.ID).Append("',");
sb.Append("'").Append(greatPeople.Name).Append("',");
sb.Append("'").Append(greatPeople.KnownFor).Append("',");
sb.Append("'").Append(greatPeople.Occupation).Append("',");
sb.Append("'").Append(greatPeople.NetWorth).Append("',");
sb.Append("'").Append(greatPeople.BirthYear).Append("')");
//sb.Append("'").Append(greatPeople.Description).Append ("')");
string sqlCommandString = sb.ToString();
using (SqlConnection sqlConn = new SqlConnection(connString))
using (SqlDataAdapter sqlAdapter = new SqlDataAdapter())
{
try
{
sqlConn.Open();
sqlAdapter.InsertCommand = new SqlCommand(sqlCommandString, sqlConn);
sqlAdapter.InsertCommand.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
Console.WriteLine("SQL Exception: {0}", sqlEx.Message);
Console.WriteLine(sqlCommandString);
}
}
}
/// <summary>
/// Update Great Person
/// </summary>
/// <param name="greatPeople">ski run object</param>
public void Update(GreatPeople greatPeople)
{
string connString = GetConnectionString();
// build out SQL command
var sb = new StringBuilder("UPDATE GreatPeoples SET ");
sb.Append("Name = '").Append(greatPeople.Name).Append("', ");
sb.Append("KnownFor = '").Append(greatPeople.KnownFor).Append("', ");
sb.Append("Occupation = '").Append(greatPeople.Occupation).Append("', ");
sb.Append("NetWorth = '").Append(greatPeople.NetWorth).Append("', ");
sb.Append("BirthYear = ").Append(greatPeople.BirthYear).Append(" ");
//sb.Append("Description = ").Append(greatPeople.Description).Append(" ");
sb.Append("WHERE ");
sb.Append("ID = ").Append(greatPeople.ID);
string sqlCommandString = sb.ToString();
using (SqlConnection sqlConn = new SqlConnection(connString))
using (SqlDataAdapter sqlAdapter = new SqlDataAdapter())
{
try
{
sqlConn.Open();
sqlAdapter.UpdateCommand = new SqlCommand(sqlCommandString, sqlConn);
sqlAdapter.UpdateCommand.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
Console.WriteLine("SQL Exception: {0}", sqlEx.Message);
Console.WriteLine(sqlCommandString);
}
}
}
private static void AddGreatPerson()
{
GreatPeopleRepositorySQL greatPersonRepository = new GreatPeopleRepositorySQL();
GreatPeople greatPerson = new GreatPeople();
greatPerson = ConsoleView.AddGreatPerson();
using (greatPersonRepository)
{
greatPersonRepository.Insert(greatPerson);
}
//ConsoleView.DisplayContinuePromptInvisible();
}
/// <summary>
/// method to add a great persons info
/// </summary>
public static GreatPeople AddGreatPerson()
{
GreatPeople greatPeople = new GreatPeople();
Console.Clear();
Console.ResetColor();
Console.ForegroundColor = System.Drawing.Color.CadetBlue;
Console.WriteLine("");;
Console.WriteLine(" Add A Great Person");
Console.WriteLine();
Console.WriteLine("************************************************************************************************************************");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
Console.Write(" Enter the Great Person ID: ");
Console.ForegroundColor = System.Drawing.Color.DeepPink;
greatPeople.ID = ConsoleUtil.ValidateIntegerResponse("Please enter the Great Person ID: ", Console.ReadLine());
Console.WriteLine("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
Console.Write(" Enter the Great Persons name: ");
Console.ForegroundColor = System.Drawing.Color.DeepPink;
greatPeople.Name = Console.ReadLine();
Console.WriteLine("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
Console.Write(" Enter what they are known for: ");
Console.ForegroundColor = System.Drawing.Color.DeepPink;
greatPeople.KnownFor = Console.ReadLine();
Console.WriteLine("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
Console.Write(" Enter the occupation: ");
Console.ForegroundColor = System.Drawing.Color.DeepPink;
greatPeople.Occupation = Console.ReadLine();
Console.WriteLine("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
Console.Write(" Enter the cause of death: ");
Console.ForegroundColor = System.Drawing.Color.DeepPink;
greatPeople.NetWorth = Console.ReadLine();
Console.WriteLine("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
Console.Write(" Enter the year of birth: ");
Console.ForegroundColor = System.Drawing.Color.DeepPink;
greatPeople.BirthYear = Console.ReadLine();
Console.WriteLine("");
//Console.ForegroundColor = System.Drawing.Color.DimGray; //Description doesnt work atm
//Console.WriteLine(" Enter a description: ");
//greatPeople.Description = Console.ReadLine();
Console.WriteLine("");
return(greatPeople);
}
private static void UpdateGreatPerson()
{
GreatPeopleRepositorySQL greatPeopleRepository = new GreatPeopleRepositorySQL();
List <GreatPeople> greatPerson = greatPeopleRepository.SelectAll();
GreatPeople greatPeople = new GreatPeople();
int greatPersonID;
using (greatPeopleRepository)
{
greatPerson = greatPeopleRepository.SelectAll();
greatPersonID = ConsoleView.GetGreatPersonID(greatPerson);
greatPeople = greatPeopleRepository.SelectById(greatPersonID);
greatPeople = ConsoleView.UpdateGreatPerson(greatPeople);
greatPeopleRepository.Update(greatPeople);
}
}
private static void DisplayGreatPersonDetail()
{
GreatPeopleRepositorySQL greatPeopleRepository = new GreatPeopleRepositorySQL();
List <GreatPeople> greatPeople;
GreatPeople greatPerson = new GreatPeople();
int greatPersonID;
using (greatPeopleRepository)
{
greatPeople = greatPeopleRepository.SelectAll();
greatPersonID = ConsoleView.GetGreatPersonID(greatPeople);
greatPerson = greatPeopleRepository.SelectById(greatPersonID);
}
ConsoleView.DisplayGreatPerson(greatPerson);
ConsoleView.DisplayContinuePromptInvisible();
}
private IEnumerable <GreatPeople> ReadAllGreatPeople()
{
IList <GreatPeople> greatPeople = new List <GreatPeople>();
string connString = GetConnectionString();
string sqlCommandString = "SELECT * from GreatPeoples";
using (SqlConnection sqlConn = new SqlConnection(connString))
using (SqlCommand sqlCommand = new SqlCommand(sqlCommandString, sqlConn))
{
try
{
sqlConn.Open();
using (SqlDataReader reader = sqlCommand.ExecuteReader())
{
if (reader != null)
{
while (reader.Read())
{
GreatPeople greatPerson = new GreatPeople(); //// used greatPerson instead
greatPerson.ID = Convert.ToInt32(reader["ID"]);
greatPerson.Name = reader["Name"].ToString();
greatPerson.KnownFor = reader["KnownFor"].ToString();
greatPerson.Occupation = reader["Occupation"].ToString();
greatPerson.NetWorth = reader["NetWorth"].ToString();
greatPerson.BirthYear = reader["BirthYear"].ToString();
greatPerson.Description = reader["Description"].ToString();
greatPeople.Add(greatPerson);
}
}
}
}
catch (SqlException sqlEx)
{
Console.WriteLine("SQL Exception: {0}", sqlEx.Message);
Console.WriteLine(sqlCommandString);
}
}
return(greatPeople);
}
public static void DisplayGreatPerson(GreatPeople greatPeople)
{
Console.ResetColor();
DisplayReset();
DisplayMessage("");
//Console.ForegroundColor = System.Drawing.Color.DarkSlateGray;
Console.WriteLine("ID: {0}", greatPeople.ID.ToString());
Console.ResetColor();
Console.ForegroundColor = System.Drawing.Color.Gold;
Console.WriteLine("Details on the Great {1} {0}.", greatPeople.Name.ToString(), greatPeople.Occupation.ToString(), WINDOW_WIDTH);
Console.ForegroundColor = System.Drawing.Color.Firebrick;
Console.WriteLine("~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~");
DisplayMessage("");
//DisplayMessage(String.Format("ID: {0}", greatPeople.ID.ToString()));
//Console.WriteLine();
//DisplayMessage(String.Format("Great Person: {0}", greatPeople.Name));
//DisplayMessage("");
DisplayMessage(String.Format("Name: {0}", greatPeople.Name.ToString(), Console.ForegroundColor = System.Drawing.Color.DarkTurquoise));
Console.WriteLine();
DisplayMessage(String.Format("Occupation: {0}", greatPeople.Occupation.ToString()));
Console.WriteLine();
DisplayMessage(String.Format("Known For: {0}", greatPeople.KnownFor.ToString()));
Console.WriteLine();
DisplayMessage(String.Format("Year Born: {0}", greatPeople.BirthYear.ToString()));
Console.WriteLine();
DisplayMessage(String.Format("Cause of Death: {0}", greatPeople.NetWorth.ToString()));
Console.WriteLine();
Console.WriteLine();
Console.WriteLine();
Console.ResetColor();
Console.ForegroundColor = System.Drawing.Color.SpringGreen;
DisplayMessage(String.Format("Description: {0}", greatPeople.Description));
DisplayMessage("");
}
private static void DeleteGreatPerson()
{
GreatPeopleRepositorySQL greatPeopleRepository = new GreatPeopleRepositorySQL();
List <GreatPeople> greatPeople = greatPeopleRepository.SelectAll();
GreatPeople greatPerson = new GreatPeople();
int greatPersonID;
string message;
greatPersonID = ConsoleView.GetGreatPersonID(greatPeople);
using (greatPeopleRepository)
{
greatPeopleRepository.Delete(greatPersonID);
}
ConsoleView.DisplayReset();
message = String.Format("Great Person ID: {0} had been deleted.", greatPersonID);
ConsoleView.DisplayMessage(message);
ConsoleView.DisplayContinuePromptInvisible();
}
public static GreatPeople UpdateGreatPerson(GreatPeople greatPeople)
{
string userResponse = "";
DisplayReset();
Console.ForegroundColor = System.Drawing.Color.CadetBlue;
DisplayMessage("");
Console.WriteLine(" Edit a Great Person");
Console.WriteLine();
Console.WriteLine("************************************************************************************************************************");
DisplayMessage("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
DisplayMessage(String.Format("Current Name: {0}", greatPeople.Name));
Console.ForegroundColor = System.Drawing.Color.ForestGreen;
DisplayPromptMessage(" Enter a new name or just press Enter to keep the current name: ");
Console.ResetColor();
Console.ForegroundColor = System.Drawing.Color.DeepSkyBlue;
userResponse = Console.ReadLine();
if (userResponse != "")
{
greatPeople.Name = userResponse;
}
DisplayMessage("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
DisplayMessage(String.Format("Reknowned Work: {0}", greatPeople.KnownFor));
Console.ForegroundColor = System.Drawing.Color.ForestGreen;
DisplayPromptMessage("Enter a new Reknowned Work or just press Enter to keep the current Reknowned Work: ");
Console.ForegroundColor = System.Drawing.Color.DeepSkyBlue;
userResponse = Console.ReadLine();
if (userResponse != "")
{
greatPeople.KnownFor = userResponse;
}
DisplayMessage("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
DisplayMessage(String.Format("Occupation: {0}", greatPeople.Occupation));
Console.ForegroundColor = System.Drawing.Color.ForestGreen;
DisplayPromptMessage("Enter a new occupation or just press Enter to keep the current occupation: ");
Console.ForegroundColor = System.Drawing.Color.DeepSkyBlue;
userResponse = Console.ReadLine();
if (userResponse != "")
{
greatPeople.Occupation = userResponse;
}
DisplayMessage("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
DisplayMessage(String.Format("Current cause of death: {0}", greatPeople.NetWorth));
Console.ForegroundColor = System.Drawing.Color.ForestGreen;
DisplayPromptMessage("Enter a new cause of death or just press Enter to keep the current cause of death: ");
Console.ForegroundColor = System.Drawing.Color.DeepSkyBlue;
userResponse = Console.ReadLine();
if (userResponse != "")
{
greatPeople.NetWorth = userResponse;
}
DisplayMessage("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
DisplayMessage(String.Format("Current year of birth: {0}", greatPeople.BirthYear));
Console.ForegroundColor = System.Drawing.Color.ForestGreen;
DisplayPromptMessage("Enter a new year of birth or just press Enter to keep the current year: ");
Console.ForegroundColor = System.Drawing.Color.DeepSkyBlue;
userResponse = Console.ReadLine();
if (userResponse != "")
{
greatPeople.BirthYear = (userResponse);
}
DisplayMessage("");
Console.ForegroundColor = System.Drawing.Color.LawnGreen;
DisplayMessage(String.Format("Description: {0}", greatPeople.Description));
Console.ForegroundColor = System.Drawing.Color.ForestGreen;
DisplayPromptMessage("Enter a new description or just press Enter to keep the current description: ");
Console.ForegroundColor = System.Drawing.Color.DeepSkyBlue;
userResponse = Console.ReadLine();
if (userResponse != "")
{
greatPeople.Description = (userResponse);
}
DisplayMessage("");
DisplayContinuePrompt();
return(greatPeople);
}