ASP.NET 連接 SQL Server 兩種寫法：DataAdapter 快速版 vs Command 參數化安全版

方法一：SqlDataAdapter 簡潔版（教學/原型適用）

public void Page_Load(object sender, EventArgs e)
{
    using (SqlConnection con = new SqlConnection(
        "Data Source=.;uid=sa;pwd=sa;Database=NorthWind"))
    {
        string username = "forever";
        string strSQL = "SELECT * FROM [table] WHERE name = '" + username + "'";
        
        SqlDataAdapter adapter = new SqlDataAdapter(strSQL, con);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        
        foreach (DataRowView drv in ds.Tables[0].DefaultView)
        {
            Response.Write(drv["欄位1"] + "|" + drv["欄位2"]);
        }
    }
}

⚠️ 風險：字串拼接 SQL 易遭注入，生產環境勿用。

方法二：參數化 Command 企業級寫法

1. Web.config 連線字串

<configuration>
  <appSettings>
    <add key="connect" value="server=.;database=NorthWind;uid=sa;pwd=sa;"/>
  </appSettings>
</configuration>

2. 通用資料存取類

string sCon = ConfigurationManager.AppSettings["connect"];
if (string.IsNullOrEmpty(sCon)) 
    Response.Write("連線字串為空！");

SqlConnection con = new SqlConnection(sCon);
if (con.State == ConnectionState.Closed) con.Open();

public SqlDataReader ExecuteDataReader(string sql, CommandType cmdType, SqlParameter[] parameters)
{
    if (con.State == ConnectionState.Closed)
    {
        Response.Write("資料庫連線未開啟！");
        return null;
    }

    SqlCommand cmd = new SqlCommand(sql, con);
    cmd.CommandType = cmdType;
    if (parameters != null)
        foreach (var p in parameters) if (p != null) cmd.Parameters.Add(p);

    try
    {
        if (cmd.ExecuteScalar() != null)
            return cmd.ExecuteReader();
    }
    catch (SqlException se)
    {
        // 記錄錯誤
        return null;
    }
    finally { cmd.Dispose(); }
    return null;
}

3. 呼叫查詢

string sql = "SELECT * FROM [table] WHERE id = N'" + id + "'"; // N 前綴支援中文
SqlParameter[] parameters = null;
SqlDataReader reader = ExecuteDataReader(sql, CommandType.Text, parameters);

string output = "";
while (reader.Read())
{
    output += "欄位1：" + reader[0] + ", 欄位2：" + reader[1] + "; ";
}
Response.Write(output);

關鍵差異對比