The C# code given below is part of a Windows Forms application designed to execute a SQL query, retrieve sensor data from a PostgreSQL database, and save the data as a CSV file. The application uses the Npgsql library to connect to the PostgreSQL database. Visual Studio Community 2019 was used to write and execute the program. The input that has to be inserted for the different sensors in the sql command textbox are as follows:
| Column 1 | Column 2 |
|---|---|
| Indoor temperature and humidity sensor | select device_name, received_at, object → ‘temperature’ AS Temperature, object → ‘humidity’ AS Humidity, object → ‘battery’ AS Device_Battery from device_up where device_name=‘R711’; |
| Presence sensor | select device_name, received_at, object → ‘occupied’ AS Occupied, object → ‘illuminance’ AS Illuminance, object → ‘temperature’ AS Temperature, object → ‘battery’ AS Device_Battery from device_up where device_name=‘RB11E’; |
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Npgsql; //install NuGet package Npgsql required
namespace csvFileSave
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
string sql = sqlBox.Text;
DataTable dt = saveToCSVFile.createDataTable(sql);
string filename = nameBox.Text + ".csv";
dt.ToCSV(filename);
}
private void label1_Click(object sender, EventArgs e)
{
}
private void nameBox_TextChanged(object sender, EventArgs e)
{
}
private void sqlBox_TextChanged(object sender, EventArgs e)
{
}
private void label3_Click(object sender, EventArgs e)
{
}
private void button1_Click_1(object sender, EventArgs e)
{
}
}
public static class saveToCSVFile
{
public static DataTable createDataTable(string sql)
{
//connection to database
var cs = "Host=13.76.129.228;Username=chirpstack_as;Password=dbpassword;Database=chirpstack_as";
var con = new NpgsqlConnection(cs);
con.Open();
//get data
NpgsqlCommand cmd;
cmd = new NpgsqlCommand(sql, con);
NpgsqlDataReader rdr = cmd.ExecuteReader();
//create data table
DataTable table = new DataTable();
//columns
for (int i = 0; i < rdr.FieldCount; i++)
{
table.Columns.Add($"{rdr.GetName(i)}");
}
//rows
while (rdr.Read())
{
DataRow inserting = table.NewRow();
for (int i = 0; i < rdr.FieldCount; i++)
{
inserting[i] = rdr.GetValue(i);
}
table.Rows.Add(inserting);
}
return table;
}
}
public static class CSVUtility
{
public static void ToCSV(this DataTable dtDataTable, string strFilePath)
{
StreamWriter sw = new StreamWriter(strFilePath, false);
//headers
for (int i = 0; i < dtDataTable.Columns.Count; i++)
{
sw.Write(dtDataTable.Columns[i]);
if (i < dtDataTable.Columns.Count - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
foreach (DataRow dr in dtDataTable.Rows)
{
for (int i = 0; i < dtDataTable.Columns.Count; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
string value = dr[i].ToString();
if (value.Contains(','))
{
value = String.Format("\"{0}\"", value);
sw.Write(value);
}
else
{
sw.Write(dr[i].ToString());
}
}
if (i < dtDataTable.Columns.Count - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
}
}
}
Can anyone please provide some guidance on how to push the sensor data to my 3D building model imported to Unreal Engine through Datasmith? Do I need to modify the above code to convert the sensor data to JSON, and then send it to Unreal Engine using HTTP post? Thanks to help.