/// <summary>
/// 查询数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void QueryData_Click(object sender, RoutedEventArgs e)
{
List <StudentScore> result = new List <StudentScore>();
var queryDataStartTime = DateTime.Parse(queryDataStartTimeDatePicker.Text);
var queryDataEndTime = DateTime.Parse(queryDataEndTimeDatePicker.Text);
//分库分表查询，再合并
//这里没有考虑时分秒维度的数据查询
var queryDate = new DateTime(queryDataStartTime.Year, queryDataStartTime.Month, queryDataStartTime.Day);
while (queryDate <= queryDataEndTime)
{
var suffix = GetSuffix(queryDate);
var tempTableName = $"{tableName}_{suffix}";
sqliteTool = new SqliteTool();
var dbName = $"HistoricalDataManageDb_{queryDate.ToString("yyyy")}";
//不必每次都重新连接，可以优化
sqliteTool.ConnectToDatabase(filePath, dbName);
//查询数据表是否存在
var tableCount = sqliteTool.ExecuteQueryCount($"SELECT name FROM sqlite_master WHERE type='table' AND name='{tempTableName}';");
if (tableCount > 0)
{
//该用SqlParameter请不要吝惜，使用sql时需要时刻考虑sql注入的危险 ————我只是懒
var scores = sqliteTool.Query <StudentScore>($"select Name,Score,CreateTime from {tempTableName} order by Name asc");
result.AddRange(scores);
}
queryDate = queryDate.AddDays(1);
}
DataContext = result;
}
/// <summary>
/// 录入数据
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void InsertData_Click(object sender, RoutedEventArgs e)
{
sqliteTool = new SqliteTool();
var insertDataTime = DateTime.Parse(insertDataTimeDatePicker.Text);
var insertDataCount = int.Parse(insertDataCountTextBox.Text);
//按年份分库
var dbName = $"HistoricalDataManageDb_{insertDataTime.ToString("yyyy")}";
sqliteTool.CreateNewDatabase(filePath, dbName);
sqliteTool.ConnectToDatabase(filePath, dbName);
//按天分表
var suffix = GetSuffix(insertDataTime);
CreateTable(suffix);
InsertData(insertDataCount, suffix, insertDataTime);
}