C# 呼叫預存程序與 SQL Server DTS/SSIS 完整實戰指南

一、呼叫 SQL Server 預存程序 (含輸出參數與回傳值)

// 預存程序範例
CREATE PROCEDURE AddNumbers
    @Result INT OUTPUT,
    @A INT,
    @B INT
AS
    SET @Result = @A + @B
    RETURN @Result
GO

// C# 呼叫
public int ExecuteAddNumbers(int a, int b)
{
    const string connStr = "Server=.;Database=TestDB;User Id=sa;Password=****;";
    using (var conn = new SqlConnection(connStr))
    using (var cmd = new SqlCommand("AddNumbers", conn) { CommandType = CommandType.StoredProcedure })
    {
        cmd.Parameters.Add("@A", SqlDbType.Int).Value = a;
        cmd.Parameters.Add("@B", SqlDbType.Int).Value = b;
        var retParam = cmd.Parameters.Add("@Result", SqlDbType.Int);
        retParam.Direction = ParameterDirection.Output;

        conn.Open();
        cmd.ExecuteNonQuery();
        return (int)retParam.Value; // 取得 OUTPUT 參數
        // 若需 RETURN 值：cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
    }
}

二、呼叫 Oracle 預存程序 / Function (Package)

// Oracle Package
CREATE OR REPLACE PACKAGE DemoPkg AS
    FUNCTION ConcatStr(p_in VARCHAR2) RETURN VARCHAR2;
    PROCEDURE GetDual(p_cur OUT SYS_REFCURSOR);
END;
/

// C# 呼叫 Function (有回傳值)
public string CallOracleFunction(string input)
{
    const string connStr = "Data Source=ORCL;User Id=scott;Password=tiger;";
    using (var conn = new OracleConnection(connStr))
    using (var cmd = new OracleCommand("DemoPkg.ConcatStr", conn) { CommandType = CommandType.StoredProcedure })
    {
        cmd.Parameters.Add("p_in", OracleDbType.Varchar2, input, ParameterDirection.Input);
        var ret = cmd.Parameters.Add("RETURN_VALUE", OracleDbType.Varchar2, 200, null, ParameterDirection.ReturnValue);
        conn.Open();
        cmd.ExecuteNonQuery();
        return ret.Value.ToString();
    }
}

// C# 呼叫 Procedure 回傳游標 (DataSet)
public DataSet CallOracleRefCursor()
{
    using (var conn = new OracleConnection(connStr))
    using (var cmd = new OracleCommand("DemoPkg.GetDual", conn) { CommandType = CommandType.StoredProcedure })
    {
        cmd.Parameters.Add("p_cur", OracleDbType.RefCursor, ParameterDirection.Output);
        var da = new OracleDataAdapter(cmd);
        var ds = new DataSet();
        da.Fill(ds);
        return ds;
    }
}

三、C# 執行 SQL Server DTS (舊版) / SSIS 套件

方法 1：xp_cmdshell + DTSRun (SQL Server 2000/2005 DTS)

// 需啟用 xp_cmdshell：EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
public void RunDtsLegacy(string packageId)
{
    using (var conn = new SqlConnection("Server=.;Database=master;User Id=sa;Password=****;"))
    using (var cmd = new SqlCommand($"EXEC master..xp_cmdshell 'DTSRun /~Z{packageId}'", conn))
    {
        conn.Open();
        cmd.ExecuteNonQuery();
    }
}

方法 2：DTSPkg COM 物件 (SQL Server 2000/2005)

// 參考 Microsoft.SqlServer.DtsPkg (Microsoft.SQLServer.DTSPkg80.dll)
public void RunDtsCom(string server, string user, string pwd, string pkgName)
{
    var pkg = new DTS.Package2Class();
    object persist = null;
    pkg.LoadFromSQLServer(server, user, pwd, DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
                          null, null, null, pkgName, ref persist);
    pkg.Execute();
    pkg.UnInitialize();
}

方法 3：現代 SSIS (SQL Server 2005+) — 推薦

// 參考 Microsoft.SqlServer.ManagedDTS (Microsoft.SqlServer.ManagedDTS.dll)
using Microsoft.SqlServer.Dts.Runtime;

public void RunSsisPackage(string pkgPath) // .dtsx 檔案路徑
{
    var app = new Application();
    var pkg = app.LoadPackage(pkgPath, null);
    var result = pkg.Execute();
    if (result == DTSExecResult.Failure)
    {
        foreach (var err in pkg.Errors)
            Console.Error.WriteLine(err.Description);
        throw new Exception("SSIS 執行失敗");
    }
}

// 若部署至 SSISDB 目錄
public void RunSsisCatalog(string folder, string project, string package)
{
    var app = new Application();
    var pkg = app.LoadFromDtsServer($@"\{folder}\{project}\{package}", ".");
    pkg.Execute(null, null, null, null, null);
}

四、T-SQL 中以 sp_OA* 呼叫 DTS/COM (舊版相容)

DECLARE @obj INT, @hr INT, @src VARCHAR(255), @desc VARCHAR(255);
EXEC @hr = sp_OACreate 'DTS.Package', @obj OUT;
IF @hr <> 0 GOTO ErrHandler;

EXEC @hr = sp_OAMethod @obj, 'LoadFromSQLServer', NULL, 'ServerName', 'PkgName', 256;
IF @hr <> 0 GOTO ErrHandler;

EXEC @hr = sp_OAMethod @obj, 'Execute';
IF @hr <> 0 GOTO ErrHandler;

EXEC sp_OADestroy @obj;
RETURN;

ErrHandler:
EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT;
RAISERROR('DTS Error: %s - %s', 16, 1, @src, @desc);

五、最佳實務總結

總結

從傳統 DTS 到現代 SSIS，從 SQL Server 到 Oracle，C# 呼叫預存程序與排程套件的核心在於：正確設定參數方向、妥善管理連線生命週期、選擇適當的執行模型。新專請優先採用 SSISDB + ManagedDTS，舊系統維護時再視環境相容性選擇 xp_cmdshell 或 COM 介面。