Friday, March 15, 2019

SQL Server recursive SELECT

Not anything to do with WPF at all, but a useful technique to know.

I have a purchasing system. Whenever a user wants to make a change to a purchase order, they have to create a change order so it can go through the appropriate authorization process. The change order is a different document. I need to take the ID of a change order and find it, all prior change orders, and the original purchase order. Each change order contains the ID of the prior purchase order/change order.

We can do this with Common Table Expressions (CTE).

Start by creating a new table


CREATE TABLE [dbo].[DocumentTable](
       [ID] [int] IDENTITY(1,1) NOT NULL,
       [DocumentType] [varchar](50) NOT NULL,
       [PriorDocumentID] [int] NULL,
       [DocumentName] [varchar](50) NOT NULL
) ON [PRIMARY]
GO


and populate it


DECLARE @ID int
INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('PO',NULL,'Purchase Order'); SELECT @ID=SCOPE_IDENTITY()
INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 1'); SELECT @ID=SCOPE_IDENTITY()
INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 2'); SELECT @ID=SCOPE_IDENTITY()
INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 3'); SELECT @ID=SCOPE_IDENTITY()
INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 4'); SELECT @ID=SCOPE_IDENTITY()
INSERT INTO DocumentTable (DocumentType, PriorDocumentID, DocumentName) VALUES ('CO',@ID,'Change Order 5'); SELECT @ID=SCOPE_IDENTITY()


Now given an ID I want to return the document with that ID and all its ancestors.


DECLARE @ID int

SET @ID=4;
WITH DocumentCTE AS
(
       SELECT * FROM DocumentTable WHERE ID=@ID
       UNION ALL
       SELECT DT.* FROM DocumentTable DT INNER JOIN DocumentCTE CTE ON DT.ID=CTE.PriorDocumentID
)

SELECT * FROM DocumentCTE

With a starting ID of 4 we see the following results. As you can see, the CTE has recursed up the chain until it found a document with no PriorDocumentID.


ID          DocumentType    PriorDocumentID DocumentName
----------- ------------    --------------- --------------
4           CO              3               Change Order 3
3           CO              2               Change Order 2
2           CO              1               Change Order 1
1           PO              NULL            Purchase Order


What happens if the recursion is infinite - for example, if we set the PriorDocumentID of the PO to 6? The algorithm recurses round and round until the maximum recursion limit (default 100) is hit. Then an error is thrown.

Msg 530, Level 16, State 1, Line 3

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.


Friday, March 1, 2019

Implementing a background processor using MEF

Managed Extensibility Framework was introduced in Framework 4.0. It is a mechanism for finding code resources at run time and executing them. We currently have a background processor that has some very complex code to locate and execute methods. It executes them as separate processes which makes it difficult to determine what the result was. Using MEF and background workers can improve this.

This demo will have a main program (it would be a windows service, in reality) that periodically scans for new tasks (which would normally come from a database), matches the task to a class, creates a background worker, executes the task, and returns the result.

We will be working in C# today.

Start a new console app project called BackgroundProcessor and target Framework 4.0 or later.


Add a reference to System.ComponentModel.Composition


MEF finds code resources by looking for classes that export a specific interface. We start by defining that interface. Add a new Interface to the project and call it IBackgroundProcessor. It will contain two interfaces - we will look for all classes that export IBackgroundProcessor and determine which class to execute by looking for the class that implements IBackgroundProcessorData in the way we want. This will become clearer later (or not!).

namespace BackgroundProcessor
{
    public interface IBackgroundProcessor
    {
        void Process(cBackgroundProcessorJob Job);
    }

    public interface IBackGroundProcessorData
    {
        string Type { get; }
    }
}


The interface uses a class called cBackgroundProcessorJob which we need to define before we can get much further. Add a class called cBackgroundProcessorJob. It will contain properties you might need to define a background task.

namespace BackgroundProcessor
{
    public class cBackgroundProcessorJob
    {
        public int ID { get; set; }
        public string Type { get; set; }
        public string Param { get; set; }
        public string Result { get; set; }
    }
}

Now we need to flesh out Program.cs. Start by adding some usings.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.ComponentModel.Composition;
using System.ComponentModel.Composition.Hosting;

In the class, add a line that causes Program to create a list of all classes that export the IBackgroundProcessor class. We will write these classes later.

[ImportMany] IEnumerable<Lazy<IBackgroundProcessor, IBackGroundProcessorData>> BackgroundProcessors;

These classes will be stored in a container.

private CompositionContainer _container;

In a console app, Main is static, so we need to create a new instance of ourselves. This won't be needed if this is part of a Windows Service.

static void Main(string[] args)
{
    Program p = new Program();   // composition is done in the non-static ctor
}

The instance ctor is defined below. Note we are looking at all assemblies in a specific folder for classes that export the IBackgroundProcessor interface. You can also look in specific assemblies. The ComposeParts method does the actual searching. If there are errors in your Import or Export definitions this method will fail. Once we have found all the assemblies we call LaunchJobs every 10 seconds.

private Program()
{
    AggregateCatalog catalog = new AggregateCatalog();
    catalog.Catalogs.Add(new DirectoryCatalog("C:\\Users\\me\\Source\\repos\\BackgroundProcessor\\BackgroundProcessor\\bin\\Debug"));
    _container = new CompositionContainer(catalog);

    try
    {
        this._container.ComposeParts(this);
        do
        {
            LaunchJobs();
            System.Threading.Thread.Sleep(10000);
        } while (true);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }

    Console.Write("Press any key");
    Console.ReadKey();
}

LaunchJobs will iterate through a list of tasks to be performed. Normally this list would come from a database or other volatile data source. For this demo we will hard-code a list in the Program class. Note the Type property - we will determine which class will process the task based on this value.

List<cBackgroundProcessorJob> Jobs = new List<cBackgroundProcessorJob>
    { new cBackgroundProcessorJob() {ID=1, Type="Scan", Param="C:/" },
      new cBackgroundProcessorJob() {ID=2, Type="Fix", Param="D:/" }
    };

LaunchJobs will look in BackgroundProcessors (a list of classes we found that can process background tasks) looking for the one that matches the Type of the task that needs to be executed. If it finds one, it starts a background worker to execute the class.

We need to pass the class and the task to the background worker but we can only pass one argument so we need a class that can hold the background process class and the task. Note I have chosen to hold the task result in the task itself which is a bit questionable but simplifies the demo.

private class cBWArg
{
    public cBackgroundProcessorJob BPJ;
    public Lazy<IBackgroundProcessor, IBackGroundProcessorData> Processor;
}

private void LaunchJobs()
{
    bool IsFound;
    Console.WriteLine("-------------");
    foreach (cBackgroundProcessorJob BPJ in Jobs)
    {
        IsFound = false;
        foreach (Lazy<IBackgroundProcessor, IBackGroundProcessorData> i in BackgroundProcessors)
        {
            if (i.Metadata.Type.Equals(BPJ.Type))
            {
                BackgroundWorker bw = new BackgroundWorker();
                bw.RunWorkerCompleted += (s, e) =>
                {
                    cBackgroundProcessorJob BPJResult = e.Result as cBackgroundProcessorJob;
                    Console.WriteLine(BPJResult.Type + ":" + BPJResult.Result);
                };

                bw.DoWork += (s, e) =>
                {
                    cBWArg a = e.Argument as cBWArg;
                    a.Processor.Value.Process(a.BPJ);
                    e.Result = a.BPJ;
                };

                Console.WriteLine("Launching " + BPJ.Type);
                cBWArg BWArg = new cBWArg() { BPJ = BPJ, Processor = i };
                bw.RunWorkerAsync(BWArg);
                IsFound = true;
            }
        }
        if (!IsFound) Console.WriteLine("Unknown type " + BPJ.Type);
    }
}

We have not written the Scan or Fix classes yet so if we run the application now, we will see the following. MEF is correctly telling us we don't have classes that can handle these types of tasks. However, we do have our infrastructure in place.


Let's start by writing our Scan class. Add a new class library project to the solution and call it Scan.


Rename Class1 to Scan and open the project properties and select the Build tab. Change the Output Path to ..\BackgroundProcessor\bin\Debug\. This puts the binary in the same folder that Program.cs is looking in.

Add a reference to BackgroundProcessor and System.ComponentModel.Composition.

The code required to connect this to Program.cs is surprisingly simple. The entire class is defined below. The decorations say "I can handle IBackgroundProcessor, you should use me when Type=Scan".

using System;
using System.ComponentModel.Composition;

namespace BackgroundProcessor
{
    [Export(typeof(IBackgroundProcessor))]
    [ExportMetadata("Type", "Scan")]
    public class Scan : IBackgroundProcessor
    {
        public void Process(cBackgroundProcessorJob Job)
        {
            Console.WriteLine("Scanning " + Job.Param);
            System.Threading.Thread.Sleep(1000);
            Job.Result = "Success";
        }
    }
}

Repeat the same process for Fix. The code is below. Note they both implement a void Process method that takes a cBackgroundProcessJob argument because this is what the IBackgroundProcessor interface states.

using System;
using System.ComponentModel.Composition;

namespace BackgroundProcessor
{
    [Export(typeof(IBackgroundProcessor))]
    [ExportMetadata("Type", "Fix")]
    public class Scan : IBackgroundProcessor
    {
        public void Process(cBackgroundProcessorJob Job)
        {
            Console.WriteLine("Fixing " + Job.Param);
            System.Threading.Thread.Sleep(5000);
            Job.Result = "Failure";
        }
    }
}

Now the output from the application is this.



Zipped Solution