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 UpdateGridView()
{
dataGridView1.ClearSelection();
TemperatureRecords?.Clear();
TemperatureRecords = DatabaseHelper.Get();
dataGridView1.DataSource = TemperatureRecords;
dataGridView1.Columns["ID"].Visible = false;
dataGridView1.ReadOnly = true;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
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;
}
}
}
private void database_Click(object sender, EventArgs e)
{
dataGridView1.ClearSelection();
TemperatureRecords?.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;
//dataGridView1.Enabled = false;
}
}
private void btnCsv_Click(object sender, EventArgs e)
{
TemperatureRecords?.Clear();
try
{
using (var ofd = new OpenFileDialog()
{
Filter = "CSV|*.csv", ValidateNames = true, Multiselect = false
})
{
if (ofd.ShowDialog() == DialogResult.OK)
{
var temp = CsvHelper.Import(ofd.FileName);
TemperatureRecords = new List <TemperatureRecord>();
foreach (var item in temp)
{
var key = Guid.NewGuid().ToString();
//MessageBox.Show(key);
TemperatureRecords.Add(new TemperatureRecord()
{
ID = key,
時間 = Convert.ToDateTime(item.時間),
第1段溫度顯示值 = Convert.ToDouble(item.第1段溫度顯示值),
第2段溫度顯示值 = Convert.ToDouble(item.第2段溫度顯示值),
第3段溫度顯示值 = Convert.ToDouble(item.第3段溫度顯示值),
第4段溫度顯示值 = Convert.ToDouble(item.第4段溫度顯示值),
第5段溫度顯示值 = Convert.ToDouble(item.第5段溫度顯示值),
第6段溫度顯示值 = Convert.ToDouble(item.第6段溫度顯示值)
});
}
dataGridView1.DataSource = TemperatureRecords;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
dataGridView1.Columns["ID"].Visible = false;
FileName = ofd.FileName;
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void delete_Click(object sender, EventArgs e)
{
if (DeleteRowSelect == null)
{
MessageBox.Show("Please select the row first !");
}
if (DeleteRowSelect != null)
{
using (var cn = new SQLiteConnection($"Data Source = {DbPath}"))
{
cn.Open();
try
{
var query = $"DELETE FROM temperature WHERE ID = @ID";
var result = cn.Execute(query, new
{
ID = DeleteRowSelect
});
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
dataGridView1.ClearSelection();
TemperatureRecords?.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;
}
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
if (TemperatureRecords == null)
{
MessageBox.Show("You have to open database first !");
}
else
{
TemperatureRecords?.Clear();
if (string.IsNullOrWhiteSpace(txtFrom.Text) && string.IsNullOrWhiteSpace(txtTo.Text))
{
MessageBox.Show("Please select Search item first !");
}
else
{
if ((SearchColName)comboBox1.SelectedItem == SearchColName.時間)
{
if (DateTime.TryParse(txtFrom.Text, out DateTime timeFrom) && DateTime.TryParse(txtTo.Text, out DateTime timeTo))
{
TemperatureRecords = DatabaseHelper.Get(timeFrom, timeTo, SearchColName.時間.ToString());
dataGridView1.DataSource = TemperatureRecords;
}
else
{
MessageBox.Show("DateTime format error !");
}
}
else
{
TemperatureRecords = DatabaseHelper.Get(comboBox1.SelectedItem.ToString(), txtFrom.Text, txtTo.Text);
dataGridView1.DataSource = TemperatureRecords;
}
dataGridView1.Columns["ID"].Visible = false;
dataGridView1.ReadOnly = true;
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
}
}
}
private void search_Click(object sender, EventArgs e)
{
if (TemperatureRecords == null)
{
MessageBox.Show("You have to open database first !");
}
if (comboBox1.SelectedIndex == 0)
{
var select = comboBox1.SelectedItem;
var timeFrom = Convert.ToDateTime(txtFrom.Text.ToString());
var timeTo = Convert.ToDateTime(txtTo.Text.ToString());
var selectList = TemperatureRecords.Where(x => x.時間 >= timeFrom && x.時間 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
if (comboBox1.SelectedIndex == 1)
{
var timeFrom = Convert.ToDouble(txtFrom.Text);
var timeTo = Convert.ToDouble(txtTo.Text);
var selectList = TemperatureRecords.Where(x => x.第1段溫度顯示值 >= timeFrom && x.第1段溫度顯示值 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
if (comboBox1.SelectedIndex == 2)
{
var timeFrom = Convert.ToDouble(txtFrom.Text);
var timeTo = Convert.ToDouble(txtTo.Text);
var selectList = TemperatureRecords.Where(x => x.第2段溫度顯示值 >= timeFrom && x.第2段溫度顯示值 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
if (comboBox1.SelectedIndex == 3)
{
var timeFrom = Convert.ToDouble(txtFrom.Text);
var timeTo = Convert.ToDouble(txtTo.Text);
var selectList = TemperatureRecords.Where(x => x.第3段溫度顯示值 >= timeFrom && x.第3段溫度顯示值 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
if (comboBox1.SelectedIndex == 4)
{
var timeFrom = Convert.ToDouble(txtFrom.Text);
var timeTo = Convert.ToDouble(txtTo.Text);
var selectList = TemperatureRecords.Where(x => x.第4段溫度顯示值 >= timeFrom && x.第4段溫度顯示值 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
if (comboBox1.SelectedIndex == 5)
{
var timeFrom = Convert.ToDouble(txtFrom.Text);
var timeTo = Convert.ToDouble(txtTo.Text);
var selectList = TemperatureRecords.Where(x => x.第5段溫度顯示值 >= timeFrom && x.第5段溫度顯示值 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
if (comboBox1.SelectedIndex == 6)
{
var timeFrom = Convert.ToDouble(txtFrom.Text);
var timeTo = Convert.ToDouble(txtTo.Text);
var selectList = TemperatureRecords.Where(x => x.第6段溫度顯示值 >= timeFrom && x.第6段溫度顯示值 <= timeTo).ToList();
dataGridView1.DataSource = selectList;
}
}