public bool AddContact(AddressBookModel model)
{
try
{
using (this.connection)
{
SqlCommand command = new SqlCommand("SpAddContactInAddressBook", this.connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@first_name", model.first_name);
command.Parameters.AddWithValue("@last_name", model.last_name);
command.Parameters.AddWithValue("@address", model.address);
command.Parameters.AddWithValue("@city", model.city);
command.Parameters.AddWithValue("@state", model.state);
command.Parameters.AddWithValue("@zip", model.zip);
command.Parameters.AddWithValue("@phone_number", model.phone_number);
command.Parameters.AddWithValue("@email", model.email);
command.Parameters.AddWithValue("@addressbook_name", model.addressbook_name);
command.Parameters.AddWithValue("@addressbook_type", model.addressbook_type);
this.connection.Open();
var result = command.ExecuteNonQuery();
this.connection.Close();
if (result != 0)
{
return(true);
}
return(false);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
this.connection.Close();
}
}
public void DeleteContactUsingName(AddressBookModel model)
{
try
{
using (this.connection)
{
SqlCommand command = new SqlCommand("SpDeleteContactBasedOnName", 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);
}
finally
{
this.connection.Close();
}
}
public bool AddAPersonInTwoAddressbookTypes(AddressBookModel model)
{
try
{
using (this.connection)
{
//Query to add persone in Family Addressbook type
SqlCommand command = new SqlCommand("SpAddContactInAddressBook", this.connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@first_name", model.first_name);
command.Parameters.AddWithValue("@last_name", model.last_name);
command.Parameters.AddWithValue("@address", model.address);
command.Parameters.AddWithValue("@city", model.city);
command.Parameters.AddWithValue("@state", model.state);
command.Parameters.AddWithValue("@zip", model.zip);
command.Parameters.AddWithValue("@phone_number", model.phone_number);
command.Parameters.AddWithValue("@email", model.email);
command.Parameters.AddWithValue("@addressbook_name", model.addressbook_name);
command.Parameters.AddWithValue("@addressbook_type", model.addressbook_type);
this.connection.Open();
var result = command.ExecuteNonQuery();
Console.WriteLine("New Contact Added Successfully");
this.connection.Close();
if (result != 0)
{
return(true);
}
//Query to add persone in Friends Addressbook type
SqlCommand command2 = new SqlCommand("SpAddContactInAddressBook", this.connection);
command2.CommandType = CommandType.StoredProcedure;
command2.Parameters.AddWithValue("@first_name", model.first_name);
command2.Parameters.AddWithValue("@last_name", model.last_name);
command2.Parameters.AddWithValue("@address", model.address);
command2.Parameters.AddWithValue("@city", model.city);
command2.Parameters.AddWithValue("@state", model.state);
command2.Parameters.AddWithValue("@zip", model.zip);
command2.Parameters.AddWithValue("@phone_number", model.phone_number);
command2.Parameters.AddWithValue("@email", model.email);
command2.Parameters.AddWithValue("@addressbook_name", model.addressbook_name);
command2.Parameters.AddWithValue("@addressbook_type", model.addressbook_type);
this.connection.Open();
var result2 = command2.ExecuteNonQuery();
Console.WriteLine("New Contact Added Successfully");
this.connection.Close();
if (result2 != 0)
{
return(true);
}
return(false);
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
this.connection.Close();
}
}
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 = 'Satara' order by first_name, last_name;", connection))
{
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine("------Sorted Contact based of first name of person belonging to city Pune-----");
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.GetInt32(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 based of first name of person belonging to city Satara-----");
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.GetInt32(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");
}
}
}
}
}
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
this.connection.Close();
}
}
static void Main(string[] args)
{
Console.WriteLine("Welcome to Address Book Database Project with ADO.NET");
Console.WriteLine("\n");
AddressBookRepo repo = new AddressBookRepo();
//repo.CheckConnection();
AddressBookModel addressBookModel = new AddressBookModel();
addressBookModel.first_name = "Mamta";
addressBookModel.last_name = "Chaudhary";
addressBookModel.address = "Rajmata chauk";
addressBookModel.city = "Jaipur";
addressBookModel.state = "Rajasthan";
addressBookModel.zip = 423233;
addressBookModel.phone_number = "7654321095";
addressBookModel.email = "*****@*****.**";
addressBookModel.addressbook_name = "AddressBook2";
addressBookModel.addressbook_type = "Friends";
//repo.AddContact(addressBookModel);
AddressBookModel addressBookModel1 = new AddressBookModel();
addressBookModel1.first_name = "Neha";
addressBookModel1.last_name = "Patil";
addressBookModel1.city = "Pushkar";
addressBookModel1.state = "Rajasthan";
addressBookModel1.email = "*****@*****.**";
addressBookModel1.addressbook_name = "AddressBook1";
addressBookModel1.addressbook_type = "Professional";
//repo.EditContactUsingPersonName(addressBookModel1);
AddressBookModel model = new AddressBookModel();
model.first_name = "Komal";
//repo.DeleteContactUsingName(model);
//repo.RetrievePersonFromPErticulatCityOrState();
//repo.AddressBookSizeByCityANDState();
//repo.SortPersonNameByCity();
//repo.GetNumberOfPersonsCountByType();
AddressBookModel addressBookModel2 = new AddressBookModel();
addressBookModel2.first_name = "Megha";
addressBookModel2.last_name = "Chinchawade";
addressBookModel2.address = "Rajmata chauk";
addressBookModel2.city = "Indor";
addressBookModel2.state = "Indor";
addressBookModel2.zip = 422233;
addressBookModel2.phone_number = "6754321095";
addressBookModel2.email = "*****@*****.**";
addressBookModel2.addressbook_name = "AddressBook3";
addressBookModel2.addressbook_type = "Family";
//repo.AddAPersonInTwoAddressbookTypes(addressBookModel2);
//Adding Same Person's COntact in AddressBook Type 'Friends'
addressBookModel2.first_name = "Megha";
addressBookModel2.last_name = "Chinchawade";
addressBookModel2.address = "Rajmata chauk";
addressBookModel2.city = "Indor";
addressBookModel2.state = "Indor";
addressBookModel2.zip = 422233;
addressBookModel2.phone_number = "6754321095";
addressBookModel2.email = "*****@*****.**";
addressBookModel2.addressbook_name = "AddressBook2";
addressBookModel2.addressbook_type = "Friends";
repo.AddAPersonInTwoAddressbookTypes(addressBookModel2);
}