//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;
}
}
}
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();
}
}
}
