protected void SendNotifications()
{
string todayCount = string.Empty;
string allCount = string.Empty;
string[] statusUpdate = new string[4];
string[] datetime = new string[4];
string[] batteryData = new string[5];
using (SqlConnection connection = new SqlConnection(conStr))
{
string query = @"SELECT [rightalertmaster].[Detection Distance], [activity].[Status] FROM [dbo].[rightalertmaster], [dbo].[activity]";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Notification = null;
SqlDependency dependency = new SqlDependency(command);
dependency.OnChange += new OnChangeEventHandler(dependency_OnChange);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
//message = reader[0].ToString();
}
/// QUERY 1:
SqlConnection conn0 = new SqlConnection(conStr);
conn0.Open();
// cmd2 RETRIEVE DATA
SqlCommand cmd0 = new SqlCommand();
cmd0.Connection = conn0;
cmd0.CommandText = @"SELECT COUNT([Detection Distance]) FROM [dbo].[rightalertmaster] WHERE Date = convert(date,dateadd(hh,-4,getdate()));";
cmd0.Prepare();
SqlDataReader rdr0 = cmd0.ExecuteReader();
//rdr0 = command.ExecuteReader();
if (rdr0.HasRows)
{
while (rdr0.Read())
{
todayCount = rdr0[0].ToString();
}
}
rdr0.Close();
conn0.Close();
///QUERY 2:
SqlConnection conn1 = new SqlConnection(conStr);
conn1.Open();
// cmd2 RETRIEVE DATA
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = conn1;
cmd1.CommandText = @"SELECT COUNT([ID]) FROM [dbo].[rightalertmaster];";
cmd1.Prepare();
SqlDataReader rdr1 = cmd1.ExecuteReader();
if (rdr1.HasRows)
{
while (rdr1.Read())
{
allCount = rdr1[0].ToString();
}
}
rdr1.Close();
conn1.Close();
/// End Query 2
// Query 3: FOR LIVE ACTIVITY FEED:
int i = 0;
SqlConnection conn2 = new SqlConnection(conStr);
conn2.Open();
// cmd2 RETRIEVE DATA
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = conn2;
cmd2.CommandText = @"SELECT TOP 4 Sender, Status, Date, Time FROM [dbo].[activity] ORDER BY ID DESC;";
cmd2.Prepare();
SqlDataReader rdr2 = cmd2.ExecuteReader();
if (rdr2.HasRows)
{
while (rdr2.Read())
{
statusUpdate[i] = "<b>" + rdr2[0].ToString() + ": </b>" + rdr2[1].ToString();
datetime[i] = rdr2[3].ToString().Substring(0, rdr2[3].ToString().IndexOf(".")) + " - " + Regex.Match(rdr2[2].ToString(), "^[^ ]+").Value;
i++;
}
}
rdr2.Close();
conn2.Close();
// END LIVE ACTIVITY FEED SECTION
///QUERY 4:
//i = 0;
SqlConnection conn3 = new SqlConnection(conStr);
conn3.Open();
// cmd4 RETRIEVE DATA
SqlCommand cmd3 = new SqlCommand();
cmd3.Connection = conn3;
cmd3.CommandText = @"SELECT TOP 1 [Bus Voltage], [Shunt Voltage], [Load Voltage], [Current], [Watts] FROM [dbo].[powerdata] ORDER BY ID DESC;";
cmd3.Prepare();
SqlDataReader rdr3 = cmd3.ExecuteReader();
if (rdr3.HasRows)
{
while (rdr3.Read())
{
batteryData[0] = rdr3[0].ToString();
batteryData[1] = rdr3[1].ToString();
batteryData[2] = rdr3[2].ToString();
batteryData[3] = rdr3[3].ToString();
batteryData[4] = rdr3[4].ToString();
//i++;
}
}
rdr3.Close();
conn3.Close();
/// End Query 4
NotificationHub nHub = new NotificationHub();
nHub.NotifyAllClients(todayCount, allCount, statusUpdate, datetime, batteryData);
}
}
}