Category Archives: Source Code

Concatenate Aggregate Function For SQL Server

Tired of the fact that, still after many years, there isn’t a convenient and built-in way to concatenate strings in an aggegrating query. Well, wait no more!

First, check if your database environment is setup to allow CLR user-defined functions:

SELECT * FROM sys.configurations WHERE name = 'clr enabled'

If value_in_use = 1, you are setup for it. If not, you can turn it on yourself via:

sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Now, fire up VS2010. Create a Visual C# SQL CLR Database Project from the SQL Server database templates. Right-click on the project in the solution and click on “Add New Item”. From the list, select “Aggregate” (and note all the other goodies you could create). I called the solution/project `ConcatMaxNullable`.

There is plenty of good information on what the various methods stubs are doing. Also, there are some hard-to-find example of concatenate aggregates out there too. Unfortunately, none of them fit my needs. I need one that:

  • Concatenated strings. (Duh!)
  • Could output to the new nvarchar(max) type and not be limited by the 8000 chars of the old nvarchar().
  • Allowed skipping nulls, or replace them with empty strings still delimited.

So, I rolled my own. I’m going to go ahead and simply post my own code for others to use. Should be self-explanatory as the methods don’t really do anything particularly novel.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Text;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(
  Format.UserDefined,
  IsInvariantToOrder = false,
  IsInvariantToNulls = true,
  IsInvariantToDuplicates = false,
  MaxByteSize = -1
  )]
public struct ConcatMaxNullable : IBinarySerialize
{
  private StringBuilder _accumulator;
  private string _delimiter;

  public Boolean IsNull { get; private set; }

  public void Init()
  {
    _accumulator = new StringBuilder();
    _delimiter = string.Empty;
    this.IsNull = true;
  }

  public void Accumulate(SqlChars item, SqlString delimiter, SqlBoolean skipNulls)
  {
    // if value is null, return if skipNulls is true
    if (item.IsNull && skipNulls.IsTrue)
      return;

    // if we have an actual delimiter
    if ((!delimiter.IsNull) && (delimiter.Value.Length > 0))
    {
      _delimiter = delimiter.Value;
      // accumulate delimiter if we have something already
      if (_accumulator.Length > 0)
        _accumulator.Append(delimiter.Value);
    }

    // if value is null, just add delimiter (above) and return
    if (item.IsNull)
      return;
    else
    {
      _accumulator.Append(item.Value);
      this.IsNull = false;
    }

  }

  public void Merge(ConcatMaxNullable Group)
  {

    if (_accumulator.Length > 0 && Group._accumulator.Length > 0)
      _accumulator.Append(_delimiter);

    _accumulator.Append(Group._accumulator.ToString());

  }

  public SqlChars Terminate()
  {

    return new SqlChars(_accumulator.ToString());

  }

  void IBinarySerialize.Read(System.IO.BinaryReader r)
  {

    _delimiter = r.ReadString();
    _accumulator = new StringBuilder(r.ReadString());

    if (_accumulator.Length != 0)
      this.IsNull = false;

  }

  void IBinarySerialize.Write(System.IO.BinaryWriter w)
  {

    w.Write(_delimiter);
    w.Write(_accumulator.ToString());

  }

}

Once you pasted this in, compile and then get the DLL somewhere the server can see it. Installing it is as easy as running these two little scripts:

CREATE ASSEMBLY ConcatMaxNullable FROM 'C:\\ConcatMaxNullable.dll'
WITH PERMISSION_SET=SAFE
GO

CREATE AGGREGATE [dbo].[ConcatMaxNullable]
(@item [nvarchar](max), @delimiter [nvarchar](8), @skipNulls bit)
RETURNS[nvarchar](max)
EXTERNAL NAME [ConcatMaxNullable].[ConcatMaxNullable]
GO

Then, you can easily do something like this query, which shows you your foreign keys, and the columns involved, in a comma-separated format ready for scripting.

SELECT
  c.[TABLE_NAME], k.[CONSTRAINT_NAME], dbo.ConcatMaxNullable([COLUMN_NAME],',',1) AS [Cols] 
FROM
  INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
INNER JOIN
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE k ON c.[CONSTRAINT_NAME] = k.[CONSTRAINT_NAME] 
WHERE
  c.[CONSTRAINT_TYPE] = 'FOREIGN KEY'
GROUP BY
  c.[TABLE_NAME], k.[CONSTRAINT_NAME]
ORDER BY
  c.[TABLE_NAME], k.[CONSTRAINT_NAME] 

Enjoy!

Advertisements

MachineKey Key Generator

It’s not super-easy to find a versatile key generator for creating truly random keys to use in your MachineKey section in web.config or machine.config. So, here’s a quick console app, and related zipped project (Current extension is .doc to bypass WordPress’ file type limitations. Just save locally using “Save Link As..”, rename extension to .zip, unzip and enjoy.) to help you out with generating any of the various allowable keys you may need. Run it from inside a command-line window by calling:

MachineKeyGeneratorConsole.exe /v:<insert validation key type> /d:<insert decryption key type>

To save to a file, just type:

MachineKeyGeneratorConsole.exe /v:<insert validation key type> /d:<insert decryption key type> > key.txt

The switches are optional. Valid values for validation key generation are: AES, AES128, AES192, AES256, MD5, SHA1, HMACSHA1, SHA256, HMACSHA256, SHA384, HMACSHA384, SHA512, HMACSHA512. If no value is given, the default value is SHA1. Furthermore, although you can give different key sizes for some algorithms, the appropriate MachineKeyValidation is returned.

Valid values for decryption keys are: AES, AES128, AES192, AES256, DES, 3DES. If no value is given, the default value is AES.

using System;
using System.Diagnostics;
using System.Linq;
using System.Security;
using System.Security.Cryptography;
using System.Text;

namespace MachineKeyGeneratorConsole
{
    static class Program
    {
        static int Main(string[] args)
        {
            if (args.Any(arg =&gt; arg.ToLower().Equals("/t") || arg.ToLower().Equals("-t")))
                Trace.Listeners.Add(new ConsoleTraceListener(true));

            try
            {
                Run(args);
                return Environment.ExitCode;
            }
            catch (Exception e)
            {
                Console.Error.WriteLine(e.Message);
                Trace.TraceError(e.ToString());

                return (Environment.ExitCode != 0) ? Environment.ExitCode : 100;
            }
        }
        
        static void Run(string[] args)
        {
            int vkeybits = 256;
            string vkeyalg = "HMACSHA256";
            string vchoice = args.FirstOrDefault(arg =&gt; (arg.ToLower().Substring(0, 3).Equals("/v:") || arg.ToLower().Substring(0, 3).Equals("-v:")));
            if (vchoice != null)
            {
                Console.WriteLine("Requesting validation key: " + vchoice.Substring(3).ToUpper());
                switch (vchoice.Substring(3).ToUpper())
                {
                    case "AES128":
                        vkeybits = 128;
                        vkeyalg = "AES";
                        break;
                    case "AES":
                    case "AES192":
                        vkeybits = 192;
                        vkeyalg = "AES";
                        break;
                    case "AES256":
                        vkeybits = 256;
                        vkeyalg = "AES";
                        break;
                    case "MD5":
                        vkeybits = 128;
                        vkeyalg = "MD5";
                        break;
                    case "SHA1":
                    case "HMACSHA1":
                        vkeybits = 160;
                        vkeyalg = "SHA1";
                        break;
                    case "3DES":
                        vkeybits = 192;
                        vkeyalg = "3DES";
                        break;
                    case "SHA256":
                    case "HMACSHA256":
                        vkeybits = 256;
                        vkeyalg = "HMACSHA256";
                        break;
                    case "SHA384":
                    case "HMACSHA384":
                        vkeybits = 384;
                        vkeyalg = "HMACSHA384";
                        break;
                    case "SHA512":
                    case "HMACSHA512":
                        vkeybits = 512;
                        vkeyalg = "HMACSHA512";
                        break;
                    default:
                        vkeybits = 160;
                        vkeyalg = "SHA1";
                        break;
                }
            }

            int dkeybits = 192;
            string dkeyalg = "AES";
            string dchoice = args.FirstOrDefault(arg =&gt; (arg.ToLower().Substring(0, 3).Equals("/d:") || arg.ToLower().Substring(0, 3).Equals("-d:")));
            if (dchoice != null)
            {
                Console.WriteLine("Requesting decryption key: " + dchoice.Substring(3).ToUpper()); 
                switch (dchoice.Substring(3).ToUpper())
                {
                    case "AES128":
                        dkeybits = 128;
                        dkeyalg = "AES";
                        break;
                    case "AES":
                    case "AES192":
                        dkeybits = 192;
                        dkeyalg = "AES";
                        break;
                    case "AES256":
                        dkeybits = 256;
                        dkeyalg = "AES";
                        break;
                    case "3DES":
                        dkeybits = 192;
                        dkeyalg = "3DES";
                        break;
                    case "DES":
                        dkeybits = 64;
                        dkeyalg = "DES";
                        break;
                    default:
                        dkeybits = 192;
                        dkeyalg = "AES";
                        break;
                }
            }


            StringBuilder section = new StringBuilder();
            string vkey = GetRandomKey(vkeybits/8); // SHA
            string dkey = GetRandomKey(dkeybits/8); // AES can be 32, 48 or 64 chars, DES is 16 chars, 3DES is 48 chars
            section.AppendLine("");
            Console.WriteLine(section.ToString());

        }

        static string GetRandomKey(int bytelength)
        {
            int len = bytelength * 2;
            byte[] buff = new byte[bytelength];
            RNGCryptoServiceProvider rng = new RNGCryptoServiceProvider();

            rng.GetBytes(buff);

            StringBuilder sb = new StringBuilder(len);
            for (int i = 0; i &lt; buff.Length; i++)
                sb.Append(string.Format(&quot;{0:X2}&quot;, buff[i]));
            return sb.ToString();
        }
    }
}