public static MeasurementDal getInstance()
{
if (_instance == null)
{
_instance = new MeasurementDal();
}
return(_instance);
}
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);
}