14.ADO.NET - Transaction
| FrmTransaction.aspx |
|
<html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
<title></title> </head> <body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnCommand" runat="server" Text="업데이트 및 삭제" onclick="btnCommand_Click" />
<asp:Button ID="cmdCommand" runat="server" Text="삭제+삭제" onclick="cmdCommand_Click" />
<br />
<asp:Label ID="lblError" runat="server" ForeColor="Red"></asp:Label>
</div>
</form> </body> </html> |
| FrmTransaction.aspx.cs |
|
protected void btnCommand_Click(object sender, EventArgs
e) {
string updateQuery =
"Update Categories Set CategoryName
= '컴퓨터' Where Num = 2";
string deleteQuery = "Delete Categories Where Num >=
20";
using (SqlConnection con = new SqlConnection(
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
con.Open();
//[1] SqlTransaction 클래스의 인스턴스 생성
SqlTransaction
tran = con.BeginTransaction(); // 트랜잭션 걸기
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tran; //[2] 현재 명령어에서 사용할 트랜잭션 지정
try
{ cmd.CommandText = updateQuery; cmd.ExecuteNonQuery(); // 수정 cmd.CommandText = deleteQuery; cmd.ExecuteNonQuery(); // 삭제 tran.Commit(); //[3] 에러가 발생하지 않으면 커밋 lblError.Text = "정상 처리"; }
catch (Exception ex)
{ lblError.Text = ex.Message; // 에러메시지 출력 tran.Rollback(); //[4] 에러가 발생하면 롤백
}
} } protected void cmdCommand_Click(object sender, EventArgs
e) {
string updateQuery = "Delete Categories Where Num = 7";
string deleteQuery = "Delete Categories Where Num = 9";
// 참조 -> .NET -> System.Transactions 추가
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection con = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{ con.Open(); SqlCommand
cmd = new SqlCommand(); cmd.Connection = con; try{ cmd.CommandText = updateQuery; cmd.ExecuteNonQuery(); // 수정 cmd.CommandText = deleteQuery; cmd.ExecuteNonQuery(); // 삭제 lblError.Text = "정상 처리"; }
catch
(Exception ex) { lblError.Text = ex.Message; // 에러메시지 출력 }
}
//scope.Complete(); // 완료...
} } |
| 결과확인 |
[그림14-1] |
FrmTransaction.aspx