Stored Procedure를 이용한 DB Handling

 

저장프로시저(SP)를 이용해서 DB 핸들링 하는 기법임

 

특징 : 빠름

대량의 데이터를 가져올 때 내부적으로 돌리다 보면 복잡한데 그러한 것들을 sql 로 작성해 놓고 매개변수로 가져옴

 

sp를 이용하기 위해서는 SqlCommand 클래스의 주요속성인 CommandType 을 이용해야 함

 

속성	기능
CommandText	데이터소스에 실행할 SQL문이나 저장 프로시져를 가져오거나 설정
CommandType	CommandText 속성이 해석될 방법 제시
Connection	SqlCommand 객체에서 SqlConnection 을 가져올 때 사용
Parmaters	SqlParmatersCollection 을 가져옴
Transaction	SqlCommand 가 실행하는 트랜잭션을 가져오거나 설정

 

- SP를 이용하여 DB를 핸들링 하는 과정

 

1. Connection 은 지금까지 배운 것과 동일

 

2. Command 에서 아래와 같이 설정해 줌

// SP 를 명시한 Command 객체 생성

// Ten Most Expensive Products 는 Northwind DB 에 있는 SP

SqlCommand comm. = new SqlCommand ("Ten Most Expensive Products",Conn);

 

// Command 객체한테 SP 를 사용할 것이라는 것을 알려줌

Comm..CommandType = CommandType.StoredProcedure;

- 매개변수를 넘기는 방법

 

1. SP 를 명시한 Command 객체생성

SqlCommand comm. = new SqlCommand("CustOrderHist, conn);

 

2. Command 객체에게 SP 를 사용할 것이라고 말함

comm..CommandType = CommandType.StoredProcrdure;

 

3. Parameters 속성을 이용해서 매개변수 넘기기

comm..Parameters.Add(new Sql Parameter("@CustomerID",custId));

 

<예제>

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient; // <1>

 

namespace usingSpEx

{

class Program

{

static void Main(string[] args)

{

try

{ // <2> 연결문자열

string strConn = "server=.\\SQLEXPRESS;database=Northwind;integrated Security=true";

 

// <3> SqlConnection 객체생성및연결

SqlConnection Conn = new SqlConnection(strConn);

Conn.Open();

 

// <SP.1> 를명시한Command 객체를생성

SqlCommand comm = new SqlCommand("CustOrderHist",Conn);

 

// <SP.2> SP를사용할거라고Command 객체에게알림

comm.CommandType = CommandType.StoredProcedure;

 

// <SP.3> Command 객체에게SP에사용할파라미터를넘김

string custid = "FURIB";

comm.Parameters.Add(new SqlParameter("@CustomerID",custid));

 

// <SP.4> 결과를가져오기

SqlDataReader sdr = null;

sdr = comm.ExecuteReader();

 

// <SP.5> 출력하기

while (sdr.Read())

{ // 40 바이트를잡아서왼쪽(음수)에서부터출력해줌

Console.WriteLine("Product : {0,-40} Total : {1,2}", sdr["ProductName"], sdr["Total"]);

}

// <SP.6> 닫기

sdr.Close();

Conn.Close();

}

catch(SqlException se)

{

Console.WriteLine(se.ToString());

}

Console.ReadLine();

}

}

}

 

 

 

 

 

 

<실습문제>

 

mytest 데이터베이스의 testoutput 에 insertdata SP를 이용하여 데이터를 입력하세요

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient; // <1>

 

namespace usingSpEx2

{

class Program

{

static void Main(string[] args)

{

try

{ // <2> 연결문자열

string strConn = "server=.\\SQLEXPRESS;database=mytest;user id=sa;password=dba007$";

 

// <3> SqlConnection 객체생성및연결

SqlConnection Conn = new SqlConnection(strConn);

Conn.Open();

 

// <SP.1> 를명시한Command 객체를생성

SqlCommand spComm = new SqlCommand("insertdata", Conn);

 

// <SP.2> SP를사용할거라고Command 객체에게알림

spComm.CommandType = CommandType.StoredProcedure;

 

// <SP.3> Command 객체에게SP에사용할파라미터를넘김

int ii = 1;

spComm.Parameters.Add(new SqlParameter("@i", ii));

 

// 결과가져오기(메모리에로드)

SqlDataReader sdr = spComm.ExecuteReader();

 

// 리더닫기

sdr.Close();

 

// 결과를가져오기

SqlCommand comm = new SqlCommand("select * from testoutput", Conn);

SqlDataReader sdr2 = comm.ExecuteReader();

while (sdr2.Read())

{

int i = 0;

for (i = 0; i < sdr2.FieldCount; i++)

Console.WriteLine(sdr2.GetString(i));

}

 

// 리더닫기

sdr2.Close();

Conn.Close();

}

catch (SqlException se)

{

Console.WriteLine(se.ToString());

}

Console.ReadLine();

}

}

}

 

 

 

- 매개변수가 없는 프로시져 생성하여 console 창에 출력해본다.

 

CREATE PROCEDURE showtable

AS

SELECT * FROM testoutput

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient; // <1>

 

namespace usingSpEx2

{

class Program

{

static void Main(string[] args)

{

try

{ // <2> 연결문자열

string strConn = "server=.\\SQLEXPRESS;database=mytest;user id=sa;password=dba007$";

 

// <3> SqlConnection 객체생성및연결

SqlConnection Conn = new SqlConnection(strConn);

Conn.Open();

 

//<SP.1> SP를명시한Command 객체를생성

SqlCommand comm = new SqlCommand("showtable", Conn);

 

// <SP.2> SP를사용할거라고Command 객체한테알림

comm.CommandType = CommandType.StoredProcedure;

 

//<SP.3> 결과를가져오기

SqlDataReader sdr = null;

sdr = comm.ExecuteReader();

 

// <SP.4> 출력하기

while (sdr.Read())

{ // 40 바이트를잡아서왼쪽(음수)에서부터출력해줌

Console.WriteLine("sNO : {0} sNAME : {1} Tel : {2} ", sdr["sNO"], sdr["sNAME"], sdr["Tel"]);

}

 

// <SP.5> 리더닫기

sdr.Close();

Conn.Close();

}

catch (SqlException se)

{

Console.WriteLine(se.ToString());

}

Console.ReadLine();

}

}

}

 

 

 

 

 

<실습예제>

 

mytest 데이터베이스에 아래와 같은 테이블을 생성

CREATE TABLE test2

(

iname varchar(12),

inum varchar(5)

)

 

INSERT INTO test2 VALUES('김창수','S0001')

INSERT INTO test2 VALUES('노주인', 'S0002')

 

test2 테이블의 데이터를 읽어오는 프로시져 생성

CREATE PROCEDURE showtest2

AS

SELECT * FROM test2

 

실제 구축

C#으로 SP를 이용하여 콘솔화면에 test2 데이터를 출력하시오.

 

using System;

using System.Collections.Generic;

using System.Text;

using System.Data;

using System.Data.SqlClient;

 

namespace usingSpTest2Ex

{

class Program

{

static void Main(string[] args)

{

try

{

string strConn = "server=.\\SQLEXPRESS;database=mytest;user id=sa;password=dba007$";

SqlConnection Conn = new SqlConnection(strConn);

Conn.Open();

 

SqlCommand Comm = new SqlCommand("showtest2", Conn);

Comm.CommandType = CommandType.StoredProcedure;

 

SqlDataReader sdr = Comm.ExecuteReader();

while (sdr.Read())

{

Console.WriteLine("iname : {0} inum : {1} ", sdr["iname"], sdr["inum"]);

}

sdr.Close();

Conn.Close();

}

catch (SqlException se)

{

Console.WriteLine(se.ToString());

}

Console.ReadLine();

}

}

}