.NET 呼叫 SQL Server 預存程序完整範例 (含參數處理)

資料庫端：建立預存程序

無參數查詢

CREATE PROC usp_GetAllStudents
AS
SELECT * FROM StudentInfo;

有參數查詢

CREATE PROC usp_GetStudentByIdName
    @StudentId VARCHAR(10),
    @StudentName VARCHAR(10)
AS
SELECT * FROM StudentInfo 
WHERE StudentId = @StudentId AND StudentName = @StudentName;

C# 呼叫端：無參數情況

using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand("usp_GetAllStudents", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    conn.Open();
    
    // 方式一：DataReader
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"{reader["StudentId"]} - {reader["StudentName"]}");
        }
    }
    
    // 方式二：填充 DataTable
    DataTable dt = new DataTable();
    using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
    {
        adapter.Fill(dt);
    }
}

C# 呼叫端：有參數情況

using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand("usp_GetStudentByIdName", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    
    // 參數添加方式一：AddWithValue (簡易，但類型推斷可能不精確)
    cmd.Parameters.AddWithValue("@StudentId", "S001");
    cmd.Parameters.AddWithValue("@StudentName", "王小明");
    
    // 參數添加方式二：明確指定型別與長度 (推薦，效能較好)
    cmd.Parameters.Add("@StudentId", SqlDbType.VarChar, 10).Value = "S001";
    cmd.Parameters.Add("@StudentName", SqlDbType.VarChar, 10).Value = "王小明";
    
    // 參數添加方式三：取得參數物件後設定值 (需重複使用時)
    SqlParameter idParam = cmd.Parameters.Add("@StudentId", SqlDbType.VarChar, 10);
    idParam.Value = "S001";
    
    conn.Open();
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // 處理結果...
    }
}

參數方向與輸出參數

// 預存程序有 OUTPUT 參數時
cmd.Parameters.Add("@ReturnCode", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.Parameters.Add("@ReturnMsg", SqlDbType.NVarChar, 200).Direction = ParameterDirection.Output;

conn.Open();
cmd.ExecuteNonQuery();

int code = (int)cmd.Parameters["@ReturnCode"].Value;
string msg = (string)cmd.Parameters["@ReturnMsg"].Value;

最佳實踐總結

Entity Framework Core 呼叫方式 (補充)

// EF Core 2.1+
var students = context.Students
    .FromSqlRaw("EXEC usp_GetStudentByIdName @StudentId, @StudentName",
        new SqlParameter("@StudentId", "S001"),
        new SqlParameter("@StudentName", "王小明"))
    .ToList();

// 純量輸出/無結果集
context.Database.ExecuteSqlRaw("EXEC usp_UpdateStats");