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);
        }

Update Timer Class (C#)

					
    class UpdateTimer
    {
        public event EventHandler processEvent;
        public event EventHandler progressEvent;
        public int minutesLeft;
        public int totalMinutes;
        public String state;
        public String instanceID;
        public DispatcherTimer minuteTimer;

        public void CountDown( Object o, EventArgs e )
        {
            if (minutesLeft - 1 > 0)
                minutesLeft -= 1;
            else
                state = "Processing";
            if( state == "Processing")
            {
                //TODO: Process timer
                processEvent(this, e);
                state = "Stopped";
            }
            progressEvent(this, e);
        }

        public UpdateTimer( String instanceID, int totalMinutes, int minutesLeft )
        {
            minuteTimer = new DispatcherTimer();
            minuteTimer.Tag = instanceID;
            this.totalMinutes = totalMinutes;
            this.minutesLeft = minutesLeft;
            minuteTimer.Interval = TimeSpan.FromMinutes(1);
            minuteTimer.Tick += CountDown;
            this.instanceID = instanceID;
            state = "Stopped";
        }

        public void Start()
        {
            minuteTimer.Start();
            state = "Waiting";
        }

        public void Restart()
        {
            if (state == "Waiting")
                minuteTimer.Stop();
            minutesLeft = totalMinutes;
            minuteTimer.Start();
            state = "Waiting";
        }

    }

Load Site (C#)

				

        public int FieldInt(SQLiteDataReader data, String key)
        {
            try
            {
                return data.GetInt32(data.GetOrdinal(key));
            }
            catch (Exception e)
            {
                return -1;
            }
        }

        public String FieldString(SQLiteDataReader data, String key)
        {
            try
            {
                return data.GetString(data.GetOrdinal(key));
            }
            catch (Exception e)
            {
                return "";
            }
        }

        public String EscapeCharacters(String value)
        {
            if (value == null)
                return "";
            String[] escapeStrings = { "'" };
            foreach (String escapeString in escapeStrings)
            {
                value = value.Replace(escapeString, "'" + escapeString);
            }
            return value;
        }

        private String LoadSiteWebClient(String url)
        {
            String html = "";
            using (WebClient client = new WebClient())
            {
                html += client.DownloadString(url);
            }
            return html;
        }


        private String LoadSiteWebRequest(String url)
        {
            WebRequest req = HttpWebRequest.Create("http://google.com");
            req.Method = "GET";
            string source = "";
            using (StreamReader reader = new StreamReader(req.GetResponse().GetResponseStream()))
            {
                source = reader.ReadToEnd();
            }
            return source;
        }

        private String LoadSiteHttpClient(String url)
        {
            String html = "";
            using (HttpClient client = new HttpClient())
            {
                using (HttpResponseMessage response = client.GetAsync(url).Result)
                {
                    using (HttpContent content = response.Content)
                    {
                        html = content.ReadAsStringAsync().Result;
                    }
                }
            }
            return html;
        }

        private String LoadSiteDynamically(String url)
        {
            var service = PhantomJSDriverService.CreateDefaultService();
            service.HideCommandPromptWindow = true;
            IWebDriver driver = new PhantomJSDriver(service);
            driver.Url = url;
            driver.Navigate();
            String source = driver.PageSource;
            driver.Close();
            driver.Quit();
            driver.Dispose();
            return source;
        }

        public String getXpathValue(String url, String xpath, bool isDynamic)
        {
            String html = "";
            try
            {
                if (isDynamic)
                    html = LoadSiteDynamically(url);
                else
                    html = LoadSiteWebClient(url);
                if (html != "")
                {
                    HtmlAgilityPack.HtmlDocument htmlDocument = new HtmlAgilityPack.HtmlDocument();
                    htmlDocument.LoadHtml(html);
                    return htmlDocument.DocumentNode.SelectSingleNode(xpath).InnerText;
                }
            }
            catch (Exception e)
            {
                return null;
            }
            return null;
        }