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