Guest User

Untitled

a guest
Feb 27th, 2020
241
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