根據預存程序名稱產生 ADO.NET 資料庫存取代碼（一）：SQL 工具預存程序完整代碼

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sqltoolforexcuteandadapter]
(
    @objName nvarchar(100), -- 預存程序名稱
    @isexcute int -- 是否為 execute 或 sqladapter，0 為 execute，1 為 sqladapter
)
AS
SET NOCOUNT ON
DECLARE @parameterCount int
DECLARE @errMsg varchar(100)
DECLARE @parameterAt varchar(1)
DECLARE @connName varchar(100)
DECLARE @outputValues varchar(100)
-- 變更下列變數為您的連接實例名稱
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) -- 不理解這個不等於 1 是幹嘛的？SQL 說明中也沒有？
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 'SqlConnection conn = new SqlConnection("");'
    PRINT 'SqlCommand com = new SqlCommand("' + @objName + '", conn);'
    PRINT 'com.CommandType = CommandType.StoredProcedure;'
    PRINT ' SqlParameter[] Parameters = 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
    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 = '@'
    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)
        PRINT ' Parameters[' + CAST(@col_order - 1 AS varchar) + '] = new SqlParameter("' + @parameterAt + @col_name + '", SqlDbType.' + @col_redef + ');'
        IF (@col_isout = 1)
        BEGIN
            PRINT ' Parameters[' + CAST(@col_order - 1 AS varchar) + '].Direction = ParameterDirection.Output;'
            SET @outputValues = @outputValues + ' ?Parameters[' + CAST(@col_order - 1 AS varchar) + '].Value;'
        END
        ELSE
        BEGIN
            PRINT ' Parameters[' + CAST(@col_order - 1 AS varchar) + '].Value = ?;'
        END
        IF (@col_xtype = 231) OR (@col_xtype = 167) OR (@col_xtype = 175) OR (@col_xtype = 99) OR (@col_xtype = 35)
        BEGIN
            PRINT ' Parameters[' + CAST(@col_order - 1 AS varchar) + '].Size = ' + CAST(@col_len AS varchar) + ';'
        END
        FETCH NEXT FROM cur INTO
            @source_name, @source_type, @col_name, @col_order, @col_len, @col_key, @col_isout, @col_xtype
    END
    PRINT ''
    PRINT ' com.Parameters.AddRange(Parameters);'
    IF @isexcute = 0 -- 使用 execute 方法執行 SQL
    BEGIN
        PRINT 'try { conn.Open(); com.ExecuteNonQuery(); } catch (Exception ee) { throw ee; } finally { conn.Close(); }'
    END
    ELSE IF @isexcute = 1 -- 需回傳資料集使用這個
    BEGIN
        PRINT 'try { da.Fill(ds); } catch (Exception ee) { throw ee; } finally { // do what you want to do or dispose resources. }'
    END
    CLOSE cur
    DEALLOCATE cur
END
IF (LEN(@errMsg) > 0) PRINT @errMsg
DROP TABLE #t_obj
SET NOCOUNT ON