public bool Insert(habitBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"INSERT INTO tbl_habit
(" + BelongTo + ","
+ HabitName + ","
+ HabitData + ","
+ AddedDate + ","
+ AddedBy + ") VALUES" +
"(@belong_to," +
"@habit_name," +
"@habit_data," +
"@added_date," +
"@added_by)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@belong_to", u.belong_to);
cmd.Parameters.AddWithValue("@habit_name", u.habit_name);
cmd.Parameters.AddWithValue("@habit_data", u.habit_data);
cmd.Parameters.AddWithValue("@added_date", u.added_date);
cmd.Parameters.AddWithValue("@added_by", u.added_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToTextAndMessageToUser(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool Update(habitBLL u)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
String sql = @"UPDATE tbl_habit SET
habit_data=@habit_data,
updated_date=@updated_date,
updated_by=@updated_by
WHERE belong_to=@belong_to AND habit_name=@habit_name";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@belong_to", u.belong_to);
cmd.Parameters.AddWithValue("@habit_name", u.habit_name);
cmd.Parameters.AddWithValue("@habit_data", u.habit_data);
cmd.Parameters.AddWithValue("@updated_date", u.updated_date);
cmd.Parameters.AddWithValue("@updated_by", u.updated_by);
conn.Open();
int rows = cmd.ExecuteNonQuery();
//if the query is executed successfully then the rows' value = 0
if (rows > 0)
{
//query successful
isSuccess = true;
}
else
{
//Query falled
isSuccess = false;
}
}
catch (Exception ex)
{
Module.Tool tool = new Module.Tool(); tool.saveToText(ex);
}
finally
{
conn.Close();
}
return(isSuccess);
}
public bool HabitInsertAndHistoryRecord(habitBLL u)
{
DataTable dt_Record = HabitSearch(u.belong_to, u.habit_name);
DateTime date = DateTime.Now;
string oldData = "NULL";
bool success = false;
if (dt_Record.Rows.Count > 0)
{
//update
u.updated_date = u.added_date;
u.updated_by = u.added_by;
foreach (DataRow row in dt_Record.Rows)
{
oldData = row[HabitData].ToString();
}
success = Update(u);
}
else
{
//insert
success = Insert(u);
}
if (!success)
{
MessageBox.Show("Failed to insert habit data!");
tool.historyRecord(text.System, "Failed to insert habit data! (habitDAL : 319)", date, MainDashboard.USER_ID);
}
else
{
tool.historyRecord(text.habit_insert, "[" + u.belong_to + "]" + u.habit_name + ": " + oldData + " --> " + u.habit_data, date, MainDashboard.USER_ID);
}
return(success);
}
