連接字串不應硬寫在程式碼中。ASP.NET 提供 <connectionStrings> 區段集中管理,並支援加密保護敏感資訊。本文示範標準設定、讀取、參數化查詢與安全強化。
<connectionStrings>
<add name="DefaultConnection"
connectionString="Server=myServer;Database=myDB;User Id=myUser;Password=myPass;Encrypt=True;TrustServerCertificate=False;"
providerName="System.Data.SqlClient" />
</connectionStrings>
DbProviderFactories 泛型寫法。using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class WebForm1 : System.Web.UI.Page
{
private string _connStr;
protected void Page_Load(object sender, EventArgs e)
{
_connStr = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
if (!IsPostBack) BindData();
}
private void BindData()
{
const string sql = "SELECT Id, Name, CreatedAt FROM Items WHERE IsActive = 1 ORDER BY CreatedAt DESC";
using (var conn = new SqlConnection(_connStr))
using (var cmd = new SqlCommand(sql, conn))
using (var da = new SqlDataAdapter(cmd))
{
var dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
開發機執行指令加密 connectionStrings 區段:
aspnet_regiis -pef "connectionStrings" "C:\Path\To\Your\WebApp" -prov "DataProtectionConfigurationProvider"
Web.config 會變成加密內容,只有該機器 (或相同金鑰容器的伺服器群) 能解密。ConfigurationManager 會自動解密。aspnet_regiis -pdf "connectionStrings" "Path"。在連接字串加入:
Max Pool Size=100;Min Pool Size=5;Connect Timeout=30;Pooling=true;
Max Pool Size=100,高併發可調大。string sql = "SELECT * FROM Users WHERE Username = @User AND Status = @Status";
using (var cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.Add("@User", SqlDbType.NVarChar, 50).Value = txtUser.Text;
cmd.Parameters.Add("@Status", SqlDbType.Int).Value = 1;
// ...
}
絕對不要使用 String.Format 或字串串接拼接 SQL。