/// <summary>
/// UC6 Retrieves the contacts from a given state or city.
/// </summary>
/// <param name="state"></param>
/// <param name="city"></param>
public void RetrieveContactFromCityOrStateName()
{
Console.WriteLine("Enter the city name:");
string city = Console.ReadLine();
Console.WriteLine("Enter the state name");
string state = Console.ReadLine();
///Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
connection = dbc.GetConnection();
AddressBookModel model = new AddressBookModel();
try
{
using (connection)
{
/// Query to get all the data from the table
string query = $@"select * from dbo.Address_Book where StateName='{state}' or City='{city}'";
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connection);
///Opening the connection.
connection.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
/// Mapping the data to the employee model class object
while (reader.Read())
{
model.FirstName = reader.GetString(0);
model.LastName = reader.GetString(1);
model.Address = reader.GetString(2);
model.City = reader.GetString(3);
model.State = reader.GetString(4);
model.Zip = reader.GetInt32(5);
model.PhoneNumber = reader.GetInt64(6);
model.EmailId = reader.GetString(7);
model.AddressBookType = reader.GetString(8);
model.AddressBookName = reader.GetString(9);
Console.WriteLine("{0},{1},{2},{3},{4},{5},{6},{7},{8},{9}", model.FirstName, model.LastName,
model.Address, model.City, model.State, model.Zip, model.PhoneNumber, model.EmailId, model.AddressBookType, model.AddressBookName);
Console.WriteLine("\n");
}
}
else
{
Console.WriteLine("No data found");
}
reader.Close();
}
}
/// Catching the null record exception
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
connection.Close();
}
}
