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