/// <summary>
/// Reads the contacts from database.
/// </summary>
/// <param name="bookName">Name of the book.</param>
/// <exception cref="Exception"></exception>
public void ReadContacts_fromDB(string bookName)
{
try
{
ContactDetail contact = new ContactDetail();
using (SqlConnection sqlConnection_Read = new SqlConnection(connection))
{
string query = String.Format("Select * from AddressBook_ADO where book_name='{0}'", bookName);
SqlCommand cmd = new SqlCommand(query, sqlConnection_Read);
sqlConnection_Read.Open();
SqlDataReader sqlDataReader = cmd.ExecuteReader();
if (sqlDataReader.HasRows)
{
Console.WriteLine(Field_Title_DB);
while (sqlDataReader.Read())
{
string tabular_Output = String.Format("{0,-15}{1,-15}{2,-15}{3,-15}{4,-15}{5,-15}{6,-15}{7,-15}{8,-15}",
sqlDataReader.GetString(0), sqlDataReader.GetString(1), sqlDataReader.GetString(2), sqlDataReader.GetString(3), sqlDataReader.GetString(4), sqlDataReader.GetString(5), sqlDataReader.GetString(6), sqlDataReader.GetString(7), sqlDataReader.GetString(8));
Console.WriteLine(tabular_Output);
}
}
else
{
Console.WriteLine("no data ");
}
sqlDataReader.Close();
}
}
catch (SqlException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// Inserts the contact into database.
/// </summary>
/// <param name="bookName">Name of the book.</param>
/// <param name="contact">The contact.</param>
/// <returns></returns>
public bool InsertContact_into_DB(string bookName, ContactDetail contact)
{
// DML statement for inserting Employee object stored as String
string sql = String.Format("Insert into AddressBook_ADO(book_name,first_Name,last_Name,address,city,state,zip,phone_Number,email)" + "Values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}')",
bookName, contact.first_Name, contact.last_Name, contact.address, contact.city, contact.state, contact.pincode, contact.phoneNum, contact.email);
try
{
using (SqlConnection sqlConnection_add = new SqlConnection(connection))
{
//set command with sql string argument
SqlCommand command = new SqlCommand(sql, sqlConnection_add);
//Open connection
sqlConnection_add.Open();
//Executing DML operation, getting affect rows due to operation
int rows_Affected = command.ExecuteNonQuery();
//If insertion affected the rows in table
if (rows_Affected == 1)
{
//Set Insertion flag returning it True
return(true);
}
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
return(false);
}
public bool UpdateContact_inDB(string first_Name, string last_Name, string bookName, ContactDetail contact)
{
return(classDetailsBuilder.UpdateContact_inDB(first_Name, last_Name, bookName, contact));
}
/// <summary>
/// Updates the contact in database.
/// </summary>
/// <param name="first_Name">The first name.</param>
/// <param name="last_Name">The last name.</param>
/// <param name="bookName">Name of the book.</param>
/// <param name="contact">The contact.</param>
/// <returns></returns>
public bool UpdateContact_inDB(string first_Name, string last_Name, string bookName, ContactDetail contact)
{
try
{
using (SqlConnection sqlConnection_Upt = new SqlConnection(connection))
{
sqlConnection_Upt.Open();
ContactDetail contact1 = new ContactDetail();
//setting command type as stored procedure
SqlCommand command = new SqlCommand("updateContactDetail", sqlConnection_Upt);
command.CommandType = CommandType.StoredProcedure;
//Setting parameters for stored procedure, passed as arguments
command.Parameters.AddWithValue("@in_book_name", bookName);
command.Parameters.AddWithValue("@in_first_Name", first_Name);
command.Parameters.AddWithValue("@in_last_Name", last_Name);
command.Parameters.AddWithValue("@book_name", bookName);
command.Parameters.AddWithValue("@first_Name", contact1.first_Name);
command.Parameters.AddWithValue("@last_Name", contact1.last_Name);
command.Parameters.AddWithValue("@address", contact1.address);
command.Parameters.AddWithValue("@city", contact1.city);
command.Parameters.AddWithValue("@state", contact1.state);
command.Parameters.AddWithValue("@zip", contact1.pincode);
command.Parameters.AddWithValue("@phone_Number", contact1.phoneNum);
command.Parameters.AddWithValue("@email", contact1.email);
//get affected rows count
int affected_Rows = command.ExecuteNonQuery();
//if rows affected
if (affected_Rows > 0)
{
return(true);
}
else
{
//Data with that particular details doesnt exist
Console.WriteLine("No data found");
}
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
}
return(false);
}
