access_time 8 years ago languageEnglish
more_vert

[Hands on SQL Server 2012] Using CLR Types and Functions

visibility 388 comment 0

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”.

image

Create a struct MyCLRType using code below:

   1:  using Microsoft.SqlServer.Server;
   2:  using System;
   3:  using System.Data.SqlTypes;
   4:   
   5:  namespace SqlServerCLR
   6:  {
   7:      [Serializable()]
   8:      [SqlUserDefinedType(Format.Native)]
   9:      public struct MyCLRType : INullable
  10:      {
  11:   
  12:          private int m_Value;
  13:          public int Value
  14:          {
  15:              get { return m_Value; }
  16:              set { m_Value = value; }
  17:          }
  18:   
  19:          private bool m_IsNull;
  20:   
  21:          public static MyCLRType Null
  22:          {
  23:              get
  24:              {
  25:                  MyCLRType type = new MyCLRType();
  26:                  type.m_IsNull = true;
  27:                  return type;
  28:              }
  29:          }
  30:   
  31:          [SqlFunction]
  32:          public static MyCLRType Parse(SqlString s)
  33:          {
  34:              if (s.IsNull)
  35:              {
  36:                  return Null;
  37:              }
  38:   
  39:              // Parse input string here to separate out coordinates 
  40:              int v = s.ToSqlInt32().Value;
  41:   
  42:              MyCLRType type = new MyCLRType();
  43:              type.Value = v;
  44:              return (type);
  45:          }
  46:   
  47:   
  48:          public override string ToString()
  49:          {
  50:              if (IsNull)
  51:                  return "Null";
  52:              else
  53:                  return Value.ToString();
  54:          }
  55:   
  56:          [SqlFunction]
  57:          public static SqlString ToSqlString(MyCLRType myType)
  58:          {
  59:              return new SqlString(myType.ToString());
  60:          }
  61:   
  62:          public bool IsNull
  63:          {
  64:              get { return m_IsNull; }
  65:          }
  66:      }
  67:  }

Build the project.

Create user defined type

   1:  CREATE ASSEMBLY SqlServerCLR
   2:  FROM 'F:\My Projects\SqlServerProjects\SqlServerCLR\bin\Release\SqlServerCLR.dll';
   3:  GO
   4:  CREATE TYPE MyCLRType 
   5:  EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType];
   6:  GO

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

   1:  exec sp_configure 'clr enabled', 0;
   2:  reconfigure;

Create scalar function

   1:  CREATE FUNCTION dbo.ParseToMyType
   2:  (@string nvarchar(max))
   3:  RETURNS MyCLRType
   4:  AS
   5:  EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType].Parse
   6:  GO
   7:   
   8:  CREATE FUNCTION dbo.ParseToNVarchar
   9:  (@myType MyCLRType)
  10:  RETURNS NVARCHAR(max)
  11:  AS
  12:  EXTERNAL NAME SqlServerCLR.[SqlServerCLR.MyCLRType].ToSqlString
  13:  GO

Use the defined type

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

Result:

1110

info Last modified by Raymond 8 years ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 405
thumb_up 0
access_time 8 years ago
visibility 1347
thumb_up 0
access_time 8 years ago