【DFRobot 云雀气象仪】学习笔记02（自动化获取数据并保存到本地数据库）
## 5：本地数据库保存 数据在实际工作中应用非常广泛，数据库的产品也比较多，oracle、DB2、SQL2000、mySQL；基于嵌入式linux的数据库主要有SQLite, Firebird, Berkeley DB, eXtremeDB。这里我们主要采用sqlite3数据库，来做本地数据存储；
!(https://boreyun.oss-cn-shanghai.aliyuncs.com/image-20231101225100427.png)
这里我们首先创建一个Weather天气的数据表，表中包含温度，湿度，海拔，起亚，电压，电池，风向，风速，以及日期的数据字段，ID代表数据序列号，为自增字段。
## 6：上位机编写
我们编写上位机来自动接收数据，并将接收到的数据放入本地数据库，也就是sqlite3数据库下。上位机实用C#开发，使用visualstudio2019主要分为以下2个步骤：
1. 数据的获取
2. 连接数据库并保存
### 6.1：数据的获取
数据的获取采用串口通讯来实现，首先进行串口的开关，然后实现串口的收发，最后添加定时器，实现数据的自动化接收。
首先事串口的打开和关闭，我们使用串口组件，和按钮以及多选框来实现串口的打开和关闭。组件设计截图如下：
!(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;
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 + "'";
for (int i = 1; i < values.Length; i++)
{
queryString += ", " + "'" + values + "'";
}
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 + "=" + "'" + colValues + "'";
for (int i = 1; i < colValues.Length; i++)
{
queryString += ", " + colNames + "=" + "'" + colValues + "'";
}
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 + operations + "'" + colValues + "'";
for (int i = 1; i < colValues.Length; i++)
{
queryString += "OR " + colNames + operations + "'" + colValues + "'";
}
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 + operations + "'" + colValues + "'";
for (int i = 1; i < colValues.Length; i++)
{
queryString += " AND " + colNames + operations + "'" + colValues + "'";
}
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 + " " + colTypes;
for (int i = 1; i < colNames.Length; i++)
{
queryString += ", " + colNames + " " + colTypes;
}
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;
for (int i = 1; i < items.Length; i++)
{
queryString += ", " + items;
}
queryString += " FROM " + tableName + " WHERE " + colNames + " " + operations + " " + colValues;
for (int i = 0; i < colNames.Length; i++)
{
queryString += " AND " + colNames + " " + operations + " " + colValues + " ";
}
return ExecuteQuery(queryString);
}
/// <summary>
/// 本类log
/// </summary>
/// <param name="s"></param>
static void Log(string s)
{
Console.WriteLine("class SqLiteHelper:::" + s);
}
}
}
```
数据信息的插入，具体的SQL语句可以参考菜鸟教程，里面有项目说明sql的各种操作。
(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();
}
}
```
最后打开的数据库工具，查看数据是否更新。
!(https://boreyun.oss-cn-shanghai.aliyuncs.com/image-20231101232420951.png)
自此，数据保存成功。
具体演示视频如下：
https://www.bilibili.com/video/BV1SH4y1z7ys/
楼主的技术牛呀，软件硬件通吃，佩服佩服！
页:
[1]