智慧大棚数据库版

创建一个SMartBigHouse数据库

在数据库创建一个表用来存储数据 这边将id设为主键并将标识增量设为1

搭建Winfrom

搭建历史查询界面

串口数据，(这边是用的一个虚拟的串口工具，需要的话私)

 ModbusSerialMaster master;DataPointCollection wenduValues; //温度DataPointCollection shiduValues; //湿度DataPointCollection yangqiValues; //氧气DataPointCollection fengsuValues; //风速public Form1(){InitializeComponent();#region 串口数据serialPort1.PortName = "COM2";serialPort1.BaudRate = 9600;serialPort1.Parity = System.IO.Ports.Parity.None;serialPort1.DataBits = 8;serialPort1.StopBits =(StopBits)1;#endregion//使用nmodbus4第三方进行数据读取master = ModbusSerialMaster.CreateRtu(serialPort1);serialPort1.Open();//初始化图表数据wenduValues = chart1.Series[0].Points;shiduValues = chart1.Series[1].Points;yangqiValues = chart1.Series[2].Points;fengsuValues = chart1.Series[3].Points;//设置数据点文本 #VALY :Y轴的数据值chart1.Series[0].Label = "#VALY℃";chart1.Series[1].Label = "#VALY%RH";chart1.Series[2].Label = "#VALY%VOL";chart1.Series[3].Label = "#VALYM/S";// 设置鼠标悬浮时候显示文本，chart1.Series[0].XValueType = ChartValueType.String;chart1.Series[1].XValueType = ChartValueType.String;chart1.Series[2].XValueType = ChartValueType.String;chart1.Series[3].XValueType = ChartValueType.String;chart1.Series[0].ToolTip = "#VALX:#VALY℃";//11-12-30:28℃chart1.Series[1].ToolTip = "#VALX:#VALY%RH";chart1.Series[2].ToolTip = "#VALX:#VALY%VOL";chart1.Series[3].ToolTip = "#VALX:#VALYM/S";//设置x可以使用鼠标选中进行局部放大效果chart1.ChartAreas[0].CursorX.IsUserEnabled = true;//是否允许用户操作x轴chart1.ChartAreas[0].CursorX.IsUserSelectionEnabled = true;//是否允许用户选择滚动条；//设置滚动条样式chart1.ChartAreas[0].AxisX.ScrollBar.BackColor = Color.Red; //滚动条的整体颜色chart1.ChartAreas[0].AxisX.ScrollBar.ButtonColor = Color.Green;//滚动条按钮的颜色}

开始停止

private void button2_Click(object sender, EventArgs e)
{if (timer1.Enabled){timer1.Stop();}else{timer1.Start();}
}

定时器的事件

 double? lastWendu = null; //上一个温度double? lastShiDu = null;double? lastYangQi = null;double? lastFengSu = null;private void timer1_Tick(object sender, EventArgs e){DateTime d1 = DateTime.Now;string time = d1.ToString("HH-mm-ss");double ? nowWendu = null; //当前温度double ? nowShiDu = null;double ? nowYangQi = null;double? nowFengSu = null;if (checkBox1.Checked) //温度选中{//ReadHoldingRegisters 读取寄存器的数据ushort[] values =  master.ReadHoldingRegisters(1, 0x000, 1);nowWendu = values[0] / 10;//当前温度wenduValues.AddXY(time, nowWendu);lastWendu = nowWendu ?? lastWendu; //记录当前温度，}else //温度未选中{int index =  wenduValues.AddXY(time,lastWendu); //添加x轴时间 y轴数据 index添加点的索引值DataPoint point =  wenduValues[index]; //找到隐藏点point.Color = Color.Transparent;//设置为透明颜色}if (wenduValues.Count == 11){wenduValues.RemoveAt(0); //保留界面只有20个点}if (checkBox2.Checked) //湿度选中{//ReadHoldingRegisters 读取寄存器的数据ushort[] values = master.ReadHoldingRegisters(1, 0x001, 1);nowShiDu = values[0] / 10;shiduValues.AddXY(time, nowShiDu);lastShiDu = nowShiDu ?? lastShiDu;}else //湿度选中{int index = shiduValues.AddXY(time, lastShiDu); //添加x轴时间 y轴数据 index添加点的索引值DataPoint point = shiduValues[index]; //找到隐藏点point.Color = Color.Transparent;//设置为透明颜色}if (shiduValues.Count == 11){shiduValues.RemoveAt(0); //保留界面只有20个点}if (checkBox3.Checked) //湿度选中{//ReadHoldingRegisters 读取寄存器的数据ushort[] values = master.ReadHoldingRegisters(1, 0x002, 1);nowYangQi = values[0] / 10;yangqiValues.AddXY(time, nowYangQi);lastYangQi = nowYangQi ?? lastYangQi;}else //湿度选中{int index = yangqiValues.AddXY(time, lastYangQi); //添加x轴时间 y轴数据 index添加点的索引值DataPoint point = yangqiValues[index]; //找到隐藏点point.Color = Color.Transparent;//设置为透明颜色}if (yangqiValues.Count == 11){yangqiValues.RemoveAt(0); //保留界面只有20个点}if (checkBox4.Checked) //湿度选中{//ReadHoldingRegisters 读取寄存器的数据ushort[] values = master.ReadHoldingRegisters(1, 0x003, 1);nowFengSu = values[0] / 10;fengsuValues.AddXY(time, nowFengSu);lastFengSu = nowFengSu ?? lastFengSu;}else //湿度选中{int index = fengsuValues.AddXY(time, lastFengSu); //添加x轴时间 y轴数据 index添加点的索引值DataPoint point = fengsuValues[index]; //找到隐藏点point.Color = Color.Transparent;//设置为透明颜色}if (fengsuValues.Count == 11){fengsuValues.RemoveAt(0); //保留界面只有20个点}//添加到数据库里面//1 连接数据库 数据库的配置在App.config进行配置//2 创建数据库操作文件，sqlhelper文件 把数据库的增删改查操作封装的sqlhelper文件//3 在使用数据库操作的时候调用sqlhelper方法即可//组织sql语句  update("")string sql = "insert into Log (time,wendu,shidu,o2,fengsu) values('{0}',{1},{2},{3},{4})";string w = nowWendu is null?"NULL":((nowWendu??0)*10).ToString(); //string s = nowShiDu is null ? "NULL" : ((nowShiDu ?? 0) * 10).ToString();string y = nowYangQi is null ? "NULL" : ((nowYangQi ?? 0) * 10).ToString();string f = nowFengSu is null ? "NULL" : ((nowFengSu ?? 0) * 10).ToString();sql = string.Format(sql, d1.ToString(), w, s, y, f);SqlHelper.Update(sql);}

//添加到数据库里面
//1 连接数据库 数据库的配置在App.config进行配置
//2 创建数据库操作文件，sqlhelper文件 把数据库的增删改查操作封装的sqlhelper文件
//3 在使用数据库操作的时候调用sqlhelper方法即可

 //组织sql语句  update("")string sql = "insert into Log (time,wendu,shidu,o2,fengsu) values('{0}',{1},{2},{3},{4})";string w = nowWendu is null?"NULL":((nowWendu??0)*10).ToString(); //string s = nowShiDu is null ? "NULL" : ((nowShiDu ?? 0) * 10).ToString();string y = nowYangQi is null ? "NULL" : ((nowYangQi ?? 0) * 10).ToString();string f = nowFengSu is null ? "NULL" : ((nowFengSu ?? 0) * 10).ToString();sql = string.Format(sql, d1.ToString(), w, s, y, f);SqlHelper.Update(sql);

跳转功能

private void button4_Click(object sender, EventArgs e)
{Form2 f2 = new Form2();f2.Show();
}

这边添加一个SqlHelper封装方法

internal class SqlHelper
{// 数据库连接信息，需要读取项目配置中的数据库连接信息//点击引用 ---》添加引用---》搜索ConfigurationManager ---》找到system.ConfigurationManager --->确定即可private static string connString = ConfigurationManager.ConnectionStrings["Serial"].ConnectionString;/// <summary>/// 根据传入的Sql执行第一行第一列的查询操作/// </summary>/// <param name="sql">要执行的Sql</param>/// <returns>第一行第一列的结果，其他数据将会被忽略，如果没有查询到任何数据返回null</returns>public static object GetSingleValue(string sql){using (SqlConnection conn = new SqlConnection(connString)){using (SqlCommand cmd = new SqlCommand(sql, conn)){try{conn.Open();object res = cmd.ExecuteScalar();return res;}catch (Exception ex){// 记录日志File.AppendAllText("sqlError.log", $"[{DateTime.Now}] (SQL：{sql}) {ex.Message}");throw ex;}}}}/// <summary>/// 根据传入的Sql执行第一行第一列的查询操作/// </summary>/// <param name="sql">要执行的Sql</param>/// <param name="parameters">给Sql中添加的参数列表</param>/// <returns>第一行第一列的结果，其他数据将会被忽略，如果没有查询到任何数据返回null</returns>public static object GetSingleValue(string sql, SqlParameter[] parameters){using (SqlConnection conn = new SqlConnection(connString)){using (SqlCommand cmd = new SqlCommand(sql, conn)){// 添加参数cmd.Parameters.AddRange(parameters);try{conn.Open();object res = cmd.ExecuteScalar();return res;}catch (Exception ex){// 记录日志File.AppendAllText("sqlError.log", $"[{DateTime.Now}] (SQL：{sql}) {ex.Message}");throw ex;}}}}/// <summary>/// 执行增删改操作/// </summary>/// <param name="sql">要执行的Sql</param>/// <returns>受影响的行数</returns>/// Update("insert into")public static int Update(string sql){using (SqlConnection conn = new SqlConnection(connString)){using (SqlCommand cmd = new SqlCommand(sql, conn)){try{conn.Open();int res = cmd.ExecuteNonQuery();return res;}catch (Exception ex){// 记录日志File.AppendAllText("sqlError.log", $"[{DateTime.Now}] (SQL：{sql}) {ex.Message}");throw ex;}}}}/// <summary>/// 执行增删改操作/// </summary>/// <param name="sql">要执行的Sql</param>/// <param name="parameters">Sql中携带的参数</param>/// <returns>受影响的行数</returns>public static int Update(string sql, SqlParameter[] parameters){using (SqlConnection conn = new SqlConnection(connString)){using (SqlCommand cmd = new SqlCommand(sql, conn)){cmd.Parameters.AddRange(parameters); // 添加参数try{conn.Open();int res = cmd.ExecuteNonQuery();return res;}catch (Exception ex){// 记录日志File.AppendAllText("sqlError.log", $"[{DateTime.Now}] (SQL：{sql}) {ex.Message}");throw ex;}}}}/// <summary>/// 执行查询操作/// </summary>/// <param name="sql">要执行的sql</param>/// <returns>查询结果的SqlDataReader</returns>public static SqlDataReader GetValue(string sql){SqlConnection conn = new SqlConnection(connString);SqlCommand cmd = new SqlCommand(sql, conn);try{conn.Open();SqlDataReader res = cmd.ExecuteReader();return res;}catch (Exception ex){// 记录日志File.AppendAllText("sqlError.log", $"[{DateTime.Now}] (SQL：{sql}) {ex.Message}");throw ex;}}
}

历史查询界面

DataPointCollection wenduValues; //温度
DataPointCollection shiduValues; //湿度
DataPointCollection yangqiValues; //氧气
DataPointCollection fengsuValues; //风速
public Form2()
{InitializeComponent();DateTime d1 = DateTime.Now;d1 = d1.AddHours(24-d1.Hour); //24-当前小时  d1 = d1.AddMinutes(-d1.Minute);d1 = d1.AddSeconds(-d1.Second);dateTimePicker1.MaxDate = d1;//结束时间最大值的要求 :开始时间的后的一天dateTimePicker2.MaxDate = d1;//首次触发dateTimePicker1_ValueChanged 手动调用一次dateTimePicker1_ValueChanged(null, null);dataGridView1.AutoGenerateColumns = false;// 表格禁止自动生成列//初始化图表数据wenduValues = chart1.Series[0].Points;shiduValues = chart1.Series[1].Points;yangqiValues = chart1.Series[2].Points;fengsuValues = chart1.Series[3].Points;//设置数据点文本 #VALY :Y轴的数据值chart1.Series[0].Label = "#VALY℃";chart1.Series[1].Label = "#VALY%RH";chart1.Series[2].Label = "#VALY%VOL";chart1.Series[3].Label = "#VALYM/S";// 设置鼠标悬浮时候显示文本，chart1.Series[0].XValueType = ChartValueType.String;chart1.Series[1].XValueType = ChartValueType.String;chart1.Series[2].XValueType = ChartValueType.String;chart1.Series[3].XValueType = ChartValueType.String;chart1.Series[0].ToolTip = "#VALX:#VALY℃";//11-12-30:28℃chart1.Series[1].ToolTip = "#VALX:#VALY%RH";chart1.Series[2].ToolTip = "#VALX:#VALY%VOL";chart1.Series[3].ToolTip = "#VALX:#VALYM/S";//设置x可以使用鼠标选中进行局部放大效果chart1.ChartAreas[0].CursorX.IsUserEnabled = true;//是否允许用户操作x轴chart1.ChartAreas[0].CursorX.IsUserSelectionEnabled = true;//是否允许用户选择滚动条；//设置滚动条样式chart1.ChartAreas[0].AxisX.ScrollBar.BackColor = Color.Red; //滚动条的整体颜色chart1.ChartAreas[0].AxisX.ScrollBar.ButtonColor = Color.Green;//滚动条按钮的颜色}

查询功能

清空之前的数据

 wenduValues.Clear();shiduValues.Clear();yangqiValues.Clear();fengsuValues.Clear();

1查询数据

string sql = $"select id,time,wendu,shidu,o2,fengsu from Log where time >='{dateTimePicker1.Value}' and time<='{dateTimePicker2.Value}'";List<Log> list = new List<Log>();

3调用查询数据库方法 进行list添加数据

SqlDataReader sr =  SqlHelper.GetValue(sql); //读取一条之后 放到sr对象中while (sr.Read()) // 如果能读到数据{list.Add(new Log(){id = Convert.ToInt16(sr["id"]),time = Convert.ToDateTime(sr["time"]),// DBNull 数据的null、 如果数据库wendu为空，表格显示未记录，如果不为空 取出除以10wendu = sr["wendu"] is DBNull ? "未记录" : ((Convert.ToInt16(sr["wendu"]) / 10).ToString()),shidu = sr["shidu"] is DBNull ? "未记录" : ((Convert.ToInt16(sr["shidu"]) / 10).ToString()),o2 = sr["o2"] is DBNull ? "未记录" : ((Convert.ToInt16(sr["o2"]) / 10).ToString()),fengsu = sr["fengsu"] is DBNull ? "未记录" : ((Convert.ToInt16(sr["fengsu"]) / 10).ToString()),}); }sr.Close();dataGridView1.DataSource = list; //设置表格数据源

添加图表数据 把查询到list数据添加到图表中

    for(int i = 0; i < list.Count; i++){Log log = list[i]; //遍历string time = log.time.ToString(); //取时间if(log.wendu == "未记录" && wenduValues.Count>0) //温度未被存到数据库里面，{// 设置点为透明颜色//wenduValues[i - 1].YValues 添加前一个点的y坐标 wenduValues.AddXY(time, wenduValues[i - 1].YValues[0]);DataPoint p = wenduValues[i];p.Color = Color.Transparent;// 设置为透明颜色}else  //温度有数据 把数据添加图标上{wenduValues.AddXY(time, log.wendu); //添加点}if (log.shidu == "未记录" && shiduValues.Count > 0) //温度未被存到数据库里面，{// 设置点为透明颜色//wenduValues[i - 1].YValues 添加前一个点的y坐标 shiduValues.AddXY(time, shiduValues[i - 1].YValues[0]);DataPoint p = shiduValues[i];p.Color = Color.Transparent;// 设置为透明颜色}else  //温度有数据 把数据添加图标上{shiduValues.AddXY(time, log.shidu); //添加点}if (log.o2 == "未记录" && yangqiValues.Count > 0) //温度未被存到数据库里面，{// 设置点为透明颜色//wenduValues[i - 1].YValues 添加前一个点的y坐标 yangqiValues.AddXY(time, yangqiValues[i - 1].YValues[0]);DataPoint p = yangqiValues[i];p.Color = Color.Transparent;// 设置为透明颜色}else  //温度有数据 把数据添加图标上{yangqiValues.AddXY(time, log.o2); //添加点}if (log.fengsu == "未记录" && fengsuValues.Count > 0) //温度未被存到数据库里面，{// 设置点为透明颜色//wenduValues[i - 1].YValues 添加前一个点的y坐标 fengsuValues.AddXY(time, fengsuValues[i - 1].YValues[0]);DataPoint p = fengsuValues[i];p.Color = Color.Transparent;// 设置为透明颜色}else  //温度有数据 把数据添加图标上{fengsuValues.AddXY(time, log.fengsu); //添加点}}
}

设置结束时间 最小值是在开始时间的30分钟之后，
            //MinDate 最小值
            // AddMinutes 添加30分钟

private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
{//设置结束时间 最小值是在开始时间的30分钟之后，//MinDate 最小值// AddMinutes 添加30分钟dateTimePicker2.MinDate = dateTimePicker1.Value.AddMinutes(30);
}

Log 包含每一条数据信息

public class Log
{public DateTime time { get; set; }public int id { get; set; }public string wendu { get; set; }public string shidu { get; set; }public string o2 { get; set; }public string fengsu { get; set; }
}