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

About author
Comments
No comments.
Add comment
Title
Title is required.
Name
Name is required.
Email
Please input your personal email with valid format.
Comments
Please input comment content.
Captcha Refresh
Input captcha:

Subscription

Statistics

Locations of visitors to this page