파라메터쿼리를 만들어주는 프로시져

C# Code Generator for Stored Procedures

# 들어가는말
파라메터쿼리가 성능이 좋고 SQL인젝션 공격등을 보안할 수 있다고 합니다.
하지만 작성하기가 여간 까다로운것이 아닙니다.
보통 테이블에 필드가 20~30개 있다고 하면 이걸 다 적어주어야 할 뿐만 아니라 타입도 sp_help등을 통해서 확인하면서 코딩을 하여야 합니다.
여간 복잡하고 귀찮은 작업이 아닐 수 없습니다.
네이버나 구글에서 검색하면 CodeProJect 에 등록된 포스트를 보실 수 있을겁니다.

# 프로시져를 만들어 보세요

Src.zip

위의 파일을 다운로드 받아서 쿼리분석기나 Microsoft SQL Express에 쿼리를 돌려보세요.

그럼 tools_CS_SPROC_Builder 이라는 프로시져가 생성이 됩니다.


# 사용법

tools_CS_SPROC_Builder 테이블명

CREATE       PROCEDURE tools_CS_SPROC_Builder
(
@objName nvarchar(100)
)
AS
/*
___________________________________________________________________
Name:          CS SPROC Builder
Version:     1
Date:        10/09/2004
Author:      Paul McKenzie
Description:     Call this stored procedue passing the name of your
  database object that you wish to insert/update
  from .NET (C#) and the code returns code to copy
  and paste into your application.  This version is
  for use with "Microsoft Data Application Block".
 
Version:     1.1
Date:         17/02/2006
Author:         Paul McKenzie
Description:    a) Updated to include 'UniqueIdentifier' Data Type
        b) Support for 'ParameterDirection.Output'

*/
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)
--Change the following variable to the name of your connection instance
SET @connName='conn.Connection'
SET @parameterAt=''
SET @outputValues=''
SELECT
     dbo.sysobjects.name AS ObjName,
     dbo.sysobjects.xtype AS ObjType,
     dbo.syscolumns.name AS ColName,
     dbo.syscolumns.colorder AS ColOrder,
     dbo.syscolumns.length AS ColLen,
     dbo.syscolumns.colstat AS ColKey,
     dbo.syscolumns.isoutparam AS ColIsOut,
     dbo.systypes.xtype
INTO #t_obj
FROM        
     dbo.syscolumns INNER JOIN
     dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN
     dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
WHERE    
     (dbo.sysobjects.name = @objName)
     AND
     (dbo.systypes.status <> 1)
ORDER BY
     dbo.sysobjects.name,
     dbo.syscolumns.colorder

SET @parameterCount=(SELECT count(*) FROM #t_obj)
IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objName
IF(@errMsg is null)
    BEGIN
          PRINT 'try'
          PRINT '   {'
          PRINT '   SqlParameter[] paramsToStore = new SqlParameter[' + cast(@parameterCount as varchar) + '];'
          PRINT ''
 
          DECLARE @source_name nvarchar,@source_type varchar,
                @col_name nvarchar(100),@col_order int,@col_type varchar(20),
                @col_len int,@col_key int,@col_xtype int,@col_redef varchar(20), @col_isout tinyint
 
          DECLARE cur CURSOR FOR
          SELECT * FROM #t_obj
          OPEN cur
          -- Perform the first fetch.
          FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
 
              if(@source_type=N'U') SET @parameterAt='@'
              -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
              WHILE @@FETCH_STATUS = 0
                  BEGIN
                   SET @col_redef=(SELECT CASE @col_xtype
                    WHEN 34 THEN 'Image'
                    WHEN 35 THEN 'Text'
                    WHEN 36 THEN 'UniqueIdentifier'
                    WHEN 48 THEN 'TinyInt'
                    WHEN 52 THEN 'SmallInt'
                    WHEN 56 THEN 'Int'
                    WHEN 58 THEN 'SmallDateTime'
                    WHEN 59 THEN 'Real'
                    WHEN 60 THEN 'Money'
                    WHEN 61 THEN 'DateTime'
                    WHEN 62 THEN 'Float'
                    WHEN 99 THEN 'NText'
                    WHEN 104 THEN 'Bit'
                    WHEN 106 THEN 'Decimal'
                    WHEN 122 THEN 'SmallMoney'
                    WHEN 127 THEN 'BigInt'
                    WHEN 165 THEN 'VarBinary'
                    WHEN 167 THEN 'VarChar'
                    WHEN 173 THEN 'Binary'
                    WHEN 175 THEN 'Char'
                    WHEN 231 THEN 'NVarChar'
                    WHEN 239 THEN 'NChar'
                    ELSE '!MISSING'
                    END AS C)

                --Write out the parameter
                PRINT '   paramsToStore[' + cast(@col_order-1 as varchar)
                    + '] = new SqlParameter("' + @parameterAt + @col_name
                    + '", SqlDbType.' + @col_redef
                    + ');'

                --Write out the parameter direction it is output
                IF(@col_isout=1)
                    BEGIN
                        PRINT '   paramsToStore['+ cast(@col_order-1 as varchar) +'].Direction=ParameterDirection.Output;'
                        SET @outputValues=@outputValues+'   ?=paramsToStore['+ cast(@col_order-1 as varchar) +'].Value;'
                    END
                    ELSE
                    BEGIN
                        --Write out the parameter value line
                           PRINT '   paramsToStore['+ cast(@col_order-1 as varchar) + '].Value = ?;'
                    END
                --If the type is a string then output the size declaration
                IF(@col_xtype=231)OR(@col_xtype=167)OR(@col_xtype=175)OR(@col_xtype=99)OR(@col_xtype=35)
                    BEGIN
                        PRINT '   paramsToStore[' + cast(@col_order-1 as varchar) + '].Size=' + cast(@col_len as varchar) + ';'
                    END

                 -- This is executed as long as the previous fetch succeeds.
                  FETCH NEXT FROM cur INTO @source_name,@source_type,@col_name,@col_order, @col_len,@col_key,@col_isout,@col_xtype
      END
  PRINT ''
  PRINT '   SqlHelper.ExecuteNonQuery(' + @connName + ', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);'
  PRINT @outputValues
  PRINT '   }'
  PRINT 'catch(Exception excp)'
  PRINT '   {'
  PRINT '   }'
  PRINT 'finally'
  PRINT '   {'
  PRINT '   ' + @connName + '.Dispose();'
  PRINT '   ' + @connName + '.Close();'
  PRINT '   }' 
  CLOSE cur
  DEALLOCATE cur
 END
if(LEN(@errMsg)>0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON

# 출력물

try
   {
   SqlParameter[] paramsToStore = new SqlParameter[46];
 
   paramsToStore[0] = new SqlParameter("@CO_GUBUN", SqlDbType.VarChar);
   paramsToStore[0].Value = ?;
   paramsToStore[0].Size=2;
   paramsToStore[1] = new SqlParameter("@CO_GBNAME", SqlDbType.VarChar);
   paramsToStore[1].Value = ?;
   paramsToStore[1].Size=30;
   paramsToStore[2] = new SqlParameter("@CO_SANAME", SqlDbType.VarChar);
   paramsToStore[2].Value = ?;
   paramsToStore[2].Size=30;
   paramsToStore[3] = new SqlParameter("@CO_ESANAME", SqlDbType.VarChar);
   paramsToStore[3].Value = ?;
   paramsToStore[3].Size=50;
   paramsToStore[4] = new SqlParameter("@CO_SETDATE", SqlDbType.VarChar);
   paramsToStore[4].Value = ?;
   paramsToStore[4].Size=8;
   paramsToStore[5] = new SqlParameter("@CO_SANO", SqlDbType.VarChar);
   paramsToStore[5].Value = ?;
   paramsToStore[5].Size=10;
   paramsToStore[6] = new SqlParameter("@CO_BUBIN", SqlDbType.VarChar);
   paramsToStore[6].Value = ?;
   paramsToStore[6].Size=13;
   paramsToStore[7] = new SqlParameter("@CO_MASTER", SqlDbType.VarChar);
   paramsToStore[7].Value = ?;
   paramsToStore[7].Size=30;
   paramsToStore[8] = new SqlParameter("@CO_EMASTER", SqlDbType.VarChar);
   paramsToStore[8].Value = ?;
   paramsToStore[8].Size=50;
   paramsToStore[9] = new SqlParameter("@CO_UPJONG", SqlDbType.VarChar);
   paramsToStore[9].Value = ?;
   paramsToStore[9].Size=50;
   paramsToStore[10] = new SqlParameter("@CO_INWON", SqlDbType.VarChar);
   paramsToStore[10].Value = ?;
   paramsToStore[10].Size=5;
   paramsToStore[11] = new SqlParameter("@CO_UPTAE", SqlDbType.VarChar);
   paramsToStore[11].Value = ?;
   paramsToStore[11].Size=50;
   paramsToStore[12] = new SqlParameter("@CO_T_DDD", SqlDbType.VarChar);
   paramsToStore[12].Value = ?;
   paramsToStore[12].Size=20;
   paramsToStore[13] = new SqlParameter("@CO_T_KKK", SqlDbType.VarChar);
   paramsToStore[13].Value = ?;
   paramsToStore[13].Size=4;
   paramsToStore[14] = new SqlParameter("@CO_T_LLL", SqlDbType.VarChar);
   paramsToStore[14].Value = ?;
   paramsToStore[14].Size=4;
   paramsToStore[15] = new SqlParameter("@CO_F_DDD", SqlDbType.VarChar);
   paramsToStore[15].Value = ?;
   paramsToStore[15].Size=20;
   paramsToStore[16] = new SqlParameter("@CO_F_KKK", SqlDbType.VarChar);
   paramsToStore[16].Value = ?;
   paramsToStore[16].Size=4;
   paramsToStore[17] = new SqlParameter("@CO_F_LLL", SqlDbType.VarChar);
   paramsToStore[17].Value = ?;
   paramsToStore[17].Size=4;
   paramsToStore[18] = new SqlParameter("@CO_POST", SqlDbType.VarChar);
   paramsToStore[18].Value = ?;
   paramsToStore[18].Size=7;
   paramsToStore[19] = new SqlParameter("@CO_JUSO", SqlDbType.VarChar);
   paramsToStore[19].Value = ?;
   paramsToStore[19].Size=100;
   paramsToStore[20] = new SqlParameter("@CO_ENGJUSO", SqlDbType.VarChar);
   paramsToStore[20].Value = ?;
   paramsToStore[20].Size=255;
   paramsToStore[21] = new SqlParameter("@CO_JUSIC", SqlDbType.Money);
   paramsToStore[21].Value = ?;
   paramsToStore[22] = new SqlParameter("@CO_JUAMT", SqlDbType.Money);
   paramsToStore[22].Value = ?;
   paramsToStore[23] = new SqlParameter("@CO_BALSU1", SqlDbType.Money);
   paramsToStore[23].Value = ?;
   paramsToStore[24] = new SqlParameter("@CO_BALSU2", SqlDbType.Money);
   paramsToStore[24].Value = ?;
   paramsToStore[25] = new SqlParameter("@CO_MAGAM", SqlDbType.VarChar);
   paramsToStore[25].Value = ?;
   paramsToStore[25].Size=6;
   paramsToStore[26] = new SqlParameter("@CO_SDATE", SqlDbType.VarChar);
   paramsToStore[26].Value = ?;
   paramsToStore[26].Size=8;
   paramsToStore[27] = new SqlParameter("@CO_EDATE", SqlDbType.VarChar);
   paramsToStore[27].Value = ?;
   paramsToStore[27].Size=8;
   paramsToStore[28] = new SqlParameter("@CO_LDATE", SqlDbType.VarChar);
   paramsToStore[28].Value = ?;
   paramsToStore[28].Size=8;
   paramsToStore[29] = new SqlParameter("@CO_JABON", SqlDbType.Money);
   paramsToStore[29].Value = ?;
   paramsToStore[30] = new SqlParameter("@CO_GITA", SqlDbType.VarChar);
   paramsToStore[30].Value = ?;
   paramsToStore[30].Size=50;
   paramsToStore[31] = new SqlParameter("@CO_GIGAN", SqlDbType.VarChar);
   paramsToStore[31].Value = ?;
   paramsToStore[31].Size=2;
   paramsToStore[32] = new SqlParameter("@CO_YUMU", SqlDbType.VarChar);
   paramsToStore[32].Value = ?;
   paramsToStore[32].Size=1;
   paramsToStore[33] = new SqlParameter("@CO_GUBYU", SqlDbType.VarChar);
   paramsToStore[33].Value = ?;
   paramsToStore[33].Size=2;
   paramsToStore[34] = new SqlParameter("@CO_INUSER", SqlDbType.VarChar);
   paramsToStore[34].Value = ?;
   paramsToStore[34].Size=8;
   paramsToStore[35] = new SqlParameter("@CO_INDATE", SqlDbType.VarChar);
   paramsToStore[35].Value = ?;
   paramsToStore[35].Size=8;
   paramsToStore[36] = new SqlParameter("@CO_TOIJIK", SqlDbType.VarChar);
   paramsToStore[36].Value = ?;
   paramsToStore[36].Size=1;
   paramsToStore[37] = new SqlParameter("@CO_PROC", SqlDbType.Int);
   paramsToStore[37].Value = ?;
   paramsToStore[38] = new SqlParameter("@CO_URL", SqlDbType.VarChar);
   paramsToStore[38].Value = ?;
   paramsToStore[38].Size=50;
   paramsToStore[42] = new SqlParameter("@CO_HOMEID", SqlDbType.VarChar);
   paramsToStore[42].Value = ?;
   paramsToStore[42].Size=50;
   paramsToStore[43] = new SqlParameter("@CO_MANAGETAX", SqlDbType.VarChar);
   paramsToStore[43].Value = ?;
   paramsToStore[43].Size=30;
   paramsToStore[44] = new SqlParameter("@CO_MANAGEOFFICE", SqlDbType.VarChar);
   paramsToStore[44].Value = ?;
   paramsToStore[44].Size=30;
   paramsToStore[45] = new SqlParameter("@CO_MASTERNO", SqlDbType.VarChar);
   paramsToStore[45].Value = ?;
   paramsToStore[45].Size=13;
   paramsToStore[49] = new SqlParameter("@co_gbubin", SqlDbType.Int);
   paramsToStore[49].Value = ?;
   paramsToStore[50] = new SqlParameter("@co_gkind", SqlDbType.Int);
   paramsToStore[50].Value = ?;
   paramsToStore[51] = new SqlParameter("@co_gsojae", SqlDbType.Int);
   paramsToStore[51].Value = ?;
 
   SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure,"c_company", paramsToStore);

이제 위의 코드에서 원하는 값만 넣고
SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure,"c_company", paramsToStore);
이부분 처럼 던지면 됩니다 ^^