Create Table Members
(
id int not null,
name varchar(10) not null,
title varchar(10) not null,
address varchar(50) not null,
iDate datetime not null default (getdate()),
constraint PK_Members_id Primary Key (id)
)
go
Insert Into Members(id, name, title, address) Values(1, '홍길동', '대리', '서울시 강남구 역삼동')
Insert Into Members(id, name, title, address) Values(2, '이순신', '대리', '서울시 강남구 역삼동2')
Insert Into Members(id, name, title, address) Values(3, '장길산', '과장', '서울시 강남구 역삼동3')
Insert Into Members(id, name, title, address) Values(4, '문길래', '부장', '서울시 강남구 역삼동4')
Insert Into Members(id, name, title, address) Values(5, '일지매', '사원', '서울시 강남구 역삼동5')
Select * From Members
--저장프로시져 생성
Create Proc up_Members_insert
(
@name varchar(10),
@title varchar(10),
@address varchar(50)
)
As
Declare @id int
Select @id = max(id) From Members
Set @id = @id+1
Insert Into Members (id, name, title, address)
Values (@id, @name, @title, @address)
--실행
up_members_insert 'bb', 'bbb', 'bbbb'
-------------------
TransactionScope데모를 위한 스크립트 입니다.
use test1
go
create table t1
(
id int identity primary key,
name varchar(10) not null
)
use test2
go
create table t2
(
id int identity primary key,
name varchar(10) not null
)
C# 데모 코드입니다.
try
{
// Create the TransactionScope to execute the commands, guaranteeing
// that both commands can commit or roll back as a single unit of work.
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection connection1 = new SqlConnection(
@"server=.\sqlExpress;database=test1;integrated security=true"))
{
// Opening the connection automatically enlists it in the
// TransactionScope as a lightweight transaction.
connection1.Open();
// Create the SqlCommand object and execute the first command.
SqlCommand command1 = new SqlCommand(
"Insert Into t1 (name) Values('AA')", connection1);
int returnValue = command1.ExecuteNonQuery();
Debug.WriteLine(returnValue);
// If you get here, this means that command1 succeeded. By nesting
// the using block for connection2 inside that of connection1, you
// conserve server and network resources as connection2 is opened
// only when there is a chance that the transaction can commit.
using (SqlConnection connection2 = new SqlConnection(
@"server=.\sqlExpress;database=test2;integrated security=true"))
{
// The transaction is escalated to a full distributed
// transaction when connection2 is opened.
connection2.Open();
// Execute the second command in the second database.
returnValue = 0;
SqlCommand command2 = new SqlCommand(
"Insert Into t2 (name) Values('BB')", connection2);
returnValue = command2.ExecuteNonQuery();
Debug.WriteLine(returnValue);
}
}
// The Complete method commits the transaction. If an exception has been thrown,
// Complete is not called and the transaction is rolled back.
scope.Complete();
}
}
catch (TransactionAbortedException ex)
{
MessageBox.Show(ex.Message);
}
catch (ApplicationException ex)
{
MessageBox.Show(ex.Message);
}
