//send sensor data to sql server private async void SendQuerytoSql() { //for testign // INSERT INTO LORA_TABLE (ID, Trans , TimeSubmit, Dust, UV, Temp, Pressure, Humidity, Altitude) VALUES ('HANK',102,'2018/7/11 4:00',0.00,0.12,28.89,10000.67,56.78,43.26); string sendQuery = String.Format( "INSERT INTO LORA_TABLE (ID, Trans , TimeSubmit, Dust, UV, Temp, Pressure, Humidity, Altitude) " + "VALUES ({0},{1},{2},{3},{4},{5},{6},{7},{8});" , ("'" + id + "'"), transn, "'" + daten + "'", dustn, uvn, tempn, pressn, humn, RSSIn); if (!isdesktop && USBLabel.Text != "Writing Logs") { await GetLogging.WritetoTxtFile("SQLQuery:" + sendQuery, ejectpendrive, "Logs.txt", true); } SqlConnectionStringBuilder sql = LoraSQLConnect.ConnectionString(); using (SqlConnection sqlConn = new SqlConnection(sql.ConnectionString)) { SqlCommand sqlCommand = new SqlCommand(sendQuery, sqlConn); //Place your query here, not the sqlConn try { sqlConn.Open(); sqlCommand.ExecuteNonQuery(); sqlstatus.Text = sqlConn.State.ToString(); if (sqlstatus.Text == "Open") { sqlstatus.Text += ",Data Sent!"; } } catch (SqlException ex) { if (!isdesktop && USBLabel.Text != "Writing Logs") { LoraSQLConnect.DisplaySqlErrors(ex, isdesktop); for (int i = 0; i < ex.Errors.Count; i++) { USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile("Index #" + i + "\n" + "Error: " + ex.Errors[i].ToString() + "\n", ejectpendrive, "Logs.txt", true); USBLabel.Text = "Logs Written"; await GetLogging.EmailSendLogs("SQL Status Exception on Lora Rpi Gateway", "Index #" + i + "\n" + "Error: " + ex.Errors[i].ToString() + "\n"); } sqlstatus.Text = "Disconnected."; ReadRestart(); } } sqlConn.Close(); } }
/// <summary> /// ListAvailablePorts /// - Use SerialDevice.GetDeviceSelector to enumerate all serial devices /// - Attaches the DeviceInformation to the ListBox source so that DeviceIds are displayed /// </summary> private async void ListAvailablePorts() { try { if (!isdesktop && USBLabel.Text != "Writing Logs") { await GetLogging.EmailSendLogs("Lora Gateway has started.", string.Format("Rpi Started on {0}", DateTime.Now)); USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile("Lora Gateway has started", ejectpendrive, "Logs.txt", true); USBLabel.Text = "Logs Written"; status.Text = "Sending Email"; } string aqs = SerialDevice.GetDeviceSelector(); var dis = await DeviceInformation.FindAllAsync(aqs); status.Text = "Select a device and connect"; for (int i = 0; i < dis.Count; i++) { listOfDevices.Add(dis[i]); } DeviceListSource.Source = listOfDevices; ConnectDevices.SelectedIndex = 1; //change this to connect to the index of the connected devices in the "Select Device:" list Start_Connection(); } catch (Exception ex) { status.Text = ex.Message; if (!isdesktop && USBLabel.Text != "Writing Logs") { rpiicon.Source = new BitmapImage(new Uri("ms-appx:///Assets/rpidiscon.jpeg")); await GetLogging.EmailSendLogs("Status Exception on Lora Rpi Gateway", status.Text); USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile(status.Text, ejectpendrive, "Logs.txt", true); USBLabel.Text = "Logs Written"; ReadRestart(); } } }
/// <summary> /// - Create a DataReader object /// - Create an async task to read from the SerialDevice InputStream /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private async void Listen() { try { if (serialPort != null) { dataReaderObject = new DataReader(serialPort.InputStream); // keep reading the serial input while (true) { await ReadAsync(ReadCancellationTokenSource.Token); } } } catch (TaskCanceledException tce) { status.Text = "Reading task was cancelled, closing device and cleaning up"; if (!isdesktop && USBLabel.Text != "Writing Logs") { rpiicon.Source = new BitmapImage(new Uri("ms-appx:///Assets/rpidiscon.jpeg")); disconnectgif.Visibility = Visibility.Visible; connectgif.Visibility = Visibility.Collapsed; await GetLogging.EmailSendLogs("Status Exception on Lora Rpi Gateway", status.Text); USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile(status.Text, ejectpendrive, "Logs.txt", true); USBLabel.Text = "Logs Written"; ReadRestart(); } CloseDevice(); } catch (Exception ex) { status.Text = ex.Message; if (!isdesktop && USBLabel.Text != "Writing Logs") { rpiicon.Source = new BitmapImage(new Uri("ms-appx:///Assets/rpidiscon.jpeg")); disconnectgif.Visibility = Visibility.Visible; connectgif.Visibility = Visibility.Collapsed; await GetLogging.EmailSendLogs("Status Exception on Lora Rpi Gateway", status.Text + String.Format("\n{0}", rcvdText.Text)); USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile(status.Text + String.Format("\n{0}", rcvdText.Text), ejectpendrive, "Logs.txt", true); USBLabel.Text = "Logs Written"; ReadRestart(); } } finally { // Cleanup once complete if (dataReaderObject != null) { dataReaderObject.DetachStream(); dataReaderObject = null; } } }
/// <summary> /// ReadAsync: Task that waits on data and reads asynchronously from the serial device InputStream /// </summary> /// <param name="cancellationToken"></param> /// <returns></returns> private async Task ReadAsync(CancellationToken cancellationToken) { Task <UInt32> loadAsyncTask; uint ReadBufferLength = 1024; // If task cancellation was requested, comply cancellationToken.ThrowIfCancellationRequested(); // Set InputStreamOptions to complete the asynchronous read operation when one or more bytes is available dataReaderObject.InputStreamOptions = InputStreamOptions.Partial; using (var childCancellationTokenSource = CancellationTokenSource.CreateLinkedTokenSource(cancellationToken)) { // Create a task object to wait for data on the serialPort.InputStream loadAsyncTask = dataReaderObject.LoadAsync(ReadBufferLength).AsTask(childCancellationTokenSource.Token); // Launch the task and wait UInt32 bytesRead = await loadAsyncTask; if (bytesRead > 0) { connectionring.IsActive = true; status.Text = "Reading Bytes..."; sqlstatus.Text = "Sending Data To Server..."; // Starts string manipulation to receive the infos rcvdText.Text = dataReaderObject.ReadString(bytesRead); string received = rcvdText.Text; string[] transmissions = received.Split("No.", StringSplitOptions.RemoveEmptyEntries); foreach (var word in transmissions) { Debug.WriteLine(word); transmission = word.Remove(word.IndexOf('f') - 1); id = word.Remove(word.IndexOf('d') - 1).Remove(0, word.IndexOf('m') + 2); date = word.Remove(word.IndexOf('k') - 5).Remove(0, word.IndexOf('m') + 2 + id.Length + 2); dust = word.Remove(word.IndexOf('k') + 6).Remove(0, word.IndexOf('d') + date.Length + 1); uv = word.Remove(word.IndexOf('w') + 6).Remove(0, word.IndexOf('k') + 6).Replace('w', 'W'); temp = word.Remove(word.LastIndexOf('c') + 1).Remove(0, word.IndexOf('b') + 1).Replace("c", "°C"); press = word.Remove(word.IndexOf('p') + 2).Remove(0, word.LastIndexOf('c') + 2).Replace('p', 'P'); hum = word.Remove(word.IndexOf('%') + 1).Remove(0, word.IndexOf('p') + 3); RSSI = word.Remove(0, word.IndexOf('%') + 2).Replace('m', ' '); string format = "yyyy-MM-dd HH:mm:ss"; date = DateTime.Now.ToString(format); //this counter is to verify the number of transmission is changing and transmission is true for the sql to upload data setter = Int32.Parse(transmission); //Update all the received infos to the labels transmissiont.Text = transmission; idt.Text = id; datet.Text = date; dustt.Text = dust; uvt.Text = uv; tempt.Text = temp; presst.Text = press; humt.Text = hum; RSSIt.Text = RSSI; //convert all of them to numbers so I can upload them to SQL transn = setter; // id is a string, no need change number daten = Convert.ToDateTime(date); dustn = Convert.ToDouble(dust.Remove(dust.Length - 6)); uvn = Convert.ToDouble(uv.Remove(uv.Length - 7)); tempn = Convert.ToDouble(temp.Remove(temp.Length - 2)); pressn = Convert.ToDouble(press.Remove(press.Length - 2)); humn = Convert.ToDouble(hum.Remove(hum.Length - 1)); RSSIn = Convert.ToDouble(RSSI.Remove(RSSI.Length - 1)); status.Text = "Bytes read successfully!"; //Add a pendrive into the rpi before running this code, otherwise an exception will be thrown if (!isdesktop && USBLabel.Text != "Writing Logs") { USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile(transmission + "," + id + "," + date + "," + dust + "," + uv + "," + temp + "," + press + "," + hum + "," + RSSI, ejectpendrive, "data.txt", false); USBLabel.Text = "Logs Written"; } SendQuerytoSql(); connectionring.IsActive = false; disconnectgif.Visibility = Visibility.Collapsed; connectgif.Visibility = Visibility.Visible; } } } }
private async void Start_Connection() { try { var selection = ConnectDevices.SelectedItems; if (selection.Count <= 0) { status.Text = "Select a device and connect"; return; } DeviceInformation entry = (DeviceInformation)selection[0]; serialPort = await SerialDevice.FromIdAsync(entry.Id); if (serialPort == null) { return; } // Configure serial settings serialPort.ReadTimeout = TimeSpan.FromMilliseconds(1000); serialPort.BaudRate = 9600; serialPort.Parity = SerialParity.None; serialPort.StopBits = SerialStopBitCount.One; serialPort.DataBits = 8; serialPort.Handshake = SerialHandshake.None; // Display configured settings status.Text = "Serial port configured successfully: "; status.Text += serialPort.BaudRate + "-"; status.Text += serialPort.DataBits + "-"; status.Text += serialPort.Parity.ToString() + "-"; status.Text += serialPort.StopBits; // Set the RcvdText field to invoke the TextChanged callback // The callback launches an async Read task to wait for data rcvdText.Text = "Waiting for data..."; // Create cancellation token object to close I/O operations when closing the device ReadCancellationTokenSource = new CancellationTokenSource(); // Enable 'WRITE' button to allow sending data Listen(); } catch (Exception ex) { status.Text = ex.Message; if (!isdesktop && USBLabel.Text != "Writing Logs") { rpiicon.Source = new BitmapImage(new Uri("ms-appx:///Assets/rpidiscon.jpeg")); await GetLogging.EmailSendLogs("Status Exception on Lora Rpi Gateway", status.Text + String.Format("\n{0}", rcvdText.Text)); USBLabel.Text = "Writing Logs"; await GetLogging.WritetoTxtFile(status.Text + String.Format("\n{0}", rcvdText.Text), ejectpendrive, "Logs.txt", true); USBLabel.Text = "Logs Written"; ReadRestart(); } } }