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

qzc0927

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

## 5：本地数据库保存 ​ 数据在实际工作中应用非常广泛，数据库的产品也比较多，oracle、DB2、SQL2000、mySQL；基于嵌入式linux的数据库主要有SQLite, Firebird, Berkeley DB, eXtremeDB。这里我们主要采用sqlite3数据库，来做本地数据存储；  ​ 这里我们首先创建一个Weather天气的数据表，表中包含温度，湿度，海拔，起亚，电压，电池，风向，风速，以及日期的数据字段，ID代表数据序列号，为自增字段。 ## 6：上位机编写 我们编写上位机来自动接收数据，并将接收到的数据放入本地数据库，也就是sqlite3数据库下。上位机实用C#开发，使用visualstudio2019主要分为以下2个步骤： 1. 数据的获取 2. 连接数据库并保存 ### 6.1：数据的获取 数据的获取采用串口通讯来实现，首先进行串口的开关，然后实现串口的收发，最后添加定时器，实现数据的自动化接收。 首先事串口的打开和关闭，我们使用串口组件，和按钮以及多选框来实现串口的打开和关闭。组件设计截图如下：  串口打开和关闭的代码如下： ```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(); } } ``` 最后打开的数据库工具，查看数据是否更新。  自此，数据保存成功。 具体演示视频如下： https://www.bilibili.com/video/BV1SH4y1z7ys/

lugl4313820

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