public bool Add(MdlFlat dbFlatObj)
{
string sqlFormattedDate = dbFlatObj.DateOfCreation.ToString("yyyy-MM-dd HH:mm:ss");
string sqlFormattedDateAvailable = dbFlatObj.Avaiable.ToString("yyyy-MM-dd HH:mm:ss");
try
{
string query = "insert into Flat_Main values ('"
+ dbFlatObj.LandlordEmail + "','"
+ dbFlatObj.Type + "','"
+ dbFlatObj.PostCode + "','"
+ dbFlatObj.Address + "',"
+ dbFlatObj.RentPrice + ","
+ dbFlatObj.Deposit + ",'"
+ sqlFormattedDateAvailable + "','"
+ sqlFormattedDate + "','"
+ dbFlatObj.Description + "')";
DbConnection.GetDbCommand(query).ExecuteNonQuery();
DbConnection.Close();
Console.Write("Thread " + Thread.CurrentThread.ManagedThreadId.ToString() + " Executed query: \n " + query);
return true;
}
catch (Exception e)
{
Console.WriteLine("Exception catched: " + e + " Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + " Time: " + DateTime.Now);
return false;
}
}
public string AddFlat(MdlFlat mdlFlatObj)
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
conn.Open();
return GetOutput(conn, mdlFlatObj);
}
}
public void AddNewFlatDb()
{
bool actualResult = false;
bool expectedResult = true;
DbFlat dbFlatObj = new DbFlat();
MdlFlat mdlFlatObj = new MdlFlat("*****@*****.**", "flat", DateTime.Now, DateTime.Now, 2000.0, 6000.0, "address", "zipCode", "Flat description");
actualResult = dbFlatObj.Add(mdlFlatObj);
Assert.AreEqual(expectedResult, actualResult);
}
public bool UpdateFlatStatus(int fId, string status, string dateOfOffer, string availableFrom)
{
DbFlat dbFlatObj = new DbFlat();
MdlFlat mdlFlatObj = new MdlFlat();
mdlFlatObj.Id = fId;
mdlFlatObj.Status = status;
mdlFlatObj.DateOfOffer = dateOfOffer;
mdlFlatObj.AvailableFrom = availableFrom;
return dbFlatObj.UpdateFlatsStatus(mdlFlatObj);
}
public bool UpdateFlat(int flatId, double rent, double deposit, string description)
{
DbFlat dbFlatObj = new DbFlat();
MdlFlat mdlFlatObj = new MdlFlat();
mdlFlatObj.Id = flatId;
mdlFlatObj.Rent = rent;
mdlFlatObj.Deposit = deposit;
mdlFlatObj.Description = description;
return dbFlatObj.UpdateFlat(mdlFlatObj);
}
private MdlFlat CreateFlatObj(string landLordEmail, string type, string address,
string postCode, string city, double rent, double
deposit, string availableFrom, string description)
{
MdlFlat mdlFlatObj = new MdlFlat();
mdlFlatObj.LandlordEmail = landLordEmail;
mdlFlatObj.Type = type;
mdlFlatObj.Address = address;
mdlFlatObj.PostCode = postCode;
mdlFlatObj.City = city;
mdlFlatObj.Rent = rent;
mdlFlatObj.Deposit = deposit;
mdlFlatObj.AvailableFrom = availableFrom;
mdlFlatObj.Description = description;
return mdlFlatObj;
}
public MdlFlat GenerateFlatObj()
{
MdlFlat mdlFlatObj = new MdlFlat();
mdlFlatObj.LandlordEmail = "*****@*****.**";
mdlFlatObj.Type = "type";
mdlFlatObj.Address = "address";
mdlFlatObj.PostCode = "postCode";
mdlFlatObj.City = "city";
mdlFlatObj.Rent = 100;
mdlFlatObj.Deposit = 100;
mdlFlatObj.AvailableFrom = DateTime.Now.AddDays(50).ToString();
mdlFlatObj.Description = "description";
mdlFlatObj.Status = "Closed";
mdlFlatObj.DateOfOffer = "None";
return mdlFlatObj;
}
public List<MdlFlat> GetFlats()
{
try
{
string query = "select * from Flat_Main";
List<MdlFlat> flatList = new List<MdlFlat>();
MdlFlat mdlFlatObj = new MdlFlat();
Console.WriteLine("Thread " + Thread.CurrentThread.ManagedThreadId.ToString() + " Executed query: \n " + query);
using (var connection = new SqlConnection(DbConnection.connectionString))
using (var command = new SqlCommand(query, connection))
{
connection.Open();
using (var sqlReader = command.ExecuteReader())
{
while (sqlReader.Read())
{
mdlFlatObj.Id = Convert.ToInt32(sqlReader.GetValue(0));
mdlFlatObj.LandlordEmail = sqlReader.GetValue(1).ToString();
mdlFlatObj.Type = sqlReader.GetValue(2).ToString();
mdlFlatObj.PostCode = sqlReader.GetValue(3).ToString();
mdlFlatObj.Address = sqlReader.GetValue(4).ToString();
mdlFlatObj.RentPrice = Convert.ToDouble(sqlReader.GetValue(5));
mdlFlatObj.Deposit = Convert.ToDouble(sqlReader.GetValue(6));
mdlFlatObj.DateOfCreation = Convert.ToDateTime(sqlReader.GetValue(7));
mdlFlatObj.Description = sqlReader.GetValue(8).ToString();
flatList.Add(mdlFlatObj);
}
}
}
return flatList;
}
catch (Exception e)
{
List<MdlFlat> flatist = new List<MdlFlat>();
Console.WriteLine("Exception catched: " + e + " Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + " Time: " + DateTime.Now);
return flatist;
}
}
public bool UpdateFlat(MdlFlat mdlFlatObj)
{
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
option.Timeout = TimeSpan.FromSeconds(3);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
SqlCommand cmd = new SqlCommand("spUpdateFlatAttributes", conn);
//set command type
cmd.CommandType = CommandType.StoredProcedure;
//input parameters
cmd.Parameters.AddWithValue("@FlatId", mdlFlatObj.Id);
cmd.Parameters.AddWithValue("@Rent", mdlFlatObj.Rent);
cmd.Parameters.AddWithValue("@Deposit", mdlFlatObj.Deposit);
cmd.Parameters.AddWithValue("@Description", mdlFlatObj.Description);
//execute
conn.Open();
cmd.ExecuteNonQuery();
scope.Complete();
return true;
}
}
catch (Exception e)
{
Transaction.Current.Rollback();
Console.WriteLine("Error. Exception: " + e);
return false;
}
finally
{
if (scope != null)
((IDisposable)scope).Dispose();
}
}
}
private string GetOutput(SqlConnection conn, MdlFlat mdlFlatObj)
{
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;
option.Timeout = TimeSpan.FromSeconds(3);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
SqlCommand cmd = new SqlCommand("spInsertNewFlat", conn);
//set command type
cmd.CommandType = CommandType.StoredProcedure;
//input parameters
cmd.Parameters.AddWithValue("@LandlordEmail", mdlFlatObj.LandlordEmail.ToString());
cmd.Parameters.AddWithValue("@Type", mdlFlatObj.Type.ToString());
cmd.Parameters.AddWithValue("@Address", mdlFlatObj.Address.ToString());
cmd.Parameters.AddWithValue("@PostCode", mdlFlatObj.PostCode.ToString());
cmd.Parameters.AddWithValue("@City", mdlFlatObj.City.ToString());
cmd.Parameters.AddWithValue("@Rent", mdlFlatObj.Rent);
cmd.Parameters.AddWithValue("@Deposit", mdlFlatObj.Deposit);
cmd.Parameters.AddWithValue("@AvailableFrom", mdlFlatObj.AvailableFrom);
cmd.Parameters.AddWithValue("@Description", mdlFlatObj.Description.ToString());
//output parameters
SqlParameter outputParameter = new SqlParameter();
outputParameter.ParameterName = "@MessageOutput";
outputParameter.SqlDbType = SqlDbType.Char;
outputParameter.Direction = ParameterDirection.Output;
outputParameter.Size = 100;
cmd.Parameters.Add(outputParameter);
//execute
cmd.ExecuteNonQuery();
scope.Complete();
return outputParameter.Value.ToString();
}
catch (Exception e)
{
Transaction.Current.Rollback();
Console.WriteLine("An Error has accured. Unable to Add new Flat. Err:" + e);
return "An Error has accured. Unable to Add new Flat.";
}
finally
{
if (scope != null)
((IDisposable)scope).Dispose();
}
}
}
public int GetFlatId(MdlFlat mdlFlatObj)
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand("select Id from flats where LandlordEmail = '" + mdlFlatObj.LandlordEmail + "'" , conn);
using (SqlDataReader sr = cmd.ExecuteReader())
{
while (sr.Read())
{
return Convert.ToInt32(sr.GetValue(0));
}
}
}
return 0;
}
