/// <summary> /// Records the Chain Stretch information passed to it to the line database in a chainstretch table. if the table doesnt exist yet it generates it. /// </summary> public void RunSQLCommand(string message) { try //try loop in case command fails. { string jsonString = message.Substring(7, message.Length - 7); //grab json data from the end. JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject; //Convert json to object SNPService.DiagnosticItems.Enqueue(new DiagnosticItem("CustomSQLFunction with contents" + receivedPacket["Command"].ToString(), 3)); using (SqlConnection connection = new SqlConnection(SNPService.ENGDBConnection.ConnectionString)) { connection.Open(); //open the connection using (SqlCommand command = new SqlCommand(receivedPacket["Command"].ToString(), connection)) { //Comand Time! int rowsAffected = command.ExecuteNonQuery(); // execute the command returning number of rows affected SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(rowsAffected + " row(s) inserted", 2)); //logit } } } catch (Exception ex) //catch exceptions { if (ex.Message.Contains("ExecuteNonQuery requires an open and available Connection.")) //if the connection crashed { SNPService.ReastablishSQL(RunSQLCommand, message); //reastablish it } SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(ex.ToString(), 1)); //else log the error and move on } }
/// <summary> /// Records the Chain Stretch information passed to it to the line database in a chainstretch table. if the table doesnt exist yet it generates it. /// </summary> public void Index(string message) { try //try loop in case command fails. { string jsonString = message.Substring(7, message.Length - 7); //grab json data from the end. JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject; //Convert json to object StringBuilder sqlStringBuilder = new StringBuilder(); sqlStringBuilder.Append(" USE [EngDb-" + receivedPacket["Line"] + "] "); //select database sqlStringBuilder.Append("IF not EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'ChainStretch') BEGIN CREATE TABLE [dbo].[ChainStretch]"); sqlStringBuilder.Append("([Timestamp] [datetime2](7) NOT NULL,[Head] [int] NOT NULL,[Stretch] [float] NOT NULL,[B32_Output] [bit] NOT NULL,[B31_Input] [bit] NOT NULL) ON [PRIMARY] END "); //start loading the command into the string sqlStringBuilder.Append("Insert into ChainStretch ([Head],[Stretch],[Timestamp],[B32_Output],[B31_Input]) values (@Head,@Stretch,@Timestamp,@Output,@Input)"); using (SqlConnection connection = new SqlConnection(SNPService.ENGDBConnection.ConnectionString)) { double stretch = 0; double.TryParse(receivedPacket["Stretch"].ToString(), out stretch); connection.Open(); //open the connection using (SqlCommand command = new SqlCommand(sqlStringBuilder.ToString(), connection)) { //Comand Time! command.Parameters.AddWithValue("@Head", Convert.ToInt32(receivedPacket["Head"])); //replace parameters with values command.Parameters.AddWithValue("@Stretch", stretch); command.Parameters.AddWithValue("@Input", Convert.ToInt32(receivedPacket["Input"]) == 1); command.Parameters.AddWithValue("@Output", Convert.ToInt32(receivedPacket["Output"]) == 1); command.Parameters.AddWithValue("@TimeStamp", DateTime.Now); int rowsAffected = command.ExecuteNonQuery(); // execute the command returning number of rows affected SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(rowsAffected + " row(s) inserted", 2)); //logit } } } catch (Exception ex) //catch exceptions { if (ex.Message.Contains("ExecuteNonQuery requires an open and available Connection.")) //if the connection crashed { SNPService.ReastablishSQL(Index, message); //reastablish it } SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(ex.ToString(), 1)); //else log the error and move on } }
/// <summary> /// Packet Sent every index for the EMP system. Simply insert into SQL for recording ( and grab a time stamp if missing) /// </summary> public void IndexPacket(string message) { SNPService.DiagnosticItems.Enqueue(new DiagnosticItem("EMPIndexPacketReceived!", 2)); //log the packet being received in Diagnostic. try //try loop in case command fails. { string jsonString = message.Substring(7, message.Length - 7); //grab json data from the end. JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject; //Convert json to jobject StringBuilder sqlStringBuilder = new StringBuilder(); //string builder used to build sql string sqlStringBuilder.Append("INSERT INTO EMPTable ("); //start building sql List <string> keys = receivedPacket.Properties().Select(p => p.Name).ToList(); //gets list of all keys in json object string keySection = ""; //stores the key section of the sql string valueSection = ""; //stores the value section of the sql bool MissingStamp = true; //used to tell weather or not a timestamp was passed in foreach (string key in keys) //foreach key { keySection += key + ", "; //Make a key valueSection += "@" + key + ", "; //and value Reference to be replaced later if (key == "TimeStamp") // if we receive a time stamp key we are receiving a timestamp { MissingStamp = false; } } if (MissingStamp) //if we didnt receive a timestamp { keySection += "TimeStamp"; //Make a Time key valueSection += "@TimeStamp"; //and value Reference to be replaced later } else { valueSection = valueSection.Substring(0, valueSection.Length - 2); //if we have extra data at the end remove it keySection = keySection.Substring(0, keySection.Length - 2); //from the key section to } sqlStringBuilder.Append(keySection + " )"); //cap of the key section sqlStringBuilder.Append("Values ( " + valueSection + " );"); //append value section to the command string string SQLString = sqlStringBuilder.ToString(); //convert vuilder to string using (SqlConnection connection = new SqlConnection(SNPService.ENGDBConnection.ConnectionString)) { connection.Open(); //open the connection using (SqlCommand command = new SqlCommand(SQLString, connection)) { //Comand Time! foreach (string key in keys) //foreach key { switch (key) { case "Temperature": //if we have the Temperature convert to decimal and update command.Parameters.AddWithValue("@" + key, Convert.ToDecimal(receivedPacket[key])); break; case "Humidity": //if we have the Humidity convert to decimal and update command.Parameters.AddWithValue("@" + key, Convert.ToDecimal(receivedPacket[key].ToString())); break; case "FlowRate": //if we have the FlowRate convert to decimal and update command.Parameters.AddWithValue("@" + key, Convert.ToDecimal((receivedPacket[key]))); break; case "ChangeOver5Seconds": //if we have the ChangeOver5Seconds convert to decimal and update command.Parameters.AddWithValue("@" + key, Convert.ToDecimal(receivedPacket[key])); break; case "TimeStamp": //if we have the TimeStamp convert to datetime and update string TimeStamp = receivedPacket[key].ToString(); int year = 2000 + Convert.ToInt32(TimeStamp.Substring(0, 2)); //grab the Year int month = Convert.ToInt32(TimeStamp.Substring(3, 2)); //month int day = Convert.ToInt32(TimeStamp.Substring(6, 2)); //day int hour = Convert.ToInt32(TimeStamp.Substring(9, 2)); //hour int minute = Convert.ToInt32(TimeStamp.Substring(12, 2)); //minute int second = Convert.ToInt32(TimeStamp.Substring(15, 2)); //second from the passed packet command.Parameters.AddWithValue("@" + key, new DateTime(year, month, day, hour, minute, second)); break; case "Location": //if we have the Location update it command.Parameters.AddWithValue("@" + key, receivedPacket[key].ToString()); break; default: break; } } if (MissingStamp) { command.Parameters.AddWithValue("@TimeStamp", DateTime.Now); //grab the timestamp if missing } int rowsAffected = command.ExecuteNonQuery(); // execute the command returning number of rows affected SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(rowsAffected + " row(s) inserted", 2)); //logit } } } catch (Exception ex) //catch exceptions { if (ex.Message.Contains("ExecuteNonQuery requires an open and available Connection.")) //if connection broke { SNPService.ReastablishSQL(IndexPacket, message); //reastablish it } SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(ex.ToString(), 1)); //so if you could log this that would be greaaaaaaat } }
/// <summary> /// SQL Section of the EMP Warning Packet /// </summary> private void SQLEMPWarningPacket(string message) { string SQLString = ""; try //try loop in case command fails. { string jsonString = message.Substring(7, message.Length - 7); //grab json data from the end. JObject receivedPacket = JsonConvert.DeserializeObject(jsonString) as JObject; //convert to jobject StringBuilder sqlStringBuilder = new StringBuilder(); //make sql string builder sqlStringBuilder.Append("INSERT INTO EMPWarningTable ("); //start making sql string List <string> keys = receivedPacket.Properties().Select(p => p.Name).ToList(); //gets list of all keys in json object string keySection = ""; //stores the key section of the sql string valueSection = ""; //stores the value section of the sql bool MissingStamp = true; //records weather we got a time stamp or not foreach (string key in keys) //foreach key { keySection += key + ", "; //Make a key valueSection += "@" + key + ", "; //and value Reference to be replaced later if (key == "TimeStamp") //if we get a TimeStamp record it { MissingStamp = false; } } if (MissingStamp) //If we dont { keySection += "TimeStamp"; //Make a Time key valueSection += "@TimeStamp"; //and value Reference to be replaced later } else { valueSection = valueSection.Substring(0, valueSection.Length - 2); //Next Remove Extra characters keySection = keySection.Substring(0, keySection.Length - 2); //Next Remove Extra characters } sqlStringBuilder.Append(keySection + " )"); //and append/capoff the strings sqlStringBuilder.Append("Values ( " + valueSection + " );"); SQLString = sqlStringBuilder.ToString(); //convert to string using (SqlConnection connection = new SqlConnection(SNPService.ENGDBConnection.ConnectionString)) { connection.Open(); //open the connection using (SqlCommand command = new SqlCommand(SQLString, connection)) { //Command Time Woo! foreach (string key in keys) //foreach key { switch (key) //replace the values { case "Warning": command.Parameters.AddWithValue("@" + key, receivedPacket[key].ToString()); break; case "Urgency": command.Parameters.AddWithValue("@" + key, Convert.ToInt32(receivedPacket[key])); break; case "TimeStamp": if (MissingStamp) { command.Parameters.AddWithValue("@" + key, DateTime.Now); } else { string TimeStamp = receivedPacket[key].ToString(); //grab the string int year = 2000 + Convert.ToInt32(TimeStamp.Substring(0, 2)); //and break it up into int month = Convert.ToInt32(TimeStamp.Substring(3, 2)); //month int day = Convert.ToInt32(TimeStamp.Substring(6, 2)); //day int hour = Convert.ToInt32(TimeStamp.Substring(9, 2)); //hour int minute = Convert.ToInt32(TimeStamp.Substring(12, 2)); //minute int second = Convert.ToInt32(TimeStamp.Substring(15, 2)); //second command.Parameters.AddWithValue("@" + key, new DateTime(year, month, day, hour, minute, second)); //replace paramet with value } break; case "Location": command.Parameters.AddWithValue("@" + key, receivedPacket[key].ToString()); break; default: break; } } int rowsAffected = command.ExecuteNonQuery(); // execute the command returning number of rows affected SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(rowsAffected + " row(s) inserted", 2)); //logit } } } catch (Exception ex) { if (ex.Message.Contains("ExecuteNonQuery requires an open and available Connection."))//if the connection crashed restablish it { SNPService.ReastablishSQL(SQLEMPWarningPacket, message); } SNPService.DiagnosticItems.Enqueue(new DiagnosticItem(ex.ToString(), 1)); //logit } }