컴퓨토피아

저장이나 수정시 for문 안에서 루프 돌면서 insert 하는경우 종종 있죠??

그럴경우 xml형태로 데이터를 전송하여 sp안에서 처리하게 하는건 어떨까요?

머 많은분들이 알고 계시겠지만 그래도~ ㅋㅋㅋ

우선 클릭 이벤트~

// 다중 입력 처리
  private void Button1_Click(object sender, System.EventArgs e)
  {
   string sXML = "";    // xml 저장소
   bool bResult = false;   // 결과값
   DataSet ds = new DataSet();
   ds.Tables.Add(MakeTable());  // 테이블 생성

   if(ds.Tables[0].Rows.Count > 0)
   {

    sXML = ds.GetXml();
   
    bResult  = AddMemo(sXML);

    if(bResult)
    {
     // 처리 성공
    }
    else
    {
     // 처리 실패
    }
   }
   else
   {
    // 테이블 생성 실패
   }
  }

DataTable 형식으로 자료를 만든다. xml로 쓰기 위함

private DataTable MakeTable()
  {
   
   char cSplit = ';';
   string[] sReceiveID = null;

   DataTable dt = new DataTable("MemoAdd");
   dt.Columns.Add("SendID");
   dt.Columns.Add("ReceiveID");
   dt.Columns.Add("Memo");
   dt.Columns.Add("DelState");
   
   string sSendID = AuthUser.CurrentUserID;
   string sMemo = txtMemo.Text.Trim().Replace("\r\n", "<br>");
   string sDelState = txtDelState.Value.Trim();

   // 수신자 아이디
   if(txtReceiveID.Text.IndexOf(cSplit) > 0)
   {
    sReceiveID = txtReceiveID.Text.Split(cSplit);
   }
   else
   {
    sReceiveID = new string[1];
    sReceiveID[0] = txtReceiveID.Text.Trim();
   }

   DataRow dr = null;
   for(int i=0;i<sReceiveID.Length;i++)
   {
    dr = dt.NewRow();
     
    dr["SendID"]    = sSendID;
    dr["ReceiveID"] = sReceiveID[i];
    dr["Memo"]  = sMemo;
    dr["DelState"]  = sDelState;
   
    dt.Rows.Add(dr);
   }
   return dt;
  }

sp 호출

public bool AddMemo(string sXML)
  {
   string sQuery = "USP_SM_MemoAdd_I";  // sp명
   bool bResult = false;         // 결과값
   SqlCommand sqlCmd = null;
   SqlParameterCollection sqlParamColl = null;
   SqlParameter[] sqlParams = null;
   
   try
   {
    sqlCmd = new SqlCommand();
    sqlParamColl = sqlCmd.Parameters;
    sqlParamColl.Add("@VarXML", sXML);
   
    sqlParams = new SqlParameter[sqlParamColl.Count];
    sqlParamColl.CopyTo(sqlParams, 0);
    sqlParamColl.Clear();

    SqlHelper.ExecuteNonQuery(CONNSTR_UTIL, CommandType.StoredProcedure, sQuery, sqlParams);  // 실행
    bResult = true;
   }
   catch(Exception ex)
   {
    throw ex;
   }
   finally
   {
    if (sqlCmd != null) sqlCmd.Dispose();
   }
   return bResult;
  }

sp 내용

Create PROC USP_SM_MemoAdd_I
 @VarXML NTEXT
AS
 DECLARE @SendID varchar(50)
 DECLARE @ReceiveID varchar(50)
 DECLARE @Memo Varchar(7000)
 DECLARE @DelState char(2)

 DECLARE @HDoc    INT
 DECLARE @RootName  VARCHAR(100)

 SET NOCOUNT ON
 BEGIN TRANSACTION

 EXEC SP_XML_PREPAREDOCUMENT @HDoc OUTPUT, @VarXML
 SET @RootName ='/' + (SELECT TOP 1 LocalName FROM OPENXML(@HDoc, '/') WHERE ID = 0) + '/'
 SET @RootName = @RootName + (SELECT TOP 1 LocalName FROM OPENXML(@HDoc, '/') WHERE ParentID = 0)

 DECLARE XML_CURSOR CURSOR FOR 
  SELECT SendID, ReceiveID, Memo, DelState
  FROM OPENXML(@HDoc, @RootName, 1)
  WITH (
   SendID varchar(50) 'SendID',
   ReceiveID varchar(50) 'ReceiveID',
   Memo Varchar(7000) 'Memo',
   DelState char(2) 'DelState'
            )
 OPEN XML_CURSOR
 FETCH NEXT FROM XML_CURSOR INTO @SendID, @ReceiveID, @Memo, @DelState

 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF ( EXISTS(SELECT 1 FROM dbo.TB_SM_BOSUser where UserID = @SendID) and EXISTS(SELECT 1 FROM dbo.TB_SM_BOSUser where UserID = @ReceiveID) )
   BEGIN
    INSERT INTO
     dbo.TB_SM_Memo (SendID, ReceiveID, Memo, DelState)
    VALUES
    (
     @SendID,
     @ReceiveID,
     @Memo,
     @DelState
    )
   END

  IF @@ERROR <> 0
  BEGIN
   ROLLBACK TRANSACTION

   CLOSE  XML_CURSOR
   DEALLOCATE  XML_CURSOR
   EXECUTE SP_XML_REMOVEDOCUMENT @HDoc   -- OpenXML을 사용하고 나서, Document를 닫고, Handle을 제거한다.
   SET NOCOUNT OFF
   RAISERROR 13000 '……'
   RETURN
  END
 
 FETCH NEXT FROM XML_CURSOR INTO @SendID, @ReceiveID, @Memo, @DelState

 END

 CLOSE XML_CURSOR
 DEALLOCATE  XML_CURSOR
 EXECUTE SP_XML_REMOVEDOCUMENT @HDoc   -- OpenXML을 사용하고 나서, Document를 닫고, Handle을 제거한다.
   
 COMMIT TRANSACTION
 SET NOCOUNT OFF
 RETURN
GO

1. BCP 유틸리티 이용

DECLARE @Query VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
DECLARE @Command VARCHAR(2000)

SET @Query = 'SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname'
SET @FileName = 'C:\Authors.txt'
SET @Command = 'BCP "' + @Query + '" queryout ' + @FileName + ' -c '
EXEC master..xp_cmdshell @Command

아무 에러가 안생겼다면 C:\에 Authors.txt 이 생겼을것이다.. 물론 DB 서버에~~~

문제점 : 데이터중 케리지리턴 값이 들어가 있으면 파일 생성 실패가 떨어진다... ㅠ.ㅠ
[MSDN에서 bcp 유틸리티 / xp_cmdshell  로 검색하면 자세한 정보를 얻을 수 있다.]

2. OSQL 유틸리티 이용

DECLARE @Query VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)
DECLARE @Command VARCHAR(2000)

SET @Query = 'SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname'
SET @FileName = 'C:\Authors.txt'
SET @Command = 'OSQL -E /Q "' + @Query + '" /o ' + @FileName+ ''
EXEC master..xp_cmdshell @Command

-- 3. OACreate 이용 (이놈 신기하네~~~)
DECLARE @FS INT
DECLARE @RC INT
DECLARE @FileID INT

DECLARE @Query VARCHAR(1000)
DECLARE @FileName VARCHAR(1000)

SET @FileName = 'C:\Authors.txt'

-- A. OLE개체 인스턴스 생성
EXEC @RC = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @RC <> 0
BEGIN
 PRINT 'Error:  Creating the file system object'
END
-- B. File Open
EXEC @RC = sp_OAMethod @FS, 'OpenTextFile' , @FileID OUT, @FileName, 8, 1
IF @RC <> 0
BEGIN
 PRINT 'Error:  Opening the specified text file'
END

-- C. Write
EXEC @RC = sp_OAMethod @FileID, 'WriteLine', Null, '테스트'
IF @RC <> 0
BEGIN
 PRINT 'Error:  Writing string data to file'
END

-- D.OLE 개체를 삭제
EXECUTE @RC = sp_OADestroy @FileID
EXECUTE @RC = sp_OADestroy @FS

1, 2번처럼 DB의 내용을 그대로 파일로 쓰는것은 아니고 지정한 텍스트를 파일로 만든다..

OACreate 를 이용하여 'Scripting.FileSystemObject'를 생성한다는게 너무 신기하지 않은가?? ㅋㅋㅋ

============================================================================================

특정 테이블의 데이타를 Row단위로 파일로 만들어야 했다..
BCP 유틸을 봤을때.. 너무 기뻤지만 (싸게 갈수 있겠구나 ㅋㅋ 라는 생각에.. )  기쁨도 잠시 ㅠ.ㅠ
케리지 리턴이 존재할 경우 파일로 만들수가 없었다...
"아니 대용량 데이타 Copy 해주는 명령어가.. 케리지 리턴이 있다고 Copy를 못해?? "
라고 생각 하시겠지만.. 프롬프트 상태에서는 BCP명령어만 주기 때문에 가능 하지만 현재는 Q/A에서 돌리기 때문에.. 한 라인으로 인식하지 못하고.. 에러가 나는게 아닐까?? 라는 생각을 해본다 ㅋㅋ(아님 말고 -_-;; 암턴 안된다.. )
2번 마찬가지 -_-;; 같은 이유로 할수 없었다...
3번을 봤을때.. 너무 기뻤다..
아니 sql에서 fso를써??? 이.럴.수.가 @.@ ~~~
MSDN에서 SP_OACreate로 검색하면... 많은 정보를 얻을수 있다..

[출처] [MSSQL] DB의 특정 자료를 파일로 만들기 |작성자 필립박