관리 코드를 사용하여 저장 프로시저 및 사용자 정의 함수 만들기(C#)
로 스콧 미첼
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
는 레코드 집합을 한 번에 하나씩 반복할 수 있도록 허용합니다. 문자열 조작 함수(예: LEFT
, CHARINDEX
및 PATINDEX
스칼라 데이터 사용) 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 인증을 선택한 다음 연결을 클릭합니다.
그림 1: 적절한 데이터베이스 인스턴스에 연결
연결되면 개체 탐색기 창에는 해당 데이터베이스, 보안 정보, 관리 옵션 등을 포함하여 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 대한 정보가 나열됩니다.
폴더에 있는 DataFiles
Northwind 데이터베이스(또는 이동한 위치)를 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 연결해야 합니다. 데이터베이스 폴더를 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 연결 옵션을 선택합니다. 그러면 데이터베이스 연결 대화 상자가 나타납니다. 추가 단추를 클릭하고 적절한 NORTHWND.MDF
파일로 드릴다운한 다음 확인을 클릭합니다. 이 시점에서 화면은 그림 2와 유사하게 표시됩니다.
그림 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로 바꿉니다.
그림 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
배치했습니다.
그림 4: 새 SQL Server 프로젝트 만들기(전체 크기 이미지를 보려면 클릭)
새 프로젝트 대화 상자에서 확인 단추를 클릭하여 솔루션 및 SQL Server 프로젝트를 만듭니다.
SQL Server 프로젝트는 특정 데이터베이스에 연결됩니다. 따라서 새 SQL Server 프로젝트를 만든 후 즉시 이 정보를 지정하라는 메시지가 표시됩니다. 그림 5에서는 1단계에서 SQL Server 2005 Express Edition 데이터베이스 인스턴스에 등록한 Northwind 데이터베이스를 가리키도록 채워진 새 데이터베이스 참조 대화 상자를 보여 줍니다.
그림 5: SQL Server 프로젝트를 Northwind 데이터베이스와 연결
이 프로젝트 내에서 만들 관리되는 저장 프로시저 및 UDF를 디버그하려면 연결에 대한 SQL/CLR 디버깅 지원을 사용하도록 설정해야 합니다. SQL Server 프로젝트를 새 데이터베이스와 연결할 때마다(그림 5에서와 같이) Visual Studio에서 연결에서 SQL/CLR 디버깅을 사용하도록 설정할지 묻습니다(그림 6 참조). 예를 클릭합니다.
그림 6: SQL/CLR 디버깅 사용
이 시점에서 새 SQL Server 프로젝트가 솔루션에 추가되었습니다. 여기에는 프로젝트에서 만든 관리되는 데이터베이스 개체를 디버깅하는 데 사용되는 이름이 지정된 Test.sql
파일이 포함된 Test Scripts
폴더가 포함되어 있습니다. 12단계에서 디버깅을 살펴보겠습니다.
이제 새 관리 저장 프로시저 및 UDF를 이 프로젝트에 추가할 수 있지만, 먼저 솔루션에 기존 웹 애플리케이션을 포함할 수 있습니다. 파일 메뉴에서 추가 옵션을 선택하고 기존 웹 사이트를 선택합니다. 적절한 웹 사이트 폴더로 이동하고 확인을 클릭합니다. 그림 7에서 보여 주듯이 웹 사이트와 ManagedDatabaseConstructs
SQL Server 프로젝트라는 두 개의 프로젝트를 포함하도록 솔루션을 업데이트합니다.
그림 7: 솔루션 탐색기 이제 두 개의 프로젝트가 포함됩니다.
현재 폴더 NORTHWNDConnectionString
의 Web.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
의 이름을 지정합니다.
그림 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
도구 모음에서 실행 아이콘을 클릭하여 위의 쿼리를 실행합니다.
그림 9: Northwind 데이터베이스의 호환성 수준 업데이트(전체 크기 이미지를 보려면 클릭)
호환성 수준을 업데이트한 후 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 사용 설정 구성 값을 1로 업데이트했지만 실행 값은 여전히 0으로 설정되어 있음을 알 수 있습니다. 이 구성 변경 내용이 영향을 미치려면 명령을 실행 RECONFIGURE
해야 합니다. 그러면 실행 값이 현재 구성 값으로 설정됩니다. 쿼리 창에 입력 RECONFIGURE
하고 도구 모음에서 실행 아이콘을 클릭하기만 하면 됩니다. 지금 실행 exec sp_configure
하면 clr 사용 설정의 구성 및 실행 값에 대해 1의 값이 표시됩니다.
clr 사용 구성이 완료되면 관리 GetDiscontinuedProducts
되는 저장 프로시저를 실행할 준비가 된 것입니다. 쿼리 창에서 명령을 exec
GetDiscontinuedProducts
입력하고 실행합니다. 저장 프로시저를 호출하면 메서드의 해당 관리 코드가 GetDiscontinuedProducts
실행됩니다. 이 코드는 중단된 모든 제품을 반환하는 쿼리를 실행하고 SELECT
이 인스턴스의 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단계: 데이터 액세스 계층에서 관리되는 저장 프로시저 호출
이 시점에서 프로젝트에 관리되는 저장 프로시저를 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
드롭다운 목록에서 관리되는 저장 프로시저를 선택하고 다음을 누릅니다.
그림 16: 관리 저장 프로시저 선택 GetDiscontinuedProducts
(전체 크기 이미지를 보려면 클릭)
그런 다음 저장 프로시저에서 행, 단일 값 또는 아무것도 반환할지 여부를 지정하라는 메시지가 표시됩니다. GetDiscontinuedProducts
중단된 제품 행 집합을 반환하므로 첫 번째 옵션(테이블 형식 데이터)을 선택하고 다음을 클릭합니다.
그림 17: 테이블 형식 데이터 옵션 선택(전체 크기 이미지를 보려면 클릭)
마지막 마법사 화면에서는 사용된 데이터 액세스 패턴과 결과 메서드의 이름을 지정할 수 있습니다. 두 확인란을 모두 선택한 상태로 두고 메서드 FillByDiscontinued
의 이름을 지정하고 GetDiscontinuedProducts
. 마침을 클릭하여 마법사를 완료합니다.
그림 18: 메서드 FillByDiscontinued
이름 지정 및 GetDiscontinuedProducts
(전체 크기 이미지를 보려면 클릭)
관리되는 저장 프로시저에 대해 명명 FillByPriceLessThan
된 GetProductsWithPriceLessThan
메서드를 ProductsTableAdapter
만들려면 다음 GetProductsWithPriceLessThan
단계를 반복합니다.
그림 19에서는 관리되는 저장 프로시저에 메서드를 추가한 ProductsTableAdapter
후 데이터 세트 디자이너의 GetDiscontinuedProducts
GetProductsWithPriceLessThan
스크린샷을 보여 줍니다.
그림 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
구성합니다.
그림 20: 클래스를 사용하도록 ProductsBLLWithSprocs
ObjectDataSource 구성(전체 크기 이미지를 보려면 클릭)
그림 21: SELECT 탭의 드롭다운 목록에서 메서드 선택 GetDiscontinuedProducts
(전체 크기 이미지를 보려면 클릭)
이 표는 제품 정보를 표시하는 데만 사용되므로 UPDATE, INSERT 및 DELETE 탭의 드롭다운 목록을 (없음)으로 설정한 다음 마침을 클릭합니다.
마법사를 완료하면 Visual Studio에서 각 데이터 필드에 대한 BoundField 또는 CheckBoxField를 ProductsDataTable
자동으로 추가합니다. GridView 및 Discontinued
ObjectDataSource의 선언적 태그가 다음과 비슷해야 하는 시점을 제외하고 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에서 클래스 메서드 GetDiscontinuedProducts
를 ProductsBLLWithSprocs
호출합니다. 7단계에서 살본 것처럼 이 메서드는 저장 프로시저를 호출하는 DAL ProductsDataTable
의 GetDiscontinuedProducts
클래스 메서드를 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
입력 매개 변수(, UnitsInStock
및 Discontinued
값)를 사용하여 형식 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
대한 , 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
목록(전체 크기 이미지를 보려면 클릭)
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
의 이름을 지정합니다.
그림 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
반환하며 입력 매개 변수를 허용하지 않습니다. 세 개의 입력 매개 변수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 메서드의 입력 매개 변수는 필드, for UnitsInStock
UnitPrice
및 SqlBoolean
Discontinued
SqlInt16
for의 해당 SQL 형식 SqlMoney
입니다. 이러한 데이터 형식은 테이블에 정의된 Products
형식을 반영합니다. 열은 형식, UnitsInStock
형식 money
열 및 Discontinued
형식smallint
bit
의 열 UnitPrice
입니다.
코드는 값 0이 SqlMoney
할당된 명명 inventoryValue
된 인스턴스를 만들어 시작합니다. 테이블은 Products
열의 UnitsInStock
데이터베이스 NULL
값을 UnitsInPrice
허용합니다. 따라서 먼저 이러한 값에 개체의IsNull
속성을 통해 수행하는 s가 SqlMoney
포함되어 NULL
있는지 확인해야 합니다. 둘 다 UnitPrice
비 UnitsInStock
값을NULL
포함하는 경우 두 값의 곱으로 계산 inventoryValue
합니다. 그런 다음 true이면 Discontinued
값을 반으로 줄입니다.
참고 항목
개체는 SqlMoney
두 SqlMoney
인스턴스를 함께 곱할 수 있습니다. 인스턴스를 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 참조). 이 설정은 서버 탐색기 창에서 데이터베이스를 마우스 오른쪽 단추로 클릭하여 수정할 수 있습니다.
그림 26: 데이터베이스에서 SQL/CLR 디버깅을 허용하는지 확인
관리되는 저장 프로시저를 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
단계별로 실행하고 입력 매개 변수의 값을 검사하는 등의 작업을 수행할 수 있습니다.
그림 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단계가 포함됩니다.
- 관리되는 데이터베이스 개체에 대한 소스 코드가 포함된 파일을 만듭니다.
- 개체를 어셈블리로 컴파일합니다.
- SQL Server 2005 데이터베이스에 어셈블리 등록 및
- 어셈블리의 적절한 메서드를 가리키는 데이터베이스 개체를 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 < @MaxPrice
을 WHERE
읽습니다. 여기서는 다음 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
그림 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
(클래스 StoredProcedures
에 있는 어셈블리 ManuallyCreatedDBObjects
에 있는)와 연결됩니다.
위의 스크립트를 실행한 후 개체 탐색기 저장 프로시저 폴더를 새로 고칩니다. 옆에 잠금 아이콘이 있는 새 저장 프로시저 항목 GetProductsWithPriceGreaterThan
이 표시됩니다. 이 저장 프로시저를 테스트하려면 쿼리 창에서 다음 스크립트를 입력하고 실행합니다.
exec GetProductsWithPriceGreaterThan 24.95
그림 32와 같이 위의 명령은 $24.95보다 큰 제품에 대한 정보를 표시합니다 UnitPrice
.
그림 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단계에서 보았듯이 만들기, 컴파일 및 배포 단계를 수동으로 수행해야 합니다.
행복한 프로그래밍!
추가 정보
이 자습서에서 설명하는 항목에 대한 자세한 내용은 다음 리소스를 참조하세요.
- 사용자 정의 함수의 이점 및 단점
- 관리 코드에서 SQL Server 2005 개체 만들기
- 방법: CLR SQL Server 저장 프로시저 만들기 및 실행
- 방법: CLR SQL Server 사용자 정의 함수 만들기 및 실행
- 방법: SQL 개체를
Test.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.