public IActionResult Index()
{
SqlServerConnHelp sqlConnManager = new SqlServerConnHelp();
SqlConnection sqlConnection = sqlConnManager.GetConnection(@"DELL-SVR\SQLEXPRESS", "SensorData", "jdw", "asdf");
//SqlConnection sqlConnection = sqlConnManager.GetConnection(@"LAPTOP-AD2UF7HI\SQLEXPRESS", "SensorData", "jdw", "asdf");
string query = "select a.UniqueID, a.Created_On, (Select Max(SensorValue) From SensorDataDetail b where b.UniqueID = a.UniqueID AND b.SensorNode = 'Temperature'), (Select Max(SensorValue) From SensorDataDetail b where b.UniqueID = a.UniqueID AND b.SensorNode = 'Moisture'), (Select Max(SensorValue) From SensorDataDetail b where b.UniqueID = a.UniqueID AND (b.SensorNode = 'Sunlight' or b.SensorNode = 'Luminosity')) from SensorData a";
SqlCommand cmd = new SqlCommand(query, sqlConnection);
var reader = cmd.ExecuteReader();
List <SensorDataFields> db = new List <SensorDataFields>();
while (reader.Read())
{
SensorDataFields row = new SensorDataFields();
row.UniqueId = reader.GetString(0).Trim();
row.date = reader.GetDateTime(1);
row.Temperature = reader.GetString(2).Trim();
row.Moisture = reader.GetString(3).Trim();
row.Luminosity = reader.GetString(4).Trim();
db.Add(row);
}
reader.Close();
cmd.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
ViewData["SensorDataFields"] = db;
return(View());
}
public IActionResult Index()
{
SqlServerConnHelp sqlConnManager = new SqlServerConnHelp();
SqlConnection sqlConnection = sqlConnManager.GetConnection(@"DELL-SVR\SQLEXPRESS", "SensorData", "jdw", "asdf");
//SqlConnection sqlConnection = sqlConnManager.GetConnection(@"LAPTOP-AD2UF7HI\SQLEXPRESS", "SensorData", "jdw", "asdf");
string query = "Select * from SensorData";
SqlCommand cmd = new SqlCommand(query, sqlConnection);
var reader = cmd.ExecuteReader();
List <SensorDataFields> db = new List <SensorDataFields>();
while (reader.Read())
{
SensorDataFields row = new SensorDataFields();
row.Id = reader.GetInt32(0);
row.UniqueId = reader.GetString(1).Trim();
row.TransmissionId = reader.GetString(2).Trim();
row.IpAddress = reader.GetString(3).Trim();
row.date = reader.GetDateTime(4);
db.Add(row);
}
reader.Close();
cmd.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
ViewData["SensorDataFields"] = db;
return(View());
}
//public void Post([FromBody] string value)
public void Post()
{
HttpRequest req = Request; //Represents the REST request
StreamReader sr = new StreamReader(req.Body); //plain-text must get data from Request.Body
string sensData = sr.ReadToEnd(); //Read entire Request.Body data stream
DataReadings dataReadings = new DataReadings();
dataReadings = JsonConvert.DeserializeObject <DataReadings>(sensData);
//Make connection to my database within SQL server on my laptop with a login
SqlServerConnHelp sqlConnManager = new SqlServerConnHelp();
//Use to connect to server
SqlConnection sqlConnection = sqlConnManager.GetConnection(@"DELL-SVR\SQLEXPRESS", "SensorData", "jdw", "asdf");
//Use to connect to my laptop
//SqlConnection sqlConnection = sqlConnManager.GetConnection(@"LAPTOP-AD2UF7HI\SQLEXPRESS", "SensorData", "jdw", "asdf");
//Used to insert data into the first database *SensorData*
//Insert data:: Insert Into Table (Column A, Column B) Values (Value A, Value B)
string query = "Insert Into SensorData (UniqueID, Transmitter_ID, IP_Address) Values (@UniqueID, @Transmitter_ID, @IP_Address)";
SqlCommand cmd = new SqlCommand(query, sqlConnection); //Create a new command for database w/ string and connection
IdGenerator idGen = new IdGenerator(); //Create auto generating variable
string uid = idGen.GetRandomTransmissionId();
//Fill out command with parameters
cmd.Parameters.AddWithValue("@UniqueID", uid);
cmd.Parameters.AddWithValue("@Transmitter_ID", "1111-FFFF");
cmd.Parameters.AddWithValue("@IP_Address", idGen.GetRandomIPAddress());
//Send command to database
cmd.ExecuteNonQuery();
//Used to insert data into the second database *SensorDataDetail*
for (int i = 0; i < 3; i++)
{
query = "Insert Into SensorDataDetail (UniqueID, SensorNode, SensorValue) Values (@UniqueID, @SensorNode, @SensorValue)";
cmd = new SqlCommand(query, sqlConnection);
cmd.Parameters.AddWithValue("@UniqueID", uid); //Fillout command with paramaters
switch (i)
{
case 0:
cmd.Parameters.AddWithValue("@SensorNode", "Temperature");
cmd.Parameters.AddWithValue("@SensorValue", dataReadings.Temperature.ToString());
break;
case 1:
cmd.Parameters.AddWithValue("@SensorNode", "Moisture");
cmd.Parameters.AddWithValue("@SensorValue", dataReadings.Moisture.ToString());
break;
case 2:
cmd.Parameters.AddWithValue("@SensorNode", "Luminosity");
cmd.Parameters.AddWithValue("@SensorValue", dataReadings.Luminosity.ToString());
break;
}
//Send command to database
cmd.ExecuteNonQuery();
}
cmd.Dispose(); //clean up
sqlConnection.Close(); //close connection to database
sqlConnection.Dispose(); //clean up
}