다음을 통해 공유


관리 코드를 사용하여 저장 프로시저 및 사용자 정의 함수 만들기(C#)

스콧 미첼

PDF 다운로드

Microsoft SQL Server 2005는 개발자가 관리 코드를 통해 데이터베이스 개체를 만들 수 있도록 .NET 공용 언어 런타임과 통합됩니다. 이 자습서에서는 Visual Basic 또는 C# 코드를 사용하여 관리되는 저장 프로시저 및 관리되는 사용자 정의 함수를 만드는 방법을 보여 줍니다. 또한 이러한 Visual Studio 버전에서 이러한 관리되는 데이터베이스 개체를 디버그할 수 있는 방법도 확인합니다.

소개

Microsoft의 SQL Server 2005와 같은 데이터베이스는 T-SQL(Transact-구조적 쿼리 언어)을 사용하여 데이터를 삽입, 수정 및 검색합니다. 대부분의 데이터베이스 시스템에는 다시 사용할 수 있는 단일 단위로 실행할 수 있는 일련의 SQL 문을 그룹화하기 위한 구문이 포함됩니다. 저장 프로시저는 한 가지 예입니다. 또 다른 하나는 UDF(사용자 정의 함수)입니다. 9단계에서 자세히 살펴볼 구문입니다.

SQL은 핵심적으로 데이터 집합을 사용하기 위해 설계되었습니다. , UPDATEDELETE 문은 SELECT기본적으로 해당 테이블의 모든 레코드에 적용되며 해당 절에 의해 WHERE 서만 제한됩니다. 그러나 한 번에 하나의 레코드로 작업하고 스칼라 데이터를 조작하도록 설계된 많은 언어 기능이 있습니다. CURSOR 레코드 집합을 한 번에 하나씩 반복할 수 있도록 허용합니다. 문자열 조작 함수(예: LEFT, CHARINDEXPATINDEX 스칼라 데이터 사용) SQL에는 다음과 같은 IF WHILE제어 흐름 문도 포함됩니다.

Microsoft SQL Server 2005 이전에는 저장 프로시저 및 UDF를 T-SQL 문의 컬렉션으로만 정의할 수 있습니다. 그러나 SQL Server 2005는 모든 .NET 어셈블리에서 사용되는 런타임인 CLR(공용 언어 런타임)통합되도록 설계되었습니다. 따라서 SQL Server 2005 데이터베이스의 저장 프로시저 및 UDF는 관리 코드를 사용하여 만들 수 있습니다. 즉, C# 클래스에서 저장 프로시저 또는 UDF를 메서드로 만들 수 있습니다. 이렇게 하면 이러한 저장 프로시저 및 UDF가 .NET Framework 및 사용자 지정 클래스의 기능을 활용할 수 있습니다.

이 자습서에서는 관리되는 저장 프로시저 및 사용자 정의 함수를 만드는 방법과 Northwind 데이터베이스에 통합하는 방법을 살펴봅니다. 시작해 보겠습니다!

참고 항목

관리되는 데이터베이스 개체는 SQL 개체에 비해 몇 가지 이점을 제공합니다. 언어의 풍요로움과 친숙함, 기존 코드와 논리를 재사용하는 기능이 주요 장점입니다. 그러나 관리되는 데이터베이스 개체는 많은 절차적 논리를 포함하지 않는 데이터 집합으로 작업할 때 효율성이 떨어집니다. 관리 코드와 T-SQL을 사용하는 이점에 대해 자세히 알아보려면 관리 코드를 사용하여 데이터베이스 개체를 만들 때의 장점을 확인 하세요.

1단계: Northwind 데이터베이스를 App_Data 외부로 이동

지금까지 모든 자습서는 웹 애플리케이션 App_Data 폴더에 Microsoft SQL Server 2005 Express Edition 데이터베이스 파일을 사용했습니다. 모든 파일이 하나의 디렉터리 내에 있으므로 데이터베이스 App_Data 를 간소화된 배포에 배치하고 이러한 자습서를 실행하며 자습서를 테스트하기 위한 추가 구성 단계가 필요하지 않습니다.

그러나 이 자습서에서는 Northwind 데이터베이스를 외부 App_Data 로 이동하고 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 명시적으로 등록해 보겠습니다. 폴더의 데이터베이스 App_Data 를 사용하여 이 자습서의 단계를 수행할 수 있지만 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 데이터베이스를 명시적으로 등록하면 여러 단계를 훨씬 더 간단하게 수행할 수 있습니다.

이 자습서의 다운로드에는 두 개의 데이터베이스 파일 NORTHWND.MDF 과 - NORTHWND_log.LDF 이 이름이 지정된 DataFiles폴더에 배치되어 있습니다. 자습서를 직접 구현하는 경우 Visual Studio를 닫고 웹 사이트 폴더의 파일과 NORTHWND_log.LDF 파일을 웹 사이트 외부의 App_Data 폴더로 이동합니다NORTHWND.MDF. 데이터베이스 파일이 다른 폴더로 이동되면 Northwind 데이터베이스를 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 등록해야 합니다. SQL Server Management Studio에서 이 작업을 수행할 수 있습니다. 컴퓨터에 비 Express 버전의 SQL Server 2005가 설치되어 있는 경우 이미 Management Studio가 설치되어 있을 수 있습니다. 컴퓨터에 SQL Server 2005 Express Edition만 있는 경우 잠시 시간을 내어 Microsoft SQL Server Management Studio를 다운로드하여 설치합니다.

SQL Server Management Studio를 시작합니다. 그림 1에서 알 수 있듯이 Management Studio는 연결할 서버를 묻는 것으로 시작합니다. 서버 이름에 localhost\SQLExpress를 입력하고 인증 드롭다운 목록에서 Windows 인증을 선택한 다음 연결을 클릭합니다.

SQL Server Management Studio의 서버에 연결 창을 보여 주는 스크린샷

그림 1: 적절한 데이터베이스 인스턴스에 연결

연결되면 개체 탐색기 창에는 해당 데이터베이스, 보안 정보, 관리 옵션 등을 포함하여 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 대한 정보가 나열됩니다.

폴더에 있는 DataFiles Northwind 데이터베이스(또는 이동한 위치)를 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 연결해야 합니다. 데이터베이스 폴더를 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 연결 옵션을 선택합니다. 그러면 데이터베이스 연결 대화 상자가 나타납니다. 추가 단추를 클릭하고 적절한 NORTHWND.MDF 파일로 드릴다운한 다음 확인을 클릭합니다. 이 시점에서 화면은 그림 2와 유사하게 표시됩니다.

데이터베이스 MDF 파일에 연결하는 방법을 보여 주는 데이터베이스 연결 창의 스크린샷

그림 2: 적절한 데이터베이스 인스턴스에 연결(전체 크기 이미지를 보려면 클릭)

참고 항목

Management Studio를 통해 SQL Server 2005 Express Edition 인스턴스에 연결할 때 데이터베이스 연결 대화 상자에서는 내 문서와 같은 사용자 프로필 디렉터리로 드릴다운할 수 없습니다. 따라서 사용자 프로필이 아닌 디렉터리에 파일과 NORTHWND_log.LDF 파일을 배치 NORTHWND.MDF 해야 합니다.

확인 단추를 클릭하여 데이터베이스를 연결합니다. 데이터베이스 연결 대화 상자가 닫히면 개체 탐색기 방금 연결된 데이터베이스를 나열해야 합니다. Northwind 데이터베이스에 다음과 같은 이름이 있는 것일 수 있습니다 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF. 데이터베이스를 마우스 오른쪽 단추로 클릭하고 이름 바꾸기를 선택하여 데이터베이스 이름을 Northwind로 바꿉니다.

데이터베이스 이름을 Northwind로 변경

그림 3: 데이터베이스 이름을 Northwind로 변경

2단계: Visual Studio에서 새 솔루션 및 SQL Server 프로젝트 만들기

SQL Server 2005에서 관리되는 저장 프로시저 또는 UDF를 만들려면 저장 프로시저 및 UDF 논리를 클래스에 C# 코드로 작성합니다. 코드가 작성되면 이 클래스를 어셈블리(파일) .dll 로 컴파일하고, 어셈블리를 SQL Server 데이터베이스에 등록한 다음, 어셈블리의 해당 메서드를 가리키는 저장 프로시저 또는 UDF 개체를 데이터베이스에 만들어야 합니다. 이러한 단계는 모두 수동으로 수행할 수 있습니다. 모든 텍스트 편집기에서 코드를 만들고, C# 컴파일러()csc.exe를 사용하여 명령줄에서 컴파일하고, 명령을 사용하여 CREATE ASSEMBLY 또는 Management Studio에서 데이터베이스에 등록하고, 유사한 수단을 통해 저장 프로시저 또는 UDF 개체를 추가할 수 있습니다. 다행히 Visual Studio의 Professional 및 Team Systems 버전에는 이러한 작업을 자동화하는 SQL Server 프로젝트 유형이 포함됩니다. 이 자습서에서는 SQL Server 프로젝트 형식을 사용하여 관리되는 저장 프로시저 및 UDF를 만드는 방법을 안내합니다.

참고 항목

Visual Web Developer 또는 Standard 버전의 Visual Studio를 사용하는 경우 수동 접근 방식을 대신 사용해야 합니다. 13단계에서는 이러한 단계를 수동으로 수행하기 위한 자세한 지침을 제공합니다. 이 단계에는 사용 중인 Visual Studio 버전에 관계없이 적용해야 하는 중요한 SQL Server 구성 지침이 포함되어 있으므로 13단계를 읽기 전에 2~12단계를 읽는 것이 좋습니다.

Visual Studio를 열어 시작합니다. 파일 메뉴에서 새 프로젝트를 선택하여 새 프로젝트 대화 상자를 표시합니다(그림 4 참조). 데이터베이스 프로젝트 형식으로 드릴다운한 다음 오른쪽에 나열된 템플릿에서 새 SQL Server 프로젝트를 만들도록 선택합니다. 이 프로젝트의 ManagedDatabaseConstructs 이름을 지정하여 솔루션 내에 Tutorial75배치했습니다.

새 SQL Server 프로젝트 만들기

그림 4: 새 SQL Server 프로젝트 만들기(전체 크기 이미지를 보려면 클릭)

새 프로젝트 대화 상자에서 확인 단추를 클릭하여 솔루션 및 SQL Server 프로젝트를 만듭니다.

SQL Server 프로젝트는 특정 데이터베이스에 연결됩니다. 따라서 새 SQL Server 프로젝트를 만든 후 즉시 이 정보를 지정하라는 메시지가 표시됩니다. 그림 5에서는 1단계에서 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 등록한 Northwind 데이터베이스를 가리키도록 채워진 새 데이터베이스 참조 대화 상자를 보여 줍니다.

SQL Server 프로젝트를 Northwind 데이터베이스와 연결

그림 5: SQL Server 프로젝트를 Northwind 데이터베이스와 연결

이 프로젝트 내에서 만들 관리되는 저장 프로시저 및 UDF를 디버그하려면 연결에 대한 SQL/CLR 디버깅 지원을 사용하도록 설정해야 합니다. SQL Server 프로젝트를 새 데이터베이스와 연결할 때마다(그림 5에서와 같이) Visual Studio에서 연결에서 SQL/CLR 디버깅을 사용하도록 설정할지 묻습니다(그림 6 참조). 예를 클릭합니다.

SQL/CLR 디버깅 사용

그림 6: SQL/CLR 디버깅 사용

이 시점에서 새 SQL Server 프로젝트가 솔루션에 추가되었습니다. 여기에는 프로젝트에서 만든 관리되는 데이터베이스 개체를 디버깅하는 데 사용되는 이름이 지정된 Test.sql파일이 포함된 Test Scripts 폴더가 포함되어 있습니다. 12단계에서 디버깅을 살펴보겠습니다.

이제 새 관리 저장 프로시저 및 UDF를 이 프로젝트에 추가할 수 있지만, 먼저 솔루션에 기존 웹 애플리케이션을 포함할 수 있습니다. 파일 메뉴에서 추가 옵션을 선택하고 기존 웹 사이트를 선택합니다. 적절한 웹 사이트 폴더로 이동하고 확인을 클릭합니다. 그림 7에서 보여 주듯이 웹 사이트와 ManagedDatabaseConstructs SQL Server 프로젝트라는 두 개의 프로젝트를 포함하도록 솔루션을 업데이트합니다.

이제 솔루션 탐색기 두 개의 프로젝트가 포함됩니다.

그림 7: 솔루션 탐색기 이제 두 개의 프로젝트가 포함됩니다.

현재 폴더 NORTHWNDConnectionStringWeb.config 파일을 참조하는 NORTHWND.MDF 값입니다 App_Data . 이 데이터베이스를 SQL Server 2005 Express Edition 데이터베이스 인스턴스에서 App_Data 제거하고 명시적으로 등록했으므로 해당 값을 업데이트 NORTHWNDConnectionString 해야 합니다. Web.config 웹 사이트에서 파일을 열고 연결 문자열 다음을 NORTHWNDConnectionString 읽도록 값을 변경합니다Data Source=localhost\SQLExpress;Initial Catalog=Northwind;Integrated Security=True. 이 변경 <connectionStrings> 후 섹션은 Web.config 다음과 유사하게 표시됩니다.

<connectionStrings>
    <add name="NORTHWNDConnectionString" connectionString=
        "Data Source=localhost\SQLExpress;Initial Catalog=Northwind;
            Integrated Security=True;Pooling=false"
        providerName="System.Data.SqlClient" />
</connectionStrings>

참고 항목

앞의 자습서에서 설명한 것처럼 클라이언트 애플리케이션(예: ASP.NET 웹 사이트)에서 SQL Server 개체를 디버깅할 때 연결 풀링을 사용하지 않도록 설정해야 합니다. 위에 표시된 연결 문자열 연결 풀링( Pooling=false )을 사용하지 않도록 설정합니다. ASP.NET 웹 사이트에서 관리되는 저장 프로시저 및 UDF를 디버깅할 계획이 없는 경우 연결 풀링을 사용하도록 설정합니다.

3단계: 관리되는 저장 프로시저 만들기

Northwind 데이터베이스에 관리되는 저장 프로시저를 추가하려면 먼저 SQL Server 프로젝트에서 저장 프로시저를 메서드로 만들어야 합니다. 솔루션 탐색기 프로젝트 이름을 마우스 오른쪽 단추로 클릭하고 ManagedDatabaseConstructs 새 항목을 추가하도록 선택합니다. 프로젝트에 추가할 수 있는 관리되는 데이터베이스 개체의 유형을 나열하는 새 항목 추가 대화 상자가 표시됩니다. 그림 8에서 볼 수 있듯이 여기에는 저장 프로시저 및 사용자 정의 함수가 포함됩니다.

먼저 중단된 모든 제품을 반환하는 저장 프로시저를 추가해 보겠습니다. 새 저장 프로시저 파일 GetDiscontinuedProducts.cs의 이름을 지정합니다.

GetDiscontinuedProducts.cs 명명된 새 저장 프로시저 추가

그림 8: 명명 GetDiscontinuedProducts.cs 된 새 저장 프로시저 추가(전체 크기 이미지를 보려면 클릭)

이렇게 하면 다음 콘텐츠가 포함된 새 C# 클래스 파일이 생성됩니다.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetDiscontinuedProducts()
    {
        // Put your code here
    }
};

저장 프로시저는 이름이 지정된 StoredProcedures클래스 파일 내에서 메서드로 static partial 구현됩니다. 또한 메서드는 GetDiscontinuedProducts 메서드를 SqlProcedure attribute저장 프로시저로 표시하는 데코레이팅됩니다.

다음 코드는 개체를 SqlCommand 만들고 필드가 1인 제품에 대해 테이블의 모든 열을 Products 반환하는 쿼리로 Discontinued 설정합니다 CommandText SELECT. 그런 다음, 명령을 실행하고 결과를 클라이언트 애플리케이션으로 다시 보냅니다. 이 코드를 GetDiscontinuedProducts 메서드에 추가합니다.

// Create the command
SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = 
      @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
               QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
               ReorderLevel, Discontinued
        FROM Products 
        WHERE Discontinued = 1";
// Execute the command and send back the results
SqlContext.Pipe.ExecuteAndSend(myCommand);

모든 관리되는 데이터베이스 개체는 호출자의 컨텍스트를 SqlContext 나타내는 개체 에 액세스할 수 있습니다. 해당 SqlContext 속성을 통해Pipe 개체SqlPipe 대한 액세스를 제공합니다. 이 SqlPipe 개체는 SQL Server 데이터베이스와 호출 애플리케이션 간에 정보를 페리하는 데 사용됩니다. 이름에서 알 수 있듯이 메서드ExecuteAndSend 전달된 SqlCommand 개체를 실행하고 결과를 클라이언트 애플리케이션으로 다시 보냅니다.

참고 항목

관리되는 데이터베이스 개체는 집합 기반 논리보다는 절차 논리를 사용하는 저장 프로시저 및 UDF에 가장 적합합니다. 절차 논리에는 행 단위로 데이터 집합을 사용하거나 스칼라 데이터를 사용하는 작업이 포함됩니다. 그러나 방금 만든 메서드에는 GetDiscontinuedProducts 절차 논리가 포함되지 않습니다. 따라서 T-SQL 저장 프로시저로 구현하는 것이 이상적입니다. 관리 저장 프로시저를 만들고 배포하는 데 필요한 단계를 보여 주는 관리 저장 프로시저로 구현됩니다.

4단계: 관리되는 저장 프로시저 배포

이 코드가 완료되면 Northwind 데이터베이스에 배포할 준비가 된 것입니다. SQL Server Project를 배포하면 코드를 어셈블리로 컴파일하고, 어셈블리를 데이터베이스에 등록하고, 데이터베이스에 해당 개체를 만들어 어셈블리의 적절한 메서드에 연결합니다. 배포 옵션에서 수행하는 정확한 작업 집합은 13단계에서 보다 정확하게 설명됩니다. 솔루션 탐색기 프로젝트 이름을 마우스 오른쪽 단추로 클릭하고 ManagedDatabaseConstructs 배포 옵션을 선택합니다. 그러나 'EXTERNAL'에 가까운 잘못된 구문 오류로 배포가 실패합니다. 이 기능을 사용하려면 현재 데이터베이스의 호환성 수준 값을 더 높게 설정해야 합니다. 저장 프로시저 sp_dbcmptlevel에 대한 도움말을 참조하세요.

이 오류 메시지는 Northwind 데이터베이스에 어셈블리를 등록하려고 할 때 발생합니다. SQL Server 2005 데이터베이스에 어셈블리를 등록하려면 데이터베이스의 호환성 수준을 90으로 설정해야 합니다. 기본적으로 새 SQL Server 2005 데이터베이스의 호환성 수준은 90입니다. 그러나 Microsoft SQL Server 2000을 사용하여 만든 데이터베이스의 기본 호환성 수준은 80입니다. Northwind 데이터베이스는 처음에 Microsoft SQL Server 2000 데이터베이스이므로 호환성 수준은 현재 80으로 설정되어 있으므로 관리되는 데이터베이스 개체를 등록하려면 90으로 늘려야 합니다.

데이터베이스의 호환성 수준을 업데이트하려면 Management Studio에서 새 쿼리 창을 열고 다음을 입력합니다.

exec sp_dbcmptlevel 'Northwind', 90

도구 모음에서 실행 아이콘을 클릭하여 위의 쿼리를 실행합니다.

Northwind 데이터베이스의 호환성 수준 업데이트

그림 9: Northwind 데이터베이스의 호환성 수준 업데이트(전체 크기 이미지를 보려면 클릭)

호환성 수준을 업데이트한 후 SQL Server 프로젝트를 다시 배포합니다. 이번에는 배포가 오류 없이 완료되어야 합니다.

SQL Server Management Studio로 돌아가서 개체 탐색기 Northwind 데이터베이스를 마우스 오른쪽 단추로 클릭하고 새로 고침을 선택합니다. 그런 다음, 프로그래밍 기능 폴더로 드릴다운한 다음 어셈블리 폴더를 확장합니다. 그림 10에서 볼 수 있듯이 Northwind 데이터베이스에는 이제 프로젝트에서 생성된 어셈블리가 ManagedDatabaseConstructs 포함됩니다.

ManagedDatabaseConstructs 어셈블리가 Northwind 데이터베이스에 등록되었습니다.

그림 10: ManagedDatabaseConstructs 이제 어셈블리가 Northwind 데이터베이스에 등록됨

저장 프로시저 폴더도 확장합니다. 이 경우 라는 GetDiscontinuedProducts저장 프로시저가 표시됩니다. 이 저장 프로시저는 배포 프로세스에 의해 만들어졌으며 어셈블리의 GetDiscontinuedProducts 메서드를 ManagedDatabaseConstructs 가리킵니다. 저장 프로시저가 GetDiscontinuedProducts 실행되면 메서드를 차례로 실행합니다 GetDiscontinuedProducts . 관리되는 저장 프로시저이므로 Management Studio를 통해 편집할 수 없습니다(따라서 저장 프로시저 이름 옆에 있는 잠금 아이콘).

GetDiscontinuedProducts 저장 프로시저가 저장 프로시저 폴더에 나열됩니다.

그림 11: GetDiscontinuedProducts 저장 프로시저가 저장 프로시저 폴더에 나열됨

관리되는 저장 프로시저를 호출하기 전에 해결해야 하는 장애물이 하나 더 있습니다. 데이터베이스는 관리 코드 실행을 방지하도록 구성됩니다. 새 쿼리 창을 열고 저장 프로시저를 실행하여 GetDiscontinuedProducts 이를 확인합니다. 다음 오류 메시지가 표시됩니다. .NET Framework에서 사용자 코드 실행을 사용할 수 없습니다. 'clr 사용 구성 옵션을 사용하도록 설정합니다.

Northwind 데이터베이스의 구성 정보를 검사하려면 쿼리 창에서 명령을 exec sp_configure 입력하고 실행합니다. 이는 clr 사용 설정이 현재 0으로 설정되어 있음을 보여 줍니다.

clr 사용 설정이 현재 0으로 설정됨

그림 12: clr 사용 설정이 현재 0으로 설정됨(전체 크기 이미지를 보려면 클릭)

그림 12의 각 구성 설정에는 최소값과 최대값, 구성 및 실행 값의 네 가지 값이 나열되어 있습니다. clr 사용 설정에 대한 구성 값을 업데이트하려면 다음 명령을 실행합니다.

exec sp_configure 'clr enabled', 1

다시 실행 exec sp_configure 하면 위의 문에서 clr 사용 설정 구성 값을 1로 업데이트했지만 실행 값은 여전히 0으로 설정되어 있음을 알 수 있습니다. 이 구성 변경 내용이 영향을 미치려면 명령을 실행 RECONFIGURE 해야 합니다. 그러면 실행 값이 현재 구성 값으로 설정됩니다. 쿼리 창에 입력 RECONFIGURE 하고 도구 모음에서 실행 아이콘을 클릭하기만 하면 됩니다. 지금 실행 exec sp_configure 하면 clr 사용 설정의 구성 및 실행 값에 대해 1의 값이 표시됩니다.

clr 사용 구성이 완료되면 관리 GetDiscontinuedProducts 되는 저장 프로시저를 실행할 준비가 된 것입니다. 쿼리 창에서 명령을 exec GetDiscontinuedProducts입력하고 실행합니다. 저장 프로시저를 호출하면 메서드의 해당 관리 코드가 GetDiscontinuedProducts 실행됩니다. 이 코드는 중단된 모든 제품을 반환하는 쿼리를 실행하고 SELECT 이 인스턴스의 SQL Server Management Studio인 호출 애플리케이션에 이 데이터를 반환합니다. Management Studio는 이러한 결과를 수신하고 결과 창에 표시합니다.

GetDiscontinuedProducts 저장 프로시저는 모든 중단된 제품을 반환합니다.

그림 13: 저장 프로시저가 GetDiscontinuedProducts 중단된 모든 제품을 반환합니다(전체 크기 이미지를 보려면 클릭).

5단계: 입력 매개 변수를 허용하는 관리되는 저장 프로시저 만들기

이러한 자습서 전체에서 만든 많은 쿼리 및 저장 프로시저에서 매개 변수를 사용했습니다. 예를 들어 Typed DataSet의 TableAdapters 자습서에 대한 새 저장 프로시저 만들기 자습서에서 명명@CategoryID된 입력 매개 변수를 수락하는 저장 GetProductsByCategoryID 프로시저를 만들었습니다. 그런 다음 저장 프로시저는 필드가 CategoryID 제공 @CategoryID 된 매개 변수의 값과 일치하는 모든 제품을 반환했습니다.

입력 매개 변수를 허용하는 관리되는 저장 프로시저를 만들려면 메서드 정의에 해당 매개 변수를 지정하기만 하면 됩니다. 이를 설명하기 위해 이름이 지정된 GetProductsWithPriceLessThan프로젝트에 다른 관리 저장 프로시저를 ManagedDatabaseConstructs 추가해 보겠습니다. 이 관리 저장 프로시저는 가격을 지정하는 입력 매개 변수를 수락하고 필드가 UnitPrice 매개 변수 값보다 작은 모든 제품을 반환합니다.

프로젝트에 새 저장 프로시저를 추가하려면 프로젝트 이름을 마우스 오른쪽 단추로 클릭하고 ManagedDatabaseConstructs 새 저장 프로시저를 추가하도록 선택합니다. 파일 이름을 GetProductsWithPriceLessThan.cs로 지정합니다. 3단계에서 보았듯이 클래스 내에 partial 배치된 메서드 GetProductsWithPriceLessThan 를 사용하여 새 C# 클래스 StoredProcedures파일을 만듭니다.

메서드의 GetProductsWithPriceLessThan 정의를 업데이트하여 명명된 price 입력 매개 변수를 SqlMoney 수락하고 코드를 작성하여 쿼리 결과를 실행하고 반환합니다.

[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetProductsWithPriceLessThan(SqlMoney price)
{
    // Create the command
    SqlCommand myCommand = new SqlCommand();
    myCommand.CommandText =
          @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                   QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                   ReorderLevel, Discontinued
            FROM Products
            WHERE UnitPrice < @MaxPrice";
    myCommand.Parameters.AddWithValue("@MaxPrice", price);
    // Execute the command and send back the results
    SqlContext.Pipe.ExecuteAndSend(myCommand);
}

메서드의 정의 및 코드는 GetProductsWithPriceLessThan 3단계에서 만든 메서드의 GetDiscontinuedProducts 정의 및 코드와 매우 유사합니다. 유일한 차이점은 메서드가 GetProductsWithPriceLessThan 입력 매개 변수(price)로 허용하고, SqlCommand 쿼리에 매개 변수(@MaxPrice)가 포함되고, 매개 변수가 컬렉션 ParametersSqlCommand 추가되고 변수 값 price 이 할당된다는 것입니다.

이 코드를 추가한 후 SQL Server 프로젝트를 다시 배포합니다. 다음으로 SQL Server Management Studio로 돌아가 저장 프로시저 폴더를 새로 고칩니다. 새 항목 GetProductsWithPriceLessThan이 표시됩니다. 쿼리 창에서 명령을 입력하고 실행합니다. 이 명령은 exec GetProductsWithPriceLessThan 25그림 14와 같이 $25 미만의 모든 제품을 나열합니다.

$25 미만의 제품이 표시됩니다.

그림 14: $25 미만의 제품이 표시됩니다(전체 크기 이미지를 보려면 클릭).

6단계: 데이터 액세스 계층에서 관리되는 저장 프로시저 호출

이 시점에서 프로젝트에 관리되는 저장 프로시저를 ManagedDatabaseConstructs 추가하고 GetDiscontinuedProducts GetProductsWithPriceLessThan Northwind SQL Server 데이터베이스에 등록했습니다. 또한 SQL Server Management Studio에서 이러한 관리되는 저장 프로시저를 호출했습니다(그림 13 및 14 참조). 그러나 ASP.NET 애플리케이션에서 이러한 관리 저장 프로시저를 사용하려면 아키텍처의 데이터 액세스 및 비즈니스 논리 계층에 추가해야 합니다. 이 단계에서는 Typed DataSet의 TableAdapters 자습서에 NorthwindWithSprocs 대한 새 저장 프로시저 만들기 자습서에서 처음 만든 Typed DataSet의 두 가지 새 메서드 ProductsTableAdapter 를 추가합니다. 7단계에서는 해당 메서드를 BLL에 추가합니다.

NorthwindWithSprocs Visual Studio에서 Typed DataSet을 열고 명명GetDiscontinuedProducts된 메서드에 새 메서드를 ProductsTableAdapter 추가하여 시작합니다. TableAdapter에 새 메서드를 추가하려면 디자이너에서 TableAdapter 이름을 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 쿼리 추가 옵션을 선택합니다.

참고 항목

Northwind 데이터베이스를 폴더에서 App_Data SQL Server 2005 Express Edition 데이터베이스 인스턴스로 이동했으므로 이 변경 사항을 반영하도록 Web.config의 해당 연결 문자열 업데이트해야 합니다. 2단계에서는 .의 값 업데이트에 대해 NORTHWNDConnectionString 설명했습니다 Web.config. 이 업데이트를 잊은 경우 쿼리를 추가하지 못했다는 오류 메시지가 표시됩니다. TableAdapter에 새 메서드를 추가하려고 할 때 대화 상자에서 개체 Web.config 에 대한 연결을 NORTHWNDConnectionString 찾을 수 없습니다. 이 오류를 해결하려면 확인을 클릭한 다음 2단계에서 설명한 대로 값으로 이동하여 Web.config 업데이트 NORTHWNDConnectionString 합니다. 그런 다음, TableAdapter에 메서드를 다시 추가해 봅니다. 이번에는 오류 없이 작동해야 합니다.

새 메서드를 추가하면 이전 자습서에서 여러 번 사용한 TableAdapter 쿼리 구성 마법사가 시작됩니다. 첫 번째 단계에서는 임시 SQL 문을 통해 또는 새 저장 프로시저 또는 기존 저장 프로시저를 통해 TableAdapter가 데이터베이스에 액세스하는 방법을 지정하도록 요청합니다. 관리되는 저장 프로시저를 GetDiscontinuedProducts 데이터베이스에 이미 만들고 등록했으므로 기존 저장 프로시저 사용 옵션을 선택하고 다음을 누릅니다.

기존 저장 프로시저 사용 옵션 선택

그림 15: 기존 저장 프로시저 사용 옵션 선택(전체 크기 이미지를 보려면 클릭)

다음 화면에서는 메서드가 호출할 저장 프로시저를 묻는 메시지를 표시합니다. GetDiscontinuedProducts 드롭다운 목록에서 관리되는 저장 프로시저를 선택하고 다음을 누릅니다.

GetDiscontinuedProducts 관리 저장 프로시저 선택

그림 16: 관리 저장 프로시저 선택 GetDiscontinuedProducts (전체 크기 이미지를 보려면 클릭)

그런 다음 저장 프로시저에서 행, 단일 값 또는 아무것도 반환할지 여부를 지정하라는 메시지가 표시됩니다. GetDiscontinuedProducts 중단된 제품 행 집합을 반환하므로 첫 번째 옵션(테이블 형식 데이터)을 선택하고 다음을 클릭합니다.

테이블 형식 데이터 옵션 선택

그림 17: 테이블 형식 데이터 옵션 선택(전체 크기 이미지를 보려면 클릭)

마지막 마법사 화면에서는 사용된 데이터 액세스 패턴과 결과 메서드의 이름을 지정할 수 있습니다. 두 확인란을 모두 선택한 상태로 두고 메서드 FillByDiscontinued 의 이름을 지정하고 GetDiscontinuedProducts. 마침을 클릭하여 마법사를 완료합니다.

메서드의 이름을 FillByDiscontinued 및 GetDiscontinuedProducts로 지정합니다.

그림 18: 메서드 FillByDiscontinued 이름 지정 및 GetDiscontinuedProducts (전체 크기 이미지를 보려면 클릭)

관리되는 저장 프로시저에 대해 명명 FillByPriceLessThanGetProductsWithPriceLessThan 메서드를 ProductsTableAdapter 만들려면 다음 GetProductsWithPriceLessThan 단계를 반복합니다.

그림 19에서는 관리되는 저장 프로시저에 메서드를 추가한 ProductsTableAdapter 후 데이터 세트 디자이너의 GetDiscontinuedProducts GetProductsWithPriceLessThan 스크린샷을 보여 줍니다.

ProductsTableAdapter에는 이 단계에서 추가된 새 메서드가 포함되어 있습니다.

그림 19: 이 ProductsTableAdapter 단계에서 추가된 새 메서드 포함(전체 크기 이미지를 보려면 클릭)

7단계: 비즈니스 논리 계층에 해당 메서드 추가

이제 4단계와 5단계에 추가된 관리되는 저장 프로시저를 호출하는 메서드를 포함하도록 데이터 액세스 계층을 업데이트했으므로 해당 메서드를 비즈니스 논리 계층에 추가해야 합니다. 클래스에 다음 두 메서드를 ProductsBLLWithSprocs 추가합니다.

[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable GetDiscontinuedProducts()
{
    return Adapter.GetDiscontinuedProducts();
}
[System.ComponentModel.DataObjectMethodAttribute
    (System.ComponentModel.DataObjectMethodType.Select, false)]
public NorthwindWithSprocs.ProductsDataTable 
    GetProductsWithPriceLessThan(decimal priceLessThan)
{
    return Adapter.GetProductsWithPriceLessThan(priceLessThan);
}

두 메서드는 단순히 해당 DAL 메서드를 호출하고 인스턴스를 반환합니다 ProductsDataTable . DataObjectMethodAttribute 각 메서드 위의 태그를 사용하면 ObjectDataSource의 데이터 원본 구성 마법사의 SELECT 탭에 있는 드롭다운 목록에 이러한 메서드가 포함됩니다.

8단계: 프레젠테이션 계층에서 관리되는 저장 프로시저 호출

비즈니스 논리 및 데이터 액세스 계층이 보강되어 관리되는 저장 프로시저 호출 GetDiscontinuedProducts GetProductsWithPriceLessThan 지원이 포함되면 이제 ASP.NET 페이지를 통해 이러한 저장 프로시저 결과를 표시할 수 있습니다.

폴더에서 ManagedFunctionsAndSprocs.aspx 페이지를 AdvancedDAL 열고 도구 상자에서 GridView를 디자이너로 끕니다. GridView의 ID 속성을 DiscontinuedProducts 스마트 태그로 설정하고 해당 속성을 새 ObjectDataSource에 DiscontinuedProductsDataSource바인딩합니다. 클래스 GetDiscontinuedProducts 의 메서드에서 해당 데이터를 끌어오도록 ObjectDataSource를 ProductsBLLWithSprocs 구성합니다.

ProductsBLLWithSprocs 클래스를 사용하도록 ObjectDataSource 구성

그림 20: 클래스를 사용하도록 ProductsBLLWithSprocs ObjectDataSource 구성(전체 크기 이미지를 보려면 클릭)

SELECT 탭의 드롭다운 목록에서 GetDiscontinuedProducts 메서드 선택

그림 21: SELECT 탭의 드롭다운 목록에서 메서드 선택 GetDiscontinuedProducts (전체 크기 이미지를 보려면 클릭)

이 표는 제품 정보를 표시하는 데만 사용되므로 UPDATE, INSERT 및 DELETE 탭의 드롭다운 목록을 (없음)으로 설정한 다음 마침을 클릭합니다.

마법사를 완료하면 Visual Studio에서 각 데이터 필드에 대한 BoundField 또는 CheckBoxField를 ProductsDataTable자동으로 추가합니다. GridView 및 DiscontinuedObjectDataSource의 선언적 태그가 다음과 비슷해야 하는 시점을 제외하고 ProductName 이러한 모든 필드를 제거합니다.

<asp:GridView ID="DiscontinuedProducts" runat="server" 
    AutoGenerateColumns="False" DataKeyNames="ProductID" 
    DataSourceID="DiscontinuedProductsDataSource">
    <Columns>
        <asp:BoundField DataField="ProductName" HeaderText="ProductName" 
            SortExpression="ProductName" />
        <asp:CheckBoxField DataField="Discontinued" 
            HeaderText="Discontinued" 
            SortExpression="Discontinued" />
    </Columns>
</asp:GridView>
<asp:ObjectDataSource ID="DiscontinuedProductsDataSource" runat="server" 
    OldValuesParameterFormatString="original_{0}"
    SelectMethod="GetDiscontinuedProducts" TypeName="ProductsBLLWithSprocs">
</asp:ObjectDataSource>

잠시 브라우저를 통해 이 페이지를 봅니다. 페이지를 방문하면 ObjectDataSource에서 클래스 메서드 GetDiscontinuedProductsProductsBLLWithSprocs 호출합니다. 7단계에서 살본 것처럼 이 메서드는 저장 프로시저를 호출하는 DAL ProductsDataTableGetDiscontinuedProducts 클래스 메서드를 GetDiscontinuedProducts 호출합니다. 이 저장 프로시저는 관리되는 저장 프로시저이며 3단계에서 만든 코드를 실행하여 중단된 제품을 반환합니다.

관리 저장 프로시저에서 반환된 결과는 DAL에 의해 패키지된 다음 BLL로 ProductsDataTable 반환된 다음, GridView에 바인딩되어 표시되는 프레젠테이션 계층으로 반환됩니다. 예상대로 그리드에는 중단된 제품이 나열됩니다.

지원되지 않는 제품이 나열됩니다.

그림 22: 중단된 제품이 나열됨(전체 크기 이미지를 보려면 클릭)

추가 연습을 위해 페이지에 TextBox 및 다른 GridView를 추가합니다. 이 GridView에서 클래스의 GetProductsWithPriceLessThan 메서드를 호출 ProductsBLLWithSprocs 하여 TextBox에 입력한 양보다 작은 제품을 표시합니다.

9단계: T-SQL UDF 만들기 및 호출

사용자 정의 함수 또는 UDF는 프로그래밍 언어에서 함수의 의미 체계를 밀접하게 모방한 데이터베이스 개체입니다. C#의 함수와 마찬가지로 UDF는 가변 수의 입력 매개 변수를 포함하고 특정 형식의 값을 반환할 수 있습니다. UDF는 스칼라 데이터(문자열, 정수 등) 또는 테이블 형식 데이터를 반환할 수 있습니다. 스칼라 데이터 형식을 반환하는 UDF부터 시작하여 두 가지 유형의 UDF를 간단히 살펴보겠습니다.

다음 UDF는 특정 제품에 대한 인벤토리의 예상 값을 계산합니다. 이렇게 하려면 특정 제품에 대한 세 개의 UnitPrice입력 매개 변수(, UnitsInStockDiscontinued 값)를 사용하여 형식 money의 값을 반환합니다. 인벤토리의 예상 값을 곱하여 계산합니다 UnitPrice UnitsInStock. 중단된 항목의 경우 이 값은 절반으로 줄입니다.

CREATE FUNCTION udf_ComputeInventoryValue
(
    @UnitPrice money,
    @UnitsInStock smallint,
    @Discontinued bit
)
RETURNS money
AS
BEGIN
    DECLARE @Value decimal
    SET @Value = ISNULL(@UnitPrice, 0) * ISNULL(@UnitsInStock, 0)
    IF @Discontinued = 1
        SET @Value = @Value * 0.5
    
    RETURN @Value
END

이 UDF가 데이터베이스에 추가되면 프로그래밍 기능 폴더, Functions 및 스칼라 값 함수를 확장하여 Management Studio를 통해 찾을 수 있습니다. 다음과 같이 쿼리에서 SELECT 사용할 수 있습니다.

SELECT ProductID, ProductName, dbo.udf_ComputeInventoryValue
    (UnitPrice, UnitsInStock, Discontinued) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

Northwind 데이터베이스에 udf_ComputeInventoryValue UDF를 추가했습니다. 그림 23은 Management Studio를 통해 볼 때 위의 SELECT 쿼리의 출력을 보여줍니다. 또한 UDF는 개체 탐색기 스칼라 값 Functions 폴더 아래에 나열됩니다.

각 제품의 인벤토리 값이 나열됩니다.

그림 23: 각 제품의 인벤토리 값이 나열됨(전체 크기 이미지를 보려면 클릭)

UDF는 테이블 형식 데이터를 반환할 수도 있습니다. 예를 들어 특정 범주에 속하는 제품을 반환하는 UDF를 만들 수 있습니다.

CREATE FUNCTION dbo.udf_GetProductsByCategoryID
(    
    @CategoryID int
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ProductID, ProductName, SupplierID, CategoryID, 
           QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
           ReorderLevel, Discontinued
    FROM Products
    WHERE CategoryID = @CategoryID
)

UDF는 udf_GetProductsByCategoryID 입력 매개 변수를 @CategoryID 수락하고 지정된 SELECT 쿼리의 결과를 반환합니다. 만든 후에는 쿼리의 FROM (또는 JOIN) 절에서 이 UDF를 SELECT 참조할 수 있습니다. 다음 예제에서는 각 음료에 ProductID대한 , ProductNameCategoryID 값을 반환합니다.

SELECT ProductID, ProductName, CategoryID
FROM dbo.udf_GetProductsByCategoryID(1)

Northwind 데이터베이스에 udf_GetProductsByCategoryID UDF를 추가했습니다. 그림 24는 Management Studio를 통해 볼 때 위의 SELECT 쿼리의 출력을 보여줍니다. 테이블 형식 데이터를 반환하는 UDF는 개체 탐색기 테이블 값 Functions 폴더에서 찾을 수 있습니다.

ProductID, ProductName 및 CategoryID는 각 음료에 대해 나열됩니다.

그림 24: ProductID각 음료에 대해 나열된 , ProductNameCategoryID 목록(전체 크기 이미지를 보려면 클릭)

참고 항목

UDF를 만들고 사용하는 방법에 대한 자세한 내용은 사용자 정의 함수 소개를 참조 하세요. 또한 사용자 정의 함수의 장점 및 단점도 확인 합니다.

10단계: 관리되는 UDF 만들기

udf_ComputeInventoryValue 위의 예제에서 만든 UDF 및 udf_GetProductsByCategoryID UDF는 T-SQL 데이터베이스 개체입니다. SQL Server 2005는 3단계와 5단계의 ManagedDatabaseConstructs 관리되는 저장 프로시저와 마찬가지로 프로젝트에 추가할 수 있는 관리되는 UDF도 지원합니다. 이 단계에서는 관리 코드에서 UDF를 udf_ComputeInventoryValue 구현해 보겠습니다.

프로젝트에 관리되는 UDF를 ManagedDatabaseConstructs 추가하려면 솔루션 탐색기 프로젝트 이름을 마우스 오른쪽 단추로 클릭하고 새 항목 추가를 선택합니다. 새 항목 추가 대화 상자에서 사용자 정의 템플릿을 선택하고 새 UDF 파일 udf_ComputeInventoryValue_Managed.cs의 이름을 지정합니다.

ManagedDatabaseConstructs 프로젝트에 새 관리되는 UDF 추가

그림 25: 프로젝트에 새 관리형 UDF ManagedDatabaseConstructs 추가(전체 크기 이미지를 보려면 클릭)

사용자 정의 함수 템플릿은 이름이 클래스 파일 이름(udf_ComputeInventoryValue_Managed이 인스턴스의 경우)과 동일한 메서드를 사용하여 명명 UserDefinedFunctions 된 클래스를 만듭니다partial. 이 메서드는 메서드를 SqlFunction 관리되는 UDF로 플래그를 지정하는 특성을 사용하여 데코레이팅됩니다.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString udf_ComputeInventoryValue_Managed()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

이 메서드는 udf_ComputeInventoryValue 현재 개체SqlString 반환하며 입력 매개 변수를 허용하지 않습니다. 세 개의 입력 매개 변수UnitPriceUnitsInStock를 허용하고 Discontinued 개체를 반환 SqlMoney 하도록 메서드 정의를 업데이트해야 합니다. 인벤토리 값을 계산하는 논리는 T-SQL udf_ComputeInventoryValue UDF의 논리와 동일합니다.

[Microsoft.SqlServer.Server.SqlFunction]
public static SqlMoney udf_ComputeInventoryValue_Managed
    (SqlMoney UnitPrice, SqlInt16 UnitsInStock, SqlBoolean Discontinued)
{
    SqlMoney inventoryValue = 0;
    if (!UnitPrice.IsNull && !UnitsInStock.IsNull)
    {
        inventoryValue = UnitPrice * UnitsInStock;
        if (Discontinued == true)
            inventoryValue = inventoryValue * new SqlMoney(0.5);
    }
    return inventoryValue;
}

UDF 메서드의 입력 매개 변수는 필드, for UnitsInStockUnitPriceSqlBoolean DiscontinuedSqlInt16 for의 해당 SQL 형식 SqlMoney 입니다. 이러한 데이터 형식은 테이블에 정의된 Products 형식을 반영합니다. 열은 형식, UnitsInStock 형식 money열 및 Discontinued 형식smallintbit의 열 UnitPrice 입니다.

코드는 값 0이 SqlMoney 할당된 명명 inventoryValue 된 인스턴스를 만들어 시작합니다. 테이블은 Products 열의 UnitsInStock 데이터베이스 NULL 값을 UnitsInPrice 허용합니다. 따라서 먼저 이러한 값에 개체의IsNull 속성을 통해 수행하는 s가 SqlMoney 포함되어 NULL 있는지 확인해야 합니다. 둘 다 UnitPriceUnitsInStock 값을NULL 포함하는 경우 두 값의 곱으로 계산 inventoryValue 합니다. 그런 다음 true이면 Discontinued 값을 반으로 줄입니다.

참고 항목

개체는 SqlMoneySqlMoney 인스턴스를 함께 곱할 수 있습니다. 인스턴스를 SqlMoney 리터럴 부동 소수점 숫자로 곱할 수 없습니다. 따라서 반으로 줄 inventoryValue 이려면 값이 0.5인 새 SqlMoney 인스턴스를 곱합니다.

11단계: 관리되는 UDF 배포

이제 관리되는 UDF가 만들어졌으므로 Northwind 데이터베이스에 배포할 준비가 되었습니다. 4단계에서 살본 것처럼 SQL Server 프로젝트의 관리되는 개체는 솔루션 탐색기 프로젝트 이름을 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 배포 옵션을 선택하여 배포됩니다.

프로젝트를 배포한 후 SQL Server Management Studio로 돌아가 스칼라 반환 Functions 폴더를 새로 고칩니다. 이제 두 개의 항목이 표시됩니다.

  • dbo.udf_ComputeInventoryValue - 9단계에서 만든 T-SQL UDF 및
  • dbo.udf ComputeInventoryValue_Managed - 방금 배포된 10단계에서 만든 관리되는 UDF입니다.

이 관리되는 UDF를 테스트하려면 Management Studio 내에서 다음 쿼리를 실행합니다.

SELECT ProductID, ProductName, 
       dbo.udf_ComputeInventoryValue_Managed(
                 UnitPrice, 
                 UnitsInStock, 
                 Discontinued
              ) as InventoryValue
FROM Products
ORDER BY InventoryValue DESC

이 명령은 T-SQL udf_ComputeInventoryValue UDF 대신 관리 udf ComputeInventoryValue_Managed 되는 UDF를 사용하지만 출력은 동일합니다. UDF 출력의 스크린샷을 보려면 그림 23을 다시 참조하세요.

12단계: 관리되는 데이터베이스 개체 디버깅

저장 프로시저 디버깅 자습서에서는 Visual Studio를 통해 SQL Server를 디버깅하는 세 가지 옵션인 직접 데이터베이스 디버깅, 애플리케이션 디버깅 및 SQL Server 프로젝트의 디버깅에 대해 설명했습니다. 관리되는 데이터베이스 개체는 직접 데이터베이스 디버깅을 통해 디버그할 수 없지만 클라이언트 애플리케이션 및 SQL Server 프로젝트에서 직접 디버그할 수 있습니다. 그러나 디버깅이 작동하려면 SQL Server 2005 데이터베이스에서 SQL/CLR 디버깅을 허용해야 합니다. 프로젝트를 처음 만들 ManagedDatabaseConstructs 때 Visual Studio에서 SQL/CLR 디버깅을 사용하도록 설정할지 물었습니다(2단계의 그림 6 참조). 이 설정은 서버 탐색기 창에서 데이터베이스를 마우스 오른쪽 단추로 클릭하여 수정할 수 있습니다.

데이터베이스에서 SQL/CLR 디버깅을 허용하는지 확인

그림 26: 데이터베이스에서 SQL/CLR 디버깅을 허용하는지 확인

관리되는 저장 프로시저를 GetProductsWithPriceLessThan 디버그하려고 했습니다. 먼저 메서드 코드 GetProductsWithPriceLessThan 내에서 중단점을 설정합니다.

GetProductsWithPriceLessThan 메서드에서 중단점 설정

그림 27: 메서드에서 GetProductsWithPriceLessThan 중단점 설정(전체 크기 이미지를 보려면 클릭)

먼저 SQL Server 프로젝트에서 관리되는 데이터베이스 개체를 디버깅하는 것을 살펴보겠습니다. 솔루션에는 웹 사이트와 함께 SQL Server 프로젝트라는 ManagedDatabaseConstructs 두 개의 프로젝트가 포함되어 있으므로 SQL Server 프로젝트에서 디버그하려면 디버깅을 시작할 때 Visual Studio에 SQL Server 프로젝트를 시작 ManagedDatabaseConstructs 하도록 지시해야 합니다. 솔루션 탐색기 프로젝트를 마우스 오른쪽 단추로 클릭하고 ManagedDatabaseConstructs 상황에 맞는 메뉴에서 시작 프로젝트로 설정 옵션을 선택합니다.

ManagedDatabaseConstructs 프로젝트가 디버거에서 시작되면 폴더에 있는 Test Scripts 파일의 Test.sql SQL 문을 실행합니다. 예를 들어 관리되는 저장 프로시저를 GetProductsWithPriceLessThan 테스트하려면 기존 Test.sql 파일 콘텐츠를 다음 문으로 바꿉니다. 이 문은 14.95 값을 전달하는 @CategoryID 관리되는 저장 프로시저를 호출합니다GetProductsWithPriceLessThan.

exec GetProductsWithPriceLessThan 14.95

위의 스크립트를 입력한 후에는 디버그 메뉴로 Test.sql이동하여 디버깅 시작을 선택하거나 도구 모음에서 F5 또는 녹색 재생 아이콘을 눌러 디버깅을 시작합니다. 이렇게 하면 솔루션 내에서 프로젝트를 빌드하고, 관리되는 데이터베이스 개체를 Northwind 데이터베이스에 배포한 다음, 스크립트를 실행합니다 Test.sql . 이 시점에서 중단점이 적중되고 메서드를 GetProductsWithPriceLessThan 단계별로 실행하고 입력 매개 변수의 값을 검사하는 등의 작업을 수행할 수 있습니다.

GetProductsWithPriceLessThan 메서드의 중단점이 적중되었습니다.

그림 28: 메서드의 GetProductsWithPriceLessThan 중단점이 적중됨(전체 크기 이미지를 보려면 클릭)

클라이언트 애플리케이션을 통해 SQL Database 개체를 디버그하려면 애플리케이션 디버깅을 지원하도록 데이터베이스를 구성해야 합니다. 서버 탐색기에서 데이터베이스를 마우스 오른쪽 단추로 클릭하고 애플리케이션 디버깅 옵션이 선택되어 있는지 확인합니다. 또한 SQL 디버거와 통합하고 연결 풀링을 사용하지 않도록 ASP.NET 애플리케이션을 구성해야 합니다. 이러한 단계는 저장 프로시저 디버깅 자습서의 2단계에서 자세히 설명했습니다 .

ASP.NET 애플리케이션 및 데이터베이스를 구성한 후 ASP.NET 웹 사이트를 시작 프로젝트로 설정하고 디버깅을 시작합니다. 중단점이 있는 관리되는 개체 중 하나를 호출하는 페이지를 방문하면 애플리케이션이 중지되고 컨트롤이 디버거로 인계됩니다. 여기서 그림 28과 같이 코드를 단계별로 실행할 수 있습니다.

13단계: 관리되는 데이터베이스 개체 수동 컴파일 및 배포

SQL Server 프로젝트를 사용하면 관리되는 데이터베이스 개체를 쉽게 만들고, 컴파일하고, 배포할 수 있습니다. 안타깝게도 SQL Server 프로젝트는 Visual Studio의 Professional 및 Team Systems 버전에서만 사용할 수 있습니다. Visual Web Developer 또는 Visual Studio Standard Edition을 사용하고 관리되는 데이터베이스 개체를 사용하려는 경우 수동으로 만들고 배포해야 합니다. 여기에는 다음 4단계가 포함됩니다.

  1. 관리되는 데이터베이스 개체에 대한 소스 코드가 포함된 파일을 만듭니다.
  2. 개체를 어셈블리로 컴파일합니다.
  3. SQL Server 2005 데이터베이스에 어셈블리 등록 및
  4. 어셈블리의 적절한 메서드를 가리키는 데이터베이스 개체를 SQL Server에 만듭니다.

이러한 작업을 설명하기 위해 지정된 값보다 큰 제품을 UnitPrice 반환하는 새 관리 저장 프로시저를 만들어 보겠습니다. 컴퓨터에 새 GetProductsWithPriceGreaterThan.cs 파일을 만들고 다음 코드를 파일에 입력합니다(Visual Studio, 메모장 또는 텍스트 편집기를 사용하여 이 작업을 수행할 수 있음).

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProductsWithPriceGreaterThan(SqlMoney price)
    {
        // Create the command
        SqlCommand myCommand = new SqlCommand();
        myCommand.CommandText =
            @"SELECT ProductID, ProductName, SupplierID, CategoryID, 
                     QuantityPerUnit, UnitPrice, UnitsInStock, UnitsOnOrder, 
                     ReorderLevel, Discontinued
              FROM Products
              WHERE UnitPrice > @MinPrice";
        myCommand.Parameters.AddWithValue("@MinPrice", price);
        // Execute the command and send back the results
        SqlContext.Pipe.ExecuteAndSend(myCommand);
    }
};

이 코드는 5단계에서 만든 메서드와 GetProductsWithPriceLessThan 거의 동일합니다. 유일한 차이점은 메서드 이름, WHERE 절 및 쿼리에 사용되는 매개 변수 이름입니다. 메서드로 GetProductsWithPriceLessThan 돌아가서 절은 다음WHERE UnitPrice < @MaxPriceWHERE 읽습니다. 여기서는 다음 GetProductsWithPriceGreaterThan을 사용합니다 WHERE UnitPrice > @MinPrice .

이제 이 클래스를 어셈블리로 컴파일해야 합니다. 명령줄에서 파일을 저장 GetProductsWithPriceGreaterThan.cs 한 디렉터리로 이동하고 C# 컴파일러(csc.exe)를 사용하여 클래스 파일을 어셈블리로 컴파일합니다.

csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

시스템에 PATH없는 폴더가 포함된 csc.exe 경우 다음과 같이 해당 경로를 %WINDOWS%\Microsoft.NET\Framework\version\완전히 참조해야 합니다.

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll GetProductsWithPriceGreaterThan.cs

어셈블리로 GetProductsWithPriceGreaterThan.cs 컴파일

그림 29: 어셈블리로 컴파일 GetProductsWithPriceGreaterThan.cs (전체 크기 이미지를 보려면 클릭)

플래그는 /t C# 클래스 파일을 실행 파일이 아닌 DLL로 컴파일되도록 지정합니다. 플래그는 /out 결과 어셈블리의 이름을 지정합니다.

참고 항목

명령줄에서 클래스 파일을 컴파일하는 GetProductsWithPriceGreaterThan.cs 대신 Visual C# Express Edition을 사용 하거나 Visual Studio Standard Edition 에서 별도의 클래스 라이브러리 프로젝트를 만들 수 있습니다. S ren Jacob Lauritsen은 이러한 Visual C# Express Edition 프로젝트에 저장 프로시저에 대한 GetProductsWithPriceGreaterThan 코드와 3단계, 5단계 및 10단계에서 만든 두 개의 관리되는 저장 프로시저 및 UDF를 제공했습니다. S ren의 프로젝트에는 해당 데이터베이스 개체를 추가하는 데 필요한 T-SQL 명령도 포함됩니다.

코드가 어셈블리로 컴파일되면 SQL Server 2005 데이터베이스 내에서 어셈블리를 등록할 준비가 된 것입니다. 이 작업은 T-SQL을 통해, 명령을 CREATE ASSEMBLY사용하거나 SQL Server Management Studio를 통해 수행할 수 있습니다. Management Studio를 사용하는 데 집중해 보겠습니다.

Management Studio에서 Northwind 데이터베이스의 프로그래밍 기능 폴더를 확장합니다. 하위 폴더 중 하나는 어셈블리입니다. 데이터베이스에 새 어셈블리를 수동으로 추가하려면 어셈블리 폴더를 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 새 어셈블리를 선택합니다. 새 어셈블리 대화 상자가 표시됩니다(그림 30 참조). 찾아보기 단추를 클릭하고 방금 컴파일한 어셈블리를 ManuallyCreatedDBObjects.dll 선택한 다음 확인을 클릭하여 데이터베이스에 어셈블리를 추가합니다. 개체 탐색기 어셈블리가 표시되지 ManuallyCreatedDBObjects.dll 않아야 합니다.

데이터베이스에 ManuallyCreatedDBObjects.dll 어셈블리 추가

그림 30: 데이터베이스에 ManuallyCreatedDBObjects.dll 어셈블리 추가(전체 크기 이미지를 보려면 클릭)

ManuallyCreatedDBObjects.dll 어셈블리가 강조 표시된 개체 탐색기 창의 스크린샷

그림 31: ManuallyCreatedDBObjects.dll 개체 탐색기 나열됩니다.

Northwind 데이터베이스에 어셈블리를 추가한 반면, 아직 저장 프로시저를 어셈블리의 GetProductsWithPriceGreaterThan 메서드와 연결하지 않았습니다. 이렇게 하려면 새 쿼리 창을 열고 다음 스크립트를 실행합니다.

CREATE PROCEDURE [dbo].[GetProductsWithPriceGreaterThan] 
( 
    @price money 
) 
WITH EXECUTE AS CALLER 
AS 
EXTERNAL NAME [ManuallyCreatedDBObjects].[StoredProcedures].[GetProductsWithPriceGreaterThan] 
GO

그러면 명명 GetProductsWithPriceGreaterThan 된 Northwind 데이터베이스에 새 저장 프로시저가 만들어지고 관리되는 메서드 GetProductsWithPriceGreaterThan (클래스 StoredProcedures에 있는 어셈블리 ManuallyCreatedDBObjects에 있는)와 연결됩니다.

위의 스크립트를 실행한 후 개체 탐색기 저장 프로시저 폴더를 새로 고칩니다. 옆에 잠금 아이콘이 있는 새 저장 프로시저 항목 GetProductsWithPriceGreaterThan 이 표시됩니다. 이 저장 프로시저를 테스트하려면 쿼리 창에서 다음 스크립트를 입력하고 실행합니다.

exec GetProductsWithPriceGreaterThan 24.95

그림 32와 같이 위의 명령은 $24.95보다 큰 제품에 대한 정보를 표시합니다 UnitPrice .

$24.95보다 큰 UnitPrice를 사용하는 제품을 표시하는 GetProductsWithPriceGreaterThan 저장 프로시저가 실행되는 Microsoft SQL Server Management Studio 창의 스크린샷

그림 32: ManuallyCreatedDBObjects.dll 개체 탐색기 나열됩니다(전체 크기 이미지를 보려면 클릭).

요약

Microsoft SQL Server 2005는 관리 코드를 사용하여 데이터베이스 개체를 만들 수 있는 CLR(공용 언어 런타임)과 통합됩니다. 이전에는 T-SQL을 사용하여 이러한 데이터베이스 개체만 만들 수 있었지만 이제는 C#와 같은 .NET 프로그래밍 언어를 사용하여 이러한 개체를 만들 수 있습니다. 이 자습서에서는 관리되는 저장 프로시저 2개와 관리되는 사용자 정의 함수를 만들었습니다.

Visual Studio의 SQL Server 프로젝트 형식을 사용하면 관리되는 데이터베이스 개체를 쉽게 만들고 컴파일하고 배포할 수 있습니다. 또한 풍부한 디버깅 지원을 제공합니다. 그러나 SQL Server 프로젝트 형식은 Visual Studio의 Professional 및 Team Systems 버전에서만 사용할 수 있습니다. Visual Web Developer 또는 Standard Edition of Visual Studio를 사용하는 경우 13단계에서 보았듯이 만들기, 컴파일 및 배포 단계를 수동으로 수행해야 합니다.

행복한 프로그래밍!

추가 정보

이 자습서에서 설명하는 항목에 대한 자세한 내용은 다음 리소스를 참조하세요.

작성자 정보

7개의 ASP/ASP.NET 책의 저자이자 4GuysFromRolla.com 창립자인 Scott Mitchell은 1998년부터 Microsoft 웹 기술을 연구해 왔습니다. Scott은 독립 컨설턴트, 트레이너 및 작가로 일합니다. 그의 최신 책은 샘스 티치 자신 ASP.NET 24 시간에 2.0입니다. 그는 에서 mitchell@4GuysFromRolla.com찾을 http://ScottOnWriting.NET수있는 자신의 블로그를 통해 도달 할 수 있습니다 .

특별 감사

이 자습서 시리즈는 많은 유용한 검토자가 검토했습니다. 이 자습서의 수석 검토자는 S ren Jacob Lauritsen이었습니다. S ren은 이 문서를 검토하는 것 외에도 관리되는 데이터베이스 개체를 수동으로 컴파일하기 위해 이 문서의 다운로드에 포함된 Visual C# Express Edition 프로젝트를 만들었습니다. 예정된 MSDN 문서를 검토하는 데 관심이 있으신가요? 그렇다면 선을 놓습니다 mitchell@4GuysFromRolla.com.