/// <summary>
/// UC16 RetrieveDataFromDB
/// </summary>
/// <returns></returns>
public static ContactsDB RetrieveData()
{
try
{
connection = new SqlConnection(connectionString);
ContactsDB contactsDB = new ContactsDB();
string query = "select c.first_name, c.last_name, c.address, c.city, c.state, c.zipcode, c.phone_no, bk.Book_Name, bk.Book_Type from Contacts c inner join BookNameType bk on c.Book_ID=bk.Bookk_ID where c.first_name='Rachit'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
contactsDB.first_name = reader.GetString(0);
contactsDB.last_name = reader.GetString(1);
contactsDB.address = reader.GetString(2);
contactsDB.city = reader.GetString(3);
contactsDB.state = reader.GetString(4);
contactsDB.zipcode = Convert.ToInt32(reader.GetInt32(5));
contactsDB.phone_no = reader.GetString(6);
contactsDB.email = reader.GetString(7);
contactsDB.Book_Name = reader.GetString(8);
contactsDB.Contact_Type = reader.GetString(9);
}
}
else
{
Console.WriteLine("No rows found");
}
reader.Close();
return(contactsDB);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
connection.Close();
}
}
/// <summary>
/// UC16 RetrieveDataFromDB
/// </summary>
/// <returns></returns>
public static ContactsDB RetrieveData()
{
try
{
connection = new SqlConnection(connectionString);
ContactsDB contactsDB = new ContactsDB();
string query = "select c.FirstName, c.LastName, c.City, c.PhoneNumber, bk.B_Name, bk.B_Type from Contacts c inner join BookNameType bk on c.B_ID=bk.B_ID where c.FirstName='Akash'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
contactsDB.first_name = reader.GetString(0);
contactsDB.last_name = reader.GetString(1);
contactsDB.city = reader.GetString(2);
contactsDB.phone = reader.GetString(3);
contactsDB.B_Name = reader.GetString(4);
contactsDB.B_Type = reader.GetString(5);
}
}
else
{
Console.WriteLine("No rows found");
}
reader.Close();
return(contactsDB);
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return(null);
}
finally
{
connection.Close();
}
}
/// <summary>
/// UC19 RetrieveDataByCityorState
/// </summary>
public static void RetrieveDataByCityorState()
{
ContactsDB contactsDB = new ContactsDB();
try
{
connection = new SqlConnection(connectionString);
string query = "select c.FirstName, c.LastName, c.City, c.State, c.PhoneNumber, bk.B_Name, bk.B_Type from Contacts c inner join BookNameType bk on c.B_ID=bk.B_ID where City='Mulund' or State='Bengal'";
SqlCommand command = new SqlCommand(query, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
contactsDB.first_name = reader.GetString(0);
contactsDB.last_name = reader.GetString(1);
contactsDB.city = reader.GetString(2);
contactsDB.state = reader.GetString(3);
contactsDB.phone = reader.GetString(4);
contactsDB.B_Name = reader.GetString(5);
contactsDB.B_Type = reader.GetString(6);
Console.WriteLine("FirstName: " + contactsDB.first_name + ", LastName: " + contactsDB.last_name + ", City: " + contactsDB.city + ", State: " + contactsDB.state + ", Phone: " + contactsDB.phone + ", BookName: " + contactsDB.B_Name + ", BookType: " + contactsDB.B_Type);
Console.WriteLine();
}
}
else
{
Console.WriteLine("No rows found");
}
reader.Close();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
connection.Close();
}
finally
{
connection.Close();
}
}
public bool AddContactDetailsInDB(ContactsDB model)
{
try
{
using (connection)
{
connection = new SqlConnection(connectionString);
SqlCommand command = new SqlCommand("InsertIntoMultipleTables", connection);
connection.Open();
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@BookID", model.B_ID);
command.Parameters.AddWithValue("@BookName", model.B_Name);
command.Parameters.AddWithValue("@BookType", model.B_Type);
command.Parameters.AddWithValue("@FirstName", model.first_name);
command.Parameters.AddWithValue("@LastName", model.last_name);
var result = command.ExecuteNonQuery();
//this.connection.Close();
if (result != 0)
{
return(true);
}
return(false);
}
}
catch (Exception e)
{
Console.WriteLine(e.Message);
connection.Close();
return(false);
}
finally
{
connection.Close();
}
}