示例#1
0
        private void InsertOrUpdateSpotsTable(ParkingSpot spot)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM ParkingSpots WHERE Name=@name", connection);
                cmd.Parameters.AddWithValue("@name", spot.Name);
                cmd.ExecuteNonQuery();
                SqlDataReader reader  = cmd.ExecuteReader();
                bool          hasRows = reader.HasRows;
                reader.Close();
                if (!hasRows)
                {
                    cmd = new SqlCommand("INSERT INTO ParkingSpots(Name, Park_Id, Location, BatteryStatus, Timestamp, Value) VALUES(@name, @id, @location, @batteryStatus, @timestamp, @value)", connection);
                    cmd.Parameters.AddWithValue("@id", spot.Id);
                    cmd.Parameters.AddWithValue("@location", spot.Location);
                    cmd.Parameters.AddWithValue("@batteryStatus", spot.BatteryStatus);
                    cmd.Parameters.AddWithValue("@timestamp", spot.Status.Timestamp);
                    cmd.Parameters.AddWithValue("@value", spot.Status.Value);
                    cmd.Parameters.AddWithValue("@name", spot.Name);
                    cmd.ExecuteNonQuery();

                    string timestamp = spot.Status.Timestamp;
                    timestamp = timestamp.Replace("/", "-");
                    string[] dateAndTime = timestamp.Split(' ');
                    timestamp = dateAndTime[0];
                    string[] time   = dateAndTime[1].Split(':');
                    string   hour   = time[0];
                    string   minute = time[1];

                    cmd = new SqlCommand("INSERT INTO SpotsHistory(Name, Timestamp, Value, Hour, Minute) VALUES(@name, @timestamp, @value, @hour, @minute)", connection);
                    cmd.Parameters.AddWithValue("@timestamp", timestamp);
                    cmd.Parameters.AddWithValue("@value", spot.Status.Value);
                    cmd.Parameters.AddWithValue("@name", spot.Name);
                    cmd.Parameters.AddWithValue("@hour", hour);
                    cmd.Parameters.AddWithValue("@minute", minute);
                    cmd.ExecuteNonQuery();
                }
                else
                {
                    /**
                     * Se o campo alterado for value, copiar linha para o historico e só depois fazer update
                     * */
                    cmd = new SqlCommand("SELECT Name, Timestamp, Value FROM ParkingSpots WHERE Name=@name", connection);
                    cmd.Parameters.AddWithValue("@name", spot.Name);
                    cmd.ExecuteNonQuery();
                    SqlDataReader valueFromDB = cmd.ExecuteReader();
                    valueFromDB.Read();
                    string value = valueFromDB.GetString(2), timestamp = spot.Status.Timestamp, name = valueFromDB.GetString(0);
                    timestamp = timestamp.Replace("/", "-");
                    string[] dateAndTime = timestamp.Split(' ');
                    timestamp = dateAndTime[0];
                    string hour   = dateAndTime[1].Substring(0, 2);
                    string minute = dateAndTime[1].Substring(3, 2);
                    valueFromDB.Close();

                    if (value != spot.Status.Value)
                    {
                        cmd = new SqlCommand("INSERT INTO SpotsHistory(Name, Timestamp, Value, Hour, Minute) VALUES(@name, @timestamp, @value, @hour, @minute)", connection);
                        cmd.Parameters.AddWithValue("@timestamp", timestamp);
                        cmd.Parameters.AddWithValue("@value", spot.Status.Value);
                        cmd.Parameters.AddWithValue("@name", spot.Name);
                        cmd.Parameters.AddWithValue("@hour", hour);
                        cmd.Parameters.AddWithValue("@minute", minute);
                        cmd.ExecuteNonQuery();
                    }

                    cmd = new SqlCommand("UPDATE ParkingSpots SET BatteryStatus=@batteryStatus, Timestamp=@timestamp, Value=@value WHERE Name=@name", connection);
                    cmd.Parameters.AddWithValue("@batteryStatus", spot.BatteryStatus);
                    cmd.Parameters.AddWithValue("@timestamp", spot.Status.Timestamp);
                    cmd.Parameters.AddWithValue("@value", spot.Status.Value);
                    cmd.Parameters.AddWithValue("@name", spot.Name);
                    cmd.ExecuteNonQuery();
                }
                connection.Close();
            }
        }