public void DeleteGroup(Group group)
{
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "DELETE FROM Groups WHERE id=@gid; DELETE FROM Histories WHERE group_id=@gid";
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = "@gid";
parameter.DbType = DbType.Int32;
parameter.Direction = ParameterDirection.Input;
parameter.Value = group.Id;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
}
public void RenameGroup(Group group, string name)
{
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = string.Format("UPDATE Groups SET name=@name WHERE id={0}", group.Id);
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = "@name";
parameter.DbType = DbType.String;
parameter.Direction = ParameterDirection.Input;
parameter.Value = name;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
command.ExecuteNonQuery();
}
}
}
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 void SaveSnippet(Group group, string content, string name = "")
{
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
command.CommandText = "INSERT INTO Histories ('name', 'content', 'group_id', 'created_at') values (@name, @content, @group_id, @timestamp)";
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 tsParam = new SQLiteParameter();
tsParam.ParameterName = "@timestamp";
tsParam.DbType = DbType.Time;
tsParam.Direction = ParameterDirection.Input;
tsParam.Value = new DateTime();
command.Parameters.Add(tsParam);
command.ExecuteNonQuery();
}
}
}
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 Group LoadGroup(string name)
{
Group group = null;
using (var conn = new SQLiteConnection(DataSource))
{
conn.Open();
using (SQLiteCommand command = new SQLiteCommand(conn))
{
var sql = "SELECT * FROM groups WHERE name=@name";
command.CommandText = sql;
SQLiteParameter parameter = new SQLiteParameter();
parameter.ParameterName = "@name";
parameter.DbType = DbType.String;
parameter.Direction = ParameterDirection.Input;
parameter.Value = name;
// Add the parameter to the Parameters collection.
command.Parameters.Add(parameter);
var reader = command.ExecuteReader();
while (reader.Read())
{
group = new Group(reader.GetInt32(0), "" + reader.GetValue(1));
}
}
}
return group;
}
