//note methods
public List <Rem_user_note> allNoteList()
{
try
{
List <Rem_user_note> lu = new List <Rem_user_note>();
string sql = @"SELECT * FROM REM_USER_NOTE";
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
sqlConnection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Rem_user_note u = new Rem_user_note();
u.fk_id_rem_user = reader.GetInt32(0);
u.date = SafeGetDate(reader, 1);
u.note_date = SafeGetDate(reader, 2);
u.IP = SafeGetString(reader, 3);
u.note = SafeGetString(reader, 4);
u.is_deleted = SafeGetInt(reader, 5);
lu.Add(u);
}
sqlConnection.Close();
return(lu);
}
catch (Exception ex)
{
sqlConnection.Close();
throw ex;
}
}
public ActionResult addNote(Rem_user_note item)
{
item.is_deleted = 0;
item.IP = GetIPAddress();
item.date = DateTime.Now;
bool result = methods.addNote(item);
return(Json(result, JsonRequestBehavior.AllowGet));
}
public bool addNote(Rem_user_note item)
{
try
{
List <Rem_user_note> lu = new List <Rem_user_note>();
string sql = @"INSERT INTO rem_user_note
(fk_id_rem_user,date,note_date, IP, note, is_deleted)
VALUES (@fk_id_rem_user, @date, @note_date, @IP, @note, @is_deleted)";
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlParameter[] p = new SqlParameter[6];
p[0] = new SqlParameter();
p[0].ParameterName = "@fk_id_rem_user";
p[0].Value = item.fk_id_rem_user;
p[1] = new SqlParameter();
p[1].ParameterName = "@date";
p[1].Value = item.date;
p[2] = new SqlParameter();
p[2].ParameterName = "@note_date";
p[2].Value = item.note_date;
p[3] = new SqlParameter();
p[3].ParameterName = "@IP";
p[3].Value = item.IP;
p[4] = new SqlParameter();
p[4].ParameterName = "@note";
p[4].Value = item.note;
p[5] = new SqlParameter();
p[5].ParameterName = "@is_deleted";
p[5].Value = item.is_deleted;
cmd.Parameters.AddRange(p);
sqlConnection.Open();
cmd.ExecuteNonQuery();
sqlConnection.Close();
return(true);
}
catch (Exception ex)
{
sqlConnection.Close();
return(false);
}
}
public List <Rem_user_note> showNote(int userId)
{
try
{
List <Rem_user_note> lu = new List <Rem_user_note>();
string sql = @"SELECT * FROM rem_user_note WHERE fk_id_rem_user=@userId";
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
SqlParameter[] p = new SqlParameter[1];
p[0] = new SqlParameter();
p[0].ParameterName = "@userId";
p[0].Value = userId;
cmd.Parameters.AddRange(p);
sqlConnection.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Rem_user_note u = new Rem_user_note();
u.id_rem_user_note = reader.GetInt32(0);
u.fk_id_rem_user = SafeGetInt(reader, 1);
u.date = SafeGetDate(reader, 2);
u.note_date = SafeGetDate(reader, 3);
u.IP = SafeGetString(reader, 4);
u.note = SafeGetString(reader, 5);
u.is_deleted = SafeGetInt(reader, 6);
lu.Add(u);
}
sqlConnection.Close();
return(lu);
}
catch (Exception ex)
{
sqlConnection.Close();
throw ex;
}
}