public void GetAllContacts()
{
try
{
AddressBookModel adressbook = new AddressBookModel();
using (this.connection)
{
string query = @"Select * from AddressBook;";
SqlCommand cmd = new SqlCommand(query, this.connection);
this.connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
adressbook.FirstName = dr.GetString(0);
adressbook.LastName = dr.GetString(1);
adressbook.Address = dr.GetString(2);
adressbook.City = dr.GetString(3);
adressbook.State = dr.GetString(4);
adressbook.Zip = dr.GetInt32(5);
adressbook.PhoneNumber = dr.GetString(6);
adressbook.AddressbookName = dr.GetString(7);
adressbook.Type = dr.GetString(8);
System.Console.WriteLine(adressbook.FirstName + " " + adressbook.LastName + " " + adressbook.Address + " " + adressbook.City + " " + adressbook.PhoneNumber + " " + adressbook.Type);
}
}
else
{
System.Console.WriteLine("No data found");
}
}
}
catch (Exception e)
{
System.Console.WriteLine(e.Message);
}
}
/// <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();
}
}