您现在的位置：程式碼之家 >> 編程語言 > >> .NET 網頁程式設計 >> C# >> 關於C#

根據預存程序名稱自動生成 ADO.NET 參數程式碼：T-SQL 工具腳本

編輯：關於C#

痛點：手寫 SqlParameter 极其繁瑣

每次呼叫預存程序都需手動宣告 SqlParameter 陣列、設定型別、長度、方向、值，錯誤率高且耗時。利用系統表 sysobjects、syscolumns、systypes 可自動產生對應程式碼。

核心預存程序：`sqltoolforexcuteandadapter`

ALTER PROCEDURE dbo.sqltoolforexcuteandadapter
    @objName nvarchar(100),   -- 預存程序名稱
    @isExcute int             -- 0: ExecuteNonQuery, 1: SqlDataAdapter.Fill
AS
    SET NOCOUNT ON
    -- 1. 取得參數中繼資料存入暫存表 #t_obj
    SELECT 
        o.name AS ObjName, o.xtype AS ObjType,
        c.name AS ColName, c.colorder AS ColOrder,
        c.length AS ColLen, c.colstat AS ColKey,
        c.isoutparam AS ColIsOut, t.xtype AS ColXType
    INTO #t_obj
    FROM syscolumns c
    INNER JOIN sysobjects o ON c.id = o.id
    INNER JOIN systypes t ON c.xtype = t.xtype
    WHERE o.name = @objName AND t.status <> 1
    ORDER BY o.name, c.colorder

    -- 2. 列印連線與命令建立程式碼
    DECLARE @paramCount int = (SELECT COUNT(*) FROM #t_obj)
    IF @paramCount = 0 PRINT 'No parameters found'
    ELSE
    BEGIN
        PRINT 'SqlConnection conn = new SqlConnection("");'
        PRINT 'SqlCommand com = new SqlCommand("' + @objName + '", conn);'
        PRINT 'com.CommandType = CommandType.StoredProcedure;'
        PRINT 'SqlParameter[] Parameters = new SqlParameter[' + CAST(@paramCount AS varchar) + '];'

        -- 3. 遊標逐行產生參數程式碼
        DECLARE cur CURSOR FOR SELECT * FROM #t_obj
        OPEN cur
        FETCH NEXT FROM cur INTO @srcName, @srcType, @colName, @colOrder, 
                                 @colLen, @colKey, @colIsOut, @colXType

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 型別對應
            DECLARE @sqlDbType varchar(20) = CASE @colXType
                WHEN 167 THEN 'VarChar' WHEN 231 THEN 'NVarChar' 
                WHEN 56 THEN 'Int' WHEN 61 THEN 'DateTime'
                -- ... 其他型別對應 ...
                ELSE '!MISSING' END

            PRINT 'Parameters[' + CAST(@colOrder-1 AS varchar) + '] = new SqlParameter("@' + @colName + '", SqlDbType.' + @sqlDbType + ');'

            IF @colIsOut = 1
                PRINT 'Parameters[' + CAST(@colOrder-1 AS varchar) + '].Direction = ParameterDirection.Output;'
            ELSE
                PRINT 'Parameters[' + CAST(@colOrder-1 AS varchar) + '].Value = ?;'

            -- 字串型別需設定 Size
            IF @colXType IN (167, 231, 175, 99, 35)
                PRINT 'Parameters[' + CAST(@colOrder-1 AS varchar) + '].Size = ' + CAST(@colLen AS varchar) + ';'

            FETCH NEXT FROM cur INTO @srcName, @srcType, @colName, @colOrder, 
                                     @colLen, @colKey, @colIsOut, @colXType
        END
        CLOSE cur; DEALLOCATE cur

        PRINT 'com.Parameters.AddRange(Parameters);'

        -- 4. 產生執行邏輯
        IF @isExcute = 0
            PRINT 'try { conn.Open(); com.ExecuteNonQuery(); } catch { throw; } finally { conn.Close(); }'
        ELSE
            PRINT 'try { da.Fill(ds); } catch { throw; } finally { /* dispose */ }'
    END
    DROP TABLE #t_obj

測試與輸出範例

EXEC sqltoolforexcuteandadapter 'YourProcName', 1

輸出：

SqlConnection conn = new SqlConnection("");
SqlCommand com = new SqlCommand("YourProcName", conn);
com.CommandType = CommandType.StoredProcedure;
SqlParameter[] Parameters = new SqlParameter[1];
Parameters[0] = new SqlParameter("@yourparam", SqlDbType.VarChar);
Parameters[0].Value = ?;
Parameters[0].Size = 6;
com.Parameters.AddRange(Parameters);
try { da.Fill(ds); } catch { throw; } finally { /* dispose */ }