萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> 程式語言綜合

C# 執行 SQL Server 預存程序完整實作範例

前言

預存程序 是資料庫端預先編譯的 SQL 批次,具備效能優勢、減少網路傳輸、封裝商業邏輯等優點。本文示範如何在 C# 中使用 SqlCommand 呼叫帶有輸出參數的預存程序 AddProduct。

資料庫端:建立預存程序 (T-SQL)

CREATE PROCEDURE AddProduct
    @MyProductID INT OUTPUT,
    @MyProductName NVARCHAR(40),
    @MySupplierID INT,
    @MyCategoryID INT,
    @MyQuantityPerUnit NVARCHAR(20),
    @MyUnitPrice MONEY,
    @MyUnitsInStock SMALLINT,
    @MyUnitsOnOrder SMALLINT,
    @MyReorderLevel SMALLINT,
    @MyDiscontinued BIT
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO Products (ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice,
                          UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued)
    VALUES (@MyProductName, @MySupplierID, @MyCategoryID, @MyQuantityPerUnit, @MyUnitPrice,
            @MyUnitsInStock, @MyUnitsOnOrder, @MyReorderLevel, @MyDiscontinued);
    SET @MyProductID = SCOPE_IDENTITY(); -- 回傳新增的自增 ID
END

C# 呼叫端完整程式碼

using System;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteStorageProcess
{
    public class ExecuteStorageProcess
    {
        public static void Main()
        {
            string connStr = "server=localhost;database=Northwind;uid=sa;pwd=sa";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())
                {
                    // 1. 指定預存程序名稱與 CommandType
                    cmd.CommandText = "AddProduct";
                    cmd.CommandType = CommandType.StoredProcedure;

                    // 2. 定義參數:方向、型別、值
                    // 輸出參數
                    SqlParameter pId = new SqlParameter("@MyProductID", SqlDbType.Int)
                    {
                        Direction = ParameterDirection.Output
                    };
                    cmd.Parameters.Add(pId);

                    // 輸入參數
                    cmd.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget";
                    cmd.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box";
                    cmd.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99m;
                    cmd.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10;
                    cmd.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
                    cmd.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5;
                    cmd.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1;

                    // 3. 執行
                    cmd.ExecuteNonQuery();

                    // 4. 讀取輸出參數
                    Console.WriteLine($"新產品 ID = {cmd.Parameters["@MyProductID"].Value}");
                }
            }
        }
    }
}

關鍵細節解析

項目說明
CommandType.StoredProcedure告知 ADO.NET 這是預存程序,而非 SQL 字串
ParameterDirection.Output標記參數為輸出,執行後由資料庫回填值
SCOPE_IDENTITY()取得當前範圍內最後插入的自增識別值,比 @@IDENTITY 更安全
參數順序使用 CommandType.StoredProcedure 時,參數順序不重要,靠名稱對應
SqlDbType 對應需與資料庫欄位型別精確對應,避免隱含轉換導致效能損耗或錯誤

進階情境

  • 多個結果集:使用 cmd.ExecuteReader() 並呼叫 reader.NextResult() 切換。
  • 回傳值 (Return Value):在預存程序用 RETURN @code,C# 端加參數 Direction = ParameterDirection.ReturnValue。
  • Table-Valued Parameter:傳遞資料表給預存程序,需定義 User-Defined Table Type,C# 端用 SqlDbType.Structured。
  • 非同步執行:await cmd.ExecuteNonQueryAsync(),避免阻塞執行緒。

最佳實踐建議

  1. 盡量使用參數化命令,防止 SQL 注入。
  2. 將連線字串放在設定檔,勿硬編碼。
  3. 使用 using 確保連線與命令物件正確釋放。
  4. 記錄執行耗時與錯誤,便於效能調優與除錯。
copyright © 萬盛學電腦網 all rights reserved