/// <summary>
/// Method to count the number of Contacts belonging to a city/state
/// </summary>
/// <param name="testQuery">query specifying the city/state to find count of</param>
/// <returns></returns>
public int CountData(string testQuery)
{
int count = 0;
try
{
ContactsModel contactModel = new ContactsModel();
using (this.connection)
{
string query = testQuery;
SqlCommand cmd = new SqlCommand(query, this.connection);
this.connection.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
count = dr.GetInt32(0);
}
}
else
{
System.Console.WriteLine("No data found");
}
}
return(count);
}
catch (Exception e)
{
System.Console.WriteLine(e.Message);
return(count);
}
}
/// <summary>
/// Method to retrieve and display all contacts from addressBook
/// hence there are 4 interdependent tables, used joins
/// </summary>
public void RetrieveAllContacts()
{
try
{
ContactsModel contactModel = new ContactsModel();
using (this.connection)
{
string query = @"select n.FirstName, n.LastName, af.Address, af.City, af.State, af.Zipcode, cf.PhoneNumber,cf.Email, rt.RelationType"
+ " from AddressInfo af join ABookTable n on af.FirstName= n.FirstName" +
" join ContactInfo cf on cf.FirstName=af.FirstName join RelationTable rt on af.FirstName=rt.FirstName;";
SqlCommand cmd = new SqlCommand(query, this.connection);
this.connection.Open();
System.Console.WriteLine("FirstName,LastName,Address,City,State,Zipcode,PhoneNumber,Email,RelationType");
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
contactModel.FirstName = dr.GetString(0);
contactModel.LastName = dr.GetString(1);
contactModel.Address = dr.GetString(2);
contactModel.City = dr.GetString(3);
contactModel.State = dr.GetString(4);
contactModel.Zipcode = dr.GetString(5);
contactModel.PhoneNumber = dr.GetString(6);
contactModel.Email = dr.GetString(7);
contactModel.RelationType = dr.GetString(8);
System.Console.WriteLine(contactModel.FirstName + "," + contactModel.LastName + "," + contactModel.Address + "," + contactModel.City +
"," + contactModel.State + "," + contactModel.PhoneNumber + "," + contactModel.Email + "," + contactModel.RelationType);
}
}
else
{
System.Console.WriteLine("No data found");
}
}
}
catch (Exception e)
{
System.Console.WriteLine(e.Message);
}
}
/// <summary>
/// MEthod to add a new contact with all the details
/// </summary>
/// <param name="model">Contact object to be added</param>
/// <returns>true if contact added successfully</returns>
public bool AddContact(ContactsModel model)
{
SqlConnection connection = new SqlConnection(connectionString);
try
{
using (connection)
{
SqlCommand command = new SqlCommand("SpAddContactDetails", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@FirstName", model.FirstName);
command.Parameters.AddWithValue("@LastName", model.LastName);
command.Parameters.AddWithValue("@DateAdded", model.DateAdded);
command.Parameters.AddWithValue("@Address", model.Address);
command.Parameters.AddWithValue("@City", model.City);
command.Parameters.AddWithValue("@State", model.State);
command.Parameters.AddWithValue("@Zipcode", model.Zipcode);
command.Parameters.AddWithValue("@PhoneNumber", model.PhoneNumber);
command.Parameters.AddWithValue("@Email", model.Email);
command.Parameters.AddWithValue("@RelationType", model.RelationType);
connection.Open();
var rowsAffected = command.ExecuteNonQuery();
if (rowsAffected != 0)
{
return(true);
}
return(false);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(false);
}
}
/// <summary>
/// Method to be specifically used in testing purposes for updation
/// </summary>
/// <param name="testQuery">query specifying the details of updation</param>
/// <returns></returns>
public string RetrieveForTesting(string testQuery)
{
string cString = @"Data Source=(LocalDB)\BLDBserver;Initial Catalog=ABook;Integrated Security=True";
SqlConnection connection2 = new SqlConnection(cString);
string modifiedCity = "";
try
{
ContactsModel contactModel = new ContactsModel();
using (connection2)
{
string query = testQuery;
SqlCommand cmd = new SqlCommand(query, connection2);
connection2.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
while (dr.Read())
{
modifiedCity = dr.GetString(0);
}
}
else
{
System.Console.WriteLine("No data found");
}
}
return(modifiedCity);
}
catch (Exception e)
{
System.Console.WriteLine(e.Message);
return(modifiedCity);
}
}
static void Main(string[] args)
{
Console.WriteLine("Hello World!");
AddressBookRepo aRepo = new AddressBookRepo();
List <ContactsModel> contactsList = new List <ContactsModel>();
//Retrieve all Contacts in AddressBook
aRepo.RetrieveAllContacts();
//Update a contact in AddressBook
string updateQuery = @"update AddressInfo set State='T.L.' where FirstName='Mohanee';";
aRepo.UpdateContact(updateQuery);
string testQuery = @"select State from AddressInfo where FirstName='Mohanee';";
Console.WriteLine(aRepo.RetrieveForTesting(testQuery));
//Delete Rows with selected DateRange
string deleteQuery = @"delete from ABookTable where DateAdded between '2012-07-29' and '2013-06-29';";
aRepo.DeleteRowsForSelectedDateRange(deleteQuery);
//Addnew Contact
ContactsModel contact = new ContactsModel();
contact.FirstName = "Ritwick";
contact.LastName = "Sharma";
contact.Address = "Lajpat Colony";
contact.City = "Delhi";
contact.State = "Chandigarh";
contact.Zipcode = "490087";
contact.PhoneNumber = "23456789";
contact.DateAdded = Convert.ToDateTime("2015-03-30");
contact.Email = "*****@*****.**";
contact.RelationType = "Friend";
aRepo.AddContact(contact);
//Add Multiple Contacts Using thread
ContactsModel contact1 = new ContactsModel();
contact1.FirstName = "Ritwick";
contact1.LastName = "Sharma";
contact1.Address = "Lajpat Colony";
contact1.City = "Delhi";
contact1.State = "Chandigarh";
contact1.Zipcode = "490087";
contact1.PhoneNumber = "9823456789";
contact1.DateAdded = Convert.ToDateTime("2015-03-30");
contact1.Email = "*****@*****.**";
contact1.RelationType = "Friend";
ContactsModel contact2 = new ContactsModel();
contact2.FirstName = "Sheela";
contact2.LastName = "Rao";
contact2.Address = "Faridabad Circle";
contact2.City = "Delhi";
contact2.State = "Chandigarh";
contact2.Zipcode = "490087";
contact2.PhoneNumber = "9223456789";
contact2.DateAdded = Convert.ToDateTime("2015-03-30");
contact2.Email = "*****@*****.**";
contact2.RelationType = "Cousin";
ContactsModel contact3 = new ContactsModel();
contact.FirstName = "Ramesh";
contact.LastName = "Verma";
contact.Address = "CityCenter";
contact.City = "Hyderabad";
contact.State = "T.L.";
contact.Zipcode = "497887";
contact.PhoneNumber = "9145678998";
contact.DateAdded = Convert.ToDateTime("2015-03-30");
contact.Email = "*****@*****.**";
contact.RelationType = "Colleague";
contactsList.Add(contact1);
contactsList.Add(contact2);
contactsList.Add(contact3);
aRepo.AddMultipleContactsUsingThreads(contactsList);
}
