create       PROCEDURE [dbo].[tools_CS_SPROC_Builder]
(
@objName nvarchar(100)
)
AS
/*
___________________________________________________________________
Name:    CS SPROC Builder
Version:  1
Date:     10/09/2008
Author:   sky
Description: 自动创建 方法 sqlHelper 版本
*/
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

go

alter  procedure [dbo].[tools_CS_SPROC_Builder_String]
(
@objName nvarchar(100)
)
as
/*
___________________________________________________________________
Name:    CS SPROC Builder
Version:  1
Date:     13/12/2008
Author:   sky
Description:  自动创建 方法 自定义方法
*/
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.xusertype
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
   --display function name
   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 @functionpar as varchar(5000)
   --display function name
    set @functionpar= 'public DataTable '+@objName+'( '
   --get function paras
   declare cur cursor for
    select * from #t_obj
    open cur
    fetch next from cur into @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
    while @@FETCH_STATUS = 0
    begin
     SET @col_redef=(select case @col_xtype
     when 34 then 'Image'
     when 35 then 'string'
     when 36 then 'UniqueIdentifier'
     when 48 then 'byte'
     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 'bool'
     when 106 then 'Decimal'
     when 122 then 'SmallMoney'
     when 127 then 'bigint'
     when 165 then 'VarBinary'
     when 167 then 'string'
     when 173 then 'Binary'
     when 175 then 'string'
     when 231 then 'string'
     when 239 then 'string'
     ELSE '!MISSING'
     END AS C)
     set @functionpar=@functionpar+@col_redef+' '+substring(@col_name,2,len(@col_name)-1)+','
     fetch next from cur into @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
    end
   set @functionpar=substring(@functionpar,1,len(@functionpar)-1)+')'
   print @functionpar
  
   --funtion body
   --------new params
    print '   {'   
    print '   string[] paramsName = new string[' + cast(@parameterCount as varchar) + '];'                          
    print '   object[] paramsValues = new object[' + cast(@parameterCount as varchar) + '];'
    
   close cur
   open cur

   fetch next from cur into @source_name,@source_type,@col_name,@col_order,@col_len,@col_key,@col_isout,@col_xtype
     -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    while @@FETCH_STATUS = 0
    begin
     if(@source_type=N'U') set @parameterAt='@'
     print ''
     --Write out the parameter
     print '   paramsName[' + cast(@col_order-1 as varchar)
         + '] = ' + @parameterAt+'"' + @col_name+'"'
         + ';'
     --Write out the parameter values
     print '   paramsValues[' + cast(@col_order-1 as varchar)
         + '] = '+ substring(@col_name,2,len(@col_name)-1)
         + ';'
     -- 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 '   return datacess.ExecProcGetDataTable("table1","' + @objName + '",paramsValues,paramsName);'
     print '   }' 
     close cur
     deallocate cur
 end
if(len(@errMsg)>0) print @errMsg
--DROP TABLE #t_obj
set nocount on