sp 호출 c#코드를 만들어 주는 sp(Stored Procedure)

쩝... 정말 재미있는걸 만드는 사람들이 많다는 생각이 드네요 ^^''

이 sp는 정말 편할 것 같습니다.

 

Stored Procedure를 C#이나 ASP에서 사용을 할 때, Command Object를 사용하는게 성능에 좋다는 것은 알고 있지만, 지긋지긋한 parameter 객체들을 만들 생각을 하다가 귀찮아서 다른 방식으로 간단하게 사용하는 경우들이 종종 있습니다.

 

이 sp는 그 문제를 해결해 주네요 ^^''

 

..more

>접기

Introduction

Ever got fed up with creating all the code behind parameters for your stored procedures? I have. So, I wrote this stored proc to do the code for me. (I love code that writes code). This has been written for use with "Microsoft Data Application Block" (SQLHelper.cs), however it could be hacked around to write the code without using the MDAB or even to write the code in VB. The script handles both input and output parameters, setting the size of text types, and you could even use it for direct access to a view or table.

Using the code

To implement, just copy the code into Query Analyzer and run. This will create a SPROC called "tools_CS_SPROC_Builder".

To use, just execute the SPROC passing the name of the SPROC you want the code for (see below). Note: don't include any owner prefix, e.g.: (dbo.).

EXEC tools_CS_SPROC_Builder 'mySprocsNameHere'

The message window in the Query Analyzer will write out all the code required for your class or code behind. There is a variable in "tools_CS_SPROC_Builder" called "@connName" which you can set to the name of your connection instance. By default, it's set to "conn.Connection" - just alter this for your own project's naming convention.

The code

CREATE     PROCEDURE tools_CS_SPROC_Builder(@objName nvarchar(100))AS/*___________________________________________________________________Name:  CS SPROC BuilderVersion: 1Date:  10/09/2004Author:  Paul McKenzieDescription: 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".    */SET NOCOUNT ONDECLARE @parameterCount intDECLARE @errMsg varchar(100)DECLARE @parameterAt varchar(1)DECLARE @connName varchar(100)//Change the following variable to the name of your connection instanceSET @connName='conn.Connection'SET @parameterAt=''  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.systypes.xtypeINTO #t_objFROM          dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtypeWHERE      (dbo.sysobjects.name = @objName)  AND  (dbo.systypes.status <> 1) ORDER BY  dbo.sysobjects.name,  dbo.syscolumns.colorderSET @parameterCount=(SELECT count(*) FROM #t_obj)IF(@parameterCount<1) SET @errMsg='No Parameters/Fields found for ' + @objNameIF(@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)   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_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 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    + ');'   --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   PRINT '   paramsToStore['+ cast(@col_order-1 as varchar)     + '].Value =  ;'      -- 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_xtype   END  PRINT ''  PRINT '   SqlHelper.ExecuteNonQuery(' + @connName +   ', CommandType.StoredProcedure,"' + @objName + '", paramsToStore);'  PRINT '   }'  PRINT 'catch(Exception excp)'  PRINT '   {'  PRINT '   }'  PRINT 'finally'  PRINT '   {'  PRINT '   ' + @connName + '.Dispose();'  PRINT '   ' + @connName + '.Close();'  PRINT '   }'    CLOSE cur  DEALLOCATE cur ENDif(LEN(@errMsg)>0) PRINT @errMsgDROP TABLE #t_objSET NOCOUNT ONGOSET QUOTED_IDENTIFIER OFF GOSET ANSI_NULLS ON GO

Output Example

try   {   SqlParameter[] paramsToStore = new SqlParameter[9];    paramsToStore[0] = new SqlParameter("@organisationid", SqlDbType.BigInt);   paramsToStore[0].Value =  ;   paramsToStore[1] = new SqlParameter("@DisplayName", SqlDbType.NVarChar);   paramsToStore[1].Size=100;   paramsToStore[1].Value =  ;   paramsToStore[2] = new SqlParameter("@DefaultCurrencyFID", SqlDbType.Int);   paramsToStore[2].Value =  ;   paramsToStore[3] = new SqlParameter("@TaxCode", SqlDbType.NVarChar);   paramsToStore[3].Size=60;   paramsToStore[3].Value =  ;   paramsToStore[4] = new SqlParameter("@UserFID", SqlDbType.BigInt);   paramsToStore[4].Value =  ;   paramsToStore[5] = new SqlParameter("@IsClient", SqlDbType.Bit);   paramsToStore[5].Value =  ;   paramsToStore[6] = new SqlParameter("@IsContractor", SqlDbType.Bit);   paramsToStore[6].Value =  ;   paramsToStore[7] = new SqlParameter("@IsSupplier", SqlDbType.Bit);   paramsToStore[7].Value =  ;   paramsToStore[8] = new SqlParameter("@IsDesigner", SqlDbType.Bit);   paramsToStore[8].Value =  ;    SqlHelper.ExecuteNonQuery(conn.Connection, CommandType.StoredProcedure,"usp_Insert_Organisation", paramsToStore);   }catch(Exception excp)   {   }finally   {   conn.Connection.Dispose();   conn.Connection.Close();   }

All you then have to do is copy-paste and fill in the values you want to pass and catch any exceptions... (Yes you do have to write some code!).

Points of Interest

In order for me to work out the @col_xtype variable from sysobjects, I ran lots of tests on a table I created with every type variation in both directions. It certainly works fine for all the usual data types, but I haven't had a chance to test all types within .NET, so if you find a bug, let me know. Enjoy!

McCodeJunky

Click here to view McCodeJunky's

 

멋진 사람 ㅋㅋ

 

출처 : http://www.codeproject.com/cs/database/CSCodeBuilder.asp