public static GoalDal getInstance()
{
if (_instance == null)
{
_instance = new GoalDal();
}
return(_instance);
}
public List <Measurement> GetMeasurementsByUser(int userId)
{
List <Measurement> result = new List <Measurement>();
Goal currGoal = GoalDal.getInstance().GetGoalByUserId(userId);
//Create the SQL Query for returning all the msrmnts
string sqlQuery = String.Format("select * from Measurements where UserID = {0} and CreationDate >= '{1}'", userId, ((DateTime)currGoal.CreationDate).ToString("yyyy-MM-dd HH:mm:ss"));
//Create and open a connection to SQL Server
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sports_db"].ConnectionString);
connection.Open();
SqlCommand command = new SqlCommand(sqlQuery, connection);
//Create DataReader for storing the returning table into server memory
SqlDataReader dataReader = command.ExecuteReader();
Measurement msrmnt = null;
//load into the result object the returned row from the database
if (dataReader.HasRows)
{
while (dataReader.Read())
{
msrmnt = new Measurement();
msrmnt.MeasurementID = Convert.ToInt32(dataReader["MeasurementID"]);
msrmnt.Weight = Convert.ToDouble(dataReader["Weight"]);
msrmnt.BodyFat = Convert.ToDouble(dataReader["BodyFat"]);
msrmnt.UserID = Convert.ToInt32(dataReader["UserID"]);
msrmnt.CreationDate = Convert.ToDateTime(dataReader["CreationDate"]);
result.Add(msrmnt);
}
}
// Close and dispose
CloseAndDispose(command, connection);
return(result);
}
public int InsertOrUpdateUser(User user)
{
/*user.MeasurementID = MeasurementDal.getInstance().InsertOrUpdateMeasurement(user.Measurement);
* user.Measurement.MeasurementID = user.MeasurementID.Value;
*
* if (user.GoalID == null || user.GoalID == 0)
* {
* user.GoalID = GoalDal.getInstance().InsertOrUpdateGoal(user.Goal);
* user.Goal.GoalID = user.GoalID.Value;
* }
* else
* {
* Goal gg = GoalDal.getInstance().GetGoalById(user.GoalID.Value);
*
* if (gg.GoalWeight != user.Goal.GoalWeight || gg.BodyFat != user.Goal.BodyFat)
* {
* user.Goal.GoalID = 0;
* user.GoalID = GoalDal.getInstance().InsertOrUpdateGoal(user.Goal);
* user.Goal.GoalID = user.GoalID.Value;
* }
* }*/
//Create the SQL Query for inserting an user
string createQuery = String.Format("Insert into Users (FirstName, LastName, Birthday, Height, Gender, Email, Password) Values('{0}', '{1}', '{2}', {3}, {4}, '{5}', '{6}');"
+ "Select @@Identity", user.FirstName, user.LastName, user.Birthday.ToString("yyyy-MM-dd"), user.Height, user.Gender, user.Email, user.Password);
string updateQuery = String.Format("Update Users SET FirstName='{0}', LastName='{1}' , Birthday='{2}', Height={3}, Gender={4}, Email='{5}', Password='******' Where UserID = {7};",
user.FirstName, user.LastName, user.Birthday.ToString("yyyy-MM-dd"), user.Height, user.Gender, user.Email, user.Password, user.UserID);
//Create and open a connection to SQL Server
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["sports_db"].ConnectionString);
connection.Open();
//Create a Command object
SqlCommand command = null; // new SqlCommand(createQuery, connection);
if (user.UserID != 0)
{
command = new SqlCommand(updateQuery, connection);
}
else if (!IsUserExists(user.Email))
{
command = new SqlCommand(createQuery, connection);
}
int savedUserID = 0;
try
{
//Execute the command to SQL Server and return the newly created ID
var commandResult = command.ExecuteScalar();
if (commandResult != null)
{
savedUserID = Convert.ToInt32(commandResult);
}
else
{
//the update SQL query will not return the primary key but if doesn't throw exception
//then we will take it from the already provided data
savedUserID = user.UserID;
}
user.Goal.UserID = savedUserID;
user.Measurement.UserID = savedUserID;
MeasurementDal.getInstance().InsertOrUpdateMeasurement(user.Measurement);
GoalDal.getInstance().InsertOrUpdateGoal(user.Goal);
}
catch (Exception ex)
{
//there was a problem executing the script
}
//Close and dispose
CloseAndDispose(command, connection);
// Set return value
return(savedUserID);
}
