SHARE
TWEET

Untitled

a guest Feb 27th, 2020 74 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. using IoTHubTrigger = Microsoft.Azure.WebJobs.EventHubTriggerAttribute;
  2.  
  3. using Microsoft.Azure.WebJobs;
  4. using Microsoft.Azure.WebJobs.Host;
  5. using Microsoft.Azure.EventHubs;
  6. using System.Text;
  7. using System.Net.Http;
  8. using Microsoft.Extensions.Logging;
  9. using System.Data.SqlClient;
  10. using Newtonsoft.Json;
  11. using System;
  12. using NewInlaminingsuppgift.Models;
  13.  
  14. namespace NewInlaminingsuppgift
  15. {
  16.     public static class SaveToSql
  17.     {
  18.         private static HttpClient client = new HttpClient();
  19.  
  20.         [FunctionName("SaveToSql")]
  21.         public static void Run([IoTHubTrigger("messages/events", Connection = "IotHubConnection", ConsumerGroup = "sql")]EventData message, ILogger log)
  22.         {
  23.             var obj = JsonConvert.DeserializeObject<DeviceMessage>(Encoding.UTF8.GetString(message.Body.Array));
  24.            
  25.             var sqlConn = Environment.GetEnvironmentVariable("sqlConnection");
  26.             using (SqlConnection conn = new SqlConnection(sqlConn))
  27.             {
  28.  
  29.                 string _deviceid = "";
  30.                 int _locationid = -1;
  31.                 int _ownerid = -1;
  32.  
  33.                 var locationQuery = "IF NOT EXISTS (SELECT 1 FROM Location WHERE Latitude = @cLatitude AND Longitud = @cLongitude) " +
  34.                     "INSERT INTO Location (Latitude, Longitud) OUTPUT INSERTED.Id " +
  35.                     "VALUES(@cLatitude, @cLongitude) ELSE SELECT Id FROM Location WHERE Latitude = @cLatitude AND Longitud = @cLongitude";
  36.  
  37.  
  38.                 var deviceQuery = "IF NOT EXISTS (SELECT 1 FROM Devices WHERE DeviceId = @cDeviceId) INSERT INTO Devices (DeviceId, LocationId, OwnerId) " +
  39.                     "OUTPUT INSERTED.DeviceId VALUES(@cDeviceId, @cLocationId, @cOwnerId) ELSE SELECT DeviceId FROM Devices WHERE DeviceId = @cDeviceId";
  40.  
  41.  
  42.                 var ownerQuery = "IF NOT EXISTS (SELECT 1 FROM Owner WHERE MyName = @cMyName) INSERT INTO Owner (MyName, ClassName, SchoolName) OUTPUT INSERTED.Id" +
  43.                     " VALUES(@cMyName, @cClassName, @cSchoolName) ELSE SELECT Id FROM Owner WHERE MyName = @cMyName";
  44.  
  45.  
  46.                 var messageQuery = "INSERT INTO Messages (MessageCounter, DeviceId, Lumen, Temperature, Humidity, Timestamp, TemperatureAlert) " +
  47.                    "VALUES(@cMessageId, @cDeviceId, @cLuminosity, @cTemperature, @cHumidity, @cEpochTime, @cTemperatureAlert)";
  48.  
  49.                 conn.Open();
  50.                 using (SqlCommand cmd = new SqlCommand(locationQuery, conn))
  51.                 {
  52.                     cmd.Parameters.AddWithValue("@cLatitude", message.Properties["latitude"]);
  53.                     cmd.Parameters.AddWithValue("@cLongitude", message.Properties["longitude"]);
  54.  
  55.                     _locationid = int.Parse(cmd.ExecuteScalar().ToString());
  56.                 }
  57.  
  58.                 using (SqlCommand cmd = new SqlCommand(ownerQuery, conn))
  59.                 {
  60.                     cmd.Parameters.AddWithValue("@cSchoolName", message.Properties["schoolName"]);
  61.                     cmd.Parameters.AddWithValue("@cMyName", message.Properties["myName"]);
  62.                     cmd.Parameters.AddWithValue("@cClassName", message.Properties["className"]);
  63.  
  64.                     _ownerid = int.Parse(cmd.ExecuteScalar().ToString());
  65.                 }
  66.  
  67.                 using (SqlCommand cmd = new SqlCommand(deviceQuery, conn))
  68.                 {
  69.                     cmd.Parameters.AddWithValue("@cDeviceId", message.Properties["device"]);
  70.                     cmd.Parameters.AddWithValue("@cLocationId", _locationid);
  71.                     cmd.Parameters.AddWithValue("@cOwnerId", _ownerid);
  72.  
  73.                     _deviceid = cmd.ExecuteScalar().ToString();
  74.                 }
  75.  
  76.                 using (SqlCommand cmd = new SqlCommand(messageQuery, conn))
  77.                 {
  78.                     cmd.Parameters.AddWithValue("@cMessageId", obj.messageCount);
  79.                     cmd.Parameters.AddWithValue("@cDeviceId", _deviceid);
  80.                     cmd.Parameters.AddWithValue("@cTemperatureAlert", message.Properties["temperatureAlert"]);
  81.                     cmd.Parameters.AddWithValue("@cTemperature", obj.Temperature);
  82.                     cmd.Parameters.AddWithValue("@cHumidity", obj.Humidity);
  83.                     cmd.Parameters.AddWithValue("@cLuminosity", obj.Luminosity);
  84.                     cmd.Parameters.AddWithValue("@cEpochTime", obj.EpochTime);
  85.  
  86.                     cmd.ExecuteNonQuery();
  87.                 }
  88.             }
  89.             }
  90.         }
  91. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top