Creating my simple XML setup file

At the end of the last blog post I said that it’d be a good idea to create some kind of config file in order to change the functions that the program could provide without having to keep recompiling the program. To fulfill this hastily decided upon requirement I’ve created XML config file with each of the queries encapsulated within their own tag (as seen in the example snippet immediately below) and a method which creates a XAML button for each query stored. The appropriate query can be run upon this button’s click.

<SQLQuery name=”…” query=”…” result=”…”/>

There are a few problems to overcome for this to work. Since the number of queries provided isn’t known until run-time, each button click must run the same method since the code to create the buttons doesn’t iterate a set number of times. Since each SQL query returns a different mix of columns (and consequently types) this has to be accounted for when retrieving the rows.

Upon successfully loading in the XML query element a ‘StoredDynamicQuery’ object is created to store the query, its name, and the format of the results. To make it simpler the content of the button is set as the query name; once the button is clicked its content is used to retrieve the necessary ‘StoredDynamicQuery’ object.

result=”CustomerID:int,Name:string,Age:int,Address:string,PostCode:string”

To account for the changes in the end-results I’ve come up with a results mechanism that could generously be described as JSON-like (as seen above). They’re written in key-value pairs separated by colons, and the results string can contain any number of these with individual pairs split by commas. The code to account for this can be seen below.

StoredDynamicQuery RetrievedQuery = RetrieveStoredQuery(QueryName);

using (SqlConnection connection = new SqlConnection(DataString))
{
     using (SqlCommand command = new SqlCommand(RetrievedQuery.QueryCommand, connection))
     {
          List IndividualResults = RetrievedQuery.Results.Split(',').ToList();
          connection.Open();

          using (SqlDataReader reader = command.ExecuteReader())
          {
               if (reader.HasRows)
               {
                    while(reader.Read())
                    {
                         string ConcatenatedString = "";
                         for (int i = 0; i < IndividualResults.Count; i++)
                         {
                              string QueryPart = IndividualResults[i].Split(':')[0];
                              string ResultType = IndividualResults[i].Split(':')[1];

                              switch (ResultType)
                              {
                                   case "string":
                                       ConcatenatedString += QueryPart + ": " + reader.GetString(reader.GetOrdinal(QueryPart)).TrimEnd() + " ";
                                       break;
                                   case "int":
                                       ConcatenatedString += QueryPart + ": " + reader.GetInt32(reader.GetOrdinal(QueryPart)) + " ";
                                       break;
                               }
                          }
                          OutputBox.Items.Add(ConcatenatedString);
                     }
                }
           }
      }
}

In the above code the QueryPart and the ResultType each represent the column name and type of the key-value pairs of the results string. In this way I can basically add in any new query methods without the need for recompilation. For examples see the below two images.

LoadedQueriesExample1   LoadedQueriesExample2

Figure 1. The initial functionality.

Figure 2. The expanded functionality based upon the updated XML file.

Figure 1 (on the left) is created with the first XML layout below. The layout in figure 2 (on the right) is an extension of the first layout. In my implementation it was possible to add an entirely new piece of functionality by just adding an additional XML line.

More work would have to be put in to account for queries that insert and update data (some way of knowing which visual element provides which bit of data for the query would have to be agreed on), but overall it provides what I wanted.

Advertisements

Using SQL connections in C#

Last time I used an SQL database with C# it all went through the LINQ plugin. It’s useful, but it always takes a good while to setup and requires me to remember a good chunk of code (like where to put the NotifyChanging and NotifyChanged parameters, what attributes are needed, etc). One of the things I discovered courtesy of this page  (accessed 13/06/2017) is that the .NET framework provides some APIs to link to a back-end database.

DatabaseManagementOpenPage

Using these I’ve created a simple database management system in order to query and update a database through a WPF GUI application. The database I’ve created is comprised of 3 tables: a customer table, an order table, and a product table. The application allows the user to query the data in the database, update the data, and insert new records.

The actual code involves first creating a data connection, then a command, and finally executing the command. It’s the ability to manually stick in the SQL string you want to execute that makes it the most useful to me, but since each row being returned is comprised of different rows it means having to come up with some alternate mechanism to account for the results of each row. This means that the finished program is lot longer than a LINQ alternative.

I think I like this approach over the LINQ option; it means writing the SQL queries in by hand, but I think this is a fair trade-off for having a simpler setup. I suppose if I’d known about this simple API beforehand then I’d probably used back-end databases in some of my older projects.

One possible change to be made to this program would be to store the SQL queries in some external file so I don’t have to recompile the thing every time I want alternatew functionality. The idea I’ve come up with is to use an external XML file and then use some kind of regular-expression replacement mechanism (this has become something of a go-to thing for string replacement lately (worrying maybe, but if it works)) in order to programmatically alter the subject of the search. This would be necessary as most of the queries inherent in the project require either data insertion or mild changes of the same query.

Creating a simple Chrome extension

Working Extension

Image 1. The current popup frame for my extension

With the help of some online tutorials and pretty good documentation I’ve managed to make a simple Google Chrome extension. Built more to experiment with the kind of functionality that can be built, my extension does some pretty basic stuff, but it’s interesting to know how these things can be built to target multiple web pages.

My extension currently has 4 bits of functionality: get the page to display a simple alert, remove all of the images on a page, replace all of the images on a page, and display all of the hypertext links on the page in an alert.

Before  Removed Images

Image 2. The original image search.

Image 3. The image search with sources removed through my extension.

Replaced Images LinkRetrieval

Image 4. The images replaced with a separate PNG image through my extension (original image here)

Image 5. The links retrieved from each <a> tag on the page

As it turns out, you can make the prompt with a simple HTML file. This was a pretty nice surprise as HTML and CSS are simple enough and I can make a fairly nice interface using them. The problem is that an extension doesn’t support inline JavaScript (so StackOverflow says) meaning that you have to add event handler’s to each different button.

One interesting mechanism I’ve had to learn about is the message passing mechanism chrome extensions have to implement for communication between content-scripts (ones that are embedded into the page) and the scripts running in the extension. The messaging code involves sending an appropriate code to the recipient, it is up to the recipient to pick an appropriate action based upon this code. It reminds me of Android’s intent mechanism.

I’ve used messaging in two places in this extension: to trigger the functionality necessary from the extension, and to initially update the extension with the number of images and links on the page.

This hasn’t been a bad experiment, now I just have to think of some kind of missing functionality to base an extension around.

I learnt a significant deal how to get started from this helpful tutorial (Getting Started, accessed June 6th 2017), without which I wouldn’t be able to create the manifest file, and based most of my messaging around the tutorials found on the official messaging page (Message Passing, accessed June 6th 2017).