Tutorial: Store data at the edge with SQL Server databases

LinkOne-Face Zhang-張東勝 0

Hi, I have a question about this tutorial. I wonder is there any changed in the coding? I followed the instructions but I couldn't received data in sql. I found out that System.Data.SqlClient already depreciated. So I have change it to Microsoft.Data.SqlClient. However, I still face with many problems. I wonder if anyone know where the code or packages need to be revised. Thank you in advance.

1 answer

Sander van de Velde | MVP 34,281 Reputation points MVP

2025-01-13T15:09:30.93+00:00

Hello @LinkOne-Face Zhang-張東勝 ,

welcome to this moderated Azure community forum.

it seems you try to work though this tutorial.

In the past, I worked with SQL Server on Azure IoT Edge, as described on these blog posts (the docker image tag version should be ignored, newer versions are available).

Regarding the two Nuget packages, it depends on which .Net version you are basing your client code (.Net 6 vs. .Net 8).

Please have the Sql Server container running first with persisted storage on disk for the database (set in the container create options; see the blog posts).

You also need to expose the port in the container create options if you want to provide access to the database on the local network.

Once this is working, program against the database and move that working code to your target custom IoT Edge module.

If you still have issues, please share details so other community members have a good impression about what is happening.

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.
Please sign in to rate this answer.
LinkOne-Face Zhang-張東勝 0 Reputation points

2025-01-14T02:18:26.93+00:00

Thank you for replying. I have revised the coding in many parts. In dockerfile.amd64, I have changed the environment to net 6.0 instead because I assumed that net 3.0 maybe outdated. For csproj's targetframework part, in order to comply with the packages, I changed to netstandard2.0;netcoreapp3.1;net6.0 I also add Microsoft.Data.SqlClient(5.1.6), System.Text.Encoding.CodePages(6.0.0), System.Configuration.ConfigurationManager(6.0.1). Now, I am currently struggling with this part of the code

if (messageBody != null && messageBody.machine.temperature > temperatureThreshold) { // Send the message to the output as the temperature value is greater than the threshold. using (var filteredMessage = new Message(messageBytes)) { // Copy the properties of the original message into the new Message object. foreach (KeyValuePair<string, string> prop in messageReceived.Properties) {filteredMessage.Properties.Add(prop.Key, prop.Value);} // Add a new property to the message to indicate it is an alert. filteredMessage.Properties.Add("MessageType", "Alert"); // Send the message. await output.AddAsync(filteredMessage); logger.LogInformation("Info: Received and transferred a message with temperature above the threshold"); } }

Here is the present stage of my logs
info: Function.sqlFunction[1]

Executing 'sqlFunction' (Reason='EdgeHub trigger fired at 2025-01-13T18:59:00.4179883+00:00', Id=d5d7e030-7413-4ed7-a191-e981d35da579)`

info: Function.sqlFunction.User[0]

Info: Received one non-empty message

info: Function.sqlFunction.User[0]

2 rows were updated

info: Function.sqlFunction.User[0]

Info: Received and transferred a message with temperature above the threshold

fail: Function.sqlFunction[3]

Executed 'sqlFunction' (Failed, Id=d5d7e030-7413-4ed7-a191-e981d35da579, Duration=10ms)

Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: sqlFunction

---> System.InvalidOperationException: Error while handling parameter output after function returned:

---> System.NotSupportedException: Specified method is not supported.

LinkOne-Face Zhang-張東勝 0 Reputation points

2025-01-14T03:36:50.41+00:00

Thank you for replying. I have revised the coding in many parts. In dockerfile.amd64, I have changed the environment to net 6.0 instead because I assumed that net 3.0 maybe outdated. For csproj's targetframework part, in order to comply with the packages, I changed to netstandard2.0;netcoreapp3.1;net6.0 I also add Microsoft.Data.SqlClient(5.1.6), System.Text.Encoding.CodePages(6.0.0), System.Configuration.ConfigurationManager(6.0.1). Now, I am currently struggling with this part of the code

if (messageBody != null && messageBody.machine.temperature > temperatureThreshold) { // Send the message to the output as the temperature value is greater than the threshold. using (var filteredMessage = new Message(messageBytes)) { // Copy the properties of the original message into the new Message object. foreach (KeyValuePair<string, string> prop in messageReceived.Properties) {filteredMessage.Properties.Add(prop.Key, prop.Value);} // Add a new property to the message to indicate it is an alert. filteredMessage.Properties.Add("MessageType", "Alert"); // Send the message. await output.AddAsync(filteredMessage); logger.LogInformation("Info: Received and transferred a message with temperature above the threshold"); } }

Here is the present stage of my logs
info: Function.sqlFunction[1]

Executing 'sqlFunction' (Reason='EdgeHub trigger fired at 2025-01-13T18:59:00.4179883+00:00', Id=d5d7e030-7413-4ed7-a191-e981d35da579)`

info: Function.sqlFunction.User[0]

Info: Received one non-empty message

info: Function.sqlFunction.User[0]

2 rows were updated

info: Function.sqlFunction.User[0]

Info: Received and transferred a message with temperature above the threshold

fail: Function.sqlFunction[3]

Executed 'sqlFunction' (Failed, Id=d5d7e030-7413-4ed7-a191-e981d35da579, Duration=10ms)

Microsoft.Azure.WebJobs.Host.FunctionInvocationException: Exception while executing function: sqlFunction

---> System.InvalidOperationException: Error while handling parameter output after function returned:

---> System.NotSupportedException: Specified method is not supported.

Sander van de Velde | MVP 34,281 Reputation points MVP

2025-01-14T14:48:06.4833333+00:00

Hello @LinkOne-Face Zhang-張東勝 ,

I propose to skip the Azure IoT Edge Function template and write a custom module.

This programming model is so much easier to debug.

Actually, why not starting prototyping with a console app first.

Once you have the code working, migrate it to a custom logic module for Azure IoT Edge.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Tutorial: Store data at the edge with SQL Server databases

1 answer

Your answer