public void DeleteContactUsingName(AddressBookModel model) { try { using (this.connection) { SqlCommand command = new SqlCommand("deletcontactProcedure", connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.AddWithValue("@First_Name", model.First_Name); connection.Open(); command.ExecuteNonQuery(); Console.WriteLine("Contact Deleted successfully..."); connection.Close(); } } catch (Exception e) { throw new Exception(e.Message); } }
static void Main(string[] args) { Console.WriteLine("***AddressBook_Using_ADO.NET***"); AddressBookRepo abrepo = new AddressBookRepo(); abrepo.CheckConnection(); AddressBookModel abmodel = new AddressBookModel(); abmodel.First_Name = "Asif"; abmodel.Last_Name = "Shaikh"; abmodel.Address = "mainRoad"; abmodel.City = "Beed"; abmodel.State = "Maharashtra"; abmodel.Zip = "145263"; abmodel.Phone_Number = "9876532147"; abmodel.Email = "*****@*****.**"; abmodel.BookName = "address002"; abmodel.AddressbookType = "friend"; bool result = abrepo.AddContact(abmodel); if (result) { Console.WriteLine("Record added successfully..."); } else { Console.WriteLine("Some problem is there..."); } AddressBookModel abmodel1 = new AddressBookModel(); abmodel1.First_Name = "Asif"; abmodel1.Last_Name = "Shaikh"; abmodel1.Address = "mainRoad"; abmodel1.City = "Beed"; abmodel1.State = "Maharashtra"; abmodel1.Zip = "145263"; abmodel1.Phone_Number = "9876532147"; abmodel1.Email = "*****@*****.**"; abmodel1.BookName = "address002"; abmodel1.AddressbookType = "family"; bool result1 = abrepo.AddContact(abmodel1); if (result1) { Console.WriteLine("Record added successfully..."); } else { Console.WriteLine("Some problem is there..."); } AddressBookModel editModel = new AddressBookModel(); editModel.First_Name = "Dipak"; editModel.Last_Name = "Nagar"; editModel.City = "Latur"; editModel.State = "Maharashtra"; editModel.Email = "*****@*****.**"; editModel.BookName = "address002"; editModel.AddressbookType = "office"; abrepo.EditContactUsingFirstName(editModel); AddressBookModel delmodel = new AddressBookModel(); delmodel.First_Name = "Asif"; abrepo.DeleteContactUsingName(delmodel); abrepo.RetrieveContactFromPerticularCityOrState(); abrepo.AddressBookSizeByCityANDState(); abrepo.SortPersonNameByCity(); abrepo.GetNumberOfContactsCountByBookType(); Console.ReadKey(); }
public void SortPersonNameByCity() { try { AddressBookModel model = new AddressBookModel(); using (this.connection) { using (SqlCommand command = new SqlCommand( @"SELECT * FROM address_book WHERE city = 'Pune' order by first_name; SELECT * FROM address_book WHERE city = 'Latur' order by first_name, last_name;", connection)) { connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { Console.WriteLine("Sorted Contact Using first name from Pune"); Console.WriteLine("==========================================="); while (reader.Read()) { model.First_Name = reader.GetString(0); model.Last_Name = reader.GetString(1); model.Address = reader.GetString(2); model.City = reader.GetString(3); model.State = reader.GetString(4); model.Zip = reader.GetString(5); model.Phone_Number = reader.GetString(6); model.Email = reader.GetString(7); Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", model.First_Name, model.Last_Name, model.Address, model.City, model.State, model.Zip, model.Phone_Number, model.Email); Console.WriteLine("\n"); } if (reader.NextResult()) { Console.WriteLine("Sorted Contact Using First_Name from Latur"); Console.WriteLine("===========================================");; while (reader.Read()) { model.First_Name = reader.GetString(0); model.Last_Name = reader.GetString(1); model.Address = reader.GetString(2); model.City = reader.GetString(3); model.State = reader.GetString(4); model.Zip = reader.GetString(5); model.Phone_Number = reader.GetString(6); model.Email = reader.GetString(7); Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", model.First_Name, model.Last_Name, model.Address, model.City, model.State, model.Zip, model.Phone_Number, model.Email); Console.WriteLine("\n"); } } } connection.Close(); } } } catch (Exception e) { throw new Exception(e.Message); } }