public DataSet UpdateDataSet(DataSet ds, MdlApplication mdlApplicationObj)
{
mdlApplicationObj = InitializeScoreQueue(mdlApplicationObj);
ds.Tables[0].TableName = "Applications";
//add new application (row) to the dataset
ds.Tables[0].Rows.Add(CreateNewDataRow(ds, mdlApplicationObj));
DataTable dt = ds.Tables[0];
//udate dataset //sort from highest score
dt.DefaultView.Sort = "Score desc";
dt = dt.DefaultView.ToTable();
//update queue in all rows
int queue = 1;
foreach (DataRow row in dt.Rows)
{
row["QueueNumber"] = queue;
queue++;
}
if (ds.Tables.Count > 0)
ds.Tables.Remove("Applications");
ds.Tables.Add(dt);
return ds;
}
public DataSet UpdateDataSetToRemove(DataSet ds, MdlApplication mdlApplicationObj)
{
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string columnValue = row["StudentEmail"].ToString().Trim();
if (columnValue.Equals(mdlApplicationObj.StudentEmail))
{
row.Delete();
}
}
//sort from highest score
dt.DefaultView.Sort = "Score desc";
dt = dt.DefaultView.ToTable();
//update queue in all rows
int queue = 1;
foreach (DataRow row in dt.Rows)
{
row["QueueNumber"] = queue;
queue++;
}
if (ds.Tables.Count > 0)
ds.Tables.Remove("Applications");
ds.Tables.Add(dt);
return ds;
}
public DataSet UpdateDataSetToRemove(DataSet ds, MdlApplication mdlApplicationObj)
{
DataTable dt = ds.Tables[0];
foreach (DataRow row in dt.Rows)
{
string columnValue = row["StudentEmail"].ToString().Trim();
if (columnValue.Equals(mdlApplicationObj.StudentEmail))
{
row.Delete();
}
}
//sort from highest score
dt.DefaultView.Sort = "Score desc";
dt = dt.DefaultView.ToTable();
//update queue in all rows
int queue = 1;
foreach (DataRow row in dt.Rows)
{
row["QueueNumber"] = queue;
queue++;
}
if (ds.Tables.Count > 0)
{
ds.Tables.Remove("Applications");
}
ds.Tables.Add(dt);
return(ds);
}
public DataSet UpdateDataSet(DataSet ds, MdlApplication mdlApplicationObj)
{
mdlApplicationObj = InitializeScoreQueue(mdlApplicationObj);
ds.Tables[0].TableName = "Applications";
//add new application (row) to the dataset
ds.Tables[0].Rows.Add(CreateNewDataRow(ds, mdlApplicationObj));
DataTable dt = ds.Tables[0];
//udate dataset //sort from highest score
dt.DefaultView.Sort = "Score desc";
dt = dt.DefaultView.ToTable();
//update queue in all rows
int queue = 1;
foreach (DataRow row in dt.Rows)
{
row["QueueNumber"] = queue;
queue++;
}
if (ds.Tables.Count > 0)
{
ds.Tables.Remove("Applications");
}
ds.Tables.Add(dt);
return(ds);
}
private MdlApplication InitializeScoreQueue(MdlApplication mdlApplicationObj)
{
//update score score = profileScore + dateScpre (appObj.score holds profile score at this point)
mdlApplicationObj.DateOfCreation = DateTime.Now;
mdlApplicationObj.Score = SumScores(CalculateScoreDate(mdlApplicationObj.DateOfCreation), mdlApplicationObj.Score);
return(mdlApplicationObj);
}
private MdlApplication GetApplicationObj(string studentEmail, int flatId, int score)
{
MdlApplication mdlApplicationObj = new MdlApplication();
mdlApplicationObj.StudentEmail = studentEmail;
mdlApplicationObj.FlatId = flatId;
mdlApplicationObj.Score = score;
return(mdlApplicationObj);
}
private DataRow CreateNewDataRow(DataSet ds, MdlApplication mdlApplicationObj)
{
DataRow currentApplication = ds.Tables[0].NewRow();
currentApplication["StudentEmail"] = mdlApplicationObj.StudentEmail;
currentApplication["FlatId"] = mdlApplicationObj.FlatId;
currentApplication["DateOfCreation"] = mdlApplicationObj.DateOfCreation;
currentApplication["Score"] = mdlApplicationObj.Score;
currentApplication["QueueNumber"] = -1;
return(currentApplication);
}
private MdlApplication UpdateModelObj(DataSet ds, MdlApplication mdlApplicationObj)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["StudentEmail"].Equals(mdlApplicationObj.StudentEmail))
{
mdlApplicationObj.Score = (int)row["Score"];
mdlApplicationObj.QueueNumber = (int)row["QueueNumber"];
}
}
return(mdlApplicationObj);
}
private static MdlApplication CalculateInsertScore(MdlApplication element)
{
ServerDatabase.DbUpdate dbUpdateObject = new ServerDatabase.DbUpdate();
//return bool, pass calculated score and ID
int dateScore = CtrApplications.CalculateScoreDate(Convert.ToDateTime(element.DateOfCreation));
int profileScore = CtrStudent.GetScore(element.StudentId);
element.Score = CtrApplications.SumScores(dateScore, profileScore);
if (!dbUpdateObject.UpdateApplicationScore(element.Id, element.Score))
Console.WriteLine("Error while updating Application score. ID: " + element.Id + " Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + "\n Time: " + DateTime.Now);
return element;
}
public bool RemoveFromWishlist(string studentEmail, int flatId)
{
MdlApplication mdlApplicationObj = GetApplicationObj(studentEmail, flatId, 0);
DbApplications dbApplicationsObj = new DbApplications();
CtrApplications ctrApplicationObj = new CtrApplications();
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
option.Timeout = TimeSpan.FromSeconds(30);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
//Get current Application table for specific flat
DataSet ds = dbApplicationsObj.GetApplicationsDataSetRomove(mdlApplicationObj.StudentEmail, mdlApplicationObj.FlatId);
if (ds.Tables.Count == 0)
{
return(false);
}
//Update dataset - add current application and order queue
ds = ctrApplicationObj.UpdateDataSetToRemove(ds, mdlApplicationObj);
//update dataset
bool result = dbApplicationsObj.UpdateApplications(ds, mdlApplicationObj);
if (result)
{
scope.Complete();
}
else
{
scope.Dispose();
}
return(result);
}
catch
{
Transaction.Current.Rollback();
return(false);
}
finally
{
if (scope != null)
{
((IDisposable)scope).Dispose();
}
}
}
}
public void AddToWishListExistingApplicaation()
{
CtrStudent ctrStudentObj = new CtrStudent();
MdlStudent mdlStudentObj = StudentTests.GenerateStudentObj();
CtrGetData ctrGetDataObj = new CtrGetData();
DataSet ds = ctrGetDataObj.GetAllFlats();
MdlApplication mdlApplicationObj = new MdlApplication();
string studentEmail = "*****@*****.**";
int flatId = (int)ds.Tables[0].Rows[0]["Id"];
int score = 100;
bool actual = ctrStudentObj.AddToWishlist(studentEmail, flatId, score);
bool expected = false;
Assert.AreEqual(expected, actual);
}
public void AddToWishListExistingApplicaation()
{
CtrStudent ctrStudentObj = new CtrStudent();
MdlStudent mdlStudentObj = StudentTests.GenerateStudentObj();
CtrGetData ctrGetDataObj = new CtrGetData();
DataSet ds = ctrGetDataObj.GetAllFlats();
MdlApplication mdlApplicationObj = new MdlApplication();
string studentEmail = "*****@*****.**";
int flatId = (int)ds.Tables[0].Rows[0]["Id"];
int score = 100;
bool actual = ctrStudentObj.AddToWishlist(studentEmail, flatId, score);
bool expected = false;
Assert.AreEqual(expected, actual);
}
public bool AddToWishlist(string studentEmail, int flatId, int score)
{
MdlApplication mdlApplicationObj = GetApplicationObj(studentEmail, flatId, score);
DbApplications dbApplicationsObj = new DbApplications();
CtrApplications ctrApplicationObj = new CtrApplications();
//Get current Application table for specific flat
DataSet ds = dbApplicationsObj.GetApplicationsDataSet(mdlApplicationObj.StudentEmail, mdlApplicationObj.FlatId);
if (ds.Tables.Count == 0)
{
return(false);
}
//Update dataset - add current application and order queue
ds = ctrApplicationObj.UpdateDataSet(ds, mdlApplicationObj);
//update dataset
return(dbApplicationsObj.UpdateApplications(ds, mdlApplicationObj));
//return false;
}
public bool UpdateApplications(DataSet ds, MdlApplication mdlApplicationObj)
{
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
option.Timeout = TimeSpan.FromSeconds(30);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
if (!RemoveApplications(mdlApplicationObj))
{
return(false);
}
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
ds.Tables[0].TableName = "Applications";
SqlDataAdapter da = new SqlDataAdapter("spGetApplications", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@StudentEmail", mdlApplicationObj.StudentEmail);
da.SelectCommand.Parameters.AddWithValue("@FlatId", mdlApplicationObj.FlatId);
SqlCommandBuilder builder = new SqlCommandBuilder(da);
int updates = da.Update(ds, "Applications");
scope.Complete();
return(true);
}
}
catch (Exception e)
{
Transaction.Current.Rollback();
return(false);
}
finally
{
if (scope != null)
{
((IDisposable)scope).Dispose();
}
}
}
}
private bool RemoveApplications(MdlApplication mdlApplicationObj)
{
try
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
SqlCommand cmd = new SqlCommand("spRemoveBufferedApplications", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FlatId", mdlApplicationObj.FlatId);
conn.Open();
cmd.ExecuteNonQuery();
return(true);
}
}
catch (Exception e)
{
Console.WriteLine("An Error has accured. remove from Wishlist Terminated. Err:" + e);
return(false);
}
}
private MdlApplication UpdateModelObj(DataSet ds, MdlApplication mdlApplicationObj)
{
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["StudentEmail"].Equals(mdlApplicationObj.StudentEmail))
{
mdlApplicationObj.Score = (int)row["Score"];
mdlApplicationObj.QueueNumber = (int)row["QueueNumber"];
}
}
return mdlApplicationObj;
}
private bool RemoveApplications(MdlApplication mdlApplicationObj)
{
try
{
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
SqlCommand cmd = new SqlCommand("spRemoveBufferedApplications", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@FlatId", mdlApplicationObj.FlatId);
conn.Open();
cmd.ExecuteNonQuery();
return true;
}
}
catch (Exception e)
{
Console.WriteLine("An Error has accured. remove from Wishlist Terminated. Err:" + e);
return false;
}
}
public bool UpdateApplications(DataSet ds, MdlApplication mdlApplicationObj)
{
var option = new TransactionOptions();
option.IsolationLevel = System.Transactions.IsolationLevel.Serializable;
option.Timeout = TimeSpan.FromSeconds(30);
using (var scope = new TransactionScope(TransactionScopeOption.Required, option))
{
try
{
if (!RemoveApplications(mdlApplicationObj))
return false;
using (SqlConnection conn = new SqlConnection(DbConnection.connectionString))
{
ds.Tables[0].TableName = "Applications";
SqlDataAdapter da = new SqlDataAdapter("spGetApplications", conn);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.AddWithValue("@StudentEmail", mdlApplicationObj.StudentEmail);
da.SelectCommand.Parameters.AddWithValue("@FlatId", mdlApplicationObj.FlatId);
SqlCommandBuilder builder = new SqlCommandBuilder(da);
int updates = da.Update(ds, "Applications");
scope.Complete();
return true;
}
}
catch (Exception e)
{
Transaction.Current.Rollback();
return false;
}
finally
{
if (scope != null)
((IDisposable)scope).Dispose();
}
}
}
private MdlApplication GetApplicationObj(string studentEmail, int flatId, int score)
{
MdlApplication mdlApplicationObj = new MdlApplication();
mdlApplicationObj.StudentEmail = studentEmail;
mdlApplicationObj.FlatId = flatId;
mdlApplicationObj.Score = score;
return mdlApplicationObj;
}
private static bool FlatIdPredicate(MdlApplication element)
{
return true;
}
public List<MdlApplication> UpdateScore()
{
try
{
MdlApplication mdlApplicationObj;
//create appropriate array
List<MdlApplication> applicationList = new List<MdlApplication>();
string query = "select * from Applications";
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())
{
mdlApplicationObj = new MdlApplication();
//while reads initialize and populate list
mdlApplicationObj.Id = Convert.ToInt32(sqlReader.GetValue(0));
mdlApplicationObj.StudentId = Convert.ToInt32(sqlReader.GetValue(1));
mdlApplicationObj.FlatId = Convert.ToInt32(sqlReader.GetValue(2));
mdlApplicationObj.DateOfCreation = Convert.ToDateTime(sqlReader.GetValue(3));
mdlApplicationObj.Score = Convert.ToInt32(sqlReader.GetValue(4));
mdlApplicationObj.QueueNumber = Convert.ToInt32(sqlReader.GetValue(5));
Console.WriteLine("Adding to row list... ");
applicationList.Add(mdlApplicationObj);
}
}
}
return applicationList;
}
catch (Exception e)
{
Console.WriteLine("Exception catched: " + e + " Thread: " + Thread.CurrentThread.ManagedThreadId.ToString() + " Time: " + DateTime.Now);
return null;
}
}
private DataRow CreateNewDataRow(DataSet ds, MdlApplication mdlApplicationObj)
{
DataRow currentApplication = ds.Tables[0].NewRow();
currentApplication["StudentEmail"] = mdlApplicationObj.StudentEmail;
currentApplication["FlatId"] = mdlApplicationObj.FlatId;
currentApplication["DateOfCreation"] = mdlApplicationObj.DateOfCreation;
currentApplication["Score"] = mdlApplicationObj.Score;
currentApplication["QueueNumber"] = -1;
return currentApplication;
}
private MdlApplication InitializeScoreQueue(MdlApplication mdlApplicationObj)
{
//update score score = profileScore + dateScpre (appObj.score holds profile score at this point)
mdlApplicationObj.DateOfCreation = DateTime.Now;
mdlApplicationObj.Score = SumScores(CalculateScoreDate(mdlApplicationObj.DateOfCreation), mdlApplicationObj.Score);
return mdlApplicationObj;
}