private void openCSV_Click(object sender, EventArgs e)
{
TemperatureRecords?.Clear();
CsvFormats?.Clear();
try
{
using (var ofd = new OpenFileDialog()
{
Filter = "CSV|*.csv", ValidateNames = true, Multiselect = false
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
TemperatureRecords?.Clear();
using (var reader = new StreamReader(ofd.FileName))
using (var csv = new CsvReader(reader))
{
var records = csv.GetRecords <CsvFormat>();
CsvFormats = records.ToList();
dataGridView1.DataSource = CsvFormats;
FileName = ofd.FileName;
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void Insert_Click(object sender, EventArgs e)
{
if (FileName == null)
{
MessageBox.Show("Select CSV File First");
}
else
{
var query = "INSERT INTO temperature (ID,時間,第1段溫度顯示值,第2段溫度顯示值,第3段溫度顯示值,第4段溫度顯示值,第5段溫度顯示值,第6段溫度顯示值) VALUES (@ID,@時間,@第1段溫度顯示值,@第2段溫度顯示值,@第3段溫度顯示值,@第4段溫度顯示值,@第5段溫度顯示值,@第6段溫度顯示值)";
using (var cn = new SQLiteConnection($"Data Source = {DbPath}"))
{
cn.Open();
try
{
using (var trans = cn.BeginTransaction())
{
foreach (var item in CsvFormats)
{
var key = Guid.NewGuid().ToString();
if (item == null)
{
continue;
}
var affectedRows = cn.Execute(query, new { ID = key, 時間 = item.時間, 第1段溫度顯示值 = item.第1段溫度顯示值, 第2段溫度顯示值 = item.第2段溫度顯示值, 第3段溫度顯示值 = item.第3段溫度顯示值, 第4段溫度顯示值 = item.第4段溫度顯示值, 第5段溫度顯示值 = item.第5段溫度顯示值, 第6段溫度顯示值 = item.第6段溫度顯示值 });
}
trans.Commit();
}
MessageBox.Show("Insert Completed");
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
dataGridView1.ClearSelection();
TemperatureRecords?.Clear();
CsvFormats?.Clear();
using (var cn = new SQLiteConnection($"Data Source = {DbPath}"))
{
var list = cn.Query <TemperatureRecord>(
"select * from temperature");
var csvFormat = cn.Query <CsvFormat>(
"select * from temperature");
TemperatureRecords = list.ToList();
CsvFormats = csvFormat.ToList();
dataGridView1.DataSource = TemperatureRecords;
dataGridView1.Columns["ID"].Visible = false;
dataGridView1.ReadOnly = true;
}
}
}
