public string IfFoundNoteName(MMNote theNote)
{
string retId = "";
bool shouldClose = false;
// Is the database already open?
if (conn.State != ConnectionState.Open)
{
shouldClose = true;
conn.Open();
}
// Execute query
using (var command = conn.CreateCommand())
{
// Create new command
//INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)
command.CommandText = "SELECT Id from [tblNotes] where UPPER(Name) = '" + theNote.Name.ToUpper() + "'";
try
{
//await DataStore.GetItemsAsync(true);
using (var reader = command.ExecuteReader())
{
if(reader.HasRows)
{
retId = Convert.ToString(reader[0]);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
}
}
// Should we close the connection to the database
if (shouldClose)
{
conn.Close();
}
return retId;
}
public void InsertNote(MMNote theNote)
{
if (IsBusy)
return;
IsBusy = true;
bool shouldClose = false;
// Is the database already open?
if (conn.State != ConnectionState.Open)
{
shouldClose = true;
conn.Open();
}
// Execute query
using (var command = conn.CreateCommand())
{
// Create new command
//INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)
command.CommandText = "INSERT into tblNotes(Name,Observations) VALUES('" + theNote.Name + "','" + theNote.Observations+ "')";
try
{
int affected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
IsBusy = false;
}
}
// Should we close the connection to the database
if (shouldClose)
{
conn.Close();
}
}
public void LoadNotes()
{
if (IsBusy)
return;
IsBusy = true;
bool shouldClose = false;
// Is the database already open?
if (conn.State != ConnectionState.Open)
{
shouldClose = true;
conn.Open();
}
// Execute query
using (var command = conn.CreateCommand())
{
// Create new command
command.CommandText = "SELECT Id,Name,Observations from [tblNotes] ORDER by Id DESC";
try
{
//await DataStore.GetItemsAsync(true);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
MMNote item = new MMNote();
// Pull values back into class
item.Id = Convert.ToString(reader[0]);
item.Name = Convert.ToString(reader[1]);
item.Observations = Convert.ToString(reader[2]);
NoteItems.Add(item);
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
IsBusy = false;
}
}
// Should we close the connection to the database
if (shouldClose)
{
conn.Close();
}
}
public void UpdateNote(MMNote theNote)
{
if (IsBusy)
return;
IsBusy = true;
bool shouldClose = false;
// Is the database already open?
if (conn.State != ConnectionState.Open)
{
shouldClose = true;
conn.Open();
}
// Execute query
using (var command = conn.CreateCommand())
{
// Create new command
//INSERT INTO klant(klant_id,naam,voornaam) VALUES(@param1,@param2,@param3)
command.CommandText = "Update tblNotes SET NAME = '" + theNote.Name + "'" +
" ,Observations = '" + theNote.Observations + "'" + " WHERE Id = '" + theNote.Id + "'";
try
{
int affected = command.ExecuteNonQuery();
if (affected <=0)
{
}
}
catch (Exception ex)
{
Debug.WriteLine(ex);
}
finally
{
IsBusy = false;
}
}
// Should we close the connection to the database
if (shouldClose)
{
conn.Close();
}
}