public AddSnippetForm(History h)
{
InitializeComponent();
var db = new DataProcess();
groups = db.LoadGroups();
_currentHistory = h;
}
void ClipboardDataChanged()
{
IDataObject iData = Clipboard.GetDataObject();
if (iData == null) { MessageBox.Show("Null!"); }
try
{
if (iData.GetDataPresent(DataFormats.Text))
{
var content = (string)iData.GetData(DataFormats.Text);
if (content == null || content == "") { return; }
var history = new History();
history.Content = content;
history.CreatedAt = DateTime.Now;
var db = new DataProcess();
db.SaveHistory(history);
UpdateTrayMenu();
}
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
//throw;
}
}
public List<History> LoadSnippetsInGroup(Group group)
{
List<History> list = new List<History>();
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
var sql = "SELECT * FROM histories WHERE group_id=@group_id ORDER BY `id` DESC";
command.CommandText = sql;
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = "@group_id";
parameter.DbType = DbType.Int32;
parameter.Direction = ParameterDirection.Input;
parameter.Value = group.Id;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
var history = new History();
history.Id = reader.GetInt32(0);
history.Name = "" + reader.GetValue(1);
history.Content = "" + reader.GetValue(2);
//obj 3, group_id 4
history.GroupID = reader.GetInt32(4);
history.CreatedAt = reader.GetDateTime(5);
list.Add(history);
}
}
}
return list;
}
public void UpdateSnippet(History history, Group group, string content, string name = "")
{
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "Update Histories SET name=@name, content=@content, group_id=@group_id WHERE id=@hid";
SQLiteParameter nameParam = new SQLiteParameter();
nameParam.ParameterName = "@name";
nameParam.DbType = DbType.String;
nameParam.Direction = ParameterDirection.Input;
nameParam.Value = name;
command.Parameters.Add(nameParam);
SQLiteParameter contentParam = new SQLiteParameter();
contentParam.ParameterName = "@content";
contentParam.DbType = DbType.String;
contentParam.Direction = ParameterDirection.Input;
contentParam.Value = content;
command.Parameters.Add(contentParam);
SQLiteParameter gidParam = new SQLiteParameter();
gidParam.ParameterName = "@group_id";
gidParam.DbType = DbType.Int32;
gidParam.Direction = ParameterDirection.Input;
gidParam.Value = group.Id;
command.Parameters.Add(gidParam);
SQLiteParameter hidParam = new SQLiteParameter();
hidParam.ParameterName = "@hid";
hidParam.DbType = DbType.Int32;
hidParam.Direction = ParameterDirection.Input;
hidParam.Value = history.Id;
command.Parameters.Add(hidParam);
command.ExecuteNonQuery();
}
}
}
public List<History> LoadHistories()
{
List<History> list = new List<History>();
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
var sql = "SELECT * FROM histories WHERE `group_id` IS NULL ORDER BY `id` DESC";
command.CommandText = sql;
var reader = command.ExecuteReader();
while (reader.Read())
{
var history = new History();
history.Id = reader.GetInt32(0);
history.Name = "" + reader.GetValue(1);
history.Content = "" + reader.GetValue(2);
// obj 3, group_id 4
history.CreatedAt = reader.GetDateTime(5);
list.Add(history);
}
}
}
return list;
}
public History LoadHistoryOfId(int hid)
{
History history = new History();
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
var sql = "SELECT * FROM Histories WHERE id=@hid";
command.CommandText = sql;
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = "@hid";
parameter.DbType = DbType.Int32;
parameter.Direction = ParameterDirection.Input;
parameter.Value = hid;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
history.Id = reader.GetInt32(0);
history.Name = "" + reader.GetValue(1);
history.Content = "" + reader.GetValue(2);
// obj 3, group_id 4
history.GroupID = reader.GetInt32(4);
history.CreatedAt = reader.GetDateTime(5);
}
}
}
return history;
}
public History LatestHistory()
{
History history = new History();
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
var sql = "SELECT * FROM Histories WHERE `group_id` IS NULL ORDER BY `id` DESC LIMIT 1";
command.CommandText = sql;
var reader = command.ExecuteReader();
while (reader.Read())
{
history.Id = reader.GetInt32(0);
history.Name = "" + reader.GetValue(1);
history.Content = "" + reader.GetValue(2);
// obj 3, group_id 4
history.CreatedAt = reader.GetDateTime(5);
}
}
}
return history;
}
public void DeleteHistory(History h)
{
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "DELETE FROM Histories WHERE id=@hid";
SQLiteParameter contentParam = new SQLiteParameter();
contentParam.ParameterName = "@hid";
contentParam.DbType = DbType.Int32;
contentParam.Direction = ParameterDirection.Input;
contentParam.Value = h.Id;
// Add the parameter to the Parameters collection.
command.Parameters.Add(contentParam);
command.ExecuteNonQuery();
}
}
}
public void SaveHistory(History history)
{
var latest = LatestHistory();
if (latest.Content == history.Content) {
return; // Do not
}
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "INSERT INTO Histories ('content', 'created_at') values (@content, @timestamp)";
SQLiteParameter contentParam = new SQLiteParameter();
contentParam.ParameterName = "@content";
contentParam.DbType = DbType.String;
contentParam.Direction = ParameterDirection.Input;
contentParam.Value = history.Content;
// Add the parameter to the Parameters collection.
command.Parameters.Add(contentParam);
SQLiteParameter tsParam = new SQLiteParameter();
tsParam.ParameterName = "@timestamp";
tsParam.DbType = DbType.Time;
tsParam.Direction = ParameterDirection.Input;
tsParam.Value = history.CreatedAt;
// Add the parameter to the Parameters collection.
command.Parameters.Add(tsParam);
command.ExecuteNonQuery();
}
}
}