ASP.NET 2.0 使用預存程序完整指南：SqlDataAdapter 參數化查詢與 GridView 繫結實作

ASP.NET 2.0 中透過 SqlDataAdapter 與 SqlCommand 執行預存程序，配合 CommandType.StoredProcedure 與參數集合，實現安全、高效、可維護的資料庫操作。本文示範帶參數與不帶參數兩種預存程序的完整呼叫模式。

資料庫預存程序定義

-- 不帶參數：查詢所有使用者
CREATE PROCEDURE dbo.oa_selectalluser
AS
    SELECT * FROM UserInfo
GO

-- 帶參數：依 ID 查詢使用者
CREATE PROCEDURE dbo.oa_SelectByID
    @id INT
AS
    SELECT * FROM UserInfo WHERE ID = @id
GO

不帶參數預存程序呼叫

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        string connStr = ConfigurationManager.ConnectionStrings["oaConnectionString"].ConnectionString;
        
        using (SqlConnection conn = new SqlConnection(connStr))
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            da.SelectCommand = new SqlCommand("oa_selectalluser", conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            
            DataSet ds = new DataSet();
            da.Fill(ds);
            
            GridView1.DataSource = ds;
            GridView1.DataBind();
        }
    }
}

帶參數預存程序呼叫

protected void btn_search_Click(object sender, EventArgs e)
{
    string connStr = ConfigurationManager.ConnectionStrings["oaConnectionString"].ConnectionString;
    
    using (SqlConnection conn = new SqlConnection(connStr))
    using (SqlDataAdapter da = new SqlDataAdapter())
    {
        da.SelectCommand = new SqlCommand("oa_SelectByID", conn);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
        
        // 建立參數
        SqlParameter param = new SqlParameter("@id", SqlDbType.Int);
        param.Direction = ParameterDirection.Input;
        param.Value = Convert.ToInt32(txt_value.Text);
        da.SelectCommand.Parameters.Add(param);
        
        DataSet ds = new DataSet();
        da.Fill(ds);
        
        GridView1.DataSource = ds;
        GridView1.DataBind();
    }
}

關鍵技術要點

CommandType.StoredProcedure

指定命令類型為預存程序，CommandText 僅需程序名稱，不需 SQL 陳述式。

參數建立最佳實務

參數加入方式

• cmd.Parameters.Add(param)：加入 SqlParameter 物件。
• cmd.Parameters.AddWithValue("@id", value)：簡易寫法（型別推斷，效能略低）。
• cmd.Parameters.Add("@id", SqlDbType.Int).Value = value：鏈式寫法。

輸出/輸入輸出參數範例

// 預存程序包含 OUTPUT 參數
CREATE PROCEDURE dbo.oa_InsertUser
    @Name NVARCHAR(50),
    @NewID INT OUTPUT
AS
    INSERT INTO UserInfo (Name) VALUES (@Name)
    SET @NewID = SCOPE_IDENTITY()
GO

// C# 呼叫取得 OUTPUT 值
SqlParameter outputParam = new SqlParameter("@NewID", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParam);

cmd.ExecuteNonQuery();
int newId = (int)cmd.Parameters["@NewID"].Value;

進階：使用 SqlDataSource 控制項聲明式繫結

<asp:SqlDataSource ID="sdsUsers" runat="server"
    ConnectionString="<%$ ConnectionStrings:oaConnectionString %>"
    SelectCommand="oa_SelectByID"
    SelectCommandType="StoredProcedure">
  <SelectParameters>
    <asp:ControlParameter Name="@id" ControlID="txt_value" PropertyName="Text" Type="Int32" />
  </SelectParameters>
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" DataSourceID="sdsUsers" />

零後置程式碼，參數自動從控制項取值，支援分頁/排序/快取。

安全與效能優勢

• 防 SQL 注入：參數化查詢徹底分離程式碼與資料。
• 執行計畫重用：預存程序編譯後快取於 SQL Server，重複呼叫效能優於動態 SQL。
• 權限控管：資料庫層級授權 EXECUTE 權限，不需直接資料表權限。
• 維護性：SQL 邏輯集中於資料庫，修改不需重新編譯部署應用程式。

常見錯誤避坑

• 參數名稱大小寫不一致（SQL Server 預設不區分，但最佳實務保持一致）。
• Direction 未設定導致 OUTPUT 參數無法回傳值。
• 連接字串未正確從 ConfigurationManager 讀取。
• 未使用 using 區塊導致連線洩漏。

掌握預存程序呼叫模式，是 ASP.NET 2.0 資料庫應用開發的核心基本功。