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