SQL Server - Using CLR Types and Functions

visibility 487 event 2013-11-10 access_time 2 years ago language English

thumb_up 0

more_vert

In this article, I am going to demonstrate how to use CLR (Common Language Runtime) data types and functions in SQL Server.

Prepare the CLR Type and Function

Open Visual Studio and create a Visual C# Class Library Project named “SqlServerCLR”.

Create a struct MyCLRType using code below:

using Microsoft.SqlServer.Server;
using System;
using System.Data.SqlTypes;
 
namespace SqlServerCLR
{
    [Serializable()]
    [SqlUserDefinedType(Format.Native)]
    public struct MyCLRType : INullable
    {
 
        private int m_Value;
        public int Value
        {
            get { return m_Value; }
            set { m_Value = value; }
        }
 
        private bool m_IsNull;
 
        public static MyCLRType Null
        {
            get
            {
                MyCLRType type = new MyCLRType();
                type.m_IsNull = true;
                return type;
            }
        }
 
        [SqlFunction]
        public static MyCLRType Parse(SqlString s)
        {
            if (s.IsNull)
            {
                return Null;
            }
 
            // Parse input string here to separate out coordinates 
            int v = s.ToSqlInt32().Value;
 
            MyCLRType type = new MyCLRType();
            type.Value = v;
            return (type);
        }
 
 
        public override string ToString()
        {
            if (IsNull)
                return "Null";
            else
                return Value.ToString();
        }
 
        [SqlFunction]
        public static SqlString ToSqlString(MyCLRType myType)
        {
            return new SqlString(myType.ToString());
        }
 
        public bool IsNull
        {
            get { return m_IsNull; }
        }
    }
}

Build the project.

Create user defined type

CREATE ASSEMBLY SqlServerCLR
FROM 'F:\My Projects\SqlServerProjects\SqlServerCLR\bin\Release\SqlServerCLR.dll';
GO
CREATE TYPE MyCLRType 
EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType];
GO

If CLR is not enabled, execute the code below to enable it:

exec sp_configure 'clr enabled', 0;
reconfigure;

Create scalar function

CREATE FUNCTION dbo.ParseToMyType
(@string nvarchar(max))
RETURNS MyCLRType
AS
EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType].Parse
GO
 
CREATE FUNCTION dbo.ParseToNVarchar
(@myType MyCLRType)
RETURNS NVARCHAR(max)
AS
EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType].ToSqlString
GO

Use the defined type

DECLARE @myType MyCLRType= dbo.ParseToMyType('1110');
print [dbo].[ParseToNVarchar](@myType);

Result:

1110

Log in with external accounts

toc Table of contents

Prepare the CLR Type and Function
Create user defined type
Create scalar function
Use the defined type

insights Stats

warning Please login first to view stats information.

tag sql server

article Extract Values from JSON String Column in SQL Server article Spark Read from SQL Server Source using Windows/Kerberos Authentication article AttributeError: type object 'java.sql.Types' has no attribute '__javaclass__' article Build Online Product Management Website using MVC 3 + Entity Framework + SQL Server article JDBC Integrated Security, NTLM and Kerberos Authentication for SQL Server Read more (40)

tagmssql