public DelphiAnnotation get(int codNote)
{
const string sql = "select * from notes where codNote = @codNote";
SQLiteCommand cmd = DBAccess.getCommand(sql);
try {
DelphiAnnotation note = null;
SQLiteParameter pCodNote = new SQLiteParameter("@codNote", codNote);
cmd.Parameters.Add(pCodNote);
SQLiteDataReader res = cmd.ExecuteReader();
try {
if (res.Read())
{
note = buildObject(res);
}
} finally {
res.Close();
}
return(note);
} catch (SqlException ex) {
throw ex;
}
}
public DelphiAnnotation createNew(int categoryId)
{
var note = new DelphiAnnotation();
note.CodNote = getLastID() + 1;
note.TitNote = "";
note.CreatedAt = DateTime.Now;
note.UpdatedAt = note.CreatedAt;
note.CategoryId = categoryId; //TODO: Rever
note.TexNote = "";
insert(note);
return(note);
}
public void insert(DelphiAnnotation annotation)
{
var cmd = DBAccess.getCommand("insert into notes (codNote, titNote, datCriacao, datAlteracao, texNote, codCateg) values (@codNote, @titNote, @datCriacao, @datAlteracao, @texNote, @codCateg)");
var pCodNote = new SQLiteParameter("@codNote", annotation.CodNote);
var pTitNote = new SQLiteParameter("@titNote", annotation.TitNote);
var pDatCriacao = new SQLiteParameter("@datCriacao", annotation.CreatedAt.ToString("yyyy-MM-dd-hh-mm-ss"));
var pDatAlteracao = new SQLiteParameter("@datAlteracao", annotation.UpdatedAt.ToString("yyyy-MM-dd-hh-mm-ss"));
var pTexNote = new SQLiteParameter("@texNote", annotation.TexNote);
var pCodCateg = new SQLiteParameter("@codCateg", annotation.CategoryId);
cmd.Parameters.Add(pCodNote);
cmd.Parameters.Add(pTitNote);
cmd.Parameters.Add(pDatCriacao);
cmd.Parameters.Add(pDatAlteracao);
cmd.Parameters.Add(pTexNote);
cmd.Parameters.Add(pCodCateg);
cmd.ExecuteNonQuery();
}
public DelphiAnnotation buildObject(SQLiteDataReader res)
{
DelphiAnnotation note = new DelphiAnnotation();
note.CodNote = res.GetInt16(res.GetOrdinal("codNote"));
note.TitNote = (string)res["titNote"];
//Há situações em que o texto não é selecionado
if (res.GetOrdinal("TexNote") > -1)
{
byte[] text = GetBytes("TexNote", res);
text = Encoding.Convert(Encoding.GetEncoding("iso-8859-1"), Encoding.UTF8, text);
note.TexNote = Encoding.UTF8.GetString(text, 0, text.Length);
}
note.CreatedAt = converToDateTime((string)res["DatCriacao"]); //createdAt
note.UpdatedAt = converToDateTime((string)res["DatAlteracao"]); //updatedAt
note.CategoryId = res.GetInt16(res.GetOrdinal("CodCateg")); //categoryId
note.Password = (res["password"] != DBNull.Value ? (string)res["password"] : null);
return(note);
}
