Example #1
0
        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;
            }
        }
Example #2
0
 public string AddFlat(MdlFlat mdlFlatObj)
 {
     using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
     {
         conn.Open();
         return GetOutput(conn, mdlFlatObj);
     }
 }
Example #3
0
        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);
        }
Example #4
0
        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);
        }
Example #5
0
        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);
        }
Example #6
0
 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;
 }
Example #7
0
        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;
        }
Example #8
0
        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;
            }
        }
Example #9
0
        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();
                }
            }
        }
Example #10
0
 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();
         }
     }
 }
Example #11
0
        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;
        }