SqlServerClrIntegration

UserPreferences

1. Sql Server CLR Integration
2. 예제
1. 프로젝트 생성
2. UDF 제작
3. 어셈블리 등록
4. UDF 등록
5. UDF 사용
3. 링크

1 Sql Server CLR Integration

사용자 함수를 만드는 것은 이전에도 가능했지만, CLR의 도입으로 그 일이 훨씬 편해졌다.

2 예제

'Inside Microsoft SQL Server 2005: T-SQL Programming'에 나온 예제로서, CLR 쪽의 정규식 함수를 SQL Server 쪽에서 사용하는 예제다. .NET 버전 차이 때문에 에러나는 부분들은 수정했다. (2.0 기준)



2.1 프로젝트 생성

• New Project -> Visual C# -> Windows -> 클래스 라이브러리
  서버를 지정해주지는 않아도 되나, 바인딩이 귀찮다.
  
• New Project -> Visual C# -> 데이터베이스 -> SQL Server 프로젝트
  개발이 편리해지기는 하나, 서버를 지정해야 한다는 점이 좀 껄끄럽다.
  
  
  

2.2 UDF 제작

using System;
using System.Collections.Generic;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
//using System.Diagnostics;
//using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

namespace ClrUtility
{
    public partial class ClrUtility
    {
        [SqlFunction(IsDeterministic = true, DataAccess = DataAccessKind.None)]
        public static SqlBoolean fn_RegExMatch(SqlString inpStr, SqlString regExStr)
        {
            if (inpStr.IsNull || regExStr.IsNull)
                return SqlBoolean.Null;
            else
                return (SqlBoolean)Regex.IsMatch(inpStr.Value, regExStr.Value, RegexOptions.CultureInvariant);
        }
    }
}

컴파일하고 DLL을 어딘가에 둔다. 어트리뷰트 쪽 좀 조사해봐야할 것 같은데.




2.3 어셈블리 등록

SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
IF DB_ID('ClrUtilityDatabase') Is NOT NULL
    DROP DATABASE ClrUtilityDatabase;
GO
CREATE DATABASE ClrUtilityDatabase;
GO
USE ClrUtilityDatabase;
GO
CREATE ASSEMBLY ClrUtility
    FROM 'C:\ClrUtility.dll'
    WITH PERMISSION_SET = SAFE;

PERMISSION_SET 부분에 들어가는 내용은 다음과 같다.


• SAFE: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 없다. 기본값이다.
  
• EXTERNAL_ACCESS: 파일, 네트워크, 환경변수, 레지스트리 등에 접근할 수 있다.
  
• UNSAFE: EXTERNAL_ACCESS 권한 + unmanaged 코드 실행 권한
  
  
참고로 EXTERNAL_ACCESS 이상의 권한을 주기 위해서는 아래와 같은 명령어를 통해 TRUSTWORTHY 속성을 켜줘야 한다.


ALTER DATABASE ClrUtilityDatabase SET TRUSTWORTHY ON





2.4 UDF 등록

USE ClrUtilityDatabase;
GO

IF OBJECT_ID('dbo.fn_RegExMatch') IS NOT NULL
        DROP FUNCTION dbo.fn_RegExMatch;
GO

CREATE FUNCTION dbo.fn_RegExMatch
    (@inpStr AS NVARCHAR(MAX), @regExStr AS NVARCHAR(MAX))
RETURNS BIT
EXTERNAL NAME ClrUtility.[ClrUtility.ClrUtility].fn_RegExMatch;

네임스페이스 부분 개같으니 주의.



2.5 UDF 사용

USE ClrUtilityDatabase;
SELECT dbo.fn_RegExMatch('abcd', '[a-z]+');

3 링크

• Introducing SQL Server 2005's CLR Integration
  
• SQL Server 2005 CLR Integration Part 2: Working with Data
  
• Using CLR Integration in SQL Server 2005
  

• see also SqlServer
  

• CategoryDatabase