Microsoft.SqlServer.Server 1.0.0

Microsoft.SqlServer.Server

Description

This is a helper library for Microsoft.Data.SqlClient, enabling cross-framework support for User-Defined Types (UDTs) in SQL Server.

The package provides the types and attributes needed to create SQL Server CLR user-defined types, user-defined aggregates, and user-defined functions that work seamlessly across .NET Framework and .NET Core/.NET.

Supportability

This package supports:

• .NET Framework 4.6+
• .NET Standard 2.0 (for .NET Core 2.0+ and .NET 5+)

Installation

Install the package via NuGet:

dotnet add package Microsoft.SqlServer.Server

Or via the Package Manager Console:

Install-Package Microsoft.SqlServer.Server

Available Types

This package provides the following types in the Microsoft.SqlServer.Server namespace:

Getting Started

Creating a User-Defined Type

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

[SqlUserDefinedType(Format.Native)]
public struct Point : INullable
{
    private bool _isNull;
    private double _x;
    private double _y;

    public bool IsNull => _isNull;

    public static Point Null
    {
        get
        {
            var p = new Point();
            p._isNull = true;
            return p;
        }
    }

    public double X
    {
        get => _x;
        set => _x = value;
    }

    public double Y
    {
        get => _y;
        set => _y = value;
    }

    public static Point Parse(SqlString s)
    {
        if (s.IsNull)
            return Null;
        
        var parts = s.Value.Split(',');
        return new Point
        {
            X = double.Parse(parts[0]),
            Y = double.Parse(parts[1])
        };
    }

    public override string ToString() => _isNull ? "NULL" : $"{_x},{_y}";
}

Creating a User-Defined Aggregate

using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;

[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public class Concatenate : IBinarySerialize
{
    private StringBuilder _builder;

    public void Init()
    {
        _builder = new StringBuilder();
    }

    public void Accumulate(SqlString value)
    {
        if (!value.IsNull)
        {
            if (_builder.Length > 0)
                _builder.Append(",");
            _builder.Append(value.Value);
        }
    }

    public void Merge(Concatenate other)
    {
        if (other._builder.Length > 0)
        {
            if (_builder.Length > 0)
                _builder.Append(",");
            _builder.Append(other._builder);
        }
    }

    public SqlString Terminate() => new SqlString(_builder.ToString());

    public void Read(BinaryReader reader) => _builder = new StringBuilder(reader.ReadString());
    public void Write(BinaryWriter writer) => writer.Write(_builder.ToString());
}

Documentation

• SQL Server CLR Integration
• CLR User-Defined Types
• CLR User-Defined Aggregates
• Microsoft.Data.SqlClient Documentation

Release Notes

Release notes are available at: https://go.microsoft.com/fwlink/?linkid=2185441

License

This package is licensed under the MIT License.

Related Packages

• Microsoft.Data.SqlClient - The main SqlClient driver