SQL Server – Jak wylistować dostępne kodowania funkcją tabelaryczną CLR?

Ostatnimi czasy przesiaduję trochę na portalu WSS.pl. Na forum tegoż portalu wywiązała się całkiem interesująca dyskusja nad konwersją pomiędzy stroną kodową Mazovia (CP620) a stroną Windows-1250. Cały wątek jest tutaj. Postanowiłem drążyć temat dalej. Na razie stanęliśmy na tym, że mamy funkcję T-SQL:

IF OBJECT_ID('dbo.ufn_MazoviaTo1250', 'FN') IS NOT NULL
  DROP FUNCTION dbo.ufn_MazoviaTo1250;
GO
CREATE FUNCTION dbo.ufn_MazoviaTo1250 (@String varchar(maX))
RETURNS varchar(max)
WITH SCHEMABINDING
AS
BEGIN
  SET @String = @String COLLATE Polish_BIN;
  RETURN
    REPLACE (
      REPLACE (
        REPLACE (
          REPLACE (
            REPLACE (
              REPLACE (
                REPLACE (
                  REPLACE (
                    REPLACE (
                      REPLACE (
                        REPLACE (
                          REPLACE (
                            REPLACE (
                              REPLACE (
                                REPLACE (
                                  REPLACE (
                                    REPLACE (
                                      REPLACE (
                                        @String, CHAR(165), CHAR(209) -- Ń
                                      ), CHAR(143), CHAR(165)         -- Ą
                                    ), CHAR(163), CHAR(211)           -- Ó
                                  ), CHAR(156), CHAR(163)             -- Ł
                                ), CHAR(149), CHAR(198)               -- Ć
                              ), CHAR(144), CHAR(202)                 -- Ę
                            ), CHAR(152), CHAR(140)                   -- Ś
                          ), CHAR(160), CHAR(143)                     -- Ź
                        ), CHAR(161), CHAR(175)                       -- Ż
                      ), CHAR(134), CHAR(185)                         -- ą
                    ), CHAR(141), CHAR(230)                           -- ć
                  ), CHAR(145), CHAR(234)                             -- ę
                ), CHAR(146), CHAR(179)                               -- ł
              ), CHAR(164), CHAR(241)                                 -- ń
            ), CHAR(162), CHAR(243)                                   -- ó
          ), CHAR(158), CHAR(156)                                     -- ś
        ), CHAR(166), CHAR(159)                                       -- ź
      ), CHAR(167), CHAR(191)                                         -- ż
    ) COLLATE database_default;
END;
GO
-- Test
SELECT dbo.ufn_MazoviaTo1250 ('Ź•śĄŁ˜ ˇ†Ť‘’¤˘ž¦§'); 

Jeden z uczestników dyskusji zasugerował, by problem rozwiązać za pomocą funkcji napisanej w .NET (CLR). Wydaje się to być rozsądnym posunięciem. W końcu CLR może się spisywać lepiej w przypadku operacji na tekście (zwłaszcza długim). Na pierwszy ogień jednak postanowiłem rzucić sobie zadanie – napisanie funkcji do listowania dostępnych kodowań z poziomu .NET. Dzięki temu dowiem się, jakie kodowania mogę wykorzystać w konwersjach z użyciem metod klas z przestrzeni System.Text (np. klasy Encoding). Czyli rozchodzi się o to, by wiedzieć, jakie strony kodowe mogę konwertować niekoniecznie znak po znaku.

Po chwili skrobania wyszło mi coś takiego:

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

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(
        FillRowMethodName="FillEncodings",
        Name="ufn_CLR_GetEncodings",
        TableDefinition="CodePage int,
          EncodingName nvarchar(255),
          DisplayName nvarchar(255)"
        )]
    public static IEnumerable ufn_CLR_GetEncodings()
    {
        EncodingInfo[] Encodings = Encoding.GetEncodings();
        return Encodings;
    }

    public static void FillEncodings(
      object Obj,
      out int CodePage,
      out string EncodingName,
      out string DisplayName)
    {
        EncodingInfo e = (EncodingInfo) Obj;
        EncodingName = e.Name;
        DisplayName = e.DisplayName;
        CodePage = e.CodePage;
    }
};

W razie wątpliwości, co należy z tym kodem zrobić, zajrzyj na przykład tutaj: http://geekswithblogs.net/frankw/archive/2008/05/03/a-quick-walk-through-of-clr-integration-with-sql-server.aspx.

Test wdrożonej funkcji wygląda tak:

SELECT * FROM dbo.ufn_CLR_GetEncodings() ORDER BY CodePage;

I okazuje się, że Mazovia oczywiście nie znalazła się na liście. No cóż, pozostaje zatem żmudna podmiana znak po znaku. Ale o tym już napiszę następnym razem, jak tylko napiszę właściwy kod i przetestuję szybkość rozwiązania CLR vs. szybkość rozwiązania T-SQL. Ciąg dalszy nastapi ;-)