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

작성자 : Scott Mitchell

PDF 다운로드

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

소개

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

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

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

이 자습서에서는 관리되는 저장 프로시저를 만들고 Functions를 User-Defined 방법과 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 데이터베이스 instance 명시적으로 등록해 보겠습니다. 폴더의 데이터베이스 App_Data 를 사용하여 이 자습서의 단계를 수행할 수 있지만 데이터베이스를 SQL Server 2005 Express Edition 데이터베이스 instance 명시적으로 등록하여 여러 단계를 훨씬 더 간단하게 수행할 수 있습니다.

이 자습서의 다운로드에는 라는 폴더DataFiles에 두 개의 데이터베이스 파일 NORTHWND.MDF 및 NORTHWND_log.LDF 가 있습니다. 자습서의 고유한 구현과 함께 팔로우하는 경우 Visual Studio를 닫고 웹 사이트 폴더의 및 NORTHWND_log.LDF 파일을 웹 사이트 외부의 App_Data 폴더로 이동합니다NORTHWND.MDF. 데이터베이스 파일이 다른 폴더로 이동되면 Northwind 데이터베이스를 SQL Server 2005 Express Edition 데이터베이스 instance 등록해야 합니다. 이 작업은 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 인증을 선택한 다음 연결을 클릭합니다.

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

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

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

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

참고

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

확인 단추를 클릭하여 데이터베이스를 연결합니다. 데이터베이스 연결 대화 상자가 닫히면 이제 개체 탐색기 방금 연결된 데이터베이스를 나열해야 합니다. Northwind 데이터베이스에 와 같은 이름이 있는 것일 9FE54661B32FDD967F51D71D0D5145CC_LINE ARTICLES\DATATUTORIALS\VOLUME 3\CSHARP\73\ASPNET_DATA_TUTORIAL_75_CS\APP_DATA\NORTHWND.MDF수 있습니다. 데이터베이스를 마우스 오른쪽 단추로 클릭하고 이름 바꾸기를 선택하여 데이터베이스 이름을 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 Project 형식을 사용하여 관리되는 저장 프로시저 및 UDF를 만드는 방법을 안내합니다.

참고

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

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

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

새 프로젝트 대화 상자에서 확인 단추를 클릭하여 솔루션을 만들고 프로젝트를 SQL Server.

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

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

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

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

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

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

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

의 값은 NORTHWNDConnectionString 현재 폴더의 NORTHWND.MDF 파일을 참조합니다App_Data.Web.config 이 데이터베이스를 에서 App_Data 제거하고 SQL Server 2005 Express Edition 데이터베이스 instance 명시적으로 등록했으므로 해당 값을 업데이트 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 Project에서 저장 프로시저를 메서드로 만들어야 합니다. 솔루션 탐색기 프로젝트 이름을 마우스 오른쪽 단추로 클릭하고 ManagedDatabaseConstructs 새 항목을 추가하도록 선택합니다. 프로젝트에 추가할 수 있는 관리되는 데이터베이스 개체의 형식을 나열하는 새 항목 추가 대화 상자가 표시됩니다. 그림 8에서 볼 수 있듯이 여기에는 저장 프로시저 및 User-Defined Functions 등이 포함됩니다.

먼저 중단된 모든 제품을 반환하는 저장 프로시저를 추가해 보겠습니다. 새 저장 프로시저 파일 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클래스 파일 내에서 메서드로 staticpartial 구현됩니다. 또한 메서드는 GetDiscontinuedProducts 로 데코레이팅되어 SqlProcedure attribute메서드를 저장 프로시저로 표시합니다.

다음 코드는 개체를 SqlCommand 만들고 필드가 1인 제품에 대해 테이블의 모든 열을 Products 반환하는 쿼리로 Discontinued 설정합니다 CommandTextSELECT. 그런 다음 명령을 실행하고 결과를 클라이언트 애플리케이션으로 다시 보냅니다. 이 코드를 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

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

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

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

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

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

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

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

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

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

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

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

exec sp_configure 'clr enabled', 1

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

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

그림 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)가 포함되고, 매개 변수가 컬렉션 Parameters 에 SqlCommand 추가되고 변수 값이 할당된다는 것입니다price.

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

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

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

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

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

참고

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

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

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

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

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

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

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

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

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

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

그림 19에서는 및 GetProductsWithPriceLessThan 관리되는 저장 프로시저에 대한 GetDiscontinuedProducts 메서드 ProductsTableAdapter 를 에 추가한 후 DataSet Designer 스크린샷을 보여 줍니다.

그림 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 메서드를 호출하고 instance 반환합니다 ProductsDataTable . 각 메서드 위의 태그를 DataObjectMethodAttribute 사용하면 이러한 메서드가 ObjectDataSource 데이터 원본 구성 마법사의 SELECT 탭에 있는 드롭다운 목록에 포함됩니다.

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

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

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

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

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

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

마법사를 완료하면 Visual Studio는 의 각 데이터 필드에 대한 BoundField 또는 CheckBoxField를 ProductsDataTable자동으로 추가합니다. 및 Discontinued를 제외한 ProductName 이러한 필드를 모두 제거하면 GridView 및 ObjectDataSource의 선언적 태그가 다음과 유사하게 표시됩니다.

<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는 클래스의 GetDiscontinuedProducts 메서드를 ProductsBLLWithSprocs 호출합니다. 7단계에서 살본 것처럼 이 메서드는 저장 프로시저를 호출하는 DAL 클래스 ProductsDataTable 의 GetDiscontinuedProducts 메서드를 GetDiscontinuedProducts 호출합니다. 이 저장 프로시저는 관리되는 저장 프로시저이며 3단계에서 만든 코드를 실행하여 중단된 제품을 반환합니다.

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

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

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

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

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

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

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가 데이터베이스에 추가되면 Management Studio를 통해 프로그래밍 가능성 폴더, Functions 및 Scalar-value Functions를 확장하여 찾을 수 있습니다. 다음과 같이 쿼리에서 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 쿼리의 결과를 반환합니다. 만든 후에는 쿼리의 (또는JOIN) 절에서 FROM 이 UDF를 SELECT 참조할 수 있습니다. 다음 예제에서는 각 음료에 ProductID대한 , ProductName및 CategoryID 값을 반환합니다.

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

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

그림 24: ProductID각 음료에 대해 , ProductName및 CategoryID 가 나열됩니다(전체 크기 이미지를 보려면 클릭).

참고

UDF를 만들고 사용하는 방법에 대한 자세한 내용은 User-Defined Functions 소개를 검사. 또한 User-Defined 함수의 장점과 단점을 검사.

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

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

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

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

User-Defined 함수 템플릿은 이름이 클래스 파일 이름(udf_ComputeInventoryValue_Managed이 instance)과 동일한 메서드를 사용하여 라는 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 반환하며 입력 매개 변수를 허용하지 않습니다. 메서드 정의가 세 개의 입력 매개 변수인 UnitPrice, UnitsInStock및 를 허용하고 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 메서드의 입력 매개 변수는 해당 SQL 형식 SqlMoney 입니다. 필드의 UnitPrice 경우 , SqlInt16UnitsInStock및 SqlBoolean 의 경우 Discontinued입니다. 이러한 데이터 형식은 테이블에 UnitPrice 정의된 형식을 Products 반영합니다. 열은 형식, UnitsInStock 형식money의 열 및 Discontinued 형식smallintbit의 열입니다.

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

참고

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

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 Project에서 직접 디버그할 수 있습니다. 그러나 디버깅이 작동하려면 SQL Server 2005 데이터베이스에서 SQL/CLR 디버깅을 허용해야 합니다. 프로젝트를 처음 만들 ManagedDatabaseConstructs 때 Visual Studio에서 SQL/CLR 디버깅을 사용하도록 설정할지 묻는 메시지가 표시됩니다(2단계의 그림 6 참조). 이 설정은 서버 Explorer 창에서 데이터베이스를 마우스 오른쪽 단추로 클릭하여 수정할 수 있습니다.

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

관리되는 저장 프로시저를 GetProductsWithPriceLessThan 디버그하고 싶다고 상상해 보십시오. 먼저 메서드의 GetProductsWithPriceLessThan 코드 내에서 중단점을 설정합니다.

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

먼저 SQL Server Project에서 관리되는 데이터베이스 개체 디버깅을 살펴보겠습니다. 솔루션에는 웹 사이트와 함께 SQL Server Project라는 두 개의 프로젝트가 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 단계별로 실행하고 입력 매개 변수의 값을 검사하는 등의 작업을 수행할 수 있습니다.

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

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

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

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

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

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 읽습니다 WHERE UnitPrice < @MaxPrice. 여기서는 에서 GetProductsWithPriceGreaterThan를 사용합니다 WHERE UnitPrice > @MinPrice .

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

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

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

C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\csc.exe /t:library /out:ManuallyCreatedDBObjects.dll 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 않아야 합니다.

그림 30: 데이터베이스에 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 (어셈블리ManuallyCreatedDBObjects에 있는 클래스 StoredProcedures에 있는 )와 연결됩니다.

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

exec GetProductsWithPriceGreaterThan 24.95

그림 32에서 알 수 있듯이 위의 명령은 $24.95보다 큰 제품에 대한 정보를 표시합니다 UnitPrice .

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

요약

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

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

행복한 프로그래밍!

추가 정보

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

• User-Defined 함수의 장점 및 단점
• 관리 코드에서 SQL Server 2005 개체 만들기
• 방법: CLR SQL Server 저장 프로시저 만들기 및 실행
• 방법: CLR SQL Server User-Defined 함수 만들기 및 실행
• 방법: 스크립트를 Test.sql 편집하여 SQL 개체 실행
• 사용자 정의 함수 소개
• 관리 코드 및 SQL Server 2005(비디오)
• Transact-SQL 참조
• 연습: 관리 코드에서 저장 프로시저 만들기

저자 정보

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.

이전다음