Small introduction how to use this Library

First Step
First of all add EasyCLRBase Assembly (or Project) to your new CLR Library. The Base Assembly contains Attributes/Classes/Interfaces that are necessary for automatic CLR recognition.

Assembly.cs
EasyCLRBase contains ClrImportable Attribute. This Attribute should be inserted into each CLR assembly (Assembly.cs)
[assembly: EasyCLRBase.AssemblyExplorer.ClrImportable]

This is only usefull to boost the working speed by scanning all assemblies. All other assemblies that are not containing this attribute will be ignored. All refenreced assemblies will be automaticly added to MS SQL server.

Second Step
We've created new CLR Assembly and finished the first step. Now we continue with creating specific CLR Types like Function/Procedure/Type/Aggregate.

HOWTO Create CLR Function/Procedures:
There are two types of CLR Function:
  1. Scalar, return scalar value.
  2. TabledValue, return Table, multirow/-column.

This two types are methods that. So we need to define a class for this methods like in EasyCLRNetwork project, Scalar.cs file. To keep the code clear and easy to understand, I prefer to split Scalar and TabledValue function in own classes.
...
...
using EasyCLRBase;
using EasyCLRBase.AssemblyExplorer;

namespace EasyCLRNetwork
{
    [Export(typeof(EasyCLRBase.IClrBase))]
    public class Scalar : ClrBase
    {
        [Microsoft.SqlServer.Server.SqlFunction(Name = "ext_net_web_request")]
        public static SqlString ClrWebRequest(
            SqlString uri, 
            SqlString userAgent, 
            SqlString method, 
            [ClrParameter(Result = "bit = 0")]SqlBoolean responseRequired)
        {
            SqlString result = String.Empty;
            ...
            ...
            return result;
        }
    }
}

Make sure that each CLR based class inherit from IClrBase or ClrBase. This will assure the this type will be exported as CLR. Each Class or Struct that contain CLR Types should be marked with Export Attribute. Forware to ExportAttribute IClrBase Type.
[Export(typeof(EasyCLRBase.IClrBase))]

Now our class is prepared for new CLR Scalar function. To create new Scalar Function, we declare new public static method call. Microsoft.SqlServer.Server.SqlFunction is required for CLR SQL Function recognition. If you like to define specific name for this function, use Name Property in SqlFunction Attribute. If Name Property is not defined, the method name will be take for CLR Scalar function.

Note that all parameter in all CLR methods have to be Sql Types. Additionally You can override Sql parameters definition with ClrParameter. This Attribute contain two fields. Result (custom value definition) and Comment (comment for the current parameter, actually this will be removed by MS SQL, so it only interesting if You save the CLR Creation script somewhere). So we add to responseRequired default value "False".

For more information how to create Scalar Functions visit MS official Web Site

TabledValue Function become a bit complicated.
        [SqlFunction(
            DataAccess = DataAccessKind.Read,
            FillRowMethodName = "FillEnumerateDirectory",
            Name = "ext_enumerate_dir",
            TableDefinition = "\r\n\tName nvarchar(max),\r\n\tFullName nvarchar(max),\r\n\tSize bigint,\r\n\tisDirectory bit\r\n\t")]
        public static IEnumerable EnumerateDirectory(SqlString directory)
        {
            ...
        }

        private static void FillEnumerateDirectory(
            Object input, 
            out SqlString name, 
            out SqlString fullName, 
            out SqlInt64 size, 
            out SqlBoolean isDirectory)
        {
            ...
        }

We need two more definition in the SqlFunction attribute.
  1. FillRowMethodName, Method where each row will be extracted and returned
  2. TableDefinition, definition of a table. Name of each column do not have to match the name of the FillRowMethodName defined Method, but have to match the by type.
For more information about Sql TabledValue functions visit MS official Web Site.

Sql Procedured require the same structure as Sql functions. We need public static void (or SqlInt32) Method call. We need to define SqlProcedure Attribute for each procedure methods.

        [Microsoft.SqlServer.Server.SqlProcedure(Name = "ext_misc_big_print")]
        public static void ClrBigPrint(SqlString print, SqlBoolean silent)
        {
            ...
        }


For more information about SQL Procedures visit MS official Web Site.

Now we can continue with Sql Aggregate functions .
Aggregate function can be built in two ways (for more questions about HOWTO create aggregate function read official MS documentation).
  1. Struct, more native, for simple aggregation. Can boost performance (see).
  2. Class, almost user defined format that can slow down the aggregation. Can perform complex aggregation (see). Required IBinarySerialize
Aggregate function required three methods.
  1. public void Init()
  2. public void Accumulate(<SqlType> value0, ....)
  3. public <SqlType> Terminate()
Accumulate and Terminate support only Sql Types or User Defined Types. If You try to use language native types like int, string, etc., GetSqlParam in ClrBase will throw an NotImplementedException.
Please, do not forget to mark the aggregation function with Export Attribute.
    [SqlUserDefinedAggregate(
        Format.UserDefined, /// Binary Serialization because of StringBuilder
        IsInvariantToOrder = false, /// order changes the result
        IsInvariantToNulls = true,  /// nulls don't change the result
        IsInvariantToDuplicates = false, /// duplicates change the result
        Name = "ext_concat",
        MaxByteSize = Constants.AggregateMaxByte)]
    [Serializable]
    [Export(typeof(IClrBase))]
    public struct ConcatenateString : IClrFailoverBase, IBinarySerialize
    ...

If You like to declare struct as aggregate function and add it to automatic script generation, please do not forget to implement inheritance of IClrBase (or IClrFailoverBase that implements IClrBase). Implement the GetClrCreationCode method with following code:
public ClrExportString GetClrCreationCode()
{
    return ClrBase.GetClrCreationCode(this.GetType());
}


There is a known Bug in SqlAggregateAttribute under .NET3.5 and older. If need to use aggregation over 8000 bytes, you have to save intermediary result in a sql native variable.
The .NET4 or higher package already fixed this bug. So you can MaxByteSize = -1 in SqlUserDefinedAggregate attribute.

The newest version contain a workaround for the MaxByteSize Bug. ConcatenateString inherit IClrFailoverBase that implements Name() and GetClrCreationCode().

        public String Name()
        {
            return "ext_concat";
        }
        public ClrExportString GetClrCreationCode()
        {
            return ClrBase.GetClrFailoverBaseCode((IClrFailoverBase)this);
        }

For the other CLR Aggregate functions, please use the same method, if you like to Install Code on MS SQL 2008 (R2) or earlier version.


User Defined Types are more or less related with aggregate function. You have to create class (do not work with MS SQL 2008, some kind of BUG?) or struct. Do not forget to override ToString() method, implement Parse method, Null Property and IsNull Property. For example EasyCLRNetwork.UdtSshDotNetSftp:
...
...
using EasyCLRBase;
using EasyCLRBase.AssemblyExplorer;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

namespace EasyCLRNetwork
{
    [Export(typeof(EasyCLRBase.IClrBase))]
    [Serializable]
    [SqlUserDefinedType(
        Format.UserDefined, 
        Name = "SftpSshNet", 
        IsByteOrdered = true, 
        MaxByteSize = Constants.UserDefinedTypeMaxByte)]
    public struct UdtSshDotNetSftp : IClrBase, IBinarySerialize, INullable, IEquatable<UdtSshDotNetSftp>
    {
        private Boolean _isNull;
        ...
        ...
        public bool IsNull
        {
            get { return (this._isNull); }
        }

        public ClrExportString GetClrCreationCode()
        {
            return ClrBase.GetClrCreationCode(this.GetType());
        }

        [SqlMethod(OnNullCall = false)]
        public static UdtSshDotNetSftp Parse(SqlString str)
        {
            UdtSshDotNetSftp result = UdtSshDotNetSftp.Null;
            ...
            ...
            return result;
        }


        public static UdtSshDotNetSftp Null
        {
            get
            {
                return new UdtSshDotNetSftp() { _isNull = true };
            }
        }

        public void Read(System.IO.BinaryReader r)
        {
            ...
        }

        public void Write(System.IO.BinaryWriter w)
        {
            ...
        }

        public override String ToString()
        {
            return this.IsNull == true ? "NULL" : String.Concat(this.Server, Environment.NewLine, this.User, Environment.NewLine, this.Password);
        }
    }
}

If UDT contains non-primitive types, this UDT automatically become a complex type that required IBinarySerialize.

For our luck, SqlUserDefinedType attribute do not have the same bug as aggregate function. So we can define our type MaxByteSize = -1. But we cannot use classes in MS SQL 2008 Server versions.

Last edited May 28, 2013 at 7:59 AM by roker002, version 5

Comments

No comments yet.