연습: 관리 코드로 저장 프로시저 만들기

아티클
08/10/2011

Visual Basic 및 Visual C# 등의 .NET Framework 언어를 사용하여 관리 코드로 Microsoft SQL Server 2005 및 SQL Server 2008 데이터베이스에 대한 저장 프로시저를 작성할 수 있습니다. 관리 코드로 작성된 저장 프로시저를 SQL Server CLR(공용 언어 런타임) 저장 프로시저라고 합니다.

SQL CLR 데이터베이스 프로젝트에 저장 프로시저 항목을 추가하여 SQL 저장 프로시저를 만들 수 있습니다. 관리 코드에서 생성한 저장 프로시저를 배포한 후에는 이러한 프로시저가 다른 저장 프로시저처럼 호출되고 실행됩니다.

이 연습에서 수행할 작업은 다음과 같습니다.

새 SQL CLR 데이터베이스 프로젝트 만들기
관리 코드로 저장 프로시저 만들기
SQL Server 2008 데이터베이스에 저장 프로시저 배포
데이터베이스에서 저장 프로시저를 테스트하는 스크립트 만들기
데이터베이스에서 데이터를 쿼리하여 저장 프로시저가 올바르게 실행되었는지 확인

사전 요구 사항

이 연습을 완료하려면 다음과 같은 요건이 필요합니다.

SQL Server 2008에서 실행 중인 AdventureWorks2008 샘플 데이터베이스에 연결. 자세한 내용은 Microsoft 웹 사이트의 Microsoft SQL Server Product Samples페이지를 참조하십시오.

프로젝트 만들기

새 SQL CLR 데이터베이스 프로젝트를 만들려면

파일 메뉴에서 새 프로젝트를 만듭니다.
SQL CLR 데이터베이스 프로젝트를 선택하고 프로젝트 이름을 SQLCLRStoredProcedure로 지정한 후 확인을 클릭합니다. 자세한 내용은 방법: SQL Server 공용 언어 런타임 통합을 사용하는 데이터베이스 개체의 프로젝트 만들기을 참조하십시오.

SQL Server 2008 데이터베이스에 연결

이 연습을 수행하려면 SQL Server 2008에서 실행 중인 AdventureWorks2008 데이터베이스에 연결해야 합니다. 서버 탐색기에서 AdventureWorks2008 샘플 데이터베이스에 연결할 수 있는 경우 해당 데이터베이스가 데이터베이스 참조 추가 대화 상자에 나열됩니다.

AdventureWorks 샘플 데이터베이스에 연결하려면

연결 추가 대화 상자를 완료하여 SQL Server 2008에 포함된 AdventureWorks2008 샘플에 연결합니다. 자세한 내용은 연결 추가/수정(Microsoft SQL Server)을 참조하십시오.

또는
데이터베이스 참조 추가 대화 상자에서 AdventureWorks2008 샘플 데이터베이스에 대한 기존 연결을 선택합니다. 자세한 내용은 데이터베이스 참조 추가 대화 상자을 참조하십시오.

SQL CLR 데이터베이스 프로젝트 구성

SQL CLR 데이터베이스 프로젝트를 만든 후 배포하기 전에 몇 가지 프로젝트 속성을 수정해야 할 수도 있습니다.

SQL CLR 데이터베이스 프로젝트에 대한 속성을 구성하려면

솔루션 탐색기에서 속성을 구성할 프로젝트를 선택합니다.
프로젝트 메뉴에서 ProjectName 속성을 클릭합니다. 여기서 ProjectName은 SQL CLR 데이터베이스 프로젝트에 지정한 이름입니다.
응용 프로그램 탭의 어셈블리 이름에서 SQL CLR 데이터베이스 프로젝트에서 빌드한 어셈블리에 지정할 이름을 지정합니다.
응용 프로그램 탭의 대상 프레임워크에서 대상 SQL Server 버전에서 지원되는 .NET Framework의 버전을 클릭합니다.

중요

SQL Server 2005 및 SQL Server 2008은 .NET Framework 버전 2.0으로 작성된 SQL Server 프로젝트만 지원합니다. 배포 하려고 하는 경우는 SQL Server 프로젝트를 SQL Server 2005 또는 SQL Server 2008, 오류 메시지가 나타납니다: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly 'AssemblyName' failed because assembly 'AssemblyName' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database(어디 AssemblyName 배포 하는 어셈블리의 이름입니다).
데이터베이스 탭의 어셈블리 소유자에서 사용자 또는 역할의 이름을 어셈블리의 소유자로 입력합니다.

중요

이 값은 현재 사용자가 멤버로 속해 있는 역할의 이름이어야 합니다. 또는 현재 사용자가 IMPERSONATE 권한을 가져야 합니다. 어셈블리 소유자를 지정하지 않으면 현재 사용자에게 소유권이 제공됩니다. 이 설정은 SQL Server CREATE ASSEMBLY 문의 AUTHORIZATION owner_name 인수에 해당합니다. 자세한 내용은 Microsoft 웹 사이트의 CREATE ASSEMBLY (Transact-SQL)를 참조하십시오.
파일 메뉴에서 모두 저장을 클릭합니다.

SQL Server 저장 프로시저 만들기

SQL CLR 데이터베이스 프로젝트를 만든 후 해당 프로젝트에 저장 프로시저를 추가합니다.

SQL Server 저장 프로시저를 만들려면

프로젝트 메뉴에서 새 항목 추가를 클릭합니다.
새 항목 추가 대화 상자에서 저장 프로시저를 선택합니다.
새 저장 프로시저의 이름으로 InsertCurrency를 입력합니다.
추가를 클릭합니다.

코드 편집기에서 코드를 다음과 같이 바꿉니다.

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures

    <SqlProcedure()>
    Public Shared Sub InsertCurrency(
        ByVal currencyCode As SqlString, ByVal name As SqlString)

        Using conn As New SqlConnection("context connection=true")

            Dim InsertCurrencyCommand As New SqlCommand()
            Dim currencyCodeParam As New SqlParameter("@CurrencyCode", SqlDbType.NVarChar)
            Dim nameParam As New SqlParameter("@Name", SqlDbType.NVarChar)

            currencyCodeParam.Value = currencyCode
            nameParam.Value = name


            InsertCurrencyCommand.Parameters.Add(currencyCodeParam)
            InsertCurrencyCommand.Parameters.Add(nameParam)

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" &
                " VALUES(@CurrencyCode, @Name, GetDate())"

            InsertCurrencyCommand.Connection = conn

            conn.Open()
            InsertCurrencyCommand.ExecuteNonQuery()
            conn.Close()
        End Using
    End Sub
End Class

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;


public partial class StoredProcedures
{
    [SqlProcedure()]
    public static void InsertCurrency_CS(
        SqlString currencyCode, SqlString name)
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
        {
            SqlCommand InsertCurrencyCommand = new SqlCommand();
            SqlParameter currencyCodeParam = new SqlParameter("@CurrencyCode", SqlDbType.NVarChar);
            SqlParameter nameParam = new SqlParameter("@Name", SqlDbType.NVarChar);

            currencyCodeParam.Value = currencyCode;
            nameParam.Value = name;

            InsertCurrencyCommand.Parameters.Add(currencyCodeParam);
            InsertCurrencyCommand.Parameters.Add(nameParam);

            InsertCurrencyCommand.CommandText =
                "INSERT Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
                " VALUES(@CurrencyCode, @Name, GetDate())";

            InsertCurrencyCommand.Connection = conn;

            conn.Open();
            InsertCurrencyCommand.ExecuteNonQuery();
            conn.Close();
        }
    }
}

저장 프로시저 배포, 실행 및 디버깅

새 저장 프로시저를 만든 후에는 F5 키를 눌러 빌드, SQL Server에 배포 및 디버깅을 수행할 수 있습니다. 먼저 프로젝트의 TestScripts 폴더에 있는 Test.sql 파일에 저장 프로시저를 실행하고 테스트하는 코드를 추가합니다. 테스트 스크립트를 만드는 방법에 대한 자세한 내용은 방법: Test.sql 스크립트를 편집하여 SQL Server 공용 언어 런타임 통합을 사용하는 개체 실행을 참조하십시오.

SQL을 디버깅하는 방법에 대한 자세한 내용은 Debugging SQL Database Objects을 참조하십시오.

InsertCurrency 저장 프로시저를 배포하고 실행하려면

솔루션 탐색기에서 TestScripts 폴더를 확장하고 Test.sql 파일을 두 번 클릭합니다.

참고

다른 스크립트를 기본 디버그 스크립트로 지정할 수 있습니다. 자세한 내용은 방법: Test.sql 스크립트를 편집하여 SQL Server 공용 언어 런타임 통합을 사용하는 개체 실행을 참조하십시오.

Test.sql 파일의 코드를 다음 코드로 바꿉니다.

'VB
EXEC InsertCurrency 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

//C#
EXEC InsertCurrency_CS 'AAA', 'Currency Test'
SELECT * from Sales.Currency where CurrencyCode = 'AAA'

F5 키를 눌러 저장 프로시저를 빌드, 배포 및 디버깅합니다. 디버깅하지 않고 배포하는 방법에 대한 자세한 내용은 방법: SQL Server에 SQL CLR 데이터베이스 프로젝트 항목 배포를 참조하십시오.

보기에 표시 된 결과 출력 창 및 선택 쇼에서 출력: 출력 데이터베이스.

참고 항목