public int RetrieveContactFromPerticularCityOrState() { try { AddressBookModel model = new AddressBookModel(); using (this.connection) { using (SqlCommand command = new SqlCommand( @"SELECT * FROM address_book WHERE city = 'Latur' OR state = 'Maharashtra'", connection)) { int count = 0; connection.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { count++; 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"); } } return(count); } } } catch (Exception e) { throw new Exception(e.Message); } }
public int getContactDataWithGivenDate() { try { int count = 0; AddressBookModel employeeModel = new AddressBookModel(); using (this.connection) { string query = @"select count(first_name) from address_book where insertDate between cast('2015-01-01' as date) and CAST('2020-01-01' as date)"; SqlCommand cmd = new SqlCommand(query, this.connection); this.connection.Open(); SqlDataReader sqlDataReader = cmd.ExecuteReader(); if (sqlDataReader.HasRows) { while (sqlDataReader.Read()) { count = sqlDataReader.GetInt32(0); } } else { Console.WriteLine("No Data Found"); } sqlDataReader.Close(); this.connection.Close(); return(count); } } catch (Exception e) { throw new Exception(e.Message); } finally { this.connection.Close(); } }