private static List<NoteInfo> searchAllNoteList(NoteInfo noteinfo)
{
List<NoteInfo> NoteList = new List<NoteInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From note where date = @date ; ";
msqlCommand.Parameters.AddWithValue("@date", noteinfo.gotoDate);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
NoteInfo Note = new NoteInfo();
Note.noteDate = msqlReader.GetDateTime("date");
NoteList.Add(Note);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return NoteList;
}
private static int DoEnterNewNoteindb(NoteInfo NewNote)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "INSERT INTO note(id,date,note) " + "VALUES(@id,@date,@note)";
msqlCommand.Parameters.AddWithValue("@id", NewNote.id);
msqlCommand.Parameters.AddWithValue("@date", NewNote.gotoDate);
msqlCommand.Parameters.AddWithValue("@note", NewNote.note);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private static List<NoteInfo> QueryAllNoteList()
{
List<NoteInfo> NoteList = new List<NoteInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From note ;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
NoteInfo Note = new NoteInfo();
//Note.id = msqlReader.GetString("id");
Note.noteDate = msqlReader.GetDateTime("date");
Note.note = msqlReader.GetString("note");
NoteList.Add(Note);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return NoteList;
}
public static List<NoteInfo> searchNoteList(NoteInfo noteinfo)
{
return searchAllNoteList(noteinfo);
}
public static List<NoteInfo> searchMnthlyNoteList(DateTime date)
{
DateTime day1st = new DateTime(date.Year, date.Month, 1);
DateTime dayLast = new DateTime(date.Year, date.Month + 1, 1);
dayLast = dayLast.Subtract(new TimeSpan(1, 0, 0, 0));
List<NoteInfo> NoteList = new List<NoteInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "SELECT * FROM note where date(note.date) >= DATE_SUB( @dayLast, INTERVAL @diff DAY) group by date;";
msqlCommand.Parameters.AddWithValue("@dayLast", dayLast);
msqlCommand.Parameters.AddWithValue("@diff", dayLast.Subtract(day1st));
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
NoteInfo Note = new NoteInfo();
Note.noteDate = msqlReader.GetDateTime("date");
Note.note = msqlReader.GetString("note");
NoteList.Add(Note);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return NoteList;
}
public static int DoEnterNewNote(NoteInfo NewNote)
{
return DoEnterNewNoteindb(NewNote);
}
private void saveNotesBtn_Click(object sender, RoutedEventArgs e)
{
if (!notesTB.Text.Equals(string.Empty) && !notesTB.Text.Equals("Start writing from here..."))
{
DNBSNData.NoteInfo newNote = new DNBSNData.NoteInfo();
newNote.id = GenerateId();
newNote.gotoDate = noteDateDP.SelectedDate.Value;
newNote.note = notesTB.Text;
DNBSNDb.DbInteraction.DoEnterNewNote(newNote);
notesTB.Clear();
noteSuccessMsgLvl.Content = "Event Submited";
fetchNoteData();
}
else
{
noteSuccessMsgLvl.Content = "Please Enter Proper Note";
}
}
private void goToDateBtn_Click(object sender, RoutedEventArgs e)
{
noteControlsTab.SelectedIndex = 1;
NoteInfo noteinfo = new NoteInfo();
noteinfo.gotoDate = goToDateDP.SelectedDate.Value;
List<NoteInfo> notes = DbInteraction.searchNoteList(noteinfo);
_allnoteCollection.Clear();
foreach (NoteInfo note in notes)
{
_allnoteCollection.Add(note);
}
}
