In-Process 데이터 액세스 예제
InProcessDataAccess 예제에는 SQL Server CLR in-process 데이터 액세스 공급자의 다양한 기능을 보여 주는 몇 가지 간단한 함수가 포함되어 있습니다.
필수 구성 요소
이 프로젝트를 만들고 실행하려면 다음 소프트웨어가 설치되어 있어야 합니다.
SQL Server 또는 SQL Server Express SQL Server Express는 SQL Server Express 설명서 및 예제 웹 사이트에서 무료로 구할 수 있습니다.
SQL Server 개발자 웹 사이트에서 제공되는 AdventureWorks 데이터베이스
.NET Framework SDK 2.0 이상 또는 Microsoft Visual Studio 2005 이상. .NET Framework SDK는 무료로 구할 수 있습니다.
또한 다음 조건을 충족해야 합니다.
사용하고 있는 SQL Server 인스턴스에 대해 CLR 통합이 설정되어 있어야 합니다.
CLR 통합을 설정하려면 다음 단계를 수행합니다.
CLR 통합 사용
- 다음 Transact-SQL 명령을 실행합니다.
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
[!참고]
CLR을 설정하려면 ALTER SETTINGS 서버 수준 사용 권한이 있어야 합니다. 이 사용 권한은 sysadmin 및 serveradmin 고정 서버 역할의 멤버가 암시적으로 소유합니다.
사용하고 있는 SQL Server 인스턴스에 AdventureWorks 데이터베이스를 설치해야 합니다.
사용하고 있는 SQL Server 인스턴스의 관리자가 아닌 경우 설치를 완료하기 위해 관리자로부터 CreateAssembly 권한을 부여 받아야 합니다.
예제 빌드
다음 지침을 사용하여 예제를 만들고 실행합니다.
Visual Studio 또는 .NET Framework 명령 프롬프트를 엽니다.
필요한 경우 예제에 대한 디렉터리를 만듭니다. 이 예에서는 C:\MySample을 사용합니다.
c:\MySample에서 inprocda.vb(Visual Basic 예제용) 또는 inprocda.cs(C# 예제용)를 만들고 적합한 Visual Basic 또는 C# 예제 코드(아래)를 파일에 복사합니다.
선택하는 언어에 따라 다음 중 하나를 실행하여 명령줄 프롬프트에서 예제 코드를 필수 어셈블리로 컴파일합니다.
Vbc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll,C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Xml.dll /target:library InProcDA.vb
Csc /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Data.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.dll /reference:C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.XML.dll /target:library inprocda.cs
Transact-SQL 설치 코드를 파일에 복사하고 해당 파일을 예제 디렉터리에 Install.sql로 저장합니다.
예제가 C:\MySample\ 이외의 디렉터리에 설치된 경우 Install.sql 파일이 해당 위치를 가리키도록 표시된 대로 편집합니다.
다음을 실행하여 어셈블리, 저장 프로시저 및 함수를 배포합니다.
- sqlcmd -E -I -i install.sql
Transact-SQL 설치 코드를 파일에 복사하고 해당 파일을 예제 디렉터리에 test.sql로 저장합니다.
명령 프롬프트에서 다음 줄을 실행하여 응용 프로그램을 테스트합니다.
- sqlcmd -E -I -i test.sql
Transact-SQL 정리 스크립트를 파일에 복사하고 해당 파일을 예제 디렉터리에 cleanup.sql로 저장합니다.
다음 명령으로 스크립트를 실행합니다.
- sqlcmd -E -I -i cleanup.sql
예제 코드
다음은 이 예제에 대한 코드 목록입니다.
C#
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
public sealed class DataAccessDemo
{
private DataAccessDemo()
{
}
/// <summary>
/// Simple example to send a message to the client.
/// </summary>
public static void SendMessage(string msg)
{
SqlContext.Pipe.Send("Message from server: " + msg);
}
/// <summary>
/// Simple example of performing data access within
/// a function
/// </summary>
/// <returns></returns>
[Microsoft.SqlServer.Server.SqlFunction(DataAccess = Microsoft.SqlServer.Server.DataAccessKind.Read)]
public static string ReportSqlVersion()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
//create a command from the current context
SqlCommand cmd = conn.CreateCommand();
//execute something
cmd.CommandText = "select @@version";
conn.Open();
//return results as scalar
return (string)cmd.ExecuteScalar();
}
}
/// <summary>
/// Create a result set on the fly and send it to the client.
/// </summary>
public static void SendTransientResultSet()
{
//create the metadata for the columns
Microsoft.SqlServer.Server.SqlMetaData[] columnSchema
= new Microsoft.SqlServer.Server.SqlMetaData[] {
new Microsoft.SqlServer.Server.SqlMetaData("stringcol", SqlDbType.NVarChar, 128)
};
//create a record based on that metadata
SqlDataRecord newRecord = new SqlDataRecord(columnSchema);
//populate it
newRecord.SetString(0, "Hello World!");
//send it
SqlContext.Pipe.Send(newRecord);
}
/// <summary>
/// Execute a command and send the results to the client directly.
/// </summary>
public static void ExecuteToClient()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select @@version";
conn.Open();
SqlContext.Pipe.ExecuteAndSend(cmd);
}
}
/// <summary>
/// Execute a command and send the resultig reader to the client
/// </summary>
public static void SendReaderToClient()
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select @@version";
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
try
{
SqlContext.Pipe.Send(rdr);
}
finally
{
rdr.Close();
}
}
}
};
Visual Basic
Imports Microsoft.SqlServer.Server
Imports Microsoft.VisualBasic
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Diagnostics
Partial Public NotInheritable Class DataAccessDemo
Private Sub New()
End Sub
''' <summary>
''' Simple example of performing data access within a function
''' </summary>
''' <returns></returns>
<SqlFunction(DataAccess:=DataAccessKind.Read)> _
Public Shared Function ReportSqlVersion() As SqlString
Using conn As New SqlConnection("context connection=true")
'create a command from the current context
Dim cmd As SqlCommand = conn.CreateCommand()
'execute something
cmd.CommandText = "SELECT @@VERSION"
conn.Open()
'return results as scalar
Return CType(cmd.ExecuteScalar(), String)
End Using
End Function
''' <summary>
''' Simple example to send a message to the client.
''' </summary>
Public Shared Sub SendMessage(ByVal msg As String)
SqlContext.Pipe.Send(("Message from server: " & msg))
End Sub
''' <summary>
''' Create a result set on the fly and send it to the client.
''' </summary>
Public Shared Sub SendTransientResultSet()
'create the metadata for the columns
Dim columnSchema() As Microsoft.SqlServer.Server.SqlMetaData _
= {New SqlMetaData("stringcol", SqlDbType.NVarChar, 128)}
'create a record based on that metadata
Dim newRecord As New SqlDataRecord(columnSchema)
'populate it
newRecord.SetString(0, "Hello World!")
'send it
SqlContext.Pipe.Send(newRecord)
End Sub
''' <summary>
''' Execute a command and send the results to the client directly.
''' </summary>
Public Shared Sub ExecuteToClient()
Using conn As New SqlConnection("context connection=true")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "SELECT @@VERSION"
conn.Open()
SqlContext.Pipe.ExecuteAndSend(cmd)
End Using
End Sub
''' <summary>
''' Execute a command and send the resulting reader to the client
''' </summary>
Public Shared Sub SendReaderToClient()
Using conn As New SqlConnection("context connection=true")
Dim cmd As SqlCommand = conn.CreateCommand()
cmd.CommandText = "SELECT @@VERSION"
conn.Open()
Dim rdr As SqlDataReader = cmd.ExecuteReader()
Try
SqlContext.Pipe.Send(rdr)
Finally
rdr.Close()
End Try
End Using
End Sub
End Class
이는 어셈블리를 배포하고 이 예에 필요한 저장 프로시저 및 함수를 만드는 Transact-SQL 설치 스크립트(Install.sql)입니다.
USE AdventureWorks;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))
DROP FUNCTION [ReportSqlVersion];
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO
DECLARE @SamplesPath nvarchar(1024)
-- You may need to modify the value of the this variable if you have installed the sample someplace other than the default location.
set @SamplesPath = N'C:\MySample\'
CREATE ASSEMBLY InProcDA FROM @SamplesPath + 'InProcDA.dll'
WITH permission_set = SAFE;
GO
CREATE PROCEDURE [SendMessage] @msg nvarchar(4000)
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendMessage];
GO
CREATE FUNCTION [ReportSqlVersion]() RETURNS nvarchar(4000)
AS EXTERNAL NAME [InProcDA].[DataAccessDemo].[ReportSqlVersion];
GO
CREATE PROCEDURE [SendTransientResultSet]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendTransientResultSet];
GO
CREATE PROCEDURE [ExecuteToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[ExecuteToClient];
GO
CREATE PROCEDURE [SendReaderToClient]
AS
EXTERNAL NAME [InProcDA].[DataAccessDemo].[SendReaderToClient];
GO
다음 Transact-SQL(test.sql)에서는 이 예제에서 정의한 저장 프로시저 및 함수를 실행하여 예를 테스트합니다.
USE AdventureWorks;
GO
-- send a message to the client
EXEC SendMessage N'This is a test message.';
-- exec a function that does data access
SELECT dbo.ReportSqlVersion();
-- exec the proc that sends a result set to the client
EXEC SendTransientResultSet;
EXEC ExecuteToClient;
EXEC SendReaderToClient;
USE master;
GO
다음 Transact-SQL은 데이터베이스에서 어셈블리, 함수 및 저장 프로시저를 제거합니다.
USE AdventureWorks
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendMessage')
DROP PROCEDURE SendMessage;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendTransientResultSet')
DROP PROCEDURE SendTransientResultSet;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'ExecuteToClient')
DROP PROCEDURE ExecuteToClient;
GO
IF EXISTS (SELECT * FROM sys.procedures WHERE name = N'SendReaderToClient')
DROP PROCEDURE SendReaderToClient;
GO
IF EXISTS (SELECT * FROM sys.objects WHERE name = N'ReportSqlVersion' and (type = 'FS' or type = 'FT'))
DROP FUNCTION [ReportSqlVersion];
GO
IF EXISTS (SELECT * FROM sys.assemblies WHERE name = N'InProcDA') DROP ASSEMBLY InProcDA;
GO