本文介绍:

在IoT Edge边缘设备中部署Azure Function;

通过Azure Function或者其他Module 将遥测数据写入SQL Edge 数据库中;

本地边缘设备上的Function 附加调试;

本文代码:https://github.com/sean8544/azure-iot-edge-quickstart/tree/main/demo09

视频:

 

图文:

在IoT Edge边缘设备中部署Azure Function;

在部署文件上右键选择 Add iot edge module:

 

选择Azure Functions:

 

填写ACR地址:

FunctionModule创建完成:

 

删除上一讲中创建的FirstModule和修改路由:

"$edgeHub": {
      "properties.desired": {
        "schemaVersion": "1.0",
        "routes": {          
          "SensorModuleToFunctionModule": "FROM /messages/modules/SensorModule/outputs/sensorOutput INTO BrokeredEndpoint(\"/modules/FunctionModule/inputs/input1\")",
          "FunctionModuleToIoTHub": "FROM /messages/modules/FunctionModule/outputs/* INTO $upstream"
        },
        "storeAndForwardConfiguration": {
          "timeToLiveSecs": 7200
        }
      }

 

通过Azure Function或者其他Module 将遥测数据写入SQL Edge 数据库中;

使用nuget添加System.Data.SqlClient引用,版本选择4.5.1(已经经过我们的测试,其他版本未测试):

 

ctrl+shift+p,输入NuGet Package Manager:Add Package

输入或选择System.Data.SqlClient

版本选择4.5.1

将程序集添加到FunctionModule上:

修改逻辑代码,增加写数据库的部分:

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Azure.Devices.Client;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.EdgeHub;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json;
using System.Data;
using System.Data.SqlClient;

namespace Functions.Samples
{
    public static class FunctionModule
    {
        [FunctionName("FunctionModule")]
        public static async Task FilterMessageAndSendMessage(
                    [EdgeHubTrigger("input1")] Message messageReceived,
                    [EdgeHub(OutputName = "output1")] IAsyncCollector<Message> output,
                    ILogger logger)
        {
            byte[] messageBytes = messageReceived.GetBytes();
            var messageString = System.Text.Encoding.UTF8.GetString(messageBytes);

            if (!string.IsNullOrEmpty(messageString))
            {

                // write message into sql edge

            var dbstring="Data Source=SQLServerModule;Initial Catalog=master;User Id=SA;Password=Strong!Passw0rd;TrustServerCertificate=False;Connection Timeout=30;";
            
           /*  string moduleID=messageReceived.ConnectionModuleId;
            string clientID=messageReceived.ConnectionDeviceId;
            Console.WriteLine(moduleID+"-"+clientID); */
            try
            {
                 
                 var sensorData=new SensorData();
                 try
                    {
                     sensorData= JsonConvert.DeserializeObject<SensorData>(messageString);
                   
                    }
                    catch(Exception ex)
                    {
                        Console.WriteLine($"error{ex.Message}");
                    }
                
                using (SqlConnection con = new SqlConnection(dbstring))
                {
                    con.Open();
                    if (con.State ==   System.Data.ConnectionState.Open)
                    {
                    
                        string strCmd = $"insert into dbo.Telemetry(temperature,humidity,funcsavedt,deviceid) values ({sensorData.Temperature},{sensorData.Humidity},'{System.DateTime.Now}','{messageReceived.ConnectionDeviceId}' )";


                        SqlCommand sqlcmd = new SqlCommand(strCmd, con);
                        int   n = sqlcmd.ExecuteNonQuery();
                        if (n > 0)
                        {
                            logger.LogInformation("save to sql edge db successfully");
                        }
                        else
                        {
                            logger.LogError("save to sql edge db error");
                        }
                      
                }  
               con.Close();
               }
            }
            catch (Exception ex)
            {
               logger.LogInformation(ex.StackTrace);
            }







                logger.LogInformation("Info: Received one non-empty message");
                using (var pipeMessage = new Message(messageBytes))
                {
                    foreach (KeyValuePair<string, string> prop in messageReceived.Properties)
                    {
                        pipeMessage.Properties.Add(prop.Key, prop.Value);
                    }
                    await output.AddAsync(pipeMessage);
                    logger.LogInformation("Info: Piped out the message");
                }
            }
        }
    }


     class SensorData
        {
            public double Temperature { get; set; }

            public double Humidity { get; set; }            
        }
}

 

在Edge设备上连接到SQL Edge,创建数据库和表,本例中直接使用了Master数据库:

建表脚本参考:

注意:这里仅做测试,实际应注意sql的相关索引等等要求。

CREATE TABLE [dbo].[telemetry](
 [temperature] [numeric](18, 2) NULL,
 [humidity] [numeric](18, 2) NULL,
 [deviceid] [nvarchar](50) NULL,
 [funcsavedt] [datetime] NULL
) ON [PRIMARY]
GO

 

本地边缘设备上的Function 附加调试;

在debug的部署文件上右键,选择 build and run iot edge solution in simulator

可以在本地看到包括SQL EDGE在内的容器已经启动:

选择 Function 项目的 remote debug,即可启动调试。