저장프로시저와 함수(C#/LINQ)

저장프로시저를 이용한 다중테이블 입력 방법

예 : Customers와 CustomersInfo 두 테이블 있고 회원가입시

       Customers에는 UserName, Password, Email을 입력하고

       CustomerInfo에는 FullName과 Customers에 Primary Key인 CustomerID를 입력한다면



DB에 Stored Procedure 생성

@Username VARCHAR(10),
@Password CHAR(40),
@Email VARCHAR(100),
@Fullname NVARCHAR(10)
AS
DECLARE @CustomerID INT;
BEGIN TRY
    INSERT INTO [Customers] VALUES(@Username, @Password, @Email);
    SELECT @CustomerID = [CustomerID] FROM [Customers] WHERE [UserName] = @Username;
    INSERT INTO [CustomerInfo]([CustomerID], [FullName]) VALUES(@CustomerID, @Fullname);
END TRY
 
BEGIN CATCH
    RETURN ERROR_NUMBER()
END CATCH
GO

Entity설정

[Function(Name="dbo.InsertCust")]
public int InsertCust(
[Parameter(Name="Username",DbType="VarChar(10)")]string username,
[Parameter(Name="Password",DbType="Char(40)")]string password,
[Parameter(Name="Email",DbType="VarChar(100)")]string email,
[Parameter(Name="FullName",DbType="NVarChar(30)")]string fullname)
{
IExecuteResult result = this.ExecuteMethodCall(
this,
((MethodInfo)(MethodInfo.GetCurrentMethod())),
username, password, email, fullname);
return ((int)(result.ReturnValue));
}

사용방법

CustomersEntity entity = new CustomersEntity(conn);
int insert = entity.InsertCust("tomebody", "han78031", "tome@naver.com", "Tome kim");