C# 程式連線
在 C# 中連線 PostgreSQL，通常使用 **Npgsql** 套件。它是 PostgreSQL 官方推薦的 .NET Data Provider。
## 1. 安裝 Npgsql
使用 .NET CLI：
```bash
dotnet add package Npgsql
```
或在 Visual Studio 的 NuGet Package Manager 安裝：
```text
Npgsql
```
---
## 2. 建立資料庫連線
PostgreSQL 的連線字串通常如下：
```text
Host=localhost;
Port=5432;
Database=mydb;
Username=postgres;
Password=your_password;
```
也可以寫成單行：
```csharp
string connectionString =
"Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=your_password;";
```
基本連線範例：
```csharp
using Npgsql;
string connectionString =
"Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=your_password;";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
Console.WriteLine("成功連線到 PostgreSQL");
```
`NpgsqlConnection` 建議搭配 `using` 或 `await using`，讓連線使用完後自動釋放。
---
# 3. 執行 SQL 指令
## 3.1 `INSERT`、`UPDATE`、`DELETE`：使用 `ExecuteNonQuery`
`ExecuteNonQuery` 用於不需要回傳資料列的 SQL 指令，回傳受影響的資料筆數。
例如建立資料表：
```csharp
string createTableSql = """
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(200) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
""";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command = new NpgsqlCommand(createTableSql, connection);
int affectedRows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"受影響的資料筆數：{affectedRows}");
```
新增資料：
```csharp
string sql = """
INSERT INTO users (name, email)
VALUES (@name, @email);
""";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command = new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue("name", "王小明");
command.Parameters.AddWithValue("email", "ming@example.com");
int rows = await command.ExecuteNonQueryAsync();
Console.WriteLine($"新增 {rows} 筆資料");
```
---
## 3.2 使用參數避免 SQL Injection
不要直接串接使用者輸入：
```csharp
// 不建議
string sql = "SELECT * FROM users WHERE name = '" + userName + "'";
```
應使用參數：
```csharp
string sql = """
SELECT id, name, email
FROM users
WHERE name = @name;
""";
await using var command = new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue("name", userName);
```
參數化查詢可以避免 SQL Injection，也能正確處理特殊字元。
若資料可能是 `null`，可以使用：
```csharp
command.Parameters.AddWithValue(
"email",
NpgsqlTypes.NpgsqlDbType.Varchar,
email ?? (object)DBNull.Value
);
```
---
# 4. 取得單一值：`ExecuteScalar`
如果 SQL 只會回傳一個值，例如筆數、最大值或新增資料的 ID，可使用 `ExecuteScalarAsync`。
## 查詢資料筆數
```csharp
string sql = "SELECT COUNT(*) FROM users;";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command = new NpgsqlCommand(sql, connection);
long count = (long)(await command.ExecuteScalarAsync())!;
Console.WriteLine($"使用者數量：{count}");
```
## 新增資料後取得自動產生的 ID
PostgreSQL 可以使用 `RETURNING`：
```csharp
string sql = """
INSERT INTO users (name, email)
VALUES (@name, @email)
RETURNING id;
""";
await using var command = new NpgsqlCommand(sql, connection);
command.Parameters.AddWithValue("name", "李小華");
command.Parameters.AddWithValue("email", "hua@example.com");
int newUserId = (int)(await command.ExecuteScalarAsync())!;
Console.WriteLine($"新增使用者 ID：{newUserId}");
```
---
# 5. 取得多筆資料：`ExecuteReader`
查詢多筆資料時，使用 `ExecuteReaderAsync`：
```csharp
string sql = """
SELECT id, name, email, created_at
FROM users
ORDER BY id;
""";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var command = new NpgsqlCommand(sql, connection);
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
int id = reader.GetInt32(reader.GetOrdinal("id"));
string name = reader.GetString(reader.GetOrdinal("name"));
string email = reader.GetString(reader.GetOrdinal("email"));
DateTime createdAt = reader.GetDateTime(reader.GetOrdinal("created_at"));
Console.WriteLine(
$"ID: {id}, 姓名: {name}, Email: {email}, 建立時間: {createdAt}"
);
}
```
也可以使用欄位索引：
```csharp
while (await reader.ReadAsync())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
Console.WriteLine($"{id} - {name} - {email}");
}
```
不過使用欄位名稱較容易維護：
```csharp
int id = reader.GetInt32(reader.GetOrdinal("id"));
```
---
# 6. 完整範例
以下範例包含：
1. 建立資料表
2. 新增資料
3. 查詢資料
```csharp
using Npgsql;
string connectionString =
"Host=localhost;" +
"Port=5432;" +
"Database=mydb;" +
"Username=postgres;" +
"Password=your_password;";
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
Console.WriteLine("資料庫連線成功");
// 建立資料表
string createTableSql = """
CREATE TABLE IF NOT EXISTS products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) NOT NULL
);
""";
await using (var command = new NpgsqlCommand(createTableSql, connection))
{
await command.ExecuteNonQueryAsync();
}
// 新增產品
string insertSql = """
INSERT INTO products (name, price)
VALUES (@name, @price)
RETURNING id;
""";
int productId;
await using (var command = new NpgsqlCommand(insertSql, connection))
{
command.Parameters.AddWithValue("name", "鍵盤");
command.Parameters.AddWithValue("price", 1299.00m);
productId = (int)(await command.ExecuteScalarAsync())!;
}
Console.WriteLine($"新增產品 ID：{productId}");
// 查詢產品
string selectSql = """
SELECT id, name, price
FROM products
ORDER BY id;
""";
await using (var command = new NpgsqlCommand(selectSql, connection))
await using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
decimal price = reader.GetDecimal(2);
Console.WriteLine($"ID={id}, 名稱={name}, 價格={price}");
}
}
```
---
# 7. 使用交易 Transaction
當多個 SQL 必須全部成功，或全部取消時，可以使用交易：
```csharp
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
await using var transaction = await connection.BeginTransactionAsync();
try
{
string sql1 = """
INSERT INTO users (name, email)
VALUES (@name, @email);
""";
await using var command1 = new NpgsqlCommand(sql1, connection, transaction);
command1.Parameters.AddWithValue("name", "甲");
command1.Parameters.AddWithValue("email", "a@example.com");
await command1.ExecuteNonQueryAsync();
string sql2 = """
INSERT INTO users (name, email)
VALUES (@name, @email);
""";
await using var command2 = new NpgsqlCommand(sql2, connection, transaction);
command2.Parameters.AddWithValue("name", "乙");
command2.Parameters.AddWithValue("email", "b@example.com");
await command2.ExecuteNonQueryAsync();
await transaction.CommitAsync();
Console.WriteLine("交易成功");
}
catch
{
await transaction.RollbackAsync();
Console.WriteLine("交易失敗，已復原");
throw;
}
```
---
# 8. 建議的實務做法
## 不要將密碼直接寫在程式碼中
開發時可以使用設定檔：
```json
{
"ConnectionStrings": {
"PostgreSQL": "Host=localhost;Port=5432;Database=mydb;Username=postgres;Password=your_password"
}
}
```
正式環境則建議使用：
- 環境變數
- Secret Manager
- Azure Key Vault
- AWS Secrets Manager
- Docker Secrets
## 連線池
Npgsql 預設會使用 connection pooling，因此通常不需要自行長期保留一個連線。建議：
```csharp
await using var connection = new NpgsqlConnection(connectionString);
await connection.OpenAsync();
```
用完即關閉，Npgsql 會把連線放回連線池，之後重複使用。
## 三種主要執行方法
| 方法 | 用途 |
|---|---|
| `ExecuteNonQueryAsync()` | `INSERT`、`UPDATE`、`DELETE`、DDL |
| `ExecuteScalarAsync()` | 取得單一值 |
| `ExecuteReaderAsync()` | 取得一筆或多筆查詢資料 |
簡單來說：
```csharp
// 執行但不取資料
await command.ExecuteNonQueryAsync();
// 取得一個值
object? value = await command.ExecuteScalarAsync();
// 逐筆讀取資料
await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
// 讀取欄位
}
```
相關學習地圖、教學課程
Python 資料工程