/// <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); }