Dustin Gibson

Updater (C#, SQL)


Updater is an application that can fetch and store specific parts of a website on a timer using XPath.

Git Link
Latest Build (October 2017)



Code Details
  • Database – Database used is SQLlite which is optimal for local read and writes.
  • UI - Instead of using WinForms, I used WPF which uses the MVVM model.
  • Multi-Threaded - The application uses a multiple threads to execute tasks simultaneously.


Challenges and Lessons Learned

  • This is one of the most heavily threaded applications I have written. It was a challenge managing threads and resolving concurrency issues.
  • Though WPF is somewhat similar to the paradigm used in my Favors app, it takes a bit getting use to comparted to Win Forms.
  • Designing with user experience in mind is more challenging than I thought. I built this mainly for myself and had to redesign to enhance usability.
  • I was new to SQLlite. It handles a little differently than other database systems I have used.
Code Samples:

Update Instance Factory (C#)

			
				

       public void RefreshInstance()
        {
            String dataSQL = "SELECT * FROM INSTANCE WHERE NAME IS NOT NULL";
            updateInstanceList.Clear();
            instanceFieldList.Clear();
            SQLiteDataReader dataReader = DatabaseInstance.Instance.Query(dataSQL);
            while (dataReader.Read())
            {
                String id = DatabaseInstance.Instance.FieldString(dataReader, "ID");
                String name = DatabaseInstance.Instance.FieldString(dataReader, "name");
                String URL = DatabaseInstance.Instance.FieldString(dataReader, "URL");
                String status = DatabaseInstance.Instance.FieldString(dataReader, "status");
                int refreshMinutes = DatabaseInstance.Instance.FieldInt(dataReader, "refresh_minutes");
                int lastRefresh = DatabaseInstance.Instance.FieldInt(dataReader, "last_refresh");
                int lastUpdate = DatabaseInstance.Instance.FieldInt(dataReader, "last_update");
                int minutesLeft = DatabaseInstance.Instance.FieldInt(dataReader, "minutes_left");
                AddUpdateInstance(id, name, status, refreshMinutes, lastRefresh, lastUpdate, minutesLeft);
                AddField(id, name, refreshMinutes, minutesLeft );
            }
            dataReader.Close();
        }

        public void AddInstance( String instanceID )
        {
            String instanceSQL = DatabaseInstance.Instance.FetchFieldByInstanceIDSQL(instanceID);
            SQLiteDataReader dataReader = DatabaseInstance.Instance.Query(instanceSQL);
            while( dataReader.Read())
            {
                String id = DatabaseInstance.Instance.FieldString(dataReader, "ID");
                String name = DatabaseInstance.Instance.FieldString(dataReader, "name");
                String URL = DatabaseInstance.Instance.FieldString(dataReader, "URL");
                String status = DatabaseInstance.Instance.FieldString(dataReader, "status");
                int refreshMinutes = DatabaseInstance.Instance.FieldInt(dataReader, "refresh_minutes");
                int lastRefresh = DatabaseInstance.Instance.FieldInt(dataReader, "last_refresh");
                int lastUpdate = DatabaseInstance.Instance.FieldInt(dataReader, "last_update");
                int minutesLeft = DatabaseInstance.Instance.FieldInt(dataReader, "minutes_left");
                AddUpdateInstance(id, name, status, refreshMinutes, lastRefresh, lastUpdate, minutesLeft);
                AddField(instanceID, name, refreshMinutes, minutesLeft);
            }
            dataReader.Close();
        }

        public void AddField( String instanceID, String instanceName, int refreshMinutes, int minutesLeft)
        {
            String fieldSQL = DatabaseInstance.Instance.FetchFieldByInstanceIDSQL(instanceID);
            SQLiteDataReader fieldReader = DatabaseInstance.Instance.Query(fieldSQL);
            while (fieldReader.Read())
            {
                int fieldID = DatabaseInstance.Instance.FieldInt(fieldReader, "id");
                String fieldName = DatabaseInstance.Instance.FieldString(fieldReader, "name");
                String fieldValue = DatabaseInstance.Instance.FieldString(fieldReader, "value");
                String fieldDate = DatabaseInstance.Instance.FieldInt(fieldReader, "DATE").ToString();
                String fieldURL = DatabaseInstance.Instance.FieldString(fieldReader, "URL");
                String historySQL = "SELECT * FROM VALUE_HISTORY WHERE FIELD_ID=" + fieldID.ToString() + " ORDER BY DATE DESC";
                SQLiteDataReader historyReader = DatabaseInstance.Instance.Query(historySQL);
                historyReader.Read();
                String lastValue = "";
                if (historyReader.Read())
                    lastValue = DatabaseInstance.Instance.FieldString(historyReader, "VALUE");
                historyReader.Close();
                if( ! instanceFieldList.ContainsKey(fieldID.ToString()))
                    instanceFieldList.Add(fieldID.ToString(), new InstanceField(fieldID.ToString(), instanceID, instanceName, fieldName, fieldValue, lastValue, refreshMinutes, minutesLeft, fieldURL, fieldDate));
            }
            fieldReader.Close();
        }

        public void CleanFields(String instanceID)
        {
            var newDictionary = instanceFieldList.Where(inst => inst.Value.instanceID != instanceID).ToDictionary(inst => inst.Key, inst => inst.Value);
            instanceFieldList = newDictionary;
        }

        public void RemoveInstance( String instanceID)
        {
            var fields = from inst in instanceFieldList
                         where inst.Value.instanceID == instanceID
                         select inst.Value;
            foreach (var field in fields)
            {
                updateInstanceList.Remove(field.fieldID);
            }
            instanceFieldList.Remove(instanceID);
        }