/// <summary>
/// UC9 -- Function to get the entire data from the table after implementing the concept of ER-Diagram
/// Using the concept of join on the table
/// </summary>
public void GetAllDataFromTableUsingJoin()
{
/// Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
/// Calling the Get connection method to establish the connection to the Sql Server
connectionToServer = dbc.GetConnection();
/// Creating the address book model class object
AddressBookModel bookModel = new AddressBookModel();
try
{
using (connectionToServer)
{
/// Query to get all the data from the table
string query = @"SELECT t.contactId,t.firstName, t.secondName, t.phoneNumber, t.emailId,
b.addressBookName, d.address, d.city, d.state, d.zip, c.contactType
FROM addressBook_Table t join address_Book b ON t.contactId = b.contactId
join address_Detail d ON t.contactId = d.contactId
join contact_type c on t.contactId = c.contactId;";
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connectionToServer);
/// Opening the connection to start mapping
connectionToServer.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
/// executing for not null
if (reader.HasRows)
{
/// Moving to the next record from the table
/// Mapping the data to the employee model class object
while (reader.Read())
{
bookModel.contactID = reader.GetInt32(0);
bookModel.firstName = reader.GetString(1);
bookModel.secondName = reader.GetString(2);
bookModel.phoneNumber = reader.GetInt64(3);
bookModel.emailId = reader.GetString(4);
bookModel.addressBookName = reader.GetString(5);
bookModel.address = reader.GetString(6);
bookModel.city = reader.GetString(7);
bookModel.state = reader.GetString(8);
bookModel.ZIP = reader.GetInt32(9);
bookModel.contactType = reader.GetString(10);
Console.WriteLine($"First Name:{bookModel.firstName}\nSecond Name:{bookModel.secondName}\n" +
$"Address:{bookModel.address}, {bookModel.city}, {bookModel.state} PinCode: {bookModel.ZIP}\n" +
$"Phone Number: {bookModel.phoneNumber}\nContact Type: {bookModel.contactType}\nAddress Book Name : {bookModel.addressBookName}");
Console.WriteLine("\n\n");
}
}
else
{
Console.WriteLine("No data found");
}
reader.Close();
}
}
/// Catching the null record exception
catch (Exception ex)
{
throw new Exception(ex.Message);
}
/// Alway ensuring the closing of the connection
finally
{
connectionToServer.Close();
}
}
/// <summary>
/// UC18 -- Get the detail of the record in the address book entered within a time frame
/// </summary>
/// <param name="date"></param>
public void RetrieveAllTheContactAddedInBetweenADate(DateTime date)
{
/// Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
/// Calling the Get connection method to establish the connection to the Sql Server
connectionToServer = dbc.GetConnection();
/// Creating the address book model class object
AddressBookModel bookModel = new AddressBookModel();
try
{
using (connectionToServer)
{
/// Query to get all the data from the table
string query = @"select * from addressBookDatabase where dateOfEntry between @parameter and CAST(GETDATE() AS Date );";
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connectionToServer);
/// Binding the parameter to the formal parameters
command.Parameters.AddWithValue("@parameter", date);
/// Opening the connection to start mapping
connectionToServer.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
/// executing for not null
if (reader.HasRows)
{
/// Moving to the next record from the table
/// Mapping the data to the employee model class object
while (reader.Read())
{
bookModel.firstName = reader.GetString(0);
bookModel.secondName = reader.GetString(1);
bookModel.address = reader.GetString(2);
bookModel.city = reader.GetString(3);
bookModel.state = reader.GetString(4);
bookModel.zip = reader.GetInt64(5);
bookModel.phoneNumber = reader.GetInt64(6);
bookModel.emailId = reader.GetString(7);
bookModel.contactType = reader.GetString(8);
bookModel.addressBookName = reader.GetString(9);
bookModel.DateOfEntry = reader.GetDateTime(10);
Console.WriteLine($"First Name:{bookModel.firstName}\nSecond Name:{bookModel.secondName}\n" +
$"Address:{bookModel.address}, {bookModel.city}, {bookModel.state} PinCode: {bookModel.zip}\n" +
$"Phone Number: {bookModel.phoneNumber}\nContact Type: {bookModel.contactType}\nAddress Book Name : {bookModel.addressBookName}\n" +
$"Date Of Entry in the Address Book: {bookModel.DateOfEntry}");
Console.WriteLine("\n\n");
}
}
else
{
Console.WriteLine("No data found");
}
reader.Close();
}
}
/// Catching the null record exception
catch (Exception ex)
{
throw new Exception(ex.Message);
}
/// Alway ensuring the closing of the connection
finally
{
connectionToServer.Close();
}
}
/// <summary>
/// UC5 -- Function to get the detail of the records belonging to a city or state
/// </summary>
/// <param name="gender"></param>
public void GetTheDetailOfRecordForCityOrState(string data, int choice, int property)
{
AddressBookModel bookModel = new AddressBookModel();
/// Creates a new connection for every method to avoid "ConnectionString property not initialized" exception
DBConnection dbc = new DBConnection();
/// Calling the Get connection method to establish the connection to the Sql Server
connectionToServer = dbc.GetConnection();
string query = "";
try
{
/// Using the connection established
using (connectionToServer)
{
if (choice == 1)
{
if (property == 1)
{
/// Query to get the data from the table
query = @"select * from dbo.addressBookDatabase
where city=@parameter";
}
else
{
query = @"SELECT t.contactId,t.firstName, t.secondName, t.phoneNumber, t.emailId,
b.addressBookName, d.address, d.city, d.state, d.zip, c.contactType
FROM addressBook_Table t join address_Book b ON t.contactId = b.contactId
join address_Detail d ON t.contactId = d.contactId
join contact_type c on t.contactId = c.contactId
where d.city = @parameter";
}
}
else if (choice == 2)
{
if (property == 1)
{
// Query to get the data from the table
query = @"select * from dbo.addressBookDatabase
where state=@parameter";
}
else
{
query = @"SELECT t.contactId,t.firstName, t.secondName, t.phoneNumber, t.emailId,
b.addressBookName, d.address, d.city, d.state, d.zip, c.contactType
FROM addressBook_Table t join address_Book b ON t.contactId = b.contactId
join address_Detail d ON t.contactId = d.contactId
join contact_type c on t.contactId = c.contactId
where d.state = @parameter";
}
}
else
{
Console.WriteLine("Wrong Choice....");
}
/// Impementing the command on the connection fetched database table
SqlCommand command = new SqlCommand(query, connectionToServer);
/// Binding the parameter to the formal parameters
command.Parameters.AddWithValue("@parameter", data);
/// Opening the connection to start mapping
connectionToServer.Open();
/// executing the sql data reader to fetch the records
SqlDataReader reader = command.ExecuteReader();
/// executing for not null
if (reader.HasRows)
{
/// Moving to the next record from the table
/// Mapping the data to the retrieved data from executing the query on the table
while (reader.Read())
{
if (property == 1)
{
bookModel.firstName = reader.GetString(0);
bookModel.secondName = reader.GetString(1);
bookModel.address = reader.GetString(2);
bookModel.city = reader.GetString(3);
bookModel.state = reader.GetString(4);
bookModel.zip = reader.GetInt64(5);
bookModel.phoneNumber = reader.GetInt64(6);
bookModel.emailId = reader.GetString(7);
bookModel.contactType = reader.GetString(8);
bookModel.addressBookName = reader.GetString(9);
Console.WriteLine($"First Name:{bookModel.firstName}\nSecond Name:{bookModel.secondName}\n" +
$"Address:{bookModel.address}, {bookModel.city}, {bookModel.state} PinCode: {bookModel.zip}\n" +
$" Phone Number: {bookModel.phoneNumber}\n Contact Type: {bookModel.contactType}\n Address Book Name : {bookModel.addressBookName}");
Console.WriteLine("\n\n");
}
else
{
bookModel.contactID = reader.GetInt32(0);
bookModel.firstName = reader.GetString(1);
bookModel.secondName = reader.GetString(2);
bookModel.phoneNumber = reader.GetInt64(3);
bookModel.emailId = reader.GetString(4);
bookModel.addressBookName = reader.GetString(5);
bookModel.address = reader.GetString(6);
bookModel.city = reader.GetString(7);
bookModel.state = reader.GetString(8);
bookModel.ZIP = reader.GetInt32(9);
bookModel.contactType = reader.GetString(10);
Console.WriteLine($"First Name:{bookModel.firstName}\nSecond Name:{bookModel.secondName}\n" +
$"Address:{bookModel.address}, {bookModel.city}, {bookModel.state} PinCode: {bookModel.ZIP}\n" +
$"Phone Number: {bookModel.phoneNumber}\nContact Type: {bookModel.contactType}\nAddress Book Name : {bookModel.addressBookName}");
Console.WriteLine("\n\n");
}
}
}
else
{
Console.WriteLine("No Data found");
}
reader.Close();
}
}
/// Catching any type of exception generated during the run time
catch (Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
connectionToServer.Close();
}
}
