/// <summary>
/// Addings the contact details in database. UC20
/// </summary>
/// <param name="contactDetails">The contact details.</param>
/// <returns></returns>
public bool AddingContactDetailsInDatabase(AddressBookContactDetails contactDetails)
{
//getting the connection
SqlConnection connection = dBConnection.GetConnection();
try
{
//if connection is valid, then commands are executed
using (connection)
{
SqlCommand command = new SqlCommand();
//stored procedure is added in command text.
command.CommandText = "InsertingData";
command.Connection = connection;
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@firstname", contactDetails.firstName);
command.Parameters.AddWithValue("@lastname", contactDetails.lastName);
command.Parameters.AddWithValue("@address", contactDetails.address);
command.Parameters.AddWithValue("@city", contactDetails.city);
command.Parameters.AddWithValue("@state", contactDetails.state);
command.Parameters.AddWithValue("@zip", contactDetails.zip);
command.Parameters.AddWithValue("@phonenumber", contactDetails.phoneNo);
command.Parameters.AddWithValue("@email", contactDetails.eMail);
command.Parameters.AddWithValue("@dateadded", contactDetails.dateAdded);
command.Parameters.AddWithValue("@addressbookname", contactDetails.addressBookName);
connection.Open();
//executing query for adding data
int result = command.ExecuteNonQuery();
if (result != 0)
{
return(true);
}
return(false);
}
}
//catching exception
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(false);
}
//finally block to close the connection
//this will execute, even if there is no error for catching exception.
finally
{
if (connection.State == System.Data.ConnectionState.Open)
{
connection.Close();
}
}
}
/// <summary>
/// Updates the contact details in data base. UC17
/// </summary>
/// <param name="contactDetails">The contact details.</param>
/// <returns></returns>
/// <exception cref="Exception"></exception>
public bool UpdateContactDetailsInDataBase(AddressBookContactDetails contactDetails)
{
//getting sql connection
SqlConnection connection = dBConnection.GetConnection();
//using connection, if available
try
{
using (connection)
{
//stored procedure for updating details using multiple tables and join statement
SqlCommand command = new SqlCommand("spUpdateContactDetails", connection);
//passing data about where condition and setting different variables using parmaeters.addwithvalue
command.CommandType = System.Data.CommandType.StoredProcedure;
command.Parameters.AddWithValue("@firstname", contactDetails.firstName);
command.Parameters.AddWithValue("@lastname", contactDetails.lastName);
command.Parameters.AddWithValue("@address", contactDetails.address);
command.Parameters.AddWithValue("@city", contactDetails.city);
command.Parameters.AddWithValue("@state", contactDetails.state);
command.Parameters.AddWithValue("@zip", contactDetails.zip);
command.Parameters.AddWithValue("@phonenumber", contactDetails.phoneNo);
command.Parameters.AddWithValue("@email", contactDetails.eMail);
command.Parameters.AddWithValue("@addressbookname", contactDetails.addressBookName);
connection.Open();
//result contain no of affected rows as Execute Non Query gives no of affected rows after query
int result = command.ExecuteNonQuery();
connection.Close();
if (result != 0)
{
return(true);
}
return(false);
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
static void Main(string[] args)
{
Console.WriteLine("Welcome to Address Book Problem, ADO.NET use cases");
//instatiating address book operations
AddressBookOperations addressBookOperations = new AddressBookOperations();
bool check = true;
while (check)
{
Console.WriteLine("\nPlease press 1 to get all contact details");
Console.WriteLine("Please press 2 to get contact details for given date range/city/state");
Console.WriteLine("Please press 3 to add contact details");
Console.WriteLine("press any other key to exit");
string option = Console.ReadLine();
switch (option)
{
case "1":
//UC16 getting contact details
try
{
List <AddressBookContactDetails> contactDetailsList = addressBookOperations.GetAllContactDetails();
contactDetailsList.ForEach(contactDetails =>
{
Console.WriteLine("ContactID:- " + contactDetails.contactID + " First Name:- " + contactDetails.firstName + " Last Name:- " + contactDetails.lastName + " Address:- " + contactDetails.address + " City:- " + contactDetails.city + " State:- " + contactDetails.state + " Zip:- " + contactDetails.zip + " phone number:- " + contactDetails.phoneNo + " Email:- " + contactDetails.eMail);
Console.WriteLine("address Book Name id: -" + contactDetails.addressBookNameId + " address book name: -" + contactDetails.addressBookName);
Console.WriteLine("Type id: -" + contactDetails.typeId + " type name: -" + contactDetails.typeName);
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
break;
case "2":
//UC18 getting contact details in date range
//UC19 getting contact details for particular city or state
try
{
Console.WriteLine("Please press 1 for date range, 2 for state and 3 for city");
int task = Convert.ToInt32(Console.ReadLine());
List <AddressBookContactDetails> contactDetailsListInDateRange = addressBookOperations.GetAllContactDetailsWithConditions(task);
contactDetailsListInDateRange.ForEach(contactDetails =>
{
Console.WriteLine("ContactID:- " + contactDetails.contactID + " First Name:- " + contactDetails.firstName + " Last Name:- " + contactDetails.lastName + " Address:- " + contactDetails.address + " City:- " + contactDetails.city + " State:- " + contactDetails.state + " Zip:- " + contactDetails.zip + " phone number:- " + contactDetails.phoneNo + " Email:- " + contactDetails.eMail + " Date:-" + contactDetails.dateAdded);
//Console.WriteLine("address Book Name id: -" + contactDetails.addressBookNameId + " address book name: -" + contactDetails.addressBookName);
//Console.WriteLine("Type id: -" + contactDetails.typeId + " type name: -" + contactDetails.typeName);
});
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
break;
case "3":
//UC20 Adding Contact Details into Database
AddressBookContactDetails contactDetails = new AddressBookContactDetails();
contactDetails.firstName = "Rachael";
contactDetails.lastName = "Jai";
contactDetails.address = "Sector14";
contactDetails.city = "new Delhi";
contactDetails.state = "unavailable";
contactDetails.zip = 111234;
contactDetails.phoneNo = 8199929262;
contactDetails.eMail = "*****@*****.**";
contactDetails.dateAdded = Convert.ToDateTime("2020-08-01");
contactDetails.addressBookName = "D";
bool result = addressBookOperations.AddingContactDetailsInDatabase(contactDetails);
Console.WriteLine(result == true ? "Details are added into database successfully" : "Details are not written in database");
break;
default:
check = false;
break;
}
}
}
/// <summary>
/// Gets all contact details with conditions.
/// for particular date range UC18
/// for particular state or city UC19
/// </summary>
/// <returns></returns>
/// <exception cref="Exception">
/// No data found in the database
/// or
/// </exception>
public List <AddressBookContactDetails> GetAllContactDetailsWithConditions(int task)
{
//defining list for adding data
List <AddressBookContactDetails> contactDetailsList = new List <AddressBookContactDetails>();
//getting sql connection
SqlConnection connection = dBConnection.GetConnection();
string query = "0";
//using connection, if available
try
{
using (connection)
{
if (task == 1)
{
//sql command using stored procedure
//for particular date range
query = "select * from addressbook where dateadded between cast('2019-01-01' as date) and getdate()";
}
if (task == 2)
{
//for particular state
query = "select * from addressbook where state='Karnataka'";
}
if (task == 3)
{
//for particular city
query = "select * from addressbook where city='Hisar'";
}
//command.CommandType = System.Data.CommandType.StoredProcedure;
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
//sql data reader class for reading data
SqlDataReader dr = command.ExecuteReader();
//executes if rows are there in database tables
if (dr.HasRows)
{
//iterates until data is read across rows
while (dr.Read())
{
//saving data in contact details object
AddressBookContactDetails contactDetails = new AddressBookContactDetails();
contactDetails.firstName = dr.GetString(0);
contactDetails.lastName = dr.GetString(1);
contactDetails.address = dr.GetString(2);
contactDetails.city = dr.GetString(3);
contactDetails.state = dr.GetString(4);
contactDetails.zip = dr.GetInt32(5);
contactDetails.phoneNo = dr.GetInt64(6);
contactDetails.eMail = dr.GetString(7);
contactDetails.contactID = dr.GetInt32(8);
contactDetails.dateAdded = dr.GetDateTime(9);
//adding details in contact details list
contactDetailsList.Add(contactDetails);
}
//closing execute reader connection
dr.Close();
//closing sql connection
connection.Close();
//returns list
return(contactDetailsList);
}
else
{
throw new Exception("No data found in the database");
}
}
}
//catching up exception
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
/// <summary>
/// Gets all contact details. UC16
/// </summary>
/// <returns></returns>
/// <exception cref="Exception">No data found in the database</exception>
public List <AddressBookContactDetails> GetAllContactDetails()
{
//defining list for adding data
List <AddressBookContactDetails> contactDetailsList = new List <AddressBookContactDetails>();
//getting sql connection
SqlConnection connection = dBConnection.GetConnection();
//using connection, if available
try
{
using (connection)
{
//sql command using stored procedure
SqlCommand command = new SqlCommand("spGetAllContacts", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
//sql data reader class for reading data
SqlDataReader dr = command.ExecuteReader();
//executes if rows are there in database tables
if (dr.HasRows)
{
//iterates until data is read across rows
while (dr.Read())
{
//saving data in contact details object
AddressBookContactDetails contactDetails = new AddressBookContactDetails();
contactDetails.contactID = dr.GetInt32(0);
contactDetails.firstName = dr.GetString(1);
contactDetails.lastName = dr.GetString(2);
contactDetails.address = dr.GetString(3);
contactDetails.city = dr.GetString(4);
contactDetails.state = dr.GetString(5);
contactDetails.zip = dr.GetInt32(6);
contactDetails.phoneNo = dr.GetInt64(7);
contactDetails.eMail = dr.GetString(8);
contactDetails.addressBookNameId = dr.GetInt32(9);
contactDetails.addressBookName = dr.GetString(10);
contactDetails.typeId = dr.GetInt32(11);
contactDetails.typeName = dr.GetString(12);
//adding details in contact details list
contactDetailsList.Add(contactDetails);
}
//closing execute reader connection
dr.Close();
//closing sql connection
connection.Close();
//returns list
return(contactDetailsList);
}
else
{
throw new Exception("No data found in the database");
}
}
}
//catching up exception
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
/// <summary>
/// Gettings the updated details. UC17
/// </summary>
/// <param name="contact">Address book contact details for selecting required data.</param>
/// <returns></returns>
/// <exception cref="Exception">No data found in the database</exception>
public AddressBookContactDetails GettingUpdatedDetails(AddressBookContactDetails contact)
{
//defining list for adding data
//List<AddressBookContactDetails> contactDetailsList = new List<AddressBookContactDetails>();
//getting sql connection
SqlConnection connection = dBConnection.GetConnection();
//using connection, if available
try
{
using (connection)
{
string query = "Select a.firstname,a.lastname,a.address,a.city,a.state,a.zip,a.phonenumber,a.email,c.addressbookname from addressbook a join addressbookmapper b on a.contactid=b.contactid join addressbooknames c on c.addressbookid=b.addressbookid where a.firstname=@firstname and a.lastname=@lastname and c.addressbookname=@addressbookname";
//sql command using stored procedure
SqlCommand command = new SqlCommand(query, connection);
command.Parameters.AddWithValue("@firstname", contact.firstName);
command.Parameters.AddWithValue("@lastname", contact.lastName);
command.Parameters.AddWithValue("@addressbookname", contact.addressBookName);
connection.Open();
//sql data reader class for reading data
SqlDataReader dr = command.ExecuteReader();
//executes if rows are there in database tables
if (dr.HasRows)
{
//iterates until data is read across rows
while (dr.Read())
{
//saving data in contact details object
AddressBookContactDetails contactDetails = new AddressBookContactDetails();
contactDetails.firstName = dr.GetString(0);
contactDetails.lastName = dr.GetString(1);
contactDetails.address = dr.GetString(2);
contactDetails.city = dr.GetString(3);
contactDetails.state = dr.GetString(4);
contactDetails.zip = dr.GetInt32(5);
contactDetails.phoneNo = dr.GetInt64(6);
contactDetails.eMail = dr.GetString(7);
contactDetails.addressBookName = dr.GetString(8);
//adding details in contact details list
//contactDetailsList.Add(contactDetails);
return(contactDetails);
}
//closing execute reader connection
dr.Close();
//closing sql connection
connection.Close();
return(null);
//returns list
}
else
{
throw new Exception("No data found in the database");
}
}
}
//catching up exception
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
