using System;
using System.Data;
using System.Data.SqlClient;
namespace ADO.NET.STUDY
{
class MainClass
{
private string ConString = null;
private SqlConnection SqlCon = null;
private SqlCommand SqlCom = null;
private SqlDataReader sReader = null;
public MainClass()
{
this.ConString = "Server=Localhost;database=Pubs;UID=sa;PWD=1111";
}
[STAThread]
static void Main(string[] args)
{
MainClass main = new MainClass();
main.use_ExcuteReader();
main.use_ExecuteScalar();
main.use_getSqlReturnValue();
main.use_Transaction();
Console.Read(); //실행화면 확인을 위한 일시중지
}
private void DBConnect()
{
if(SqlCon == null)
SqlCon = new SqlConnection(ConString);
if(SqlCon.State == ConnectionState.Closed)
try
{
SqlCon.Open();
}
catch(Exception e)
{
Console.WriteLine(e.Message);
}
}
private void DBClose()
{
if(SqlCon.State != ConnectionState.Closed)
SqlCon.Close();
}
// 저장프로시저를 이용한 ExcuteReader, ExecuteNonQuery사용예제
// 저장프로시저 매개변수 사용법 및 OUTPUT, RETRUN값 받는 예제 포함.
private void use_ExcuteReader()
{
this.DBConnect();
string strSQLSel = "sp_getUser";
//string strSQLUpdate = "UPDATE authors SET au_fname = @fname TEL = @tel WHERE au_lname = @lname";
/*
* 프로시저 sp_getUesr의 내용
* create proc sp_getUser(@state varchar(20), @Cnt int output) as
* SELECT au_id, phone FROM authors WHERE state = @state
* SET @Cnt = @@rowcount
*/
try
{
this.DBConnect(); // DB연결하는 메소드
SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandTimeout = 15;
// 명령 실행을 종료하고 오류를 생성하기 전 대기 시간, 기본값은 30초
//SqlCom.CommandText = "SELECT au_id, phone FROM authors WHERE state = @state";
SqlCom.CommandText = strSQLSel;
SqlCom.CommandType = CommandType.StoredProcedure;
//명령줄의 형식은 프로시저임,
//일반쿼리문임을 나타내는 Text타입를 제외한 나머지 타입인 경우는 꼭 기재해야함.
SqlCom.Parameters.Add("@state", SqlDbType.VarChar, 20).Value = "CA";
//프로시저의 @state매개변수의 값에 "CA"라는 이름을 넣는다
SqlCom.Parameters.Add("@cnt", SqlDbType.Int, 4);
SqlCom.Parameters["@cnt"].Direction = ParameterDirection.Output;
/* 또는
* SqlParameter spName = new SqlParameter ();
* spName.ParameterName = "@state";
* spName.SqlDbType = SqlDbType.VarChar;
* spName.Size = 20;
* spName.Value = "CA";
* spName.SourceVersion = DataRowVersion.Current;
* SqlCom.Parameters.Add(spName);
*/
int cnt = SqlCom.ExecuteNonQuery();
/* ExecuteNonQuery : 해당쿼리문에 적용된 레코드의 개수 반환
*
* SQL 문에서는 기본적으로 해당 명령(INSERT, DELETE, UPDATE)문의
* 영향을 받은 레코드 수를 나타내는 정수 값이 반환이 되는데 ExecuteNonQuery에서
* 바로 그 값을 받는것이다.
*
* 참고 : INSERT, DELETE, UPDATE문이 아닌 다른 명령문(SELECT), 또는 롤백이 일어났을 경우 -1이 리턴.
*/
Console.WriteLine("ExecuteNonQuery에서 적용받은 레코드의 수 : {0}", cnt);
// SELECT문을 이용했기때문에 기본적으로 return되는 값은 -1
Console.WriteLine("Output으로 받은 레크드의 개수 :" + SqlCom.Parameters["@cnt"].Value);
// SELECT한 레코드의 수를 가져오기위해서는 위처럼 OUTPUT파라미터 이용
/*
* System.SqlClient.SqlCommand.ExecuteReader를 호출하여 반환되는
* Output, InputOut 및 ReturnValue 매개 변수에 액세스하려면 SqlDataReader에서
* System.SqlClient.SqlDataReader.Close 또는 Dispose를 호출해야 합니다.(도움말)
*
* 위 명령의 경우 ExecuteNonQuery는 Close를 할 필요가 없기때문에 매개변수값이 출력됩니다.
* 이 소스의 아래부분에 있는 출력매개변수 출력부분을 sReader.Close() 명령이 일어나기 전에 넣어서 테스트해보세요.
*
* 참고 : 반환되는 값이 출력매개변수 값 하나이면 Close를 먼저 하지 않아도 결과값을 받을수 있습니다.
*/
sReader = SqlCom.ExecuteReader();
//authors테이블에서 state열 값이 "CA"인 레코드 반환
while (sReader.Read())
{
Console.WriteLine(sReader.GetString(0) + ", " + sReader.GetString(1));
// 반환된 레코드중 첫번째필드(au_id)와 두번째필드(phone)값 콘솔에 출력
}
sReader.Close(); // DataReader사용후 꼭 닫아줘어야 함.
Console.WriteLine("Output으로 받은 레크드의 개수 : " + SqlCom.Parameters["@cnt"].Value);
// SELECT한 레코드의 수를 가져오기위해서는 위처럼 OUTPUT파라미터 이용
this.DBClose(); // DB연결을 닫는 메소드
}
catch (Exception ec)
{
Console.WriteLine(ec.Message);
}
}
/* ExecuteScalar 이용한 간단한 예제 */
private void use_ExecuteScalar()
{
SqlCon = new SqlConnection(ConString);
try
{
SqlCon.Open();
}
catch(Exception ec)
{
Console.WriteLine(ec.Message);
}
SqlCom = SqlCon.CreateCommand();
SqlCom.CommandText = "select count(au_id) from authors";
SqlCom.CommandType = CommandType.Text; // Default, 생략가능
int result = (int) SqlCom.ExecuteScalar();
Console.WriteLine("authors테이블의 레크드 개수 : "+ result.ToString());
this.DBClose();
}
/* SQL의 사용자정의 함수의 Return값 받는 예제 */
private void use_getSqlReturnValue()
{
this.DBConnect();
SqlCom = SqlCon.CreateCommand();
SqlCom.CommandText = "sp_ReturnName";
SqlCom.CommandType = CommandType.StoredProcedure;
/*
* create function sp_ReturnName(@au_id varchar(20))
* returns varchar(30)
* as
* begin
* declare @name varchar(30)
* select @name = au_fname + ' ' + au_lname from authors where au_id=@au_id
* return(@name)
* end
* go
*/
SqlCom.Parameters.Add("@au_id", SqlDbType.VarChar, 20).Value = "527-72-3246";
SqlParameter sampParm = SqlCom.Parameters.Add("ReturnValue", SqlDbType.VarChar, 30);
sampParm.Direction = ParameterDirection.ReturnValue;
// ExecuteReader를 호출하여 매개변수를 반환받는다.
// ParameterDirection 이 출력이면, 관련 SqlCommand의 실행은 값을 반환하지 않으며,
// SqlParameter는 null 값을 포함하지 않습니다.
sReader = SqlCom.ExecuteReader();
string result = (string) SqlCom.Parameters["ReturnValue"].Value;
Console.WriteLine("프로시저의 리턴값: {0}", result);
result = "이세영";
sReader.Close();
this.DBClose();
}
/* 트랜잭션사용예제 */
private void use_Transaction()
{
this.DBConnect();
SqlTransaction myTrans = SqlCon.BeginTransaction(); // 트랜잭션 처리 시작
SqlCom = SqlCon.CreateCommand();
SqlCom.Transaction = myTrans;
try
{
SqlCom.CommandText = "update titleauthor set au_ord = 3 where au_id = '172-32-1176'";
int updateCnt = (int) SqlCom.ExecuteNonQuery();
Console.WriteLine("업데이트수행 결과 : " + updateCnt);
// myTrans.Save("SavePointPosition");
// 트랜잭션의 흐름 중에서 전체를 롤백하고 싶지 않을 경우 표시한 부분까지만 롤백하고자 할 때
SqlCom.CommandText = "insert into titleauthor (au_id, title_id, au_ord, royaltyper) values('111-11-1111', 'AB0001', 3, 50)";
int insertCnt = (int) SqlCom.ExecuteNonQuery();
Console.WriteLine("업데이트수행 결과 : " + insertCnt);
myTrans.Commit();
// 현재 위 쿼리가 모두 수행되었을때만 트랜젝션 완료.
// 하나라도 오류발생하면 수행했던 모든 실행 취소
Console.WriteLine("update와 insert가 정상적으로 완료되었습니다.");
}
catch(Exception e)
{
try
{
myTrans.Rollback();
// myTrans.Rollback("SavePointPosition");
// SavePointPosition의 위치까지만 Rollback한다고 선언.
// Savepoint 이전의 명령들은 처리가 된다
}
catch (SqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine(ex.GetType());
}
}
Console.WriteLine(e.GetType());
Console.WriteLine("update와 insert명령이 둘다 완료되지 못했습니다.");
}
finally
{
this.DBClose();
}
}
}
}