[活动] 【DFRobot 云雀气象仪】学习笔记02（自动化获取数据并保存到本地数据库）

qzc0927 2023-11-1 23:37 楼主

## 5：本地数据库保存

数据在实际工作中应用非常广泛，数据库的产品也比较多，oracle、DB2、SQL2000、mySQL；基于嵌入式linux的数据库主要有SQLite, Firebird, Berkeley DB, eXtremeDB。这里我们主要采用sqlite3数据库，来做本地数据存储；
![image-20231101225100427](https://boreyun.oss-cn-shanghai.aliyuncs.com/image-20231101225100427.png)

这里我们首先创建一个Weather天气的数据表，表中包含温度，湿度，海拔，起亚，电压，电池，风向，风速，以及日期的数据字段，ID代表数据序列号，为自增字段。

## 6：上位机编写

我们编写上位机来自动接收数据，并将接收到的数据放入本地数据库，也就是sqlite3数据库下。上位机实用C#开发，使用visualstudio2019主要分为以下2个步骤：

1. 数据的获取
2. 连接数据库并保存

### 6.1：数据的获取

数据的获取采用串口通讯来实现，首先进行串口的开关，然后实现串口的收发，最后添加定时器，实现数据的自动化接收。

首先事串口的打开和关闭，我们使用串口组件，和按钮以及多选框来实现串口的打开和关闭。组件设计截图如下：

![image-20231101230018858](https://boreyun.oss-cn-shanghai.aliyuncs.com/image-20231101230018858.png)

串口打开和关闭的代码如下：

```C#
  private void btnSerialOpen_Click(object sender, EventArgs e)
  {
    if (serialPort.IsOpen)
    {
      //System.Windows.Forms.Application.DoEvents();
      //serialPort.Dispose();
      serialPort.Close();
      cmbSerialNo.Enabled = true;//关闭串口后可以选择串口号和波特率
      cmbBand.Enabled = true;
    }
    else
    {
      /*获取串口名字*/
      serialPort.PortName = cmbSerialNo.Text;
      /*获取波特率信息，并转化为int类型*/
      serialPort.BaudRate = Convert.ToInt32(cmbBand.Text, 10);
      try
      {
      serialPort.Open(); //打开串口
      serialPort.DataReceived += new SerialDataReceivedEventHandler(serialPort_DataReceived); //数据接收事件的方法
      cmbBand.Enabled = false;//关闭使能,无法选择
      cmbSerialNo.Enabled = false;
      }
      catch
      {
      MessageBox.Show("串口打开失败！");
      return;
      }

}
    //设置串口按钮状态
    btnSerialOpen.Text = serialPort.IsOpen ? "关闭" : "打开";
    lblSerialState.ForeColor = serialPort.IsOpen ? Color.Green : Color.Red;

toolStripStatusLabel1.Text = serialPort.IsOpen ? "状态:"+"串口已打开" : "状态:" + "串口已关闭";
   
  }
```

其次，对于串口的收发处理，使用功能函数serialPort_DataReceived来实现，具体代码为：

```C#
  private void serialPort_DataReceived(object sender, SerialDataReceivedEventArgs e)
  {

SerialPort serialPort = (SerialPort)sender;
    //将当前线程挂起50ms,缓存数据，让数据接收完毕
    System.Threading.Thread.Sleep(100);

// 读取接收缓冲区中的数据
    int bytesToRead = serialPort.BytesToRead;
    //如果有数据
    if (bytesToRead>0)
    {

byte[] buffer = new byte[bytesToRead];
      serialPort.Read(buffer, 0, bytesToRead);

// 添加数据到接收缓存区
      receiveBuffer.AddRange(buffer);

//显示接受到的数据
      Invoke(new Action(() => { rtxRecvBuff.Text += System.Text.Encoding.UTF8.GetString(receiveBuffer.ToArray()); }));
      // 处理接收缓存区中的数据
      ProcessReceivedData();
    }
  }
```

最后的添加定时器，进行自动化接收天气数据，为了有效获取数据，我们分别读取各个传感器的数据信息，使用不同的串口指令，利用状态机的形式，配合定时器，循环得到各个传感器的数据，具体代码为：

```C#
  private void weatherTimer_Tick(object sender, EventArgs e)
  {
    switch (sensorState) //写完枚举后敲回车将自动补完枚举类型
    {
      case OptCode.Init:
      timeCount++;
      if (timeCount == 1) //延时2S*10=20S
      {
        timeCount = 0;
        Weather_SQL_Insert();
        sensorState = OptCode.Temp;
      }
      break;
      case OptCode.Temp:
      GetTemp_Cmd_Send();
      break;
      case OptCode.Humi:
      GetHumi_Cmd_Send();
      break;
      case OptCode.WindSpeed:
      GetWindSpeed_Cmd_Send();
      break;
      case OptCode.WindDirection:
      GetWindDirection_Cmd_Send();
      break;
      case OptCode.Altitude:
      GetAltitude_Cmd_Send();
      break;
      case OptCode.Pressure:
      GetPressure_Cmd_Send();
      break;
      default:
      break;
    }
  }
```

### 6.2：连接数据库并保存

首先创建sql数据库类，方便数据库操作，具体实现代码如下：

```C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;

namespace SerialPortHelper
{
  class SqLiteHelper
  {
  /// <summary>
  /// 数据库连接定义
  /// </summary>
  private SQLiteConnection dbConnection;

/// <summary>
  /// SQL命令定义
  /// </summary>
  private SQLiteCommand dbCommand;

/// <summary>
  /// 数据读取定义
  /// </summary>
  private SQLiteDataReader dataReader;

/// <summary>
  /// 构造函数
  /// </summary>
  /// <param name="connectionString">连接SQLite库字符串</param>
  public SqLiteHelper(string connectionString)
  {
    try
    {
      dbConnection = new SQLiteConnection(connectionString);
      dbConnection.Open();
    }
    catch (Exception e)
    {
      Log(e.ToString());
    }
  }
  /// <summary>
  /// 执行SQL命令
  /// </summary>
  /// <returns>The query.</returns>
  /// <param name="queryString">SQL命令字符串</param>
  public SQLiteDataReader ExecuteQuery(string queryString)
  {
    try
    {
      dbCommand = dbConnection.CreateCommand();
      dbCommand.CommandText = queryString;
      dataReader = dbCommand.ExecuteReader();
    }
    catch (Exception e)
    {
      Log(e.Message);
    }

return dataReader;
  }
  /// <summary>
  /// 关闭数据库连接
  /// </summary>
  public void CloseConnection()
  {
    //销毁Commend
    if (dbCommand != null)
    {
      dbCommand.Cancel();
    }
    dbCommand = null;
    //销毁Reader
    if (dataReader != null)
    {
      dataReader.Close();
    }
    dataReader = null;
    //销毁Connection
    if (dbConnection != null)
    {
      dbConnection.Close();
    }
    dbConnection = null;

}

/// <summary>
  /// 读取整张数据表
  /// </summary>
  /// <returns>The full table.</returns>
  /// <param name="tableName">数据表名称</param>
  public SQLiteDataReader ReadFullTable(string tableName)
  {
    string queryString = "SELECT * FROM " + tableName;
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// 向指定数据表中插入数据
  /// </summary>
  /// <returns>The values.</returns>
  /// <param name="tableName">数据表名称</param>
  /// <param name="values">插入的数值</param>
  public SQLiteDataReader InsertValues(string tableName, string[] values)
  {
    //获取数据表中字段数目
    int fieldCount = ReadFullTable(tableName).FieldCount;
    //当插入的数据长度不等于字段数目时引发异常
    if (values.Length != fieldCount)
    {
      throw new SQLiteException("values.Length!=fieldCount");
    }

string queryString = "INSERT INTO " + tableName + " VALUES (" + "'" + values[0] + "'";
    for (int i = 1; i < values.Length; i++)
    {
      queryString += ", " + "'" + values[i] + "'";
    }
    queryString += " )";
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// 更新指定数据表内的数据
  /// </summary>
  /// <returns>The values.</returns>
  /// <param name="tableName">数据表名称</param>
  /// <param name="colNames">字段名</param>
  /// <param name="colValues">字段名对应的数据</param>
  /// <param name="key">关键字</param>
  /// <param name="value">关键字对应的值</param>
  /// <param name="operation">运算符：=,<,>,...，默认“=”</param>
  public SQLiteDataReader UpdateValues(string tableName, string[] colNames, string[] colValues, string key, string value, string operation = "=")
  {
    //当字段名称和字段数值不对应时引发异常
    if (colNames.Length != colValues.Length)
    {
      throw new SQLiteException("colNames.Length!=colValues.Length");
    }

string queryString = "UPDATE " + tableName + " SET " + colNames[0] + "=" + "'" + colValues[0] + "'";
    for (int i = 1; i < colValues.Length; i++)
    {
      queryString += ", " + colNames[i] + "=" + "'" + colValues[i] + "'";
    }
    queryString += " WHERE " + key + operation + "'" + value + "'";
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// 删除指定数据表内的数据
  /// </summary>
  /// <returns>The values.</returns>
  /// <param name="tableName">数据表名称</param>
  /// <param name="colNames">字段名</param>
  /// <param name="colValues">字段名对应的数据</param>
  public SQLiteDataReader DeleteValuesOR(string tableName, string[] colNames, string[] colValues, string[] operations)
  {
    //当字段名称和字段数值不对应时引发异常
    if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
    {
      throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
    }

string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
    for (int i = 1; i < colValues.Length; i++)
    {
      queryString += "OR " + colNames[i] + operations[0] + "'" + colValues[i] + "'";
    }
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// 删除指定数据表内的数据
  /// </summary>
  /// <returns>The values.</returns>
  /// <param name="tableName">数据表名称</param>
  /// <param name="colNames">字段名</param>
  /// <param name="colValues">字段名对应的数据</param>
  public SQLiteDataReader DeleteValuesAND(string tableName, string[] colNames, string[] colValues, string[] operations)
  {
    //当字段名称和字段数值不对应时引发异常
    if (colNames.Length != colValues.Length || operations.Length != colNames.Length || operations.Length != colValues.Length)
    {
      throw new SQLiteException("colNames.Length!=colValues.Length || operations.Length!=colNames.Length || operations.Length!=colValues.Length");
    }

string queryString = "DELETE FROM " + tableName + " WHERE " + colNames[0] + operations[0] + "'" + colValues[0] + "'";
    for (int i = 1; i < colValues.Length; i++)
    {
      queryString += " AND " + colNames[i] + operations[i] + "'" + colValues[i] + "'";
    }
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// 创建数据表
  /// </summary> +
  /// <returns>The table.</returns>
  /// <param name="tableName">数据表名</param>
  /// <param name="colNames">字段名</param>
  /// <param name="colTypes">字段名类型</param>
  public SQLiteDataReader CreateTable(string tableName, string[] colNames, string[] colTypes)
  {
    string queryString = "CREATE TABLE IF NOT EXISTS " + tableName + "( " + colNames[0] + " " + colTypes[0];
    for (int i = 1; i < colNames.Length; i++)
    {
      queryString += ", " + colNames[i] + " " + colTypes[i];
    }
    queryString += ") ";
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// Reads the table.
  /// </summary>
  /// <returns>The table.</returns>
  /// <param name="tableName">Table name.</param>
  /// <param name="items">Items.</param>
  /// <param name="colNames">Col names.</param>
  /// <param name="operations">Operations.</param>
  /// <param name="colValues">Col values.</param>
  public SQLiteDataReader ReadTable(string tableName, string[] items, string[] colNames, string[] operations, string[] colValues)
  {
    string queryString = "SELECT " + items[0];
    for (int i = 1; i < items.Length; i++)
    {
      queryString += ", " + items[i];
    }
    queryString += " FROM " + tableName + " WHERE " + colNames[0] + " " + operations[0] + " " + colValues[0];
    for (int i = 0; i < colNames.Length; i++)
    {
      queryString += " AND " + colNames[i] + " " + operations[i] + " " + colValues[0] + " ";
    }
    return ExecuteQuery(queryString);
  }

/// <summary>
  /// 本类log
  /// </summary>
  /// <param name="s"></param>
  static void Log(string s)
  {
    Console.WriteLine("class SqLiteHelper:::" + s);
  }
  }
}

```

数据信息的插入，具体的SQL语句可以参考菜鸟教程，里面有项目说明sql的各种操作。

[SQLite 教程 | 菜鸟教程 (runoob.com)](https://www.runoob.com/sqlite/sqlite-tutorial.html)

这里我们就不细说，直接上关键的数据库连接代码。

```C#
  public void Weather_SQL_Insert()
  {

try
    {
      //连接数据库

Global.sqlConn = new SqLiteHelper("data source=SerialPortHelper.db");

string insertString = "INSERT INTO Weather(Temp,Humi,WindSpeed,WindDirection,Altitude,Pressure,Date)VALUES(" + tempStr + " ," + humiStr + ","+ speedStr + ","+ dirStr + ","+ altiStr + ","+ pressStr + ","+ DateTime.Now.ToLocalTime().ToString("yyyyMMddHHmmss") + ")";
      Global.sqlConn.ExecuteQuery(insertString);
      Global.sqlConn.CloseConnection();
    }
    catch (Exception ex)
    {

MessageBox.Show(ex.Message);
    }
    finally
    {
      Global.sqlConn.CloseConnection();

}
  }
```

最后打开的数据库工具，查看数据是否更新。

![image-20231101232420951](https://boreyun.oss-cn-shanghai.aliyuncs.com/image-20231101232420951.png)

自此，数据保存成功。
具体演示视频如下：
https://www.bilibili.com/video/BV1SH4y1z7ys/

回复评论（1）

沙发 lugl4313820

楼主的技术牛呀，软件硬件通吃，佩服佩服！

点赞 2023-11-2 06:56

[活动] 【DFRobot 云雀气象仪】学习笔记02（自动化获取数据并保存到本地数据库）

回复评论 （1）

沙发 lugl4313820

回复评论（1）