C# 建立與部署 SQL Server CLR 預存程序完整範例

開發流程三步驟

1. 建立：Visual Studio 2005 專案範本「預存程序」。
2. 部署：VS 直接部署至 SQL Server（需啟用 CLR 整合）。
3. 呼叫：C# 使用 SqlCommand + CommandType.StoredProcedure。

CLR 預存程序範例：GetProduct

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetProduct(int id)
    {
        // 內部連線：Context Connection=true
        using (SqlConnection conn = new SqlConnection("Context Connection=true"))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = 
                    "SELECT ProductID, ProductName, CategoryID, Quantity " +
                    "FROM Products WHERE ProductID = @ID";
                cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id;

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    SqlPipe pipe = SqlContext.Pipe;
                    pipe.Send(reader); // 直接將結果集串流回用戶端
                }
            }
        }
    }
}

C# 用戶端呼叫測試

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

namespace Magci.Test.SQLServer.TestProc
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = @"server=.\sqlexpress;database=MGC;trusted_connection=true";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "GetProduct";
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@id", 1));

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(
                                "Name: {0}, CategoryID: {1}, Quantity: {2}",
                                reader["ProductName"],
                                reader["CategoryID"],
                                reader["Quantity"]);
                        }
                    }
                }
            }
            Console.ReadLine();
        }
    }
}

關鍵注意事項

• 資料庫需啟用 CLR：EXEC sp_configure 'clr enabled', 1; RECONFIGURE;
• 專案屬性 → Database → 目標連線字串設定正確。
• 部署時會自動在資料庫建立組件與預存程序對應。
• Context Connection=true 僅限 CLR 內部使用，外部呼叫需一般連線字串。
• 效能考量：CLR 適合複雜邏輯、正則運算、外部資源存取；單純 CRUD 仍以 T-SQL 為佳。