public WriteNote(User currentUser)
{
InitializeComponent();
this.currentUser = currentUser;
this.currentNote = new Note(0, this.noteText.Rtf, this.tagsBox.Text, this.titleBox.Text, 0, currentUser.getId());
data = new SQLconnect();
loadNotebooks();
}
public WriteNote(Note note, User currentUser)
{
data = new SQLconnect();
InitializeComponent();
this.currentNote = note;
this.currentUser = currentUser;
loadNotebooks();
updateFields();
}
public void save()
{
Notebook notebook = (Notebook)this.notebooksBox.SelectedItem;
this.currentNote = data.insertNote(new Note(currentNote.getId(), this.noteText.Rtf, this.titleBox.Text, this.tagsBox.Text, notebook.getId(), currentUser.getId()));
updateFields();
}
public List<Note> SearchNotes(String keyword)
{
SqlDataReader reader;
List<Note> notelist = new List<Note>();
SqlCommand query = new SqlCommand("SELECT DISTINCT note.noteId,note.text,note.title,note.tags,note.notebookId,note.userId " +
"FROM note,users WHERE note.tags LIKE '%'+@keyword +'%' "+
"OR note.title LIKE '%'+@keyword +'%' "+
"OR users.username LIKE '%'+@keyword +'%' "+
"AND note.userId = users.userId", con);
SqlParameter parKeyword = new SqlParameter("@keyword", SqlDbType.VarChar);
parKeyword.Value = keyword;
query.Parameters.Add(parKeyword);
this.openCon();
try
{
reader = query.ExecuteReader();
while (reader.Read())
{
Note note = new Note(Convert.ToInt32(reader["noteId"].ToString()),
reader["text"].ToString(),
reader["title"].ToString(),
reader["tags"].ToString(),
Convert.ToInt32(reader["notebookId"].ToString()),
Convert.ToInt32(reader["userId"].ToString()));
notelist.Add(note);
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
this.closeCon();
return notelist;
}
public Note insertNote(Note note)
{
String sql;
if (note.getId() > 0)
{
sql = "UPDATE note " +
"SET notebookId = @notebookId, userId = @userId," +
"text = @text, tags = @tags, title= @title " +
"WHERE noteId = @noteId";
}
else
{
sql = "INSERT INTO note (notebookId, userId, text, tags, title)" +
"VALUES (@notebookId, @userId, @text, @tags, @title)";
}
SqlCommand query = new SqlCommand(sql, con);
SqlParameter parNotebookId = new SqlParameter("@notebookId", SqlDbType.Int);
SqlParameter parUserId = new SqlParameter("@userId", SqlDbType.VarChar, 50);
SqlParameter parText = new SqlParameter("@text", SqlDbType.VarChar);
SqlParameter parTags = new SqlParameter("@tags", SqlDbType.VarChar, 50);
SqlParameter parTitle = new SqlParameter("@title", SqlDbType.VarChar, 50);
SqlParameter parNoteId = new SqlParameter("@noteId", SqlDbType.Int);
parNotebookId.Value = note.getNotebookId();
parUserId.Value = note.getUserId();
parText.Value = note.getText();
parTags.Value = note.getTags();
parTitle.Value = note.getTitle();
parNoteId.Value = note.getId();
query.Parameters.Add(parNotebookId);
query.Parameters.Add(parUserId);
query.Parameters.Add(parText);
query.Parameters.Add(parTags);
query.Parameters.Add(parTitle);
query.Parameters.Add(parNoteId);
Console.WriteLine(query.CommandText);
try
{
this.openCon();
query.ExecuteScalar();
this.closeCon();
if(note.getId()>0)
{
List<Note> notes = getNotes(note.getId(),"noteId");
return notes.ElementAt(0);
}
else
{
List<Note> notes = getNotes(note.getUserId(),"userId");
return notes.ElementAt(notes.Count-1);
}
}
catch (Exception e)
{
Console.WriteLine("Adding note failed");
Console.WriteLine(e.Message);
this.closeCon();
return note;
}
}
public List<Note> getNotes(int id, String type)
{
SqlDataReader reader;
List<Note> notelist = new List<Note>();
SqlCommand query = new SqlCommand("SELECT * FROM note WHERE "+type+" = @id", con);
SqlParameter parId = new SqlParameter("@id", SqlDbType.Int);
parId.Value = id;
query.Parameters.Add(parId);
this.openCon();
try
{
reader = query.ExecuteReader();
while (reader.Read())
{
Note note = new Note(Convert.ToInt32(reader["noteId"].ToString()),
reader["text"].ToString(),
reader["title"].ToString(),
reader["tags"].ToString(),
Convert.ToInt32(reader["notebookId"].ToString()),
Convert.ToInt32(reader["userId"].ToString()));
notelist.Add(note);
}
}
catch (SqlException e)
{
Console.WriteLine(e.Message);
}
this.closeCon();
return notelist;
}