SQL Server - Using CLR Types and Functions
insights Stats
warning Please login first to view stats information.
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
info Last modified by Raymond 3 years ago
copyright
This page is subject to Site terms.
comment Comments
No comments yet.