관리 코드를 사용하여 저장 프로시저 및 사용자 정의 함수 만들기(C#)
작성자 : Scott Mitchell
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 Scripts
Test.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
클래스 파일 내에서 메서드로 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 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
되는 저장 프로시저를 실행할 준비가 된 것입니다. 쿼리 창에서 명령을 exec
GetDiscontinuedProducts
입력하고 실행합니다. 저장 프로시저를 호출하면 메서드의 해당 관리 코드가 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
(전체 크기 이미지를 보려면 클릭)
관리되는 저장 프로시저에 대해 및 GetProductsWithPriceLessThan
ProductsTableAdapter
라는 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.aspx
AdvancedDAL
페이지를 열고 도구 상자에서 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
의 값을 반환합니다. 에 를 곱하여 인벤토리의 예상 값을 계산합니다 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가 데이터베이스에 추가되면 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
경우 , SqlInt16
UnitsInStock
및 SqlBoolean
의 경우 Discontinued
입니다. 이러한 데이터 형식은 테이블에 UnitPrice
정의된 형식을 Products
반영합니다. 열은 형식, UnitsInStock
형식money
의 열 및 Discontinued
형식smallint
bit
의 열입니다.
코드는 값 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를 사용하고 관리되는 데이터베이스 개체를 사용하려는 경우 수동으로 만들고 배포해야 합니다. 여기에는 다음 네 단계가 포함됩니다.
- 관리되는 데이터베이스 개체에 대한 소스 코드가 포함된 파일을 만듭니다.
- 개체를 어셈블리로 컴파일합니다.
- 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
읽습니다 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.
피드백
https://aka.ms/ContentUserFeedback
출시 예정: 2024년 내내 콘텐츠에 대한 피드백 메커니즘으로 GitHub 문제를 단계적으로 폐지하고 이를 새로운 피드백 시스템으로 바꿀 예정입니다. 자세한 내용은 다음을 참조하세요.다음에 대한 사용자 의견 제출 및 보기