• Download SQLXAgent_Final.zip - 1.1 MB

Part 1 - Using SQLXAgent
Part 2 - Architecture and Design Decisions
Part 3 - The CSV and Excel Importer Code
Part 4 - Job Scheduling Code
Part 5 - How Packages Are Run
Part 6 - Interesting Coding, and Challenges (this article)

Introduction

This article is the 6th part of a larger article series describing the SQLXAgent utility. In this article, I'll be describing some of the interesting coding that was done. I don't anticipate this article being very long, because to be honest, after writing five parts for this article series, I'm kinda bored with the whole thing.

NOTE: Code snippets presented in the article may or may not reflect the absolutely latest and greatest version of the code. In the event that it does not exactly match the actual code, it will fairly close. Such is the nature of noticing issues while writing an article.

Creating the SQLXAgent Database From Scripts

The act of creating the SQLXAgent database required a bit more effort than I was expecting.

What's Happening?

I wanted to be able top report progress as the scripts were run, and this required an interface, which meant that the code that was responsible for creating the database had to be "notifiable".

In order to make the term "notifiable" meaningful, I had to break up one big script into 35 discreet script files. In the process of testing this code, I discovered that SQL Server (Express?) does not support GO statements in batch queries. I also discovered that creating a store procedure via a batch query requires that the CREATE PROCEDURE line is the first non-comment line that can appear in the query.

The act of running the scripts was made somewhat less tedious because I included them as (non-copied) embedded resources, so I could process all queries dealing with tables, and then stored procedures by processing them in a loop. Here's the method that runs the stored procedure queries.

/// <summary>
/// Adds the stored procedures to the database one at a time
/// </summary>
private bool AddStoredProcs()
{
    bool result = true;
    // get a list of all the stored procedure resources
    string[] procs = this.assy.GetManifestResourceNames().Where(x=>x.IsLike("%SQLXAgent_SP_%")).ToArray();
    // set the appropriate connection string
    DBObject2.ConnectionString = this.sqlxConnStr;
    // loop through the list of stored proc queries
    foreach (string name in procs)
    {
        // do some completely unnecessary name formatting for the benefit of the UI.
        string[] parts = name.Split('.');
        string filename = string.Concat(parts[parts.Length-2],'.', parts[parts.Length-1]);
        string info = filename.Replace("SQLXAgent_", "").Replace("SP_", "sp_");
        // run the sql query
        if (!this.RunSQLCommandFromFile(filename, string.Concat("Create proc ", 
                                                                info.Substring(0, info.IndexOf(".")))))
        {
            result = false;
            break;
        }
    }
    return result;
}

Schedule Timing

When I first started testing the scheduling code, I observed that each subsequent run of a given job would start running a number of seconds beyond the expected start time. To remedy the situation, I recalculate the next run time at the beginning of the RunSchedule method, zeroing the seconds out along the way.

Application Settings

The problem was that I had multiple applications that required certain settings that were modified in SQLXAgent. The solution was to "map" the SQLXAgent.exe.config file so that I could load it from any application within the solution. Here's the code I used:

ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap() { ExeConfigFilename = this.ConfigFile };
this.AppConfig = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);

Even the application that owns the file - SQLXAgent - uses the code above (conveniently made available via a static class in the SQLXAgentSettings assembly). This kept things much simpler downstream.

XAML Stuff

The single most problematic XAML issue I encountered was binding radio buttons to a boolean property. After taking a couple fo stabs at (okay, about a dozen stabs is a more accurate number), I found this CodeProject tip/trick:

Binding radio Buttons to a Single Propert, by Volodymyr Trubachov.

History

• 29 Sep 2017 - Initial publication.