Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- using IoTHubTrigger = Microsoft.Azure.WebJobs.EventHubTriggerAttribute;
- using Microsoft.Azure.WebJobs;
- using Microsoft.Azure.WebJobs.Host;
- using Microsoft.Azure.EventHubs;
- using System.Text;
- using System.Net.Http;
- using Microsoft.Extensions.Logging;
- using System.Data.SqlClient;
- using Newtonsoft.Json;
- using System;
- using NewInlaminingsuppgift.Models;
- namespace NewInlaminingsuppgift
- {
- public static class SaveToSql
- {
- private static HttpClient client = new HttpClient();
- [FunctionName("SaveToSql")]
- public static void Run([IoTHubTrigger("messages/events", Connection = "IotHubConnection", ConsumerGroup = "sql")]EventData message, ILogger log)
- {
- var obj = JsonConvert.DeserializeObject<DeviceMessage>(Encoding.UTF8.GetString(message.Body.Array));
- var sqlConn = Environment.GetEnvironmentVariable("sqlConnection");
- using (SqlConnection conn = new SqlConnection(sqlConn))
- {
- string _deviceid = "";
- int _locationid = -1;
- int _ownerid = -1;
- var locationQuery = "IF NOT EXISTS (SELECT 1 FROM Location WHERE Latitude = @cLatitude AND Longitud = @cLongitude) " +
- "INSERT INTO Location (Latitude, Longitud) OUTPUT INSERTED.Id " +
- "VALUES(@cLatitude, @cLongitude) ELSE SELECT Id FROM Location WHERE Latitude = @cLatitude AND Longitud = @cLongitude";
- var deviceQuery = "IF NOT EXISTS (SELECT 1 FROM Devices WHERE DeviceId = @cDeviceId) INSERT INTO Devices (DeviceId, LocationId, OwnerId) " +
- "OUTPUT INSERTED.DeviceId VALUES(@cDeviceId, @cLocationId, @cOwnerId) ELSE SELECT DeviceId FROM Devices WHERE DeviceId = @cDeviceId";
- var ownerQuery = "IF NOT EXISTS (SELECT 1 FROM Owner WHERE MyName = @cMyName) INSERT INTO Owner (MyName, ClassName, SchoolName) OUTPUT INSERTED.Id" +
- " VALUES(@cMyName, @cClassName, @cSchoolName) ELSE SELECT Id FROM Owner WHERE MyName = @cMyName";
- var messageQuery = "INSERT INTO Messages (MessageCounter, DeviceId, Lumen, Temperature, Humidity, Timestamp, TemperatureAlert) " +
- "VALUES(@cMessageId, @cDeviceId, @cLuminosity, @cTemperature, @cHumidity, @cEpochTime, @cTemperatureAlert)";
- conn.Open();
- using (SqlCommand cmd = new SqlCommand(locationQuery, conn))
- {
- cmd.Parameters.AddWithValue("@cLatitude", message.Properties["latitude"]);
- cmd.Parameters.AddWithValue("@cLongitude", message.Properties["longitude"]);
- _locationid = int.Parse(cmd.ExecuteScalar().ToString());
- }
- using (SqlCommand cmd = new SqlCommand(ownerQuery, conn))
- {
- cmd.Parameters.AddWithValue("@cSchoolName", message.Properties["schoolName"]);
- cmd.Parameters.AddWithValue("@cMyName", message.Properties["myName"]);
- cmd.Parameters.AddWithValue("@cClassName", message.Properties["className"]);
- _ownerid = int.Parse(cmd.ExecuteScalar().ToString());
- }
- using (SqlCommand cmd = new SqlCommand(deviceQuery, conn))
- {
- cmd.Parameters.AddWithValue("@cDeviceId", message.Properties["device"]);
- cmd.Parameters.AddWithValue("@cLocationId", _locationid);
- cmd.Parameters.AddWithValue("@cOwnerId", _ownerid);
- _deviceid = cmd.ExecuteScalar().ToString();
- }
- using (SqlCommand cmd = new SqlCommand(messageQuery, conn))
- {
- cmd.Parameters.AddWithValue("@cMessageId", obj.messageCount);
- cmd.Parameters.AddWithValue("@cDeviceId", _deviceid);
- cmd.Parameters.AddWithValue("@cTemperatureAlert", message.Properties["temperatureAlert"]);
- cmd.Parameters.AddWithValue("@cTemperature", obj.Temperature);
- cmd.Parameters.AddWithValue("@cHumidity", obj.Humidity);
- cmd.Parameters.AddWithValue("@cLuminosity", obj.Luminosity);
- cmd.Parameters.AddWithValue("@cEpochTime", obj.EpochTime);
- cmd.ExecuteNonQuery();
- }
- }
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement