Paulo Morgado

Brincando Com SQL Server CLR Integration – Parte II

Na minha última entrada, mostrei como converter um saco de propriedades armazenado como texto numa tabela usando um CLR Table-Valued Function.

Comecei a pensar que podia obter o valor das propriedades, mas não os podia alterar ou acrescentar novas propriedades.

Passar uma tabela como parâmetro ainda não é possível em SQL Server 2005, que seria a plataforma alvo.

Poderia criar funções para Criar, Actualizar e Apagar propriedades.

Ou poderia usar XML. Tudo o que seria necessário era criar uma CLR Scalar-Valued Function para converter o saco de propriedades numa representação em XML e outra para converter o XML de volta para o formato do saco de propriedades.

Ainda tenho de carregar todo o saco de propriedades para uma variável se quiser fazer alguma alteração, mas pode ser usado em SQL Server 2005.

Converter o saco de propriedades para um documento XML pode ser feito usando o enumerador desenvolvido anteriormente:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToXml",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlXml ShortPropsToXml(string shortPropsText)
{
    var xml = new XElement("ShortProps",
        from shortProp in ShortPropsEnumerable(shortPropsText)
        select new XElement("p",
            new XAttribute("n", shortProp.Key),
            new XCData(shortProp.Value)));

    using (var buffer = new MemoryStream())
    {
        using (var xmlWriter = XmlWriter.Create(buffer, new XmlWriterSettings { CheckCharacters = false }))
        {
            xml.WriteTo(xmlWriter);
        }

        buffer.Position = 0;

        using (XmlReader xmlReader = XmlReader.Create(buffer, new XmlReaderSettings { CheckCharacters = false }))
        {
            return new SqlXml(xmlReader);
        }
    }
}

Converter o documento XML de volta ao formato do saco de propriedades também é fácil:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "XmlToShortProps",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static SqlChars XmlToShortProps(SqlXml shortPropsXml)
{
    var xml = XDocument.Parse(shortPropsXml.Value);

    var textBuilder = new StringBuilder();
    foreach (var item in xml.Document.Element("ShortProps").Elements("p"))
    {
        textBuilder.AppendFormat("[[[{1}]]]{0}{2}{0}", Environment.NewLine, item.Attribute("n").Value, item.Value);
    }

    return new SqlChars(textBuilder.ToString().ToCharArray());
}

Agora é só actualizar a assembly na base de dados:

ALTER ASSEMBLY [MyAssembly]
FROM '...\MyAssembly.dll'
GO

Definir as Scalar-Valued Functions em Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToXml](@shortPropsText [nvarchar](4000))
RETURNS [xml] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[ShortPropsToXml]
GO

CREATE FUNCTION [dbo].[XmlToShortProps](@shortPropsXml [xml])
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [Esi.SA.Encyclopedia].[ShortProps].[XmlToShortProps]
GO

E está pronto para ser usado.

Agora, dada esta definição do saco de propriedades:

declare @text nvarchar(max)='[[[name1]]]
value1
[[[name2]]]
value2
[[[name3]]]
value3
'

Posso convertê-lo em XML:

DECLARE @xml [xml] = dbo.ShortPropsToXml(@text)

• Alterar o valor de uma propriedade:

  set @xml.modify('replace value of (/ShortProps/p[@n="name2"]/text())[1] with "new value2"')
  

• Inserir uma nova propriedade:

  set @xml.modify('insert <p n="name4">Value4.1
  Value4.2</p> after (/ShortProps/p[@n="name2"])[1]')

• Apagar uma propriedade:

  set @xml.modify('delete (/ShortProps/p[@n="name3"])[1]')

• E converter de volta para o formato de saco de propriedades:

  print dbo.XmlToShortProps(@xml)

  [[[name1]]]
  value1
  [[[name2]]]
  new value2
  [[[name4]]]
  Value4.1
  Value4.2

• Consultar como se fosse uma tabela:

  select T.C.value('./@n', 'nvarchar(max)') as Name, T.C.value('.', 'nvarchar(max)') as Value from @xml.nodes('/ShortProps/p') T(C)

  Name	Value
  name1	value1
  name2	new value2
  name4	Value4.1 Value4.2

Infelizmente, a aplicação usa caracteres que são inválidos para o SQL Server como caracteres XML e não posso usar estas funções.