internal void changeSubTaskName(int id, string newName)
{
newName.Replace("'", "\'");
string sql = string.Format($"UPDATE subtask SET subtask_name = '{newName}' WHERE id = {id}");
SQLiteExecutor.execute(sql);
}
public SQLiteTest()
{
db = SQLite.ConnectFile(@"C:\Temp\1.db");
db.T("DROP TABLE IF EXISTS Test1").ExecuteNonQuery();
db.T(@"
CREATE TABLE [Test1]
(
[Name] NVARCHAR(50) NOT NULL ,
[Content] NTEXT NULL,
[Index] INT NOT NULL
)").ExecuteNonQuery();
}
public SQLiteTest()
{
db = SQLite.ConnectFile( @"C:\Temp\1.db" );
db.T( "DROP TABLE IF EXISTS Test1" ).ExecuteNonQuery();
db.T( @"
CREATE TABLE [Test1]
(
[Name] NVARCHAR(50) NOT NULL ,
[Content] NTEXT NULL,
[Index] INT NOT NULL
)" ).ExecuteNonQuery();
}
internal void changeDate(int taskId, DateTime?date)
{
List <Task> list = selectById(taskId);
if (list.Count == 0)
{
return;
}
DateTime taskDate = list.ElementAt(0).TaskDate;
string sql_update = string.Format($"update task set task_date = '{date.ToString().Split(' ')[0]} {taskDate.TimeOfDay}' where id = {taskId}");
SQLiteExecutor.execute(sql_update);
}
private SubTask addParentSubTask(string sql, SubTask subTask)
{
//插入子任务
SQLiteExecutor.execute(sql);
//获取刚插入的数据返回
string sql_select = string.Format("select id from subtask order by id desc limit 1");
SQLiteDataReader sQLiteDataReader = SQLiteExecutor.select(sql);
if (!sQLiteDataReader.Read())
{
return(subTask);
}
subTask.Id = sQLiteDataReader.GetInt32(0);
return(subTask);
}
private static List <SubTask> getSubTasks(int taskId, string parentIdName)
{
List <SubTask> result = new List <SubTask>();
string sql_select = string.Format($"select id,subtask_name,subtask_state from subtask where {parentIdName} = {taskId}");
SQLiteDataReader sQLiteDataReader = SQLiteExecutor.select(sql_select);
while (sQLiteDataReader.Read())
{
SubTask subTask = new SubTask();
subTask.Id = sQLiteDataReader.GetInt32(0);
subTask.SubTaskName = sQLiteDataReader.GetString(1);
subTask.SubTaskState = sQLiteDataReader.GetInt32(2);
result.Add(subTask);
}
return(result);
}
public Task addNewTask(Task task)
{
string sql = string.Format($"INSERT INTO task(task_name,task_priority,task_state,task_description,task_date) VALUES ('{task.TaskName}','{task.Priority}','{task.TaskState}','{task.TaskDescription}','{task.TaskDate}')");
//插入子任务
SQLiteExecutor.execute(sql);
//获取刚插入的数据返回
string sql_select = string.Format("select id from task order by id desc limit 1");
SQLiteDataReader sQLiteDataReader = SQLiteExecutor.select(sql);
if (!sQLiteDataReader.Read())
{
return(task);
}
task.TaskId = sQLiteDataReader.GetInt32(0);
return(task);
}
private static List <Task> select(string sql_select)
{
SQLiteDataReader sQLiteDataReader = SQLiteExecutor.select(sql_select);
List <Task> result = new List <Task>();
while (sQLiteDataReader.Read())
{
int id = sQLiteDataReader.GetInt32(0);
string taskName = sQLiteDataReader.GetString(1);
string desc = sQLiteDataReader.GetString(4);
string dateString = sQLiteDataReader.GetString(5);
DateTime dateTime = DateTime.Now;
try {
dateTime = Convert.ToDateTime(dateString);
}
catch (Exception e) {
}
int priority = sQLiteDataReader.GetInt32(2);
Task task = new Task(id, taskName, desc, dateTime, priority);
result.Add(task);
}
return(result);
}
internal void changePriority(int taskId, int priority)
{
string sql_update = string.Format($"update task set task_priority = {priority} where id = {taskId}");
SQLiteExecutor.execute(sql_update);
}
internal void changeDescription(int taskId, string description)
{
string sql_update = string.Format($"update task set task_description = '{description}' where id = {taskId}");
SQLiteExecutor.execute(sql_update);
}
internal void changeName(int id, string newName)
{
string sql_update = string.Format($"update task set task_Name = '{newName}' where id = {id}");
SQLiteExecutor.execute(sql_update);
}
