[SQL server] C# - ASP.NET CORE 7.0 MVC에 사용한 프로시저 생성 및 사용 예제

안녕하세요. 먼저 테이블과 프로시저를 적고 하단에 설명을 하겠습니다.

ASP.NET CORE 7.0 버전의 MVC 모델에 sql server를 사용하여 만든 토이프로젝트 내에 테이블과 프로시저입니다.

 

각 테이블은 ENTITY FRAMEWORK CORE의 모델을 이용해 프로그램이 생성해준 테이블입니다.

 

CREATE TABLE [dbo].[Worker] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [CompanyId] INT           NOT NULL,
    [Name]      NVARCHAR (20) NOT NULL,
    [Email]     NVARCHAR (20) NOT NULL,
    [Phone]     NVARCHAR (12) NOT NULL,
    [IsDelete]  NVARCHAR (1)  DEFAULT ('N') NOT NULL,
    CONSTRAINT [PK_Worker] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_Worker_Company_CompanyId] FOREIGN KEY ([CompanyId]) REFERENCES [dbo].[Company] ([Id]) ON DELETE CASCADE
);

CREATE TABLE [dbo].[User] (
    [Id]       INT           IDENTITY (1, 1) NOT NULL,
    [LoginId]  NVARCHAR (20) NOT NULL,
    [Password] NVARCHAR (20) NOT NULL,
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[Company] (
    [Id]       INT           IDENTITY (1, 1) NOT NULL,
    [Name]     NVARCHAR (20) NOT NULL,
    [Address]  NVARCHAR (50) NOT NULL,
    [Contact]  NVARCHAR (12) NOT NULL,
    [IsDelete] NVARCHAR (1)  DEFAULT ('N') NOT NULL,
    CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[ChangeHistory] (
    [Id]          INT            IDENTITY (1, 1) NOT NULL,
    [UserId]      INT            NOT NULL,
    [LoginId]     NVARCHAR (MAX) NOT NULL,
    [CompanyId]   INT            NULL,
    [CompanyName] NVARCHAR (MAX) NULL,
    [WorkerId]    INT            NULL,
    [WorkerName]  NVARCHAR (MAX) NULL,
    [ActIP]       NVARCHAR (MAX) NOT NULL,
    [Act]         NVARCHAR (MAX) NOT NULL,
    [ActDate]     DATETIME2 (7)  DEFAULT (sysdatetime()) NOT NULL,
    CONSTRAINT [PK_ChangeHistory] PRIMARY KEY CLUSTERED ([Id] ASC)
);

 

 

 다음은 sql server내에서 사용한 sql 파일을 그대로 가져왔습니다.
  한 번 설명한 내용은 그 밑에선 생략하겠습니다!

drop procedure if exists CreateCompany 

-- drop proceduce 는 말그대로 프로시저를 db에서 drop 시킨다(삭제한다) 이며
-- if exists를 붙이므로서 존재하면 삭제하는 기능입니다.
-- 보통 spl 파일 전체를 실행 시키므로 프로시저나 테이블생성문 앞에 붙여 사용합니다.

go	-- go는 앞문장과 뒷문장을 구분지어주는 역할을 합니다.



-- CreateCompany 이라는 stored procedure를 생성합니다.
-- 변수는 @Name ~ @Act 까지 총 9개를 받아서 처리하며
-- 이 프로시저를 실행시키면
-- Company 테이블에는 @Name ~ @Address 변수를 이용하여 한개의 레코드를 insert하고
-- ChangeHistory 테이블에는 나머지 6개의 변수를 이용하여 한개의 레코드를 insert 합니다.

create procedure CreateCompany
	@Name  NVARCHAR (20),
	@Contact  NVARCHAR (12),
	@Address  NVARCHAR (50),

	@UserId int,
	@LoginId  nvarchar(MAX),
	@CompanyId int ,
	@CompanyName nvarchar(MAX),
	@ActIP nvarchar(MAX),
	@Act nvarchar(1)
AS
BEGIN
	insert into Company (Name, Contact, Address)
	values (@Name, @Contact, @Address)

	insert into ChangeHistory (UserId, LoginId, CompanyId, CompanyName, ActIP, Act)
	values (@UserId, @LoginId, @CompanyId, @CompanyName, @ActIP, @Act)
END
go


-- declare는 프로시저 내에서 변수를 선언하는 문법으로 해석자체가 '선언하다'입니다.
-- @CompanyName을 선언하여 
-- 바로 아래 문장인
-- select @CompanyName =Name from Company where Id = @id;
-- Company 테이블에서 Id가 @id인 레코드(Id는 Primary key 이므로 단일 레코드가 셀렉됩니다.)
-- 중 Name 컬럼의 값을 @CompanyName이라는 변수에 집어 넣는다.
-- @DorR은 Delete or Restore의 줄임말로 
-- 		Y인 경우 Delete를(IsDelete = 'Y'),
-- 		N인 경우 Restore를(IsDelete = 'N')합니다.

-- 회사를 삭제하는데 회사에 worker가 있으면 worker들도 모두 IsDelete를 Y로 만드는 프로시저입니다.
-- 복구시에는 회사만 복구됩니다.

drop procedure if exists DorRCompany 
go
create procedure DorRCompany
	@id int,
	@DorR nvarchar(1),
	@UserId int,
	@LoginId  nvarchar(MAX),
	@ActIP nvarchar(MAX),
	@Act nvarchar(1)
as
begin
	declare @CompanyName NVARCHAR(MAX);
	select @CompanyName =Name from Company where Id = @id;

	update Company set IsDelete = @DorR where Id = @id;
	insert into ChangeHistory (UserId, LoginId, CompanyId, CompanyName, ActIP, Act)
	values (@UserId, @LoginId, @id, @CompanyName, @ActIP, @Act)

	declare @WorkerCountInCompany int;
	select @WorkerCountInCompany=count(CompanyId)  from Worker group by CompanyId having CompanyId = @id
	if @WorkerCountInCompany > 0 and @DorR = 'Y'
	begin
		update Worker set IsDelete = 'Y' where CompanyId = @id
	end
end
go

drop procedure if exists CreateWorker 
go
create procedure CreateWorker
	@CompanyId int,
	@Name nvarchar(MAX),
	@Email nvarchar(MAX),
	@Phone nvarchar(MAX),

	@UserId int,
	@LoginId nvarchar(MAX),
	@ActIP nvarchar(MAX)
AS
begin
	declare @WorkerId int;
	insert into Worker (CompanyId, Name, Email, Phone)
	values (@CompanyId, @Name, @Email, @Phone);

	select @WorkerId =Max(Id) from Worker;
	insert into ChangeHistory (UserId, LoginId, WorkerId, WorkerName, ActIP, Act)
	values (@UserId, @LoginId, @WorkerId, @Name, @ActIP, 'C')
end
go

drop procedure if exists DorRWorker
go
create procedure DorRWorker
	@id int,
	@DorR nvarchar(1),
	@UserId int,
	@LoginId  nvarchar(MAX),
	@ActIP nvarchar(MAX),
	@Act nvarchar(1)
as
begin
	declare @WorkerName NVARCHAR(MAX);
	select @WorkerName =Name from Worker where Id = @id;

	update Worker set IsDelete = @DorR where Id = @id;
	insert into ChangeHistory (UserId, LoginId, WorkerId, WorkerName, ActIP, Act)
	values (@UserId, @LoginId, @id, @WorkerName, @ActIP, @Act)
end

 

 

 

다음은 실행방법 예제입니다.

본문 제일 상단에서 설명한 실행환경에서 사용했고 Controller 중 메소드 하나를 통째로 가져왔습니다.

 

var 선언할변수명 = new SqlParameter(쿼리에서쓰이는변수명, 값) 으로 선언한 값을

_context.Database.ExecuteSqlRawAsync(쿼리문, 선언한변수1, 선언한변수2, ... )

으로 실행시킬 수 있습니다.

 

여기서 Database는 정말로 데이터 베이스를 가리키는 말로 보통의 Context뒤에 오는 테이블명과는 다릅니다.

프로시저 실행 쿼리문은 하단 예제에 query를 참고하시면 됩니다.

 

SqlParameter()로 선언된 변수들은 변수명.SqlValue로 해당변수의 값을 뽑아내서 사용하는 것이 가능합니다.

 

참고 ) SqlParameter() 대신 일반 변수에 담아 $"{변수명}"으로 사용하는 것이 불가능 하지않으나 동적인 부분에서는 안정성이 떨어진다는 경고 메시지가 나옵니다. 처음 접하시는분들은 이 방법으로도 한번 해보시고 메세지를 직접 보고 어떨때 예상과 결과값이 다르게 나오는 것인지 확인해보는 것도 좋을 것 같습니다.

 

 

 

[HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("Id,CompanyId,Name,Email,Phone, Company")] Worker worker)
        {
            int userId = HttpContext.Session.GetInt32("userId") ?? 0;
            string? userLoginId = HttpContext.Session.GetString("userLoginId");

            if (userId != 0 && userLoginId != null)
            {
                var p1 = new SqlParameter("@CompanyId", worker.CompanyId);
                var p2 = new SqlParameter("@Name", worker.Name);
                var p3 = new SqlParameter("@Email", worker.Email);
                var p4 = new SqlParameter("@Phone", worker.Phone);
                var p5 = new SqlParameter("@UserId", userId);
                var p6 = new SqlParameter("@LoginId", userLoginId);
                var p7 = new SqlParameter("@ActIP", HttpContext.Connection.RemoteIpAddress?.ToString() ?? string.Empty);

                string query = @"EXEC CreateWorker @CompanyId, @Name, @Email, @Phone, @UserId, @LoginId, @ActIP";
                await _context.Database.ExecuteSqlRawAsync(query, p1, p2, p3, p4, p5, p6, p7);

                _logger.Log(LogLevel.Information,
                    $"delete company => UserId = {p5.SqlValue}, UserLoginId = {p6.SqlValue}, " +
                    $"target = Worker, CompanyId ={p1.SqlValue}, WorkerName = {p2.SqlValue} IP = {p7.SqlValue}, time = {DateTime.Now}");

                await _context.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            ViewData["CompanyId"] = new SelectList(_context.Company, "Id", "Address", worker.CompanyId);
            return View(worker);
        }