//number of rows the SQL table currently has
public void readnumberofrows()
{
string retrieve = "SELECT COUNT(*) FROM LORA_TABLE";
SqlConnectionStringBuilder sql = LoraSQLConnect.ConnectionString();
using (SqlConnection sqlConn = new SqlConnection(sql.ConnectionString))
{
SqlCommand sqlCommand = new SqlCommand(retrieve, sqlConn);
try
{
sqlConn.Open();
sqlCommand.ExecuteNonQuery();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
norows = reader.GetInt32(0);
}
}
}
catch (SqlException ex)
{
LoraSQLConnect.DisplaySqlErrors(ex,true);
}
}
}
public async static Task DownloadCSV(string sqlquery)
{
FileSavePicker picker = new FileSavePicker();
picker.FileTypeChoices.Add("file style", new string[] { ".csv" });
picker.SuggestedStartLocation = PickerLocationId.DocumentsLibrary;
picker.SuggestedFileName = "LoraGateWayDatasets";
StorageFile file = await picker.PickSaveFileAsync();
if (file != null)
{
string retrieve = sqlquery;
//build connenction string
SqlConnectionStringBuilder sql = LoraSQLConnect.ConnectionString();
using (SqlConnection sqlConn = new SqlConnection(sql.ConnectionString))
{
SqlCommand sqlCommand = new SqlCommand(retrieve, sqlConn);
try
{
sqlConn.Open();
sqlCommand.ExecuteNonQuery();
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
DateTime time = reader.GetDateTime(3);
string dataset = string.Format("{0},{1},{2},{3},{4},{5},{6},{7},{8}",
reader.GetString(1), reader.GetValue(2), time, reader.GetValue(4), reader.GetValue(5),
reader.GetValue(6), reader.GetValue(7), reader.GetValue(8), reader.GetValue(9));
await FileIO.AppendTextAsync(file, dataset + System.Environment.NewLine);
}
}
}
catch (SqlException ex)
{
LoraSQLConnect.DisplaySqlErrors(ex, true);
}
sqlConn.Close();
MessageDialog popup = new MessageDialog("Datasets downloaded", "Your CSV file is ready");
await popup.ShowAsync();
}
}
else
{
MessageDialog popup = new MessageDialog("Dataset Download Aborted", "Dataset Download Aborted");
await popup.ShowAsync();
}
}
//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();
}
}
//function for search date and retrieve sensor data from server
private void findsqlDate(DateTime date)
{
start = 0;
end = 59;
object lastrow = 0;
int counter = 0;
string retrieve = String.Format("SET ROWCOUNT 60; select * from(select Row_Number() over (order by TIMESUBMIT) as RowIndex, * from LORA_TABLE) " +
"as Sub where TimeSubmit >= '{0}';", date.ToString("MM-dd-yyyy HH:mm:ss"));
Debug.WriteLine("interesting", retrieve);
var loradata = LoraSQLConnect.GetLoraDatabaseData(retrieve, true, date, counter, lastrow);
var hankrecords = loradata.Item1;
var lorarecords = loradata.Item2;
CurrentDate.Text = loradata.Item5;
counter = loradata.Item3;
lastrow = loradata.Item4;
start += Convert.ToInt32(lastrow)-counter+1;
end += Convert.ToInt32(lastrow)-counter+1;
(dustChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.rssi).ToList();
(dustChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.rssi).ToList();
}
//load the graphs of sensor data collected
public void LoadChartContents()
{
string retrieve = string.Format("select * from (select Row_Number() over (order by TIMESUBMIT) as RowIndex, * from LORA_TABLE) as Sub Where Sub.RowIndex >= {0} and Sub.RowIndex <= {1};",start,end);
var loradata = LoraSQLConnect.GetLoraDatabaseData(retrieve,false,empty,0,0);
var hankrecords = loradata.Item1;
var lorarecords = loradata.Item2;
CurrentDate.Text = loradata.Item5;
(dustChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[0] as LineSeries).ItemsSource = hankrecords.SelectMany(i => i.rssi).ToList();
(dustChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.dust).ToList();
(uvChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.uv).ToList();
(temperatureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.temperature).ToList();
(pressureChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.pressure).ToList();
(humidityChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.humidity).ToList();
(RSSIChart.Series[1] as LineSeries).ItemsSource = lorarecords.SelectMany(i => i.rssi).ToList();
}