internal void DeleteNote(Note note)
{
string sql = @"
DELETE FROM Case_Note
WHERE Case_ID = @CaseID
AND Note_ID = @NoteID";
using (SqlConnection conn = new SqlConnection(Constants.DSN))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("CaseID", CaseID);
cmd.Parameters.AddWithValue("NoteID", note.NoteID);
cmd.ExecuteNonQuery();
}
}
public static List<Note> GetNotes()
{
string dsn = ConfigurationManager.ConnectionStrings["RJLouEntities"].ToString();
List<Note> results = new List<Note>();
string sql = "SELECT * FROM NOTE";
using (SqlConnection conn = new SqlConnection(dsn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
SqlDataReader read = cmd.ExecuteReader();
while (read.Read())
{
Note result = new Note()
{
NoteID = Convert.ToInt32(read["Note_ID"]),
Author = InternalUser.Get(Convert.ToInt32(read["Author"])),
NoteText = read["NoteText"].ToString()
};
var readCreateDate = read["CreateDate"];
if (readCreateDate is DBNull)
result.CreateDate = default(DateTime);
else
result.CreateDate = Convert.ToDateTime(read["CreateDate"]);
var readEditDate = read["EditDate"];
if (readEditDate is DBNull)
result.EditDate = default(DateTime);
else
result.EditDate = Convert.ToDateTime(read["EditDate"]);
results.Add(result);
}
}
return results;
}
internal void AddNote(Note note)
{
string sql = @"
INSERT INTO Case_Note (Case_ID, Note_ID)
VALUES (@CaseID, @NoteID)";
using (SqlConnection conn = new SqlConnection(Constants.DSN))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("CaseID", CaseID);
cmd.Parameters.AddWithValue("NoteID", note.NoteID);
cmd.ExecuteNonQuery();
}
}
public static Note Get(int id)
{
string dsn = ConfigurationManager.ConnectionStrings["RJLouEntities"].ToString();
string sql = @"SELECT * FROM NOTE WHERE Note_ID = @ID";
using (SqlConnection conn = new SqlConnection(dsn))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("ID", id);
SqlDataReader read = cmd.ExecuteReader();
if (read.Read())
{
Note result = new Note()
{
NoteID = Convert.ToInt32(read["Note_ID"]),
CreateDate = read["CreateDate"] as DateTime? ?? default(DateTime),
EditDate = read["EditDate"] as DateTime? ?? default(DateTime),
NoteText = read["Note_Text"] as string
};
var author = read["Author"] as int? ?? default(int);
result.Author = InternalUser.Get(author);
return result;
}
}
return null;
}