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

147 views 0 comments posted at about 5 years ago Raymond

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

Add comment

Comments (0)

No comments yet.
In this Page