1. 概要
本文主要利用ESP8266模块、EMQX平台(开源版)、SQL Server数据库以及基于C#的简易前后端构建一个基础的物联网平台。文章从ESP8266硬件说明、Arduino IDE硬件环境配置、硬件代码编写,基础SQL Server数据库安装 、SSMS工具安装,以及C#前后端编写等方面尽可能详细讲解了整个搭建过程,同时提供了完整的代码和工程,方便大家学习和实践,同时也是自身一个从零开始学习物联网技术的过程。希望各位大家能对我分享的过程多提意见和建议,我也会积极向大家学习。
该平台能够实现单个节点的数据采集和传输,并将通过MQTT服务器将数据存储到SQL Server数据库中,用户可以通过C#编写的界面进行数据的可视化展示和分析。整体思路的框图如下所示。
2. 整体流程
2.1 ESP8266开发
ESP8266具有高度集成、低功耗、低成本、广泛的应用领域等特点,是一款非常优秀的物联网芯片。我用的ESP8266就某宝买的。比较便宜,适合用于练习项目。
这个ESP8266是基于Arduino的,所以需要安装配置好Arduino IDE平台,并且配置对应的支持库才能进行硬件程序编写,具体过程可以参考我之前发Arduino IDE 2.1.1 ESP8266开发板安装方案。
Arduino IDE 2.1.1 ESP8266开发板安装_Secede.的博客-CSDN博客
Arduino在有对应支持库的情况下,编程是比较简单的。
void setup() {
// 这写初始化的配置:
}
void loop() {
// 这写循环体:
}
ESP8266硬件编写代码如下所示:
#include <AHT10.h>
#include <Wire.h>
#include <ESP8266WiFi.h>
#include <PubSubClient.h>
#include <ArduinoJson.h>
#ifndef STASSID
#define STASSID "abbc"
#define STAPSK "123456"
#endif
const char* ssid = STASSID;
const char* password = STAPSK;
const char* mqtt_server = "127.0.0.1"; //PC
long lastReconnectAttempt = 0;
DynamicJsonDocument msg4device(100);
char msg[100] = {0};
int stats = 0;
String output;
WiFiClient espClient;
PubSubClient client(espClient);
boolean reconnect() {
if (client.connect("arduinoClient")) {
// Once connected, publish an announcement...
client.publish("announcement","ready");
// ... and resubscribe
client.subscribe("test3");
client.subscribe("refresh");
}
return client.connected();
}
void callback(char* topic, byte* payload, unsigned int length) {
byte* n_payload = (byte*)malloc(length);
memcpy(n_payload,payload,length);
if(!strcmp(topic,"test3"))
{
// Switch on the LED if an 1 was received as first character
if ((char)n_payload[0] == '1') {
digitalWrite(LED_BUILTIN, LOW); // Turn the LED on (Note that LOW is the voltage level
stats = 1;
// but actually the LED is on; this is because
// it is acive low on the ESP-01)
} else {
digitalWrite(LED_BUILTIN, HIGH); // Turn the LED off by making the voltage HIGH
stats = 0;
}
}
if(!strcmp(topic,"refresh"))
{
reconnect();
}
}
void setup() {
pinMode(LED_BUILTIN, OUTPUT);
digitalWrite(LED_BUILTIN, HIGH);
Serial.begin(115200);
// We start by connecting to a WiFi network
Wire.begin(12,14);
if (!AHT10::begin()) {
Serial.println(F("AHT10 not detected!"));
Serial.flush();
}
Serial.print("Connecting to ");
Serial.println(ssid);
WiFi.mode(WIFI_STA);
WiFi.begin(ssid, password);
while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}
Serial.println("");
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());
Serial.print("Setting MQTT Server");
client.setServer(mqtt_server, 1883);
client.setCallback(callback);
}
void loop()
{
if (!client.connected())
{
long now = millis();
if (now - lastReconnectAttempt > 2000)
{
lastReconnectAttempt = now;
// Attempt to reconnect
if (reconnect())
{
lastReconnectAttempt = 0;
}
}
}
else
{
// Client connected
client.loop();
float temp, hum;
if (AHT10::measure(&temp, &hum))
{
msg4device["humidity"] = hum;
msg4device["temperature"] = temp;
msg4device["state"] = stats;
}
else
{
Serial.println(F("AHT10 read error!"));
}
output.clear();
serializeJson(msg4device,output);
Serial.print("Publish message: ");
Serial.println(output);
client.publish("test", output.c_str());
delay(1500);
}
}
上面的代码有关AHT10的部分是AHT10温湿度传感器的相关驱动程序,如果需要下载可以直接在Github上搜索,然后放在首选项所设定的文件夹下的libraries目录即可。如果在Github上下载不方便的话,可以提供对应的下载连接如下:
https://download.csdn.net/download/qq_41858327/88238320
硬件代码中具体函数和变量对应的意义入下表所示:
STASSID | WiFi 名字 |
STAPSK | WFi 密码 |
mqtt_server | MQTT服务器地址 |
msg4device | 传感器数据存入Json |
reconnect() | ESP8266与MQTT服务器断连重连函数 |
callback() | ESP8266接收MQTT服务器发送信息回调函数 |
订阅主题 test | ESP8266 发送 物联网控制平台 温湿度数据包 |
订阅主题 test3 | 物联网控制平台 发送 ESP8266 数据包 |
2.2 MQTT服务器与SQL Server数据库
MQTT服务器选择EMQX开源版 。
官网连接:下载 EMQX,下载对应zip文件解压即可。
bin文件夹下有emqx.cmd,运行emqx.cmd,输入start,MQTT服务器就运行起来了。
此外,也可以在命令行输入E:\MQTT\emqx-windows-4.3.16\emqx\bin\emqx.cmd start(你的MQTT文件夹路径)。
进入EMQX的Dashboard :http://localhost:18083/ 默认账户admin 默认密码public
然后是配置SQL Server, SQL Server数据库的基础安装配置现有介绍已经非常全面了,大家自行查找即可。
安装好SQL Server数据库与SSMS工具之后,我们要创建对应的数据库与数据表(无所谓系统或者数据库类型,能做到后续程序连接到对应的数据库即可),用于存储我们从ESP8266传输的温湿度数据。如下:
这里用的是本地的SQL Server数据库,如果您使用的是云服务器,那就对您的数据库进行数据库和表进行配置即可。
2.3 C#的订阅发布功能实现
要想在EMQX开源版本中实现对于硬件发送数据的持久化存储,常见的方法有两种:①通过在后台创建超级用户,订阅所有主题,对对应主题进行二次的响应;②利用EMQX自带的WebHook + Web API 实现自动将硬件数据存储到数据库中。方案1主要存在的问题在于响应速度慢,较大数据量的情况不适用。方法2稍微复杂一点,需要对EMQX中的WebHook功能进行配置,再就是需要有web端后台的设计。本此实验主要是对第一种方案进行探索和研究,会在后期对第二种方案进行研究。
此外,针对不同的编程语言和应用场景,EMQX官方都有对应的入门文档可以参考,推荐大家配合官方文档进行学习。
回到本次项目,本次项目主要利用C#和对应的支持库MQTTnet对后台订阅发布功能进行实现。实现的主体由3部分组成:①MQTT服务器的配置连接;②MQTT订阅与回调函数配置;③MQTT回调函数编写
首先在Visual Studio中添加MQTTnet支持库[项目-> 管理NuGet程序包]
安装MQTTnet和MQTTnet.Extensions.ManagedClient两个包。
首先应该建立MQTT Client,并设置对应的订阅主题
private IMqttClient mqttClient;
public const string topic1 = "test";
public const string topic2 = "announcement";
public const string mantopic2 = "test2";
public const string pubtopic3 = "test3";
public const string refreshtopic = "refresh";
private SQLexe sql = new SQLexe();
Task task,task1;
public void MqttConnectAsync()
{
try
{
var mqttFactory = new MqttFactory();
var mqttClientOptions = new MqttClientOptionsBuilder()
.WithTcpServer("127.0.0.1", 1883)
.WithProtocolVersion(MQTTnet.Formatter.MqttProtocolVersion.V311)
.WithClientId("TransferService")
.WithCleanSession(false)
.WithKeepAlivePeriod(TimeSpan.FromSeconds(60))
.WithCredentials(null)
.Build();
mqttClient = mqttFactory.CreateMqttClient();
var mqttSubscribeOptions = mqttFactory.CreateSubscribeOptionsBuilder()
.WithTopicFilter(
f =>
{
f.WithTopic(topic1);
}
)
.WithTopicFilter(
f =>
{
f.WithTopic(topic2);
}
)
.WithTopicFilter(
f =>
{
f.WithTopic(mantopic2);
}
)
.Build();
mqttClient.DisconnectedAsync += MqttClient_DisconnectedAsync;
mqttClient.ConnectedAsync += MqttClient_ConnectedAsync;
mqttClient.ApplicationMessageReceivedAsync += MqttClient_ApplicationMessageReceivedAsync;
task = mqttClient.ConnectAsync(mqttClientOptions, CancellationToken.None);
task.Wait();
task1 = mqttClient.SubscribeAsync(mqttSubscribeOptions, CancellationToken.None);
task1.Wait();
Console.WriteLine("MQTT client subscribed to ({0} , {1} , {2}) topics.", topic1, topic2, pubtopic3);
}
catch (Exception ex)
{
task.Dispose();
task1.Dispose();
mqttClient.Dispose();
Console.WriteLine(ex.ToString());
}
}
根据设置定义回调函数
MQTT服务连接成功回调函数
private Task MqttClient_ConnectedAsync(MqttClientConnectedEventArgs args)
{
Console.WriteLine($"Mqtt Client Connected.");
return Task.CompletedTask;
}
MQTT服务断开连接回调函数
private Task MqttClient_DisconnectedAsync(MqttClientDisconnectedEventArgs arg)
{
Console.WriteLine($"Mqtt Client DisConnected.");
return Task.CompletedTask;
}
MQTT消息接收处理回调函数
private async Task MqttClient_ApplicationMessageReceivedAsync(MqttApplicationMessageReceivedEventArgs arg)
{
if (arg.ReasonCode == MqttApplicationMessageReceivedReasonCode.Success)
{
string topic_n = arg.ApplicationMessage.Topic;
string mess = Encoding.UTF8.GetString(arg.ApplicationMessage.PayloadSegment);
if (!string.IsNullOrEmpty(mess))
{
switch (topic_n)
{
case topic1:
Dictionary<string, string> msg = JsonToDictionary(mess);
float humidity = float.Parse(msg.ElementAt(0).Value);
float temperature = float.Parse(msg.ElementAt(1).Value);
state = int.Parse(msg.ElementAt(2).Value);
sql.insertTest(humidity, temperature, state);
Console.WriteLine("humidity is {0}%\ntemperature is {1}℃\nStats is {2}", humidity, temperature, state);
// Max humidity should not be bigger than 92%
if (humidity > 92)
{
state = 0;
var publishMessage = new MqttApplicationMessageBuilder()
.WithTopic(pubtopic3)
.WithPayload("0") // shutdown device
.Build();
await mqttClient.PublishAsync(publishMessage, CancellationToken.None);
}
break;
case topic2:
if (mess.Equals("ready"))
{
Console.WriteLine("MQTT Server Reconnect Ready");
}
break;
case mantopic2:
if (mess.Equals("open"))
{
var publishMessage = new MqttApplicationMessageBuilder()
.WithTopic(pubtopic3)
.WithPayload("1") // shutdown device
.Build();
await mqttClient.PublishAsync(publishMessage, CancellationToken.None);
state = 1;
}
else
{
var publishMessage = new MqttApplicationMessageBuilder()
.WithTopic(pubtopic3)
.WithPayload("0") // shutdown device
.Build();
await mqttClient.PublishAsync(publishMessage, CancellationToken.None);
state = 0;
}
break;
default:
break;
}
}
}
}
消息接收处理回调函数中主要是对不同订阅主题的数据进行简单的操作和处理,其中就包括对发送Json数据的解包和数据库存储,异常数据的快速响应处理(上述代码中就针对湿度超过92%的情况进行了报警,在湿度超过92%后,向ESP8266发送关闭指令,模拟实际场景下某种指标超过预警阈值对设备进行急停操作)。
Dictionary<string, string> msg = JsonToDictionary(mess);
float humidity = float.Parse(msg.ElementAt(0).Value);
float temperature = float.Parse(msg.ElementAt(1).Value);
state = int.Parse(msg.ElementAt(2).Value);
sql.insertTest(humidity, temperature, state);
上述代码中这5行就是主要对硬件数据进行Json解包,然后将数据存入数据库中的关键操作。
数据库操作这块,应该是要做个接口去做数据存储,我这边就是弄得简单点,直接直连了(FreeSQL),代码如下:
using ABV物联网监控平台.Entity;
using dotNettest.Entity;
namespace ABV物联网监控平台
{
internal class SQLexe
{
public static IFreeSql freeSql { get; } = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.SqlServer, @"server=127.0.0.1;database=TutorialDB;User Id=sa;Password=123456;TrustServerCertificate=true;Pooling=true;Min Pool Size=1;Packet Size=512")
.UseAutoSyncStructure(true)
.Build();
private bool errorCode = true;
public bool Check(string usrname, string password)
{
List<Info> result = freeSql.Select<Info>()
.Where(a => a.usrname.Equals(usrname) && a.password.Equals(password))
.ToList();
if (result.Count != 0 )
{
return true;
}
return false;
}
public void insertTest(float humidity, float temperature, int state)
{
freeSql.Insert(new 温湿度监测设备
{
humidity = humidity,
temperature = temperature,
state = state
}).IgnoreColumns(x => x.id).ExecuteAffrows();
}
public List<温湿度监测设备> GetInfo()
{
try
{
List<温湿度监测设备> result = freeSql.Select<温湿度监测设备>()
.WithSql("select top 100 * from 温湿度监测设备 order by id desc")
.ToList();
return result;
}
catch (Exception ex)
{
if (errorCode)
{
errorCode = false;
MessageBox.Show("SQL Server服务器连接超时");
}
}
return null;
}
}
}
顺便贴个String转Dictionary的常用代码吧(Newtonsoft.Json)
public static Dictionary<string, string> JsonToDictionary(string jsonStr)
{
try
{
Dictionary<string, string> dic = JsonConvert.DeserializeObject<Dictionary<string, string>>(jsonStr);
return dic;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return null;
}
2.4 C#界面设计
界面的话,就弄个自己喜欢的样子就行,我这就做的winForm的,搞得比较简单。数据直接定时从数据库进行拉取,然后刷新图表就可以了,然后根据硬件上传数据中的硬件开关状态更新界面上显示的提示文字,再就没啥了。
帆软报表界面
c#前端代码
using MQTTClient;
using Newtonsoft.Json;
using ScottPlot;
using System.Text;
using System.Timers;
namespace ABV物联网监控平台
{
public partial class Form2 : Form
{
MQTTexe mQT = new MQTTexe();
FORMexe fORMexe;
TransferService transferservice = new TransferService();
private System.Timers.Timer timer, timer1 = null;
ScottPlot.Plot plt1, plt2 = null;
public Form2()
{
this.MaximizeBox = false;
InitializeComponent();
notifyIcon1.Icon = ABV物联网监控平台.Properties.Resources.WST_notify_LOGO;
notifyIcon1.BalloonTipClosed += NotifyIcon1_BalloonTipClosed;
}
private void NotifyIcon1_BalloonTipClosed(object? sender, EventArgs e)
{
notifyIcon1.Visible = false;
}
private void Form2_Load(object sender, EventArgs e)
{
notifyIcon1.BalloonTipTitle = "登陆成功";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "个人主页已被允许操作";
notifyIcon1.ShowBalloonTip(0);
TreeNode fcun1 = treeView1.Nodes.Add("功能1");
fcun1.Nodes.Add("detail1");
fcun1.Nodes.Add("detail2");
fcun1.Nodes.Add("detail3");
fcun1.ExpandAll();
TreeNode fcun2 = treeView1.Nodes.Add("功能2");
fcun2.Nodes.Add("detail1");
fcun2.Nodes.Add("detail2");
fcun2.Nodes.Add("detail3");
fcun2.Nodes.Add("detail4");
fcun2.ExpandAll();
TreeNode fcun3 = treeView1.Nodes.Add("功能3");
fcun3.Nodes.Add("detail1");
fcun3.Nodes.Add("detail2");
fcun3.Nodes.Add("detail3");
fORMexe = new FORMexe(formsPlot1, formsPlot2);
}
private void MQTTtrans_Click(object sender, EventArgs e)
{
transferservice.MqttConnectAsync();
if (transferservice.GetMQTTStats())
{
this.label1.Text = "已启动";
this.label1.ForeColor = Color.Green;
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT业务服务已启动成功";
notifyIcon1.ShowBalloonTip(0);
timer = new System.Timers.Timer();
timer.Interval = 1000;
timer.AutoReset = true;
timer.Elapsed += new System.Timers.ElapsedEventHandler(timer_Elapsed);
timer.Start();
}
else
{
this.label1.Text = "未启动";
this.label1.ForeColor = Color.Red;
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT业务服务启动失败";
notifyIcon1.ShowBalloonTip(0);
}
}
private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
{
}
private void dataGrap_CheckedChanged(object sender, EventArgs e)
{
timer1 = new System.Timers.Timer();
timer1.Interval = 1000;
timer1.AutoReset = true;
timer1.Elapsed += new System.Timers.ElapsedEventHandler(timer1_Elapsed);
timer1.Start();
}
private void startMQTTservice_Click(object sender, EventArgs e)
{
CMDexe cM = new CMDexe();
cM.StartService();
if (mQT.GetMQTTStats("startCheck"))
{
this.MQTTstats.Text = "已启动";
this.MQTTstats.ForeColor = Color.Green;
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT服务器已启动成功";
notifyIcon1.ShowBalloonTip(0);
}
else
{
this.MQTTstats.Text = "未启动";
this.MQTTstats.ForeColor = Color.Red;
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT服务器启动失败";
notifyIcon1.ShowBalloonTip(0);
}
}
private void timer1_Elapsed(object? sender, ElapsedEventArgs e)
{
UpdateChart();
}
private void UpdateChart()
{
//创建一个委托,用于封装一个方法,在这里是封装了 控制更新控件 的方法
Action invokeAction = new Action(UpdateChart);
//判断操作控件的线程是否创建控件的线程
//调用方调用方位于创建控件所在的线程以外的线程中,如果在其他线程则对控件进行方法调用时必须调用 Invoke 方法
if (this.InvokeRequired)
{
//与调用线程不同的线程上创建(说明您必须通过 Invoke 方法对控件进行调用)
this.Invoke(invokeAction);
}
else
{
fORMexe.UpdateChart(formsPlot1, formsPlot2);
}
}
private void timer_Elapsed(object? sender, ElapsedEventArgs e)
{
UpdateUIText();
}
private void UpdateUIText()
{
//创建一个委托,用于封装一个方法,在这里是封装了 控制更新控件 的方法
Action invokeAction = new Action(UpdateUIText);
//判断操作控件的线程是否创建控件的线程
//调用方调用方位于创建控件所在的线程以外的线程中,如果在其他线程则对控件进行方法调用时必须调用 Invoke 方法
if (this.InvokeRequired)
{
//与调用线程不同的线程上创建(说明您必须通过 Invoke 方法对控件进行调用)
this.Invoke(invokeAction);
}
else
{
if (transferservice.GetMQTTStats())
{
this.MQTTstats.Text = "已启动";
this.MQTTstats.ForeColor = Color.Green;
}
else
{
this.MQTTstats.Text = "未启动";
this.MQTTstats.ForeColor = Color.Red;
}
if (transferservice.GetDeviceStats())
{
this.devicestats.Text = "打开";
this.devicestats.ForeColor = Color.Green;
}
else
{
this.devicestats.Text = "关闭";
this.devicestats.ForeColor = Color.Red;
}
}
}
private void stopMQTTservice_Click(object sender, EventArgs e)
{
CMDexe cM = new CMDexe();
cM.StoptService();
if (!mQT.GetMQTTStats("stopCheck"))
{
this.MQTTstats.Text = "未启动";
this.MQTTstats.ForeColor = Color.Red;
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT服务器已关闭成功";
notifyIcon1.ShowBalloonTip(0);
}
else
{
this.MQTTstats.Text = "已启动";
this.MQTTstats.ForeColor = Color.Green;
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT服务器关闭失败";
notifyIcon1.ShowBalloonTip(0);
}
}
private void MQTTstats_Click(object sender, EventArgs e)
{
}
private void activateMoniter_Click(object sender, EventArgs e)
{
try
{
if (transferservice.GetMQTTStats())
{
mQT.ActivateDevice();
}
else
{
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "M QTT服务未激活,请先激活MQTT服务";
notifyIcon1.ShowBalloonTip(0);
}
}
catch (Exception ex)
{
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "设备联通失败,请检查MQTT服务是否正常";
notifyIcon1.ShowBalloonTip(0);
Console.WriteLine(ex.ToString());
}
}
private void disposeMoniter_Click(object sender, EventArgs e)
{
try
{
if (transferservice.GetMQTTStats())
{
mQT.DeactivateDevice();
}
else
{
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "MQTT服务未激活,请先激活MQTT服务";
notifyIcon1.ShowBalloonTip(0);
}
}
catch (Exception ex)
{
notifyIcon1.BalloonTipTitle = "提示";
notifyIcon1.Visible = true;
notifyIcon1.BalloonTipText = "设备联通失败,请检查MQTT服务是否正常";
notifyIcon1.ShowBalloonTip(0);
Console.WriteLine(ex.ToString());
}
}
private void refresh_device_stats_Click(object sender, EventArgs e)
{
transferservice.RefreshDeviceStats();
}
private async void GPT_Click(object sender, EventArgs e)
{
var values = new Dictionary<string, string>
{
{ "Gretting", "Nice to talk to you" },
};
var jsonData = JsonConvert.SerializeObject(values);
var contentData = new StringContent(jsonData, Encoding.UTF8, "application/json");
var client = new HttpClient();
var content = new FormUrlEncodedContent(values);
var response = await client.PostAsync("http://127.0.0.1:5000", content);
var responseString = await response.Content.ReadAsStringAsync();
MessageBox.Show(responseString);
}
}
}
3.小结
主要介绍了一种基于ESP8266、EMQX开源版本MQTT服务器与C#的物联网平台实验,主要在不借助云服务平台的情况下,第一次打通了从硬件到服务端的双向数据交互过程,实现了硬件数据的定时上传以及服务器端对硬件的及时控制,我认为此次实验所达到的物联网服务距离真正的工业场景可能还有比较大的差距,但是针对智能家居领域应该还是可以应用的,本地化不需要借助各类的云服务。
现在的开发过程还是过于野路子,后续需要对整个项目进行规范化设计,再多深入思考思考,多看看更底层的原理。