SQL Server - Using CLR Types and Functions
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 2 years ago
copyright
This page is subject to Site terms.
comment Comments
No comments yet.
Log in with external accounts
warning Please login first to view stats information.
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)