|
DB 제작할때 쿼리 문은 항상 작성하는 습관을 들이자.
이 코드를 통해서 여러가지 테스트할때 바로바로 적용해볼 수 있도록 하기 위함이다.
[순서]
1. UDL 파일을 만들어서 DB 커넥션 문을 저장해 놓는다.
2. 쿼리 테이블을 제작
3. 필요하면 로그인 계정 추가
4. 샘플 쿼리 문을 만든다. 아래와 같이
use exam
go
-- 고객 테이블
Create Table dbo.Customers
(
CustomerID int Identity(1,1) Primary Key Not NULL, --일련번호
Name Varchar(25) Null, --이름
Email Varchar(100) Null, --이메일
RegistDate DateTime Default(GetDate()) --등록일
)
Go
--입력 예시
Insert Customers Values('홍길동', 'h@h.com', GetDate())
--출력 예시
Select * From Customers Order By CustomerID Desc
--세부출력 예시
Select * From Customers Where Name = '홍길동'
--수정 예시
Begin Tran
Update Customers
Set Name = '백두산', Email = 'b@b.com'
Where Name = '홍길동' And Email = 'h@h.com'
--RollBack Tran
Commit Tran
--삭제 예시
Delete Customers Where Name = '백두산'
--검색 예시
Select * From Customers
Where Name Like '%백%' Or Email Like '%b%'
수정 및 삭제시에는 트랜을 꼭 해주는것도 좋을듯 하다.
ComboBox의 내용을 토대로 해서 "업데이트" 버튼을 누르면 DataGridView에 내용을 업데이트 하는 샘플 입니다.
원래 콤보 박스의 내용을 xml이나 Collection의 객체를 사용해서 연동할려고 했지만.
역시 -.- 안되더군요. (잘몰라서, 아시는분 꼭 연락 주세요 ^^)
그래서 아래와 같이 아이템은 입력 했습니다.
enum enItemType { IT_C, IT_B, IT_R, IT_S };
private string GetItemType(enItemType it)
{
switch (it)
{
case enItemType.IT_C:
return "C";
case enItemType.IT_B:
return "B";
case enItemType.IT_R:
return "R";
case enItemType.IT_S:
return "S";
}
return "";
}
private void button1_Click(object sender, EventArgs e)
{
enItemType curit = (enItemType)comboBox1.SelectedIndex;
string selvalue = GetItemType(curit);
this.itemTableAdapter.FilterItemTypeToFill(this.testDataSet.item, selvalue);
}
string selvalue = GetItemType(curit);
이 코드를 통해서 selvalue에 나오는 값은 해당 테이블의 값이 생성되게 된다.
이걸 사용해서... 쿼리 문에 입력해 줍니다.
this.itemTableAdapter.FilterItemTypeToFill(this.testDataSet.item, selvalue);
[참고]
[DB 테이블 내용]
NoteDB
> 테이블
>note(dbo.note)
nid : int : No null
name : varchar(20) : null
content : text : null
email : varchar(32) : null
date : datetime : null
[저장 프로시저 내용]
USE [NoteDB]
GO
/****** 개체: StoredProcedure [dbo].[sp_AddNote] 스크립트 날짜: 09/27/2007 01:57:39 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER Procedure [dbo].[sp_AddNote]
(
@Name varchar(50),
@Content varchar(400),
@Email varchar(50)
)
AS
INSERT INTO Note
(
name, content, email, date
)
VALUES
(
@Name, @Content, @Email, getdate()
)
[코드 내용]
//커텍션 스트링 지정
string source = @"Data Source=GSI;Initial Catalog=NoteDB;Persist Security Info=True;User ID=sa;Password=****";
//커넥션 개체 생성
SqlConnection conn;
conn = new SqlConnection(source);
//커넥션 오픈
conn.Open();
//저장 프로시저 오픈 및 값 저장
SqlCommand cmd = new SqlCommand("sp_AddNote", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param1 = new SqlParameter("@Name", SqlDbType.VarChar, 50);
param1.Value = name; //컨트롤로 부터 이름 저장
cmd.Parameters.Add(param1);
SqlParameter param2 = new SqlParameter("@Content", SqlDbType.VarChar, 400);
param2.Value = content; //컨트롤로 부터 컨텐트 저장
cmd.Parameters.Add(param2);
SqlParameter param3 = new SqlParameter("@Email", SqlDbType.VarChar, 50);
param3.Value = email; //컨트롤로 부터 이메일 저장
cmd.Parameters.Add(param3);
//
cmd.ExecuteNonQuery();
//기타 내용 추가 하기
//커넥션 닫기
conn.Close();
DB 테이블 내용
NoteDB
> 테이블
>note(dbo.note)
nid : int : No null
name : varchar(20) : null
content : text : null
email : varchar(32) : null
date : datetime : null
위의 내용은 SQL DB 내용을 요약한 것입니다.
한줄 짜리 메모용으로 사용되어질 테이블 내역입니다.
위의 내용을 SqlDataReader를 사용해서 접근하고 데이터를 가져 오는 방법을 기술하였습니다.
//커넥션 스트링을 지정합니다.
string source = @"Data Source=GSI;Initial Catalog=NoteDB;Persist Security Info=True;User ID=sa;Password=****";
//커텍션 스트링을 사용해서 SqlConnection을 생성합니다.
SqlConnection conn;
conn = new SqlConnection(source);
//커텍션을 오픈합니다.
conn.Open();
//SqlDataReader 용 객체를 가져 옵니다.
//이때 쿼리문을 커넥션에 적용해서 값을 가져 오게 됩니다.
SqlDataReader reader = conn.ExecuteReader("SELECT * FROM note");
//아래의 코드는 웹에서 Response를 사용해서 값을 가져 오는 방법입니다.
Response.Write("<td>" + reader["nid"] + "</td>");
Response.Write("<td>" + reader["name"] + "</td>");
Response.Write("<td>" + reader["content"] + "</td>");
Response.Write("<td>" + reader["email"] + "</td>");
Response.Write("<td>" + reader["date"] + "</td>");
>>위와 같이 reader["컬럼이름"]를 사용해서 값을 가져 올 수 있습니다.
//커넥션을 닫습니다.
conn.Close();
XML 템플릿 사용
XML에서 템플릿(template)은 데이터를 처리하기 위해 질의문을 미리 생성해 놓은 문서를 말한다. 사용자가 직접 서버에 URL 질의를 입력해서 데이터를 검색하는 것보다 XML 템플릿을 이요하면 좀더 안정적이고, 제어 가능한 환경을 만들 수 있다. 템플릿은 데이터베이스에서 데이터를 검색하기 위한 질의를 하나 이상 포함하고 있고, 그 결과는 호출한 브라우저나 클라이언트 애플리케이션에서 보낸다. 템플릿은 XML-SQL 네임스페이스를 기반으로 하는 XML문서다.
HTTP와 템플릿을 사용하기 위해서 가상 이름에 템플릿 파일을 저장하고 템플릿에 접근하기 위해 URL을 통해 템플릿 파일에 접근할 수 있다. 예를 들면 products.xml이란 파일명으로 저장한 템플릿 파일이 있다면 다음과 같은 URL을 통해 접근할 수 있다.
템플릿을 사용하려면 가상 이름에 템플릿 질의 허용을 체크해 놓아야 한다. IIS에 SQL XML 구성 지원 MMC 스냅인 툴에서 설정 탭의 템플릿 질의 허용을 체크하는 것으로 해당하는 가상 디렉토리에 템플릿 질의를 허용할 수 있다.
템플릿 생성과 사용
기본적으로 XML 템플릿의 구성은 XML 문서와 동일한 구조다. 다만 SQL문을 포함하고 있다는 점이 다르다. SQL 문을 사용하기 위해서 최상위 요소에 'sql' 네임스페이스를 지정하고 <query> 요소를 사용한다. 다음은 노스윈드(northwind) 데이터베이스로부터 제품목록을 보여 주는 템플릿을 생성한 예다.
템플릿 가상 이름(templates)을 지정한 곳(c:\sqlroots\northwinddata\)에 이 제폼 목록 템플릿을 categories.xml로 저장한다. 이 코드에서 템플릿은 ADO를 통한 질의 템플릿을 사용하는 것과 같은 형식을 가진다. 템플릿에 접근할 때 <query> 태그는 XML 데이터를 처리하며, 수행한 질의 결과는 호출한 곳으로 반환한다. 다음은 XML 템플릿을 IIS 웹 서버를 통해 실행하게 된다.
이 코드를 인터넷 웹 브라우저에서 실행해 보길 바란다.
원하는 결과가 나왔다면 다행이지만..
HTTP:500 내부 서버 오류 라는 말이 나오게 되면 xml 파일의 이상이다.
템플릿 확장
템플릿을 확장하는 방법으로 파라미터 추가가 있다. 파라미터는 사용자가 하나 이상의 다앙햔 결과를 볼 수 있게 한다. 예를 들면, 템플릿을 파라미터를 받을 수 있게 생성하고, 사용자가 CategoryID 파라미터를 넘겨 주면 제품 목록 중 특정 제품의 상세 정보를 나타나게 작성할 수 있다. 파라미터는 템플릿 헤더에 위치하고, <header> 태그를 사용해서 정의한다. 각 파라미터는 <param> 태그를 사용해 정의할 수 있다. 다름은 파라미터를 사용해서 카테고리 번호를 넘겨주고, 해당 카테고리의 제품 목록을 출력하는 XML 템플릿이다.
products.xml 템플릿 문서에서는 categoryid 기본값으로 1을 지정한다 .파라미터를 인식하려면 <param> 태그를 name 속성으로 사용해야 한다. 이 이름을 @ 기호와 함께 사용해서 SQL 질의에서 사용할 수 있다. 파라미터화한 템플릿으로 데이터에 접근하기 위해 URL 질의 스트링에 파라미터를 지정한다. 다음은 카테고리 번호 2를 파라미터로 전달하고, 템플릿을 실행하는 코드이다.
categoryid를 2로 입력된 하면을 볼 수 있다.
저번장까지 IIS를 설치하고 가상 디렉토리까지 마무리 했습니다.
이제 HTTP 를 사용해서 어떻게 사용하는 지에 대해서 간단하게 짚고 넘어 가겠습니다.
URL 질의 사용
가상 디렉토리가 제대로 설정되어 있는지 확인하는 간단한 방법은 URL 질의를 가능하게 한 후, XML 가능 브라우저를 사용해서 질의문을 실행해 보는 것입니다. URL에서 데이터를 추출할 경우 질의와 다른 설정이 적절한지 확인하기 위해 파라미터를 이용할 수 있다. 이 파라미터는 URL 질의 문자열에 표준 형식을 사용해서 보낼 수 있다. URL과 파라미터를 분리하려면 ? 기호를 사용하고, 여러개의 파라미터를 보낼 때는 각 파라미터를 & 기호를 이용해서 분리한다. 다음 URL 포맷은 2개의 파라미터를 이요해서 URL로 데이터에 접근할 때 사용한 예입니다.
URL 질의를 이용해서 FOR XML 질의를 포함하는 SQL 파라미터를 지정할 수 XML 문서를 추출할 수 있다. FOR XML 질의의 결과가 잘 구성한 XML 문서를 반환하기 보다 XML 데이터를 반환하기 때문에 최상위 요소를 URL에 반드시 지정해야 한다. 그래서 최상위 요소를 정확히 지정하거나 최상위 요소 파라미터를 지정해 처리할 수 있다.
다음 예제는 Northwind 데이터베이스의 Products 테이블로부터 데이터를 검색하는 것으로 사용하는 URL 질의다. 최상위 요소는 SQL 파라미터의 일부로 포함한다. 최상위 요소는 <catalog>로 임의의 요소를 정의하였다.
위의 내용을 인터넷 브라우저에서 확인 가능하다. 한번씩 해보길 바란다.
URL 질의를 하나하나 SQL문으로 작성하는 것은 상당히 번거로운 작업이다. 이러한 번거로움은 T-SQL의 EXECUTE 구문이나 ODBC를 호출해 저장 프로시저를 수행하면 해결할 수 있다. 효율적으로 URL 질의를 사용할 수 있을 뿐만 아니라 보안 측면에서도 훨씬 유리하다. 실제로 UTL질의 문을 한번 살펴 보자.
결과는 직접 확인 바랍니다.
테스트 환경 : SQL 2000 입니다.
IIS 가상 디렉토리를 통한 데이터베이스 처리
SQL 서버 2000의 HTTP 처리를 위한 툴은 MMC에 스냅인 된 IIS에 SQL XML 구성 툴을 사용한다. SQL XML 지원 구성을 실행하려면 [시작]>[프로그램]>[Microsoft Sql server]>[IIS에 SQL XML 지원 구성]을 차례로 선택하여 실행한다.
SQL 서버 2000에서 제공하는 새로운 기능 중 HTTP를 이용해서 XML 데이터에 접근하는 방법을 알아본다. HTTP를 이용해서 인트라넷이나 인터넷 기반 애플리케이션을 구축할 수 있다. 이 방법은 Micorsoft IIS의 가상 디렉토리를 구축하는 방법으로 XML 데이터를 처리하거나 다른 애플리케이션에 쉽게 적용할 수 있다. HTTP로 데이터를 처리하는 기능으로 ASP 애플리케이션보다 적은 코드를 이용하면서도 쉽고 빠르게 데이터 중심적인 웹 사이트를 구축할 수 있다.
XSL 스타일시트를 XML 데이터에 적용해서 HTML 문서로 변형한 후, 브라우저 기반 클라이언트로 전송하거나 WML과 같은 다른 문서 형태로 변환해서 WAP 형식으로 휴대폰, PDA 등에 사용이 가능해 진다.
SQL 서버의 HTTP 처리 구조
SQL 서버는 SQLISAPI(SQL 서버에서 제공하는 ISAPI 애플리케이션)FMF DLDYDGOTJ ㅗㅅ세FMF CJFLGKSEK. IIS에 가상 디렉토리 루트를 생성한 후, HTTP URL을 이용하면 SQL 서버의 데이터베이스에 접근할 수 있다. SQLISAPI 애플리케이션은 SQL 서버 2000의 OLE-DB 공급자를 이용해서 클라이언트로 XML이나 HTML 데이터를 보낸다.
SQL 서버를 IIS 웹 서버와 동일한 시스템에 구축할 수 있다. SQL 서버에는 데이터를 테이블에 저장할 수 있고, 클라이언트로부터 IIS 웹 서버에 의해 질의문을 전송받고, 전송받은 질의문을 실행해서 질의한 결과의 데이터를 XML 문서의 형태로 IIS 웹 서버를 통해 클라이언트에게 전송한다. 클라이언트 애플리케이션은 다음과 같이 네 가지의 방법 중 하나로 데이터를 요청할 수 있다.
DataRelation의 주요한 기능 중 하나는 DataSet 내의 한 DataTable에서 다른 DataTable로 이동을 허용한다는 것입니다. 따라서, 연관된 DataTable의 DataRow가 하나 주어지면 연관된 다른 DataTable에 있는 모든 연관된 DataRow 개체를 검색할 수 있습니다. 예를 들어, 고객 테이블과 주문 테이블 사이에 DataRelation을 만든 다음 DataRow.GetChildRows를 사용하여 특정 고객 행에 대한 모든 주문 행을 검색할 수 있습니다.
다음 코드 예제에서는 DataSet의 Customers 테이블과 Orders 테이블 사이에 DataRelation을 만들고 각 고객에 대한 모든 주문을 반환합니다.
[C#]
DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
foreach (DataRow custRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine(custRow["CustomerID"]);
foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
Console.WriteLine(orderRow["OrderID"]);
}
다음 예제에서는 이전 예제를 기반으로 네 개의 테이블을 모두 연관시키고 이들 관계를 탐색합니다. 이전 예제에서와 마찬가지로 CustomerID는 Customers 테이블을 Orders 테이블에 연관시킵니다. Customers 테이블의 각 고객에 대해 Orders 테이블의 모든 자식 행이 결정되어 특정 고객의 주문 개수와 이들의 OrderID 값이 반환됩니다.
확장된 예제에서는 OrderDetails 및 Products 테이블의 값도 반환됩니다. Orders 테이블은 각 고객 주문에 대해 주문된 제품과 수량을 결정하기 위해 OrderID를 사용하여 OrderDetails 테이블에 연관됩니다. OrderDetails 테이블에는 주문된 제품의 ProductID만 있으므로 OrderDetails는 ProductName을 반환하기 위해 ProductID를 사용하여 Products에 연관됩니다. 이 관계에서 Products 테이블은 부모 테이블이고 Order Details 테이블은 자식 테이블입니다. 그 결과, OrderDetails 테이블을 반복하여 검색할 때는 GetParentRow가 호출되어 연관된 ProductName 값이 검색됩니다.
Customers 및 Orders 테이블에 대해 DataRelation을 만들면 createConstraints 플래그(기본값은 true)의 값이 지정되지 않습니다. 이런 경우에는 Orders 테이블의 모든 행이 부모 테이블인 Customers에 존재하는 CustomerID 값을 가지고 있다고 가정합니다. Customers 테이블에 없는 CustomerID가 Orders 테이블에 있으면 ForeignKeyConstraint에 의해 예외가 throw됩니다.
부모 열에 포함되어 있지 않은 값이 자식 열에 있는 경우 DataRelation을 추가할 때 createConstraints 플래그를 false로 설정합니다. 예제에서는 Orders 테이블과 OrderDetails 테이블 사이의 DataRelation에 대해 createConstraints 플래그가 false로 설정됩니다. 이렇게 되면 응용 프로그램에서는 런타임에 예외를 발생시키지 않고 OrderDetails 테이블의 모든 레코드와 Orders 테이블 레코드의 일부분만 반환할 수 있습니다. 확장된 예제는 다음과 같은 형식의 출력을 생성합니다.
Customer ID: NORTS
Order ID: 10517
Order Date: 4/24/1997 12:00:00 AM
Product: Filo Mix
Quantity: 6
Product: Raclette Courdavault
Quantity: 4
Product: Outback Lager
Quantity: 6
Order ID: 11057
Order Date: 4/29/1998 12:00:00 AM
Product: Outback Lager
Quantity: 3
다음 코드 예제는 확장된 예제로서 OrderDetails 및 Products 테이블의 값과 반환되고 있는 Orders 테이블 레코드의 일부분만 반환합니다.
[C#]
DataRelation custOrderRel = custDS.Relations.Add("CustOrders",
custDS.Tables["Customers"].Columns["CustomerID"],
custDS.Tables["Orders"].Columns["CustomerID"]);
DataRelation orderDetailRel = custDS.Relations.Add("OrderDetail",
custDS.Tables["Orders"].Columns["OrderID"],
custDS.Tables["OrderDetails"].Columns["OrderID"], false);
DataRelation orderProductRel = custDS.Relations.Add("OrderProducts",
custDS.Tables["Products"].Columns["ProductID"],
custDS.Tables["OrderDetails"].Columns["ProductID"]);
foreach (DataRow custRow in custDS.Tables["Customers"].Rows)
{
Console.WriteLine("Customer ID: " + custRow["CustomerID"]);
foreach (DataRow orderRow in custRow.GetChildRows(custOrderRel))
{
Console.WriteLine(" Order ID: " + orderRow["OrderID"]);
Console.WriteLine("\tOrder Date: " + orderRow["OrderDate"]);
foreach (DataRow detailRow in orderRow.GetChildRows(orderDetailRel))
{
Console.WriteLine("\t Product: " + detailRow.GetParentRow(orderProductRel)["ProductName"]);
Console.WriteLine("\t Quantity: " + detailRow["Quantity"]);
}
}
}