C# 與 SQL Server 同步操作完整指南
前言
在現代應用程式開發中，資料庫操作是不可或缺的一環。本文將深入探討如何在 C# 中使用 SQL Server 進行同步操作，涵蓋從基礎連線到進階查詢的各種技術。無論您是剛開始學習資料庫程式設計的新手，還是希望複習基礎知識的資深開發者，本文都能提供實用的參考。
目錄
環境設定與基礎配置
安裝必要的 NuGet 套件
首先，我們需要在專案中安裝必要的 NuGet 套件。對於 SQL Server 操作，主要有兩個選擇：
- System.Data.SqlClient - 傳統方式，適用於 .NET Framework
- Microsoft.Data.SqlClient - 新版推薦，適用於 .NET Core/.NET 5+
在 .NET Core 或 .NET 5+ 專案中，建議使用 Microsoft.Data.SqlClient：
<PackageReference Include="Microsoft.Data.SqlClient" Version="5.1.5" />使用 Package Manager Console 安裝：
Install-Package Microsoft.Data.SqlClient或使用 .NET CLI：
dotnet add package Microsoft.Data.SqlClient專案結構建議
MyProject/
├── Models/
│ ├── Product.cs
│ ├── Order.cs
│ └── Customer.cs
├── Repositories/
│ ├── IProductRepository.cs
│ ├── ProductRepository.cs
│ └── BaseRepository.cs
├── Services/
│ └── DatabaseService.cs
├── Configuration/
│ └── DatabaseConfig.cs
└── Program.cs建立資料庫連線
連線字串配置
資料庫連線是所有操作的基礎。以下是不同情境下的連線字串範例：
// Windows 驗證
string connectionString = @"Server=.\SQLEXPRESS;Database=MyDatabase;Trusted_Connection=True;";
// SQL Server 驗證
string connectionString = @"Server=localhost;Database=MyDatabase;User Id=sa;Password=YourPassword;";
// 包含其他選項的完整連線字串
string connectionString = @"Server=localhost;Database=MyDatabase;
User Id=sa;Password=YourPassword;
Encrypt=True;TrustServerCertificate=True;
Connection Timeout=30;MultipleActiveResultSets=True;";連線管理類別
建立一個專門管理資料庫連線的類別：
using Microsoft.Data.SqlClient;
using System;
namespace MyProject.Configuration
{
public class DatabaseConfig
{
private readonly string _connectionString;
public DatabaseConfig(string connectionString)
{
if (string.IsNullOrWhiteSpace(connectionString))
throw new ArgumentException("連線字串不能為空", nameof(connectionString));
_connectionString = connectionString;
}
public SqlConnection CreateConnection()
{
return new SqlConnection(_connectionString);
}
public bool TestConnection()
{
try
{
using (var connection = CreateConnection())
{
connection.Open();
return true;
}
}
catch (SqlException)
{
return false;
}
}
}
}基本 CRUD 操作
定義資料模型
首先定義我們的資料模型：
namespace MyProject.Models
{
public class Product
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
public int Quantity { get; set; }
public DateTime CreatedDate { get; set; }
public DateTime? ModifiedDate { get; set; }
}
}查詢資料（SELECT）
實作查詢操作的完整範例：
using System;
using System.Collections.Generic;
using System.Data;
using Microsoft.Data.SqlClient;
using MyProject.Models;
namespace MyProject.Repositories
{
public class ProductRepository : IProductRepository
{
private readonly string _connectionString;
public ProductRepository(string connectionString)
{
_connectionString = connectionString;
}
// 取得所有產品
public List<Product> GetAllProducts()
{
var products = new List<Product>();
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"SELECT Id, Name, Description, Price, Quantity,
CreatedDate, ModifiedDate
FROM Products
ORDER BY Name";
using (var command = new SqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
products.Add(MapReaderToProduct(reader));
}
}
}
}
return products;
}
// 根據 ID 取得單一產品
public Product GetProductById(int id)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"SELECT Id, Name, Description, Price, Quantity,
CreatedDate, ModifiedDate
FROM Products
WHERE Id = @Id";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Id", id);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return MapReaderToProduct(reader);
}
return null;
}
}
}
}
// 搜尋產品
public List<Product> SearchProducts(string searchTerm)
{
var products = new List<Product>();
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"SELECT Id, Name, Description, Price, Quantity,
CreatedDate, ModifiedDate
FROM Products
WHERE Name LIKE @SearchTerm
OR Description LIKE @SearchTerm
ORDER BY Name";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@SearchTerm", $"%{searchTerm}%");
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
products.Add(MapReaderToProduct(reader));
}
}
}
}
return products;
}
// 輔助方法：將 SqlDataReader 對應到 Product 物件
private Product MapReaderToProduct(SqlDataReader reader)
{
return new Product
{
Id = reader.GetInt32(reader.GetOrdinal("Id")),
Name = reader.GetString(reader.GetOrdinal("Name")),
Description = reader.IsDBNull(reader.GetOrdinal("Description"))
? null
: reader.GetString(reader.GetOrdinal("Description")),
Price = reader.GetDecimal(reader.GetOrdinal("Price")),
Quantity = reader.GetInt32(reader.GetOrdinal("Quantity")),
CreatedDate = reader.GetDateTime(reader.GetOrdinal("CreatedDate")),
ModifiedDate = reader.IsDBNull(reader.GetOrdinal("ModifiedDate"))
? null
: reader.GetDateTime(reader.GetOrdinal("ModifiedDate"))
};
}
}
}新增資料（INSERT）
插入資料時，務必使用參數化查詢以防止 SQL 注入攻擊：
public int AddProduct(Product product)
{
if (product == null)
throw new ArgumentNullException(nameof(product));
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"INSERT INTO Products
(Name, Description, Price, Quantity, CreatedDate)
VALUES
(@Name, @Description, @Price, @Quantity, @CreatedDate);
SELECT SCOPE_IDENTITY();";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Name", product.Name);
command.Parameters.AddWithValue("@Description",
product.Description ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@Price", product.Price);
command.Parameters.AddWithValue("@Quantity", product.Quantity);
command.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
// 取得新增資料的 ID
var newId = Convert.ToInt32(command.ExecuteScalar());
product.Id = newId;
return newId;
}
}
}
// 批次新增多筆資料
public void AddProducts(List<Product> products)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var transaction = connection.BeginTransaction())
{
try
{
string query = @"INSERT INTO Products
(Name, Description, Price, Quantity, CreatedDate)
VALUES
(@Name, @Description, @Price, @Quantity, @CreatedDate)";
foreach (var product in products)
{
using (var command = new SqlCommand(query, connection, transaction))
{
command.Parameters.AddWithValue("@Name", product.Name);
command.Parameters.AddWithValue("@Description",
product.Description ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@Price", product.Price);
command.Parameters.AddWithValue("@Quantity", product.Quantity);
command.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
command.ExecuteNonQuery();
}
}
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
}更新資料（UPDATE）
更新操作同樣需要使用參數化查詢：
public bool UpdateProduct(Product product)
{
if (product == null)
throw new ArgumentNullException(nameof(product));
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"UPDATE Products
SET Name = @Name,
Description = @Description,
Price = @Price,
Quantity = @Quantity,
ModifiedDate = @ModifiedDate
WHERE Id = @Id";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Id", product.Id);
command.Parameters.AddWithValue("@Name", product.Name);
command.Parameters.AddWithValue("@Description",
product.Description ?? (object)DBNull.Value);
command.Parameters.AddWithValue("@Price", product.Price);
command.Parameters.AddWithValue("@Quantity", product.Quantity);
command.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0;
}
}
}
// 部分更新（只更新特定欄位）
public bool UpdateProductPrice(int productId, decimal newPrice)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"UPDATE Products
SET Price = @Price,
ModifiedDate = @ModifiedDate
WHERE Id = @Id";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Id", productId);
command.Parameters.AddWithValue("@Price", newPrice);
command.Parameters.AddWithValue("@ModifiedDate", DateTime.Now);
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0;
}
}
}刪除資料（DELETE）
刪除操作相對簡單，但要注意處理相關的外鍵約束：
public bool DeleteProduct(int id)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// 先檢查是否可以刪除（例如：檢查是否有相關訂單）
string checkQuery = @"SELECT COUNT(*) FROM OrderDetails WHERE ProductId = @Id";
using (var checkCommand = new SqlCommand(checkQuery, connection))
{
checkCommand.Parameters.AddWithValue("@Id", id);
int relatedOrders = (int)checkCommand.ExecuteScalar();
if (relatedOrders > 0)
{
throw new InvalidOperationException($"無法刪除產品，因為有 {relatedOrders} 筆相關訂單");
}
}
// 執行刪除
string deleteQuery = "DELETE FROM Products WHERE Id = @Id";
using (var command = new SqlCommand(deleteQuery, connection))
{
command.Parameters.AddWithValue("@Id", id);
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0;
}
}
}
// 軟刪除（標記為已刪除而非實際刪除）
public bool SoftDeleteProduct(int id)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
string query = @"UPDATE Products
SET IsDeleted = 1,
DeletedDate = @DeletedDate
WHERE Id = @Id";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Id", id);
command.Parameters.AddWithValue("@DeletedDate", DateTime.Now);
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0;
}
}
}進階技巧與最佳實踐
使用 SqlDataAdapter 和 DataTable
對於需要離線操作資料的情況，可以使用 SqlDataAdapter 和 DataTable：
public class DataTableExample
{
private readonly string _connectionString;
public DataTableExample(string connectionString)
{
_connectionString = connectionString;
}
// 載入資料到 DataTable
public DataTable GetProductsDataTable()
{
using (var connection = new SqlConnection(_connectionString))
{
string query = "SELECT * FROM Products WHERE IsDeleted = 0";
var adapter = new SqlDataAdapter(query, connection);
var dataTable = new DataTable("Products");
adapter.Fill(dataTable);
return dataTable;
}
}
// 使用 DataTable 進行批次更新
public void UpdateProductsDataTable(DataTable dataTable)
{
using (var connection = new SqlConnection(_connectionString))
{
var adapter = new SqlDataAdapter();
// 設定 SELECT 命令
adapter.SelectCommand = new SqlCommand(
"SELECT * FROM Products", connection);
// 自動產生 INSERT, UPDATE, DELETE 命令
var builder = new SqlCommandBuilder(adapter);
// 執行更新
adapter.Update(dataTable);
}
}
// 進階：自訂更新命令
public void CustomUpdateProductsDataTable(DataTable dataTable)
{
using (var connection = new SqlConnection(_connectionString))
{
var adapter = new SqlDataAdapter();
// SELECT 命令
adapter.SelectCommand = new SqlCommand(
"SELECT Id, Name, Price FROM Products", connection);
// UPDATE 命令
adapter.UpdateCommand = new SqlCommand(
@"UPDATE Products
SET Name = @Name, Price = @Price, ModifiedDate = GETDATE()
WHERE Id = @Id", connection);
adapter.UpdateCommand.Parameters.Add("@Name", SqlDbType.NVarChar, 100, "Name");
adapter.UpdateCommand.Parameters.Add("@Price", SqlDbType.Decimal, 0, "Price");
adapter.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
// 執行更新
adapter.Update(dataTable);
}
}
}交易處理
當需要執行多個相關聯的資料庫操作時，使用交易可以確保資料的一致性：
public class OrderService
{
private readonly string _connectionString;
public OrderService(string connectionString)
{
_connectionString = connectionString;
}
// 建立訂單（包含訂單主檔和明細）
public int CreateOrder(Order order)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlTransaction transaction = null;
try
{
transaction = connection.BeginTransaction();
// 1. 插入訂單主檔
string insertOrderQuery = @"
INSERT INTO Orders (CustomerId, OrderDate, TotalAmount)
VALUES (@CustomerId, @OrderDate, @TotalAmount);
SELECT SCOPE_IDENTITY();";
int orderId;
using (var command = new SqlCommand(insertOrderQuery, connection, transaction))
{
command.Parameters.AddWithValue("@CustomerId", order.CustomerId);
command.Parameters.AddWithValue("@OrderDate", order.OrderDate);
command.Parameters.AddWithValue("@TotalAmount", order.TotalAmount);
orderId = Convert.ToInt32(command.ExecuteScalar());
}
// 2. 插入訂單明細並更新庫存
foreach (var detail in order.OrderDetails)
{
// 插入訂單明細
string insertDetailQuery = @"
INSERT INTO OrderDetails (OrderId, ProductId, Quantity, Price)
VALUES (@OrderId, @ProductId, @Quantity, @Price)";
using (var command = new SqlCommand(insertDetailQuery, connection, transaction))
{
command.Parameters.AddWithValue("@OrderId", orderId);
command.Parameters.AddWithValue("@ProductId", detail.ProductId);
command.Parameters.AddWithValue("@Quantity", detail.Quantity);
command.Parameters.AddWithValue("@Price", detail.Price);
command.ExecuteNonQuery();
}
// 更新產品庫存
string updateStockQuery = @"
UPDATE Products
SET Quantity = Quantity - @Quantity
WHERE Id = @ProductId AND Quantity >= @Quantity";
using (var command = new SqlCommand(updateStockQuery, connection, transaction))
{
command.Parameters.AddWithValue("@ProductId", detail.ProductId);
command.Parameters.AddWithValue("@Quantity", detail.Quantity);
int affected = command.ExecuteNonQuery();
if (affected == 0)
{
throw new InvalidOperationException(
$"產品 {detail.ProductId} 庫存不足");
}
}
}
// 3. 記錄交易日誌
string logQuery = @"
INSERT INTO TransactionLog (OrderId, Action, ActionDate)
VALUES (@OrderId, @Action, @ActionDate)";
using (var command = new SqlCommand(logQuery, connection, transaction))
{
command.Parameters.AddWithValue("@OrderId", orderId);
command.Parameters.AddWithValue("@Action", "OrderCreated");
command.Parameters.AddWithValue("@ActionDate", DateTime.Now);
command.ExecuteNonQuery();
}
// 提交交易
transaction.Commit();
return orderId;
}
catch (Exception ex)
{
// 發生錯誤時回滾交易
transaction?.Rollback();
throw new Exception("建立訂單失敗", ex);
}
}
}
// 使用儲存點的複雜交易
public void ComplexTransaction()
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
SqlTransaction transaction = connection.BeginTransaction();
try
{
// 第一個操作
ExecuteCommand("INSERT INTO Table1...", connection, transaction);
// 設定儲存點
transaction.Save("SavePoint1");
try
{
// 第二個操作（可能失敗）
ExecuteCommand("INSERT INTO Table2...", connection, transaction);
}
catch
{
// 只回滾到儲存點
transaction.Rollback("SavePoint1");
}
// 第三個操作
ExecuteCommand("INSERT INTO Table3...", connection, transaction);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
private void ExecuteCommand(string query, SqlConnection connection, SqlTransaction transaction)
{
using (var command = new SqlCommand(query, connection, transaction))
{
command.ExecuteNonQuery();
}
}
}儲存程序的使用
儲存程序可以提高效能並增強安全性：
public class StoredProcedureExample
{
private readonly string _connectionString;
public StoredProcedureExample(string connectionString)
{
_connectionString = connectionString;
}
// 呼叫簡單的儲存程序
public List<Product> GetProductsByCategory(int categoryId)
{
var products = new List<Product>();
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = new SqlCommand("sp_GetProductsByCategory", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CategoryId", categoryId);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
products.Add(MapReaderToProduct(reader));
}
}
}
}
return products;
}
// 呼叫具有輸出參數的儲存程序
public void GetProductStats(out int totalProducts, out decimal averagePrice)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = new SqlCommand("sp_GetProductStats", connection))
{
command.CommandType = CommandType.StoredProcedure;
// 輸出參數
var totalParam = new SqlParameter("@TotalProducts", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
var avgParam = new SqlParameter("@AveragePrice", SqlDbType.Decimal)
{
Direction = ParameterDirection.Output,
Precision = 18,
Scale = 2
};
command.Parameters.Add(totalParam);
command.Parameters.Add(avgParam);
command.ExecuteNonQuery();
totalProducts = (int)totalParam.Value;
averagePrice = (decimal)avgParam.Value;
}
}
}
// 呼叫回傳值的儲存程序
public int ExecuteStoredProcWithReturnValue(string procedureName, params SqlParameter[] parameters)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var command = new SqlCommand(procedureName, connection))
{
command.CommandType = CommandType.StoredProcedure;
// 加入參數
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
// 加入回傳值參數
var returnParam = new SqlParameter("@ReturnValue", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
command.Parameters.Add(returnParam);
command.ExecuteNonQuery();
return (int)returnParam.Value;
}
}
}
private Product MapReaderToProduct(SqlDataReader reader)
{
// 實作省略（同前面的範例）
return new Product();
}
}對應的儲存程序範例：
-- 簡單查詢儲存程序
CREATE PROCEDURE sp_GetProductsByCategory
@CategoryId INT
AS
BEGIN
SET NOCOUNT ON;
SELECT p.Id, p.Name, p.Description, p.Price, p.Quantity,
p.CreatedDate, p.ModifiedDate
FROM Products p
INNER JOIN ProductCategories pc ON p.Id = pc.ProductId
WHERE pc.CategoryId = @CategoryId
AND p.IsDeleted = 0
ORDER BY p.Name;
END
GO
-- 具有輸出參數的儲存程序
CREATE PROCEDURE sp_GetProductStats
@TotalProducts INT OUTPUT,
@AveragePrice DECIMAL(18,2) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT @TotalProducts = COUNT(*),
@AveragePrice = AVG(Price)
FROM Products
WHERE IsDeleted = 0;
END
GO
-- 具有回傳值的儲存程序
CREATE PROCEDURE sp_UpdateProductStock
@ProductId INT,
@Quantity INT,
@Operation CHAR(1) -- 'A' for Add, 'R' for Remove
AS
BEGIN
SET NOCOUNT ON;
DECLARE @CurrentStock INT;
-- 取得目前庫存
SELECT @CurrentStock = Quantity
FROM Products
WHERE Id = @ProductId;
-- 檢查產品是否存在
IF @CurrentStock IS NULL
RETURN -1; -- 產品不存在
-- 更新庫存
IF @Operation = 'A'
BEGIN
UPDATE Products
SET Quantity = Quantity + @Quantity,
ModifiedDate = GETDATE()
WHERE Id = @ProductId;
END
ELSE IF @Operation = 'R'
BEGIN
-- 檢查庫存是否足夠
IF @CurrentStock < @Quantity
RETURN -2; -- 庫存不足
UPDATE Products
SET Quantity = Quantity - @Quantity,
ModifiedDate = GETDATE()
WHERE Id = @ProductId;
END
ELSE
RETURN -3; -- 無效的操作
RETURN 0; -- 成功
END
GO效能優化建議
連線池管理
SQL Server 預設會使用連線池，但我們可以透過連線字串進行調整：
public class ConnectionPoolingExample
{
// 優化的連線字串
private const string OptimizedConnectionString = @"
Server=localhost;
Database=MyDatabase;
User Id=sa;
Password=YourPassword;
Min Pool Size=5;
Max Pool Size=100;
Connect Timeout=30;
Pooling=true;
MultipleActiveResultSets=true;";
// 監控連線池狀態
public void MonitorConnectionPool()
{
using (var connection = new SqlConnection(OptimizedConnectionString))
{
connection.StateChange += (sender, e) =>
{
Console.WriteLine($"連線狀態變更: {e.OriginalState} -> {e.CurrentState}");
};
connection.Open();
// 取得連線池資訊
var poolInfo = connection.RetrieveStatistics();
Console.WriteLine($"連線池統計資訊:");
Console.WriteLine($"- 目前連線數: {poolInfo["NumberOfActiveConnections"]}");
Console.WriteLine($"- 池中連線數: {poolInfo["NumberOfPooledConnections"]}");
Console.WriteLine($"- 非池連線數: {poolInfo["NumberOfNonPooledConnections"]}");
}
}
}批次操作
當需要插入大量資料時，使用批次操作可以顯著提升效能：
public class BulkOperations
{
private readonly string _connectionString;
public BulkOperations(string connectionString)
{
_connectionString = connectionString;
}
// 使用 SqlBulkCopy 進行大量插入
public void BulkInsertProducts(List<Product> products)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
using (var bulkCopy = new SqlBulkCopy(connection))
{
// 設定目標資料表
bulkCopy.DestinationTableName = "Products";
// 設定批次大小和逾時時間
bulkCopy.BatchSize = 1000;
bulkCopy.BulkCopyTimeout = 300; // 5 分鐘
// 建立 DataTable
var dataTable = CreateProductDataTable(products);
// 設定欄位對應
bulkCopy.ColumnMappings.Add("Name", "Name");
bulkCopy.ColumnMappings.Add("Description", "Description");
bulkCopy.ColumnMappings.Add("Price", "Price");
bulkCopy.ColumnMappings.Add("Quantity", "Quantity");
bulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate");
// 事件處理
bulkCopy.SqlRowsCopied += (sender, e) =>
{
Console.WriteLine($"已複製 {e.RowsCopied} 筆資料");
};
// 執行批次插入
bulkCopy.WriteToServer(dataTable);
}
}
}
// 進階：使用 Table-Valued Parameters
public void BulkInsertWithTVP(List<Product> products)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// 建立 DataTable
var table = CreateProductDataTable(products);
using (var command = new SqlCommand("sp_BulkInsertProducts", connection))
{
command.CommandType = CommandType.StoredProcedure;
// 使用 Table-Valued Parameter
var parameter = command.Parameters.AddWithValue("@Products", table);
parameter.SqlDbType = SqlDbType.Structured;
parameter.TypeName = "dbo.ProductTableType";
command.ExecuteNonQuery();
}
}
}
// 批次更新
public void BatchUpdateProducts(List<Product> products)
{
const int batchSize = 100;
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
for (int i = 0; i < products.Count; i += batchSize)
{
var batch = products.Skip(i).Take(batchSize);
var query = BuildBatchUpdateQuery(batch);
using (var command = new SqlCommand(query, connection))
{
AddBatchParameters(command, batch);
command.ExecuteNonQuery();
}
}
}
}
private DataTable CreateProductDataTable(List<Product> products)
{
var dataTable = new DataTable();
// 定義結構
dataTable.Columns.Add("Name", typeof(string));
dataTable.Columns.Add("Description", typeof(string));
dataTable.Columns.Add("Price", typeof(decimal));
dataTable.Columns.Add("Quantity", typeof(int));
dataTable.Columns.Add("CreatedDate", typeof(DateTime));
// 加入資料
foreach (var product in products)
{
dataTable.Rows.Add(
product.Name,
product.Description,
product.Price,
product.Quantity,
DateTime.Now
);
}
return dataTable;
}
private string BuildBatchUpdateQuery(IEnumerable<Product> products)
{
var sb = new StringBuilder();
int index = 0;
foreach (var product in products)
{
sb.AppendLine($@"
UPDATE Products
SET Name = @Name{index},
Price = @Price{index},
Quantity = @Quantity{index},
ModifiedDate = GETDATE()
WHERE Id = @Id{index};");
index++;
}
return sb.ToString();
}
private void AddBatchParameters(SqlCommand command, IEnumerable<Product> products)
{
int index = 0;
foreach (var product in products)
{
command.Parameters.AddWithValue($"@Id{index}", product.Id);
command.Parameters.AddWithValue($"@Name{index}", product.Name);
command.Parameters.AddWithValue($"@Price{index}", product.Price);
command.Parameters.AddWithValue($"@Quantity{index}", product.Quantity);
index++;
}
}
}對應的 SQL Server 設定：
-- 建立 Table Type
CREATE TYPE dbo.ProductTableType AS TABLE
(
Name NVARCHAR(100),
Description NVARCHAR(500),
Price DECIMAL(18,2),
Quantity INT,
CreatedDate DATETIME
);
GO
-- 使用 Table-Valued Parameter 的儲存程序
CREATE PROCEDURE sp_BulkInsertProducts
@Products dbo.ProductTableType READONLY
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Products (Name, Description, Price, Quantity, CreatedDate)
SELECT Name, Description, Price, Quantity, CreatedDate
FROM @Products;
END
GO查詢優化技巧
public class QueryOptimization
{
private readonly string _connectionString;
public QueryOptimization(string connectionString)
{
_connectionString = connectionString;
}
// 使用分頁查詢
public List<Product> GetProductsPaged(int pageNumber, int pageSize, string sortColumn = "Id")
{
var products = new List<Product>();
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// 使用 OFFSET-FETCH (SQL Server 2012+)
string query = $@"
SELECT Id, Name, Description, Price, Quantity, CreatedDate, ModifiedDate
FROM Products
WHERE IsDeleted = 0
ORDER BY {sortColumn}
OFFSET @Offset ROWS
FETCH NEXT @PageSize ROWS ONLY";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Offset", (pageNumber - 1) * pageSize);
command.Parameters.AddWithValue("@PageSize", pageSize);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
products.Add(MapReaderToProduct(reader));
}
}
}
}
return products;
}
// 使用 NOLOCK 提示（謹慎使用）
public List<Product> GetProductsNoLock()
{
var products = new List<Product>();
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// NOLOCK 可能讀取到未提交的資料
string query = @"
SELECT Id, Name, Price, Quantity
FROM Products WITH (NOLOCK)
WHERE IsDeleted = 0";
using (var command = new SqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
products.Add(MapReaderToProduct(reader));
}
}
}
}
return products;
}
// 使用索引提示
public Product GetProductByIdWithIndex(int id)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// 強制使用特定索引
string query = @"
SELECT Id, Name, Price, Quantity
FROM Products WITH (INDEX(IX_Products_Id))
WHERE Id = @Id";
using (var command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Id", id);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return MapReaderToProduct(reader);
}
return null;
}
}
}
}
// 執行計畫分析
public void AnalyzeQueryPlan(string query)
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
// 開啟執行計畫
using (var command = new SqlCommand("SET STATISTICS IO ON", connection))
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand("SET STATISTICS TIME ON", connection))
{
command.ExecuteNonQuery();
}
// 執行查詢
using (var command = new SqlCommand(query, connection))
{
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
// 讀取資料
}
}
}
// 關閉執行計畫
using (var command = new SqlCommand("SET STATISTICS IO OFF", connection))
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand("SET STATISTICS TIME OFF", connection))
{
command.ExecuteNonQuery();
}
}
}
private Product MapReaderToProduct(SqlDataReader reader)
{
// 實作省略
return new Product();
}
}錯誤處理與日誌記錄
完善的錯誤處理機制是穩定應用程式的關鍵：
using System;
using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Logging;
public class RobustDatabaseService
{
private readonly ILogger<RobustDatabaseService> _logger;
private readonly string _connectionString;
private readonly int _commandTimeout;
public RobustDatabaseService(
ILogger<RobustDatabaseService> logger,
string connectionString,
int commandTimeout = 30)
{
_logger = logger;
_connectionString = connectionString;
_commandTimeout = commandTimeout;
}
// 通用的執行方法，包含重試邏輯
public T ExecuteWithRetry<T>(
Func<SqlConnection, T> operation,
int maxRetries = 3,
int delayMilliseconds = 1000)
{
int retryCount = 0;
while (retryCount < maxRetries)
{
try
{
using (var connection = new SqlConnection(_connectionString))
{
connection.Open();
_logger.LogDebug("開啟資料庫連線成功");
var result = operation(connection);
_logger.LogDebug("資料庫操作執行成功");
return result;
}
}
catch (SqlException ex) when (IsTransientError(ex))
{
retryCount++;
if (retryCount >= maxRetries)
{
_logger.LogError(ex,
"達到最大重試次數 {MaxRetries}，操作失敗",
maxRetries);
throw;
}
int delay = delayMilliseconds * retryCount;
_logger.LogWarning(
"發生暫時性錯誤 {ErrorNumber}: {Message}，" +
"將在 {Delay} 毫秒後重試 ({RetryCount}/{MaxRetries})",
ex.Number, ex.Message, delay, retryCount, maxRetries);
System.Threading.Thread.Sleep(delay);
}
catch (SqlException ex)
{
// 非暫時性錯誤，記錄並拋出
LogSqlException(ex);
throw;
}
catch (Exception ex)
{
_logger.LogError(ex, "執行資料庫操作時發生未預期的錯誤");
throw;
}
}
throw new InvalidOperationException("不應該到達這裡");
}
// 判斷是否為暫時性錯誤
private bool IsTransientError(SqlException ex)
{
// SQL Server 暫時性錯誤代碼
int[] transientErrors = new[]
{
1205, // Deadlock
1222, // Lock request timeout
2601, // Duplicate key (在某些情況下可重試)
8645, // Timeout waiting for memory resource
49918, // Cannot process request. Not enough resources
49919, // Cannot process create or update request
49920, // Cannot process request. Too many operations
4060, // Cannot open database
40143, // Connection was terminated
40613, // Database is currently unavailable
40501, // Service is currently busy
40540, // Service has encountered an error
40197, // Service has encountered an error processing request
10929, // Resource ID exceeded limit
10928, // Resource limit reached
10060, // Network error
10054, // Connection forcibly closed
10053, // Connection aborted
233, // Connection initialization error
64 // Connection was successfully established but then failed
};
return Array.Exists(transientErrors, e => e == ex.Number);
}
// 詳細記錄 SQL 異常
private void LogSqlException(SqlException ex)
{
_logger.LogError(ex,
"SQL 錯誤 - 編號: {Number}, 嚴重性: {Class}, " +
"狀態: {State}, 程序: {Procedure}, 行號: {LineNumber}",
ex.Number, ex.Class, ex.State,
ex.Procedure ?? "N/A", ex.LineNumber);
// 記錄所有錯誤
foreach (SqlError error in ex.Errors)
{
_logger.LogError(
"SQL 錯誤詳細: 編號 {Number}, 訊息: {Message}, " +
"來源: {Source}, 程序: {Procedure}",
error.Number, error.Message,
error.Source, error.Procedure ?? "N/A");
}
}
// 安全執行查詢，包含參數記錄
public T ExecuteQuery<T>(
string query,
Func<SqlDataReader, T> mapper,
params SqlParameter[] parameters)
{
return ExecuteWithRetry(connection =>
{
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = _commandTimeout;
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
// 記錄查詢和參數（注意不要記錄敏感資料）
_logger.LogDebug("執行查詢: {Query}", query);
foreach (var param in parameters)
{
if (!IsSensitiveParameter(param.ParameterName))
{
_logger.LogDebug("參數 {Name} = {Value}",
param.ParameterName, param.Value);
}
}
}
using (var reader = command.ExecuteReader())
{
return mapper(reader);
}
}
});
}
// 判斷是否為敏感參數
private bool IsSensitiveParameter(string parameterName)
{
string[] sensitiveNames = { "password", "pwd", "secret", "token", "key" };
return Array.Exists(sensitiveNames,
name => parameterName.ToLower().Contains(name));
}
// 執行非查詢命令，回傳受影響的資料列數
public int ExecuteNonQuery(string query, params SqlParameter[] parameters)
{
return ExecuteWithRetry(connection =>
{
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = _commandTimeout;
if (parameters != null && parameters.Length > 0)
{
command.Parameters.AddRange(parameters);
}
var affected = command.ExecuteNonQuery();
_logger.LogInformation(
"執行非查詢命令完成，影響 {RowCount} 筆資料",
affected);
return affected;
}
});
}
}
// 自訂例外類別
public class DatabaseOperationException : Exception
{
public string OperationType { get; set; }
public string TableName { get; set; }
public int? ErrorCode { get; set; }
public DatabaseOperationException(
string message,
string operationType,
string tableName,
Exception innerException = null)
: base(message, innerException)
{
OperationType = operationType;
TableName = tableName;
if (innerException is SqlException sqlEx)
{
ErrorCode = sqlEx.Number;
}
}
}實戰範例：訂單管理系統
讓我們整合前面學到的概念，建立一個完整的訂單管理系統：
// 資料模型
public class Order
{
public int Id { get; set; }
public int CustomerId { get; set; }
public DateTime OrderDate { get; set; }
public decimal TotalAmount { get; set; }
public OrderStatus Status { get; set; }
public List<OrderDetail> OrderDetails { get; set; }
}
public class OrderDetail
{
public int Id { get; set; }
public int OrderId { get; set; }
public int ProductId { get; set; }
public int Quantity { get; set; }
public decimal Price { get; set; }
public decimal Subtotal => Quantity * Price;
}
public enum OrderStatus
{
Pending = 0,
Processing = 1,
Shipped = 2,
Delivered = 3,
Cancelled = 4
}
// Repository 介面
public interface IOrderRepository
{
int CreateOrder(Order order);
Order GetOrderById(int orderId);
List<Order> GetOrdersByCustomer(int customerId);
bool UpdateOrderStatus(int orderId, OrderStatus status);
OrderSummary GetOrderSummary(DateTime startDate, DateTime endDate);
}
// Repository 實作
public class OrderRepository : IOrderRepository
{
private readonly RobustDatabaseService _dbService;
private readonly ILogger<OrderRepository> _logger;
public OrderRepository(
RobustDatabaseService dbService,
ILogger<OrderRepository> logger)
{
_dbService = dbService;
_logger = logger;
}
public int CreateOrder(Order order)
{
return _dbService.ExecuteWithRetry(connection =>
{
using (var transaction = connection.BeginTransaction())
{
try
{
// 插入訂單主檔
int orderId;
string insertOrderQuery = @"
INSERT INTO Orders (CustomerId, OrderDate, TotalAmount, Status)
VALUES (@CustomerId, @OrderDate, @TotalAmount, @Status);
SELECT SCOPE_IDENTITY();";
using (var command = new SqlCommand(insertOrderQuery, connection, transaction))
{
command.Parameters.AddWithValue("@CustomerId", order.CustomerId);
command.Parameters.AddWithValue("@OrderDate", order.OrderDate);
command.Parameters.AddWithValue("@TotalAmount", order.TotalAmount);
command.Parameters.AddWithValue("@Status", (int)order.Status);
orderId = Convert.ToInt32(command.ExecuteScalar());
}
// 插入訂單明細
foreach (var detail in order.OrderDetails)
{
string insertDetailQuery = @"
INSERT INTO OrderDetails
(OrderId, ProductId, Quantity, Price)
VALUES
(@OrderId, @ProductId, @Quantity, @Price)";
using (var command = new SqlCommand(insertDetailQuery, connection, transaction))
{
command.Parameters.AddWithValue("@OrderId", orderId);
command.Parameters.AddWithValue("@ProductId", detail.ProductId);
command.Parameters.AddWithValue("@Quantity", detail.Quantity);
command.Parameters.AddWithValue("@Price", detail.Price);
command.ExecuteNonQuery();
}
// 更新庫存
if (!UpdateProductStock(
connection, transaction, detail.ProductId, detail.Quantity))
{
throw new InvalidOperationException(
$"產品 {detail.ProductId} 庫存不足");
}
}
transaction.Commit();
_logger.LogInformation(
"成功建立訂單 {OrderId}，客戶 {CustomerId}，金額 {Amount}",
orderId, order.CustomerId, order.TotalAmount);
return orderId;
}
catch (Exception ex)
{
transaction.Rollback();
_logger.LogError(ex, "建立訂單失敗");
throw;
}
}
});
}
public Order GetOrderById(int orderId)
{
return _dbService.ExecuteWithRetry(connection =>
{
Order order = null;
// 查詢訂單主檔
string orderQuery = @"
SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE Id = @OrderId";
using (var command = new SqlCommand(orderQuery, connection))
{
command.Parameters.AddWithValue("@OrderId", orderId);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
order = new Order
{
Id = reader.GetInt32(0),
CustomerId = reader.GetInt32(1),
OrderDate = reader.GetDateTime(2),
TotalAmount = reader.GetDecimal(3),
Status = (OrderStatus)reader.GetInt32(4),
OrderDetails = new List<OrderDetail>()
};
}
}
}
if (order != null)
{
// 查詢訂單明細
string detailQuery = @"
SELECT Id, ProductId, Quantity, Price
FROM OrderDetails
WHERE OrderId = @OrderId";
using (var command = new SqlCommand(detailQuery, connection))
{
command.Parameters.AddWithValue("@OrderId", orderId);
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
order.OrderDetails.Add(new OrderDetail
{
Id = reader.GetInt32(0),
OrderId = orderId,
ProductId = reader.GetInt32(1),
Quantity = reader.GetInt32(2),
Price = reader.GetDecimal(3)
});
}
}
}
}
return order;
});
}
public List<Order> GetOrdersByCustomer(int customerId)
{
return _dbService.ExecuteQuery(
@"SELECT Id, CustomerId, OrderDate, TotalAmount, Status
FROM Orders
WHERE CustomerId = @CustomerId
ORDER BY OrderDate DESC",
reader =>
{
var orders = new List<Order>();
while (reader.Read())
{
orders.Add(new Order
{
Id = reader.GetInt32(0),
CustomerId = reader.GetInt32(1),
OrderDate = reader.GetDateTime(2),
TotalAmount = reader.GetDecimal(3),
Status = (OrderStatus)reader.GetInt32(4)
});
}
return orders;
},
new SqlParameter("@CustomerId", customerId)
);
}
public bool UpdateOrderStatus(int orderId, OrderStatus status)
{
var affected = _dbService.ExecuteNonQuery(
@"UPDATE Orders
SET Status = @Status, ModifiedDate = GETDATE()
WHERE Id = @OrderId",
new SqlParameter("@OrderId", orderId),
new SqlParameter("@Status", (int)status)
);
return affected > 0;
}
public OrderSummary GetOrderSummary(DateTime startDate, DateTime endDate)
{
return _dbService.ExecuteWithRetry(connection =>
{
using (var command = new SqlCommand("sp_GetOrderSummary", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@StartDate", startDate);
command.Parameters.AddWithValue("@EndDate", endDate);
using (var reader = command.ExecuteReader())
{
if (reader.Read())
{
return new OrderSummary
{
TotalOrders = reader.GetInt32(0),
TotalRevenue = reader.GetDecimal(1),
AverageOrderValue = reader.GetDecimal(2),
TopSellingProductId = reader.GetInt32(3),
TopSellingProductName = reader.GetString(4)
};
}
return null;
}
}
});
}
private bool UpdateProductStock(
SqlConnection connection,
SqlTransaction transaction,
int productId,
int quantity)
{
string updateQuery = @"
UPDATE Products
SET Quantity = Quantity - @Quantity
WHERE Id = @ProductId AND Quantity >= @Quantity";
using (var command = new SqlCommand(updateQuery, connection, transaction))
{
command.Parameters.AddWithValue("@ProductId", productId);
command.Parameters.AddWithValue("@Quantity", quantity);
return command.ExecuteNonQuery() > 0;
}
}
}
public class OrderSummary
{
public int TotalOrders { get; set; }
public decimal TotalRevenue { get; set; }
public decimal AverageOrderValue { get; set; }
public int TopSellingProductId { get; set; }
public string TopSellingProductName { get; set; }
}最佳實踐總結
1. 連線管理
- 始終使用
using語句確保連線正確關閉 - 利用連線池提高效能
- 避免長時間持有連線
2. 安全性
- 永遠使用參數化查詢防止 SQL 注入
- 不要在程式碼中硬編碼連線字串
- 適當處理敏感資料的日誌記錄
3. 效能優化
- 只查詢需要的欄位，避免
SELECT * - 使用適當的索引
- 考慮使用批次操作處理大量資料
- 實施查詢結果快取策略
4. 錯誤處理
- 實施重試邏輯處理暫時性錯誤
- 提供有意義的錯誤訊息
- 記錄詳細的錯誤資訊以便除錯
5. 程式碼組織
- 使用 Repository 模式分離資料存取邏輯
- 實作介面以提高可測試性
- 保持方法簡潔，每個方法只做一件事
結論
本文詳細介紹了在 C# 中使用 SQL Server 進行同步操作的各種技術和最佳實踐。從基本的 CRUD 操作到進階的交易處理、批次操作和效能優化，這些技術構成了資料庫應用程式開發的基礎。
雖然現代開發越來越傾向使用 ORM 框架如 Entity Framework Core，但理解底層的 ADO.NET 操作仍然非常重要。這不僅有助於解決複雜的效能問題，也能讓您在需要時寫出更高效的資料存取程式碼。
記住，選擇同步或非同步操作應該基於您的應用程式需求。對於桌面應用程式或簡單的批次處理程式，同步操作可能更簡單直接。但對於高並發的 Web 應用程式，考慮使用非同步操作將能提供更好的擴展性。
希望這篇文章能為您的 C# 資料庫程式設計之旅提供實用的參考。持續學習和實踐，您將能夠建立出高效、穩定且易於維護的資料庫應用程式。