public void writeBeamToDB(Beam_UDT Beam, SqlCommand command, int Pointer, SqlConnection cnn) { // Write everything from PLC buffer to DB except for anything with BeamID= 0 if (Beam.Beam_Parameters.Value.ID.Value != 0) { Console.WriteLine("Writing Beam with ID:{0} to the database.", Beam.Beam_Parameters.Value.ID.Value); //command is use to perform read and write operations in the database //Command object, which is used to execute the SQL statement against the database command = new SqlCommand(SqlQueryInsertIntoBeams(Beam, Pointer), cnn); //DataAdapter object is used to perform specific SQL operations such as insert, delete and update commands SqlDataAdapter adapter = new SqlDataAdapter(); //we now associate the insert SQL command to our adapter adapter.InsertCommand = new SqlCommand(SqlQueryInsertIntoBeams(Beam, Pointer), cnn); //then issue the ExecuteNonQuery method which is used to execute the Insert statement against our database adapter.InsertCommand.ExecuteNonQuery(); //Dispose of all temp objects created adapter.Dispose(); command.Dispose(); } }
public string SqlQueryInsertIntoBeams(Beam_UDT Beam, int pointer) { return("INSERT INTO Beams (" + "BeamID" + "," + "UniqueID" + "," + "Pointer" + "," + "Ticks" + "," + "Length" + "," + "Width" + "," + "Height" + "," + "Fillet" + "," + "[Drop]" + "," + "[RH Lock weld time]" + "," + "[RH Lock weld score]" + "," + "[LH Lock weld time]" + "," + "[LH Lock weld score]" + "," + "[Beam rejected ?]" + "," + "[Reject reason]" + "," + "[Beam Processed Timestamp]" + "," + "[For welding RH Lock: beam on jig ?]" + "," + "[For welding RH Lock: beam on jig number]" + "," + "[Welding RH Lock: in progress ?]" + "," + "[Welding RH Lock: completed ?]" + "," + "[Progressing to weld 2 ?]" + "," + "[For welding LH Lock: beam on jig ?]" + "," + "[For welding LH Lock: beam on jig number]" + "," + "[Welding LH Lock: in progress ?]" + "," + "[Welding LH Lock: completed ?]" + "," + "[Beam moving towards stillage ?]" + "," + "[Beam moving towards paintline ?]" + "," + "[Beam moving towards reject bin ?]" + "," + "[Beam at stillage ?]" + "," + "[Beam at paintline ?]" + "," + "[Beam at reject bin ?]" + ")" + " Values (" + Beam.Beam_Parameters.Value.ID.Value + "," + "\'" + Beam.Beam_Parameters.Value.ID.Value + "-" + Beam.Beam_Processing_Complete_Timestamp.Value.ToString("s", new CultureInfo("en-US")) + "\'" + "," + pointer + "," + Beam.Beam_Processing_Complete_Timestamp.Value.Ticks + "," + Beam.Beam_Parameters.Value.length.Value + "," + Beam.Beam_Parameters.Value.width.Value + "," + Beam.Beam_Parameters.Value.height.Value + "," + Beam.Beam_Parameters.Value.fillet.Value + "," + Beam.Beam_Parameters.Value.drop.Value + "," + Beam.Weld1_Time.Value + "," + Beam.Weld1_Score.Value + "," + Beam.Weld2_Time.Value + "," + Beam.Weld2_Score.Value + "," + "\'" + Beam.Beam_Reject.Value + "\'" + "," + "\'" + Beam.Beam_Reject_Reason.Value + "\'" + "," + // date format in SQL DB 09/13/2019 9:17:56 AM - US // date format from PLC 13/09/2019 9:17:56 AM - British "\'" + Beam.Beam_Processing_Complete_Timestamp.Value.ToString("G", new CultureInfo("en-US")) + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.weld_1_beam_on_jig.Value + "\'" + "," + Beam.Beam_Process_Stage.Value.weld_1_beam_on_jig_number.Value + "," + "\'" + Beam.Beam_Process_Stage.Value.weld_1_in_progress.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.weld_1_complete.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.moving_to_weld_2.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.weld_2_beam_on_jig.Value + "\'" + "," + Beam.Beam_Process_Stage.Value.weld_2_beam_on_jig_number.Value + "," + "\'" + Beam.Beam_Process_Stage.Value.weld_2_in_progress.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.weld_2_complete.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.moving_to_stillage.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.moving_to_paintline.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.moving_to_reject.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.beam_at_stillage.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.beam_on_paintline.Value + "\'" + "," + "\'" + Beam.Beam_Process_Stage.Value.beam_at_reject.Value + "\'" + ")"); }