Paulo Morgado : SQLServer2008

Playing With SQL Server CLR Integration – Part III

Paulo Morgado — Fri, 12 Jun 2009 00:11:00 GMT

You might have noticed that I used LINQ in my last Playing With SQL Server CLR Integration posts (Part I, Part II).

I couldn’t make it work with the standard Visual Studio 2008 SQL CLR project template. Changing the Target Framework to .NET Framework 3.5 wasn’t enough. I had to edit the .csproj file by hand:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="3.5" DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <!— ... -->
  <ItemGroup>
    <!— ... -->
    <Reference Include="System.Core">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
    <Reference Include="System.Xml.Linq">
      <RequiredTargetFramework>3.5</RequiredTargetFramework>
    </Reference>
  </ItemGroup>
  <!— ... -->
</Project>

Playing With SQL Server CLR Integration – Part II

Paulo Morgado — Thu, 11 Jun 2009 23:52:37 GMT

On my last post, I showed how to convert a property bag stored in text to a CLR Table-Valued Function.

I started thinking that I could retrieve the property values, but I couldn’t change them or add new properties.

Passing a table as a parameter is still not possible in SQL Server 2005. And that would force me to load the table into a variable, change it and load it back into the property bag.

Or I could create functions to Create, Update and Delete properties from the property bag.

Or I could just use XML. All it takes is creating a CLR Scalar-Valued Function to convert the property bag to an XML representation and another to convert the XML back to the property bag format.

It still makes me load the property bag into a variable if I want to change it, but it’s usable in SQL Server 2005, which was the targeted platform.

Converting the property bag to an XML document is easy using the previously created enumerator:

[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);
        }
    }
}

Converting the an XML document back into the property bag format is also easy:

[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());
}

Now it’s just uploading the new version of the assembly to the database:

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

And defining the Scalar-Valued Functions in 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

And it’s all set to go.

Now, given this property bag definition:

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

I can change it into XML:

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

Update:

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

Insert:

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

Delete

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

Convert back to the property bag format:

print dbo.XmlToShortProps(@xml)

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

Query as a table:

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

Unfortunately, the application uses characters that are invalid to SQL Server as XML characters and I can’t use it on that application. I’ll have to understand those values better and add other fields to the table and XML.

Playing With SQL Server CLR Integration – Part I

Paulo Morgado — Thu, 11 Jun 2009 23:41:14 GMT

I’m currently working with an application that stores a property bag in a SQL Server column like this:

[[[name1]]]
value1
[[[name2]]]
value2.1
value2.2
[[[name3]]]
value3

Don’t ask me why it’s done like this. It just is.

The application decodes this property bag into its inner structures and all works fine.

Sometimes I would like to query the database directly or do some reporting on those properties and just can’t.

So, I thought this was a good use case for SQL Server CLR Integration. I decided to build a CLR Table-Valued Function that would return the property bag as a two column table.

Parsing the property bag text can easily be achieved with a simple regular expression:

new Regex(
        string.Format(@"(?<Name>(?<=\[\[\[).*(?=\]\]\]{0}))\]\]\]{0}(?<Value>(([\s\S]*?(?={0}\[\[\[))|([\s\S]*?(?={0}$))))", Environment.NewLine),
        RegexOptions.Multiline | RegexOptions.ExplicitCapture | RegexOptions.CultureInvariant | RegexOptions.Compiled);

Ultrapico’s Expresso was a big help when developing this regular expression.

In case you don’t know, the way CLR Table-Valued Function are built is using an initial method to take the input and return an IEnumerable and row filler method the receives the enumerator item and outputs the column values.

Since these are really key-value pairs of strings, I decided to use )>) Structure" href="http://msdn.microsoft.com/library/5tbh8a42.aspx" target=_blank>KeyValuePair<string, string> instances to store each item and the enumerator became simply this:

private static IEnumerable<KeyValuePair<string, string>> ShortPropsEnumerable(string shortPropsText)
{
    return from Match m in shortPropsRegex.Matches(shortPropsText)
           select new KeyValuePair<string, string>(m.Groups["Name"].Value, m.Groups["Value"].Value);
}

And the implementation of the CLR Table-Valued Function is as simple as this:

[Microsoft.SqlServer.Server.SqlFunction(
    Name = "ShortPropsToTable",
    FillRowMethodName = "ShortPropsToTableFillRow",
    TableDefinition = "Name NVARCHAR(4000), Value NVARCHAR(4000)",
    IsDeterministic = true,
    IsPrecise = false,
    DataAccess = DataAccessKind.None,
    SystemDataAccess = SystemDataAccessKind.None)]
public static IEnumerable ShortPropsToTable(string shortPropsText)
{
    return ShortPropsEnumerable(shortPropsText);
}

public static void ShortPropsToTableFillRow(object item, out SqlChars name, out SqlChars value)
{
    KeyValuePair<string, string> shortProp = (KeyValuePair<string, string>)item;

    name = new SqlChars(shortProp.Key);
    value = new SqlChars(shortProp.Value);
}

To use this in SQL Server a few simple steps are need:

Load the assembly into the database:

CREATE ASSEMBLY [MyAssembly]
AUTHORIZATION [dbo]
FROM '...\MyAssembly.dll'
WITH PERMISSION_SET = SAFE
GO

Defining the Table-Valued User-Defined Function in Transact-SQL:

CREATE FUNCTION [dbo].[ShortPropsToTable](@shortPropsText [nvarchar](4000))
RETURNS  TABLE (
    [Name] [nvarchar](4000) NULL,
    [Value] [nvarchar](4000) NULL
) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [MyAssembly].[ShortProps].[ShortPropsToTable]
GO

Enable CLR Integration:

EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

And all is set to go.

Now I can just query the property bag as a table:

SELECT
    e.[ID],
    e.[Name],
    e.[Class],
    e.[Type],
    p.[Name],
    p.[Value]
FROM
    dbo.Entity as e
    CROSS APPLY dbo.ShortPropsToTable(e.[ShortProps]) as p

Just for curiosity, for a little over 50000 entities (that can be retrieved on about 1 second on my laptop), I got a little under 630000 properties in les then 40 seconds.

40 seconds might seem a lot compared to the 1 second, but I would like to see better times using T-SQL. And develop and test the TVF in just a couple of hours.

Now, if only he DBAs would allow me to use it. It doesn’t fit into the company’s “security policy”, you know.

Microsoft Releases SQL Server 2008

Paulo Morgado — Wed, 06 Aug 2008 22:44:00 GMT

SQL Server 2008 is finally out and comes with the Entity Framework, which means that SP1 for Visual Studio 2008 and the .NET Framework is almost out.