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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s