Part 1 - Using SQLXAgent
Part 2 - Architecture and Design Decisions (this article)
Part 3 - The CSV and Excel Importer Code
Part 4 - Job Scheduling Code
Part 5 - How Packages Are Run
Part 6 - Interesting Coding
Introduction
This article is the second in a larger series of articles describing the SQLXAgent utility, specifically discussion the design and architecture considerations. There won't be very many screen shots, or code snippets - mostly just a bunch of boring narrative. If you don't care about the subject matter of this article part, feel free to ignore it and go find something else to do. Specific coding areas and challenges are discussed in parts 3 through 6 of this article series.
IMPORTANT!!! All components (except packages) MUST reside in the same folder. The reason is that they all share the SQLXAgent.exe.config file.
Assumptions
This article was written for experienced programmers that have a reasonable working knowledge of SQL Server, WPF, and C#. If there's something you don't understand, google it.
Being in the target audience, I assume you have the programming chops to change stuff that you'd rather see done a different way. However, keep in mind that like all the other lazy bastards out there, I only did what I needed to get the code working - FOR MY PURPOSES. If the first way I tried something worked, I generally avoided exploring better ways to do it unless the first way that worked truly sucked huge hairy donkey testicles.
You're using SQL Express as a cheap/easy way to utilize a database back-end in a NON-ENTERPRISE environment. For instance, the entire reason I came up with this project was to maintain a database of movie files I have spread across my network. I wrote a package that scans a series of network shares for new/changed files, and records the names and file info in a database. That is ALL I use this for. I don't have to worry about permissions, active directory groups, or any of that sys-admin crap. If you do need that kind of infrastructure, you really should consider using the full-blown retail SQL Server setup and its built-in support for SQL agents, as well as ActiveDirectory to manage users.
Coding Notes
The code in this solution is pretty heavily commented, so you shouldn't have any problems reading it and understanding what I did, or why I did it. If you find it hard to wrap your head around this, maybe you should consider another line of work, because this ain't theory code, and I don't do really bizarre stuff unless it's absolutely necessary to get the job done. I am a firm believer in the KISS theory.
Lastly, I do NOT use Enterprise Framework for my database access. I think it's WAY too heavy and over-the-top for what I needed to accomplish.
Building Blocks
There's no point in reinventing wheels. There are several components used by this code that were already available from 3rd parties, and even stuff I submitted here in the form of articles and tips. This section describes those components.
3rd Party Libraries
Like I said, I'm lazy and quite frankly, I'm getting bored with the project. Coincidentally, the boredom started right about the halfway mark on part 1 of this article series. Anyway, NuGet provides a wealth of already-written code that any programmer with half a brain will take advantage of. I have at least half a brain, and the following packages were used in the making of this film.
- XCeed WPF Extended Toolkit - This is almost required for any WPF application - I simply cannot live without it. It has a bunch of extended and new controls that Microsoft failed to provide. Best of all, it's free.
- CommandLine - allows you to easily parse a console application's command line arguments. Again, free, free, free.
- System.Reactive - among other things, this library allows you to configure timers and react to the timer events in a multi-threaded fashion. This is used in SQLXAgent for the scheduling of job execution.
- ExcelDataReader - this library enables you to load the contents of an Excel worksheet into a
DataTable. I use it to implement my Excel importer code.
I should point out that to keep the download for this article series to a minimum, I moved the applicable assemblies from these NuGet packages into the 3rdParty folder, and deleted the NuGet-related folders that were created when I downloaded these packages. This means that some of the files in a given package are not included in the download because it would have resulted in a HUGE file size. If you want to restore the NuGet stuff, you can re-download it, but personally, I see no reason to do this unless they add some massive must-have features to these libraries that you simply must have.
Other Codeproject Articles/Tips Used
I also utilized at least part of the code from the following CodeProject articles/tips that I submitted.
- CSV File Parser - This is an article I posted back in 2016, and based on the code I wrote for the Excel file importer that I developed for this project, I came up with a more efficient method for providing data to the code that uses it. (I may be posting a new article about this one day in the future.)
- Watching Messages in the Output Window - I wrote this tip as a direct result of my work in this project. It allows you to easily present messages via the output window while you're debugging your code.
- Comparing Two Complex or primitive objects of same class (Alternative) - This tip provides an extension method that can be used to compare objects for equality, regardless of their complexity.
General Architecture
Below is an assembly reference diagram that illustrates what parts of this suite are used by the other parts. Also included are the 3rd party components downloaded via NuGet.
Solutions
The SQLXAgent solution is responsible for building all of the core assemblies. At first, I included development of packages in the main solution, but I became concerned that this could potentially cause developer-induced problems if one of the core assemblies was in some way corrupted/broken by the developer who ony wanted to create a package.
The SQLXAgent Solution
The following assemblies are implemented in this solution:
- SQLXAgent (WPF application) - this app is used to configure agents.
- SQLXAgentSettings (class library) - provides access to the application settings objects to the various applications.
- SQLXAgentSvc (Windows Service) - this is the service that runs agents according to the specified schedules.
- SQLXCommon (class library) - provides methods and classes that is common between all other assemblies in the solution.
- SQLXData (class library) - provides model and view-model objects that represent agents.
- SQLXPkgBase (class library) - provides base classes (including excel and csv importer base classes). All package classes should use this library.
- SQLXPkgRunner (console application) - runs SQLXAgent package DLLs.
- SQLXThreadManager (class library) - manages agent jobs in the service.
- TPLTesterUI (WPF application) - test harness app that exercises the
SQLXThreadManager library (if it runs here, it *should* run in the service as well).
- WpfCommon (WPF class library) - provides methods and classes that are used by WPF assemblies in the solution.
The SQLXPackages Solution
This solution is located here:
I included a few sample packages:
- PkgSampleImportCSV (package) - sample package that demonstrates importing from a CSV file.
- PkgSampleImportExcel2 (package) - sample package that demonstrates importing from an Excel file.
- PkgSampleSQL (package) - sample package that demonstrates running a SQL query.
When the SQLXAgent solution builds, the post-build steps for the SQLXCommon and SQLXPkgBase assemblies causes those assemblies to be copied to the DLLBin folder. When you perform a Clean on the SQLXAgent solution, these copied files are not deleted, so referencing them in a package should be fairly trouble-free.
In the event that the programmer developing the packages needs to develop "common" code used by multiple packages, another assembly is provided, called PkgCommon that implements a static class called PkgGlobals. It is located within the SQLXCommon name space. This assembly should be used with the aim of avoiding having to recompile the SQLXCommon assembly (and possibly break the entire SQLXAgent solution).
SQLXAgent
I recreated the SQL Server Agent properties form to the extent that was reasonable, so most of the interface in this app will already be somewhat familiar to anyone with experience creating jobs for the full blown version of SQL Server. I made accommodations for SQL Express and what made sense to me, so if you don't like some aspect of the interface, you have all the source code, so... well... you know. The UI for this application is discussed in detail in part 1 of this article series (there's a link at the top of this article).
MVVM Support (SQLXData)
The data is implemented in the SQLXData assembly. I used a separate assembly because multiple applications require access to the classes. The model is comprised of the following:
- JobItem - represents a "job". Each job contains one or more steps.
- JobList - represents a
List of jobs.
- StepItem - represents a job "step".
- StepList - represents a
List of steps.
- ConnStringItem - represents a connection string item.
- ConnStringList - represents a list of the last ten connection strings you have specified.
- JobHistoryItem - represents a history item at the job level. The reason we separate job and step history items is for display in the SQLXAgent application.
- StepHistoryItem - represents a history item at the step level.
- JobHistoryList - represents a list of job-level history items.
- StepHistoryList - represents a list of step-level history items. This list is implemented as a property in the job-level history item class.
The JobItem and StepItem classes are derived from ItemModelBase, which provides some common functionality related to add/update/retrieval processing from the database.
The view-model is your standard WPF type of deal. Put simply, there is one view-model class for every model class. No more, no less. It is responsible for handling the model, and providing additional special properties for use in the UI. I'm not using IDataErrorInfo, but it is implemented in a base class (WpfCommon.Notifiable) if you feel the need to turn it on. Of course, you'll have to implement ALL of the required coding in the UI, but that should go without saying.
SQLXAgentSvc
The service part of this utility is admittedly a pretty minor part of the whole thing. All it does is run the Job thread manager. The act of installing, uninstalling, starting, and stopping the service is handled in the SQLXAgent application. This makes management much simpler than using the command line to install/uninstall, or "managing" the service through the Windows services console.
The Job Thread Manager
The management of the jobs and their schedules is the most complex part of this whole utility. I redesigned this several times before I landed on something that was reasonably simple to implement, and even simpler in terms of additional work needed by you, the developer. My desire was to make the developer only responsible for developing the packages he/she wanted to run.
I mentioned this several times in Part 1 of this article series, but it bears repeating here. This utility is NOT intended for use in an enterprise environment, because SQL Express is NOT intended for use in such an environment. With that in mind, consider that I anticipate no more than a handful of jobs to be running in any given installation. I don't really know what the reasonable threshold is, but I would start lookin’ sideways at folks that have more than 5-10 jobs specified. However, feel free to push on the code as hard as possible. I've tested with three jobs, and everything appears to work fine. Depending on what your jobs actually do, YOUR MILEAGE WILL VARY. I make no apologies for this.
Initially, I was going to use actual multi-threading code to implement a running job. However, that soon became unnecessarily unwieldy and complex, so I went in search of an alternative. That's when I discovered System.Reactive (aka, "Rx").
Essentially, each job calculates its next execution date/time (based on the schedule specified for the job), and sets what amounts to a timer interval. Rx fires an event when the interval expires, and the job thread item does its work (executes each enabled step in the sequence specified by the user), and calculates the next execution time.
Running Packages
If a step is configured as a "PKG", that means the developer developed a package in the SQLXPackages solution. In order for this to work, a few requirements must be met. These requirements are discussed in detail in part 1 of this article series. Packages are "run" as follows.
When a package needs to be run, the SQLXPkgRunner command line application is run by the job thread item. The job thread provides command line arguments to the SQLXPkgRunner application so that it can dynamically load the appropriate package. A properly written package can communicate its result (0 or non-zero status) to the calling app, which in turn, notifies the job thread object so that the audit log can be updated with appropriate information. Coding details are provided in part 4 of this article series.
TPLTesterUI.exe - The Test Application
The name indicates one of the threading technique I was playing with at the time - I wanted to start testing the job thread manager, so don't judge me. In a nutshell, this app simply lets you start the job manager, and stop the job manager. The intent is to give you a way to debug the job thread manager and watch its output in the IDE's Output window.
Application Settings
Like seemingly everything else in this app, the code for the app's settings is currently in the 3rd iteration of the Settings form. Admittedly, the VMSettings class (and the supporting model) that supports the latest iteration was thrown together rather hastily, and I simply may not have gotten around to making some of the app use it. This section merely recognizes that fact, and in fact, that might not represent reality by the time this article series is published.
The settings objects were eventually moved into their own assembly to avoid circular references between various namespaces. This may not have been the best idea, but it worked.
The VMSettings class is not specifically discussed anywhere because its relatively minor role in the grand scheme of things.
History
- 29 Sep 2017 - Initial publication.
