MSSQL C# CLR 라이브러리 SQL 쿼리

 

C# CLR 라이브러리를 사용하여 SQL 서버에 SQL을 실행 방법을 설명한다.

 

필요한 헤더 using 

using Microsoft.SqlServer.Server; // SQL 에서 함수 정의 시 필요.

using System.Data.SqlClient; // SQL에 접속에 필요한 Client 사용 시 필요. 

 

간단한 SQL Select 쿼리 소스

[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int getSession()
{
    using (SqlConnection conn = new SqlConnection("context connection=true"))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand("select @@SPID, HOST_NAME() ", conn);
        SqlDataReader sqlDataReader = cmd.ExecuteReader();
        sqlDataReader.Read();

        int spid = sqlDataReader.GetInt16(0);
        String hostname = sqlDataReader.GetString(1);

        sqlDataReader.Close();
        conn.Close();

        return spid;
    }
}

 

자세한 SqlConnection 은 아래 링크로 확인.

https://docs.microsoft.com/ko-kr/dotnet/api/system.data.sqlclient.sqlconnection?view=dotnet-plat-ext-6.0 

SqlConnection 클래스 (System.Data.SqlClient)

 

주요 내용만 적자면 SqlConnection 클래스 객체를 생성을 먼저 진행하고, 생성 할 때 연결 문자열을 작성한다. 

'context connection=true'  의미는 내부 접속을 위한 연결이라고 보면 된다. 

이것 또한 아래 링크에 자세히 설명 되어 있다. 

https://docs.microsoft.com/ko-kr/sql/relational-databases/clr-integration/data-access/context-connection?view=sql-server-ver15 

컨텍스트 연결 - SQL Server

 

컨텍스트의 연결과 일반 연결의 차이는 컨텍스트 연결은 대개 저장 프로시저나 함수가 실행 중인 동일한 서버에 연결해야 하는 경우 사용하고,  컨텍스트 연결을 사용하면 동일한 트랜잭션 공간에서 실행되므로 다시 인증할 필요가 없다는 장점이 있다고 적혀 있다. 

 

일반 접속하는 경우에는 연결 문자열을  'Data Source=(localhost);Initial Catalog=testdb;User ID=test;Password=test123;' 이런식으로 접속 address와 DB명 user, pswd 를 입력하여 사용한다. 

 

SqlConnection 로 서버의 연결을 준비 한 후 open() 함수로 연결을 진행한다. conn.open() 

 

SqlCommend로 SQL을 실행할 명령어를 conn 과 같이 넣어 준다.

select @@SPID, HOST_NAME()

예시로 새션 id 와 호스트이름을 가져오는 쿼리이며, 접속 된 툴에서 돌리면 아래와 같은 형태의 결과를 얻을 수 있다. 

다음으로 SqlDataReader 에 cmd.ExecuteReader(); 을 실행한 결과를 넣는다. 

1개 값을 얻을 경우에는 ExecuteScalar() 라는 함수를 사용해도 된다. 리턴 타입은 Object 형태이다. 

 

결과를 넣은 변수에서 쿼리 결과를 읽는다. sqlDataReader.Read(); 

여러 건일 경우에는 while 문 사용하여 계속 읽어서 사용하면 된다. 

 

쿼리 결과의 컬럼별 결과를 타입 별로 변수에 넣는다. 

int spid = sqlDataReader.GetInt16(0);
String hostname = sqlDataReader.GetString(1);

 

@@SPID의 반환 형태가 GetInt16이라 저 타입을 사용하였고, 일반 varchar 형태는 GetString으로 받으면 된다. 

Get 함수 안에 파라미터는 컬럼 즉 select 한 순서라고 보면 된다. 2개 결과 값이니 0, 1을 넣었다. 

Get 함수는 다양한 타입을 제공하니 실제 조회하는 Table 에 컬럼에 맞게 작성하면 된다. 

 

메서드 종류에 대한 자세한 설명이 나온 링크는 아래에서 확인한다. 

https://docs.microsoft.com/ko-kr/dotnet/api/system.data.sqlclient.sqldatareader?view=dotnet-plat-ext-6.0 

SqlDataReader 클래스 (System.Data.SqlClient)

 

그리고 다 사용한 후에는 close 함수를 이용하여 모두 닫아 준다. 

sqlDataReader.Close();
conn.Close(); 

 

당연히 넣어주는건대 무시하거나 빼먹으면 나중에 메모리 문제로 골치 아파지니 잊지 말고 넣어야한다. 

 

이제 생성한 dll 파일을 SQL 서버에 복사하여 CLR을 등록해 주자. 

CREATE ASSEMBLY sqlclr
FROM 'C:\Users\Administrator\Desktop\sclr\sqlclr.dll'
WITH PERMISSION_SET = SAFE;
go

CREATE FUNCTION [dbo].[getSession] ()
RETURNS [int]
AS EXTERNAL NAME [sqlclr].[UserDefinedFunctions].[getSession];
GO

함수에 입력 파라미터는 사용안하고 리턴으로 int 받는 함수이다. 

 

 Select 쿼리로 생성한 getSession 함수 테스트 진행하면 아래와 같이 새션 ID를 확인 할 수 있다.