Example #1
0
        //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);
                }
            }
        }
Example #2
0
        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();
            }
        }
Example #3
0
        //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();
            }
        }
Example #4
0
        //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();
        }
Example #5
0
        //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(); 
        }