C# Oracle 資料庫操作完整指南
身為 C# 開發者,在企業級應用程式開發中,Oracle 資料庫是我們經常會遇到的資料庫系統。本文將為您詳細介紹如何在 C# 中有效地操作 Oracle 資料庫,從基本的連線設定到進階的交易處理,讓您快速掌握所有必要的技能。
環境準備
在開始之前,您需要先安裝 Oracle 的 .NET 驅動程式。建議使用官方的 Oracle.ManagedDataAccess NuGet 套件:
Install-Package Oracle.ManagedDataAccess
這個套件是 Oracle 官方提供的託管驅動程式,不需要額外安裝 Oracle Client,使用起來更加方便。
資料庫連線
基本連線設定
首先,我們需要建立與 Oracle 資料庫的連線。以下是標準的連線方式:
using Oracle.ManagedDataAccess.Client;
// 連線字串範例
string connectionString = "Data Source=localhost:1521/XE;User Id=username;Password=password;";
// 建立並開啟連線
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
Console.WriteLine("資料庫連線成功!");
// 在這裡執行您的資料庫操作
}
連線字串說明
- Data Source: 資料庫伺服器位址和服務名稱
- User Id: 資料庫使用者名稱
- Password: 資料庫密碼
- Connection Timeout: 連線超時時間(可選)
基本 CRUD 操作
查詢資料 (SELECT)
查詢是最常用的資料庫操作之一。以下示範如何安全地執行查詢:
string sql = "SELECT ID, NAME, EMAIL, AGE FROM USERS WHERE AGE > :age";
using (OracleCommand command = new OracleCommand(sql, connection))
{
// 使用參數化查詢防止 SQL 注入
command.Parameters.Add(":age", OracleDbType.Int32).Value = 18;
using (OracleDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
int id = reader.GetInt32("ID");
string name = reader.GetString("NAME");
string email = reader.GetString("EMAIL");
int age = reader.GetInt32("AGE");
Console.WriteLine($"ID: {id}, 姓名: {name}, 信箱: {email}, 年齡: {age}");
}
}
}
新增資料 (INSERT)
新增資料時,同樣建議使用參數化查詢:
string sql = "INSERT INTO USERS (NAME, EMAIL, AGE, CREATE_DATE) VALUES (:name, :email, :age, :createDate)";
using (OracleCommand command = new OracleCommand(sql, connection))
{
command.Parameters.Add(":name", OracleDbType.Varchar2).Value = "王小明";
command.Parameters.Add(":email", OracleDbType.Varchar2).Value = "wang@example.com";
command.Parameters.Add(":age", OracleDbType.Int32).Value = 28;
command.Parameters.Add(":createDate", OracleDbType.Date).Value = DateTime.Now;
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"成功新增 {rowsAffected} 筆資料");
}
更新資料 (UPDATE)
更新現有資料的範例:
string sql = "UPDATE USERS SET EMAIL = :email, AGE = :age WHERE ID = :id";
using (OracleCommand command = new OracleCommand(sql, connection))
{
command.Parameters.Add(":email", OracleDbType.Varchar2).Value = "newemail@example.com";
command.Parameters.Add(":age", OracleDbType.Int32).Value = 30;
command.Parameters.Add(":id", OracleDbType.Int32).Value = 1;
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
Console.WriteLine("資料更新成功");
else
Console.WriteLine("找不到要更新的資料");
}
刪除資料 (DELETE)
刪除資料的操作:
string sql = "DELETE FROM USERS WHERE ID = :id";
using (OracleCommand command = new OracleCommand(sql, connection))
{
command.Parameters.Add(":id", OracleDbType.Int32).Value = 1;
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
Console.WriteLine("資料刪除成功");
else
Console.WriteLine("找不到要刪除的資料");
}
進階操作技巧
使用 DataTable 處理大量資料
當需要處理大量資料時,DataTable 是一個很好的選擇:
string sql = "SELECT * FROM USERS ORDER BY CREATE_DATE DESC";
using (OracleDataAdapter adapter = new OracleDataAdapter(sql, connection))
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
Console.WriteLine($"共查詢到 {dataTable.Rows.Count} 筆資料");
foreach (DataRow row in dataTable.Rows)
{
string name = row["NAME"].ToString();
string email = row["EMAIL"].ToString();
Console.WriteLine($"姓名: {name}, 信箱: {email}");
}
}
交易處理
對於需要確保資料一致性的操作,交易處理是必不可少的:
using (OracleTransaction transaction = connection.BeginTransaction())
{
try
{
// 第一個操作:新增使用者
string insertSql = "INSERT INTO USERS (NAME, EMAIL) VALUES (:name, :email)";
using (OracleCommand cmd1 = new OracleCommand(insertSql, connection, transaction))
{
cmd1.Parameters.Add(":name", OracleDbType.Varchar2).Value = "張三";
cmd1.Parameters.Add(":email", OracleDbType.Varchar2).Value = "zhang@example.com";
cmd1.ExecuteNonQuery();
}
// 第二個操作:更新統計資料
string updateSql = "UPDATE USER_STATS SET TOTAL_USERS = TOTAL_USERS + 1";
using (OracleCommand cmd2 = new OracleCommand(updateSql, connection, transaction))
{
cmd2.ExecuteNonQuery();
}
// 全部成功才提交
transaction.Commit();
Console.WriteLine("交易執行成功");
}
catch (Exception ex)
{
// 發生錯誤時回滾
transaction.Rollback();
Console.WriteLine($"交易執行失敗,已回滾:{ex.Message}");
throw;
}
}
呼叫預存程序
Oracle 預存程序的呼叫方式:
using (OracleCommand command = new OracleCommand("SP_GET_USER_INFO", connection))
{
command.CommandType = CommandType.StoredProcedure;
// 輸入參數
command.Parameters.Add("p_user_id", OracleDbType.Int32).Value = 1;
// 輸出參數
OracleParameter nameParam = new OracleParameter("p_user_name", OracleDbType.Varchar2, 100);
nameParam.Direction = ParameterDirection.Output;
command.Parameters.Add(nameParam);
OracleParameter emailParam = new OracleParameter("p_user_email", OracleDbType.Varchar2, 200);
emailParam.Direction = ParameterDirection.Output;
command.Parameters.Add(emailParam);
command.ExecuteNonQuery();
string userName = nameParam.Value?.ToString();
string userEmail = emailParam.Value?.ToString();
Console.WriteLine($"使用者姓名: {userName}");
Console.WriteLine($"使用者信箱: {userEmail}");
}
效能優化建議
1. 使用連線池
Oracle.ManagedDataAccess 預設啟用連線池,但您可以透過連線字串進行調整:
string connectionString = "Data Source=localhost:1521/XE;User Id=username;Password=password;" +
"Min Pool Size=5;Max Pool Size=100;Connection Lifetime=300;";
2. 批次處理
對於大量資料操作,使用批次處理可以顯著提升效能:
string sql = "INSERT INTO USERS (NAME, EMAIL) VALUES (:name, :email)";
using (OracleCommand command = new OracleCommand(sql, connection))
{
command.ArrayBindCount = 1000; // 批次大小
string[] names = new string[1000];
string[] emails = new string[1000];
// 填充資料陣列...
command.Parameters.Add(":name", OracleDbType.Varchar2, names, ParameterDirection.Input);
command.Parameters.Add(":email", OracleDbType.Varchar2, emails, ParameterDirection.Input);
int rowsAffected = command.ExecuteNonQuery();
}
常見問題與注意事項
1. 參數命名
Oracle 使用 :
作為參數前綴,而不是 SQL Server 的 @
:
// 正確 ✓
command.Parameters.Add(":userId", OracleDbType.Int32).Value = 1;
// 錯誤 ✗
command.Parameters.Add("@userId", OracleDbType.Int32).Value = 1;
2. 欄位名稱大小寫
Oracle 預設將欄位名稱轉換為大寫,在程式中存取時要注意:
// 通常需要使用大寫
string name = reader.GetString("NAME");
3. 資源釋放
務必使用 using
語句確保資源正確釋放:
using (var connection = new OracleConnection(connectionString))
using (var command = new OracleCommand(sql, connection))
using (var reader = command.ExecuteReader())
{
// 程式碼...
}
4. 例外處理
建議針對 Oracle 特定的例外進行處理:
try
{
// 資料庫操作
}
catch (OracleException ex)
{
Console.WriteLine($"Oracle 錯誤 ({ex.Number}): {ex.Message}");
}
catch (Exception ex)
{
Console.WriteLine($"一般錯誤: {ex.Message}");
}
完整範例
以下是一個完整的範例,展示如何建立一個簡單的使用者管理類別:
public class UserManager
{
private readonly string _connectionString;
public UserManager(string connectionString)
{
_connectionString = connectionString;
}
public async Task<List<User>> GetUsersAsync(int minAge = 0)
{
var users = new List<User>();
using (var connection = new OracleConnection(_connectionString))
{
await connection.OpenAsync();
string sql = "SELECT ID, NAME, EMAIL, AGE FROM USERS WHERE AGE >= :minAge ORDER BY NAME";
using (var command = new OracleCommand(sql, connection))
{
command.Parameters.Add(":minAge", OracleDbType.Int32).Value = minAge;
using (var reader = await command.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
users.Add(new User
{
Id = reader.GetInt32("ID"),
Name = reader.GetString("NAME"),
Email = reader.GetString("EMAIL"),
Age = reader.GetInt32("AGE")
});
}
}
}
}
return users;
}
public async Task<bool> CreateUserAsync(User user)
{
using (var connection = new OracleConnection(_connectionString))
{
await connection.OpenAsync();
string sql = "INSERT INTO USERS (NAME, EMAIL, AGE) VALUES (:name, :email, :age)";
using (var command = new OracleCommand(sql, connection))
{
command.Parameters.Add(":name", OracleDbType.Varchar2).Value = user.Name;
command.Parameters.Add(":email", OracleDbType.Varchar2).Value = user.Email;
command.Parameters.Add(":age", OracleDbType.Int32).Value = user.Age;
int rowsAffected = await command.ExecuteNonQueryAsync();
return rowsAffected > 0;
}
}
}
}
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string Email { get; set; }
public int Age { get; set; }
}
結論
透過本文的介紹,您應該已經掌握了在 C# 中操作 Oracle 資料庫的基本技能。記住以下重點:
- 始終使用參數化查詢防止 SQL 注入
- 適當使用
using
語句管理資源 - 對於複雜操作使用交易確保資料一致性
- 注意 Oracle 特有的語法和命名規則
- 在生產環境中做好例外處理和日誌記錄
掌握這些技巧後,您就能夠在企業級應用程式中有效地使用 Oracle 資料庫了。建議在實際專案中多加練習,熟能生巧!