C# SQL Server 資料庫操作完整指南

SQL Server 是微軟生態系統中最重要的資料庫系統之一，與 C# 的整合度非常高。本文將深入介紹如何在 C# 中高效操作 SQL Server 資料庫，從基礎連線到進階功能，讓您成為資料庫操作的專家。

環境準備

安裝必要套件

在 .NET 專案中，您可以使用以下幾種方式連接 SQL Server：

方法一：使用內建的 System.Data.SqlClient

// .NET Framework 已內建，無需額外安裝

方法二：使用新版 Microsoft.Data.SqlClient（推薦）

Install-Package Microsoft.Data.SqlClient

方法三：使用 Entity Framework Core

Install-Package Microsoft.EntityFrameworkCore.SqlServer

本文主要介紹使用 Microsoft.Data.SqlClient 的方式，這是微軟推薦的新版驅動程式。

資料庫連線

連線字串設定

SQL Server 的連線字串有多種格式，以下是常見的幾種：

using Microsoft.Data.SqlClient;

// 基本連線字串（SQL Server 驗證）
string connectionString = "Server=localhost;Database=TestDB;User Id=sa;Password=yourpassword;";

// Windows 驗證
string connectionString = "Server=localhost;Database=TestDB;Integrated Security=true;";

// 包含加密和信任憑證
string connectionString = "Server=localhost;Database=TestDB;Integrated Security=true;Encrypt=true;TrustServerCertificate=true;";

建立連線

using Microsoft.Data.SqlClient;

public async Task TestConnectionAsync()
{
    string connectionString = "Server=localhost;Database=TestDB;Integrated Security=true;";
    
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        try
        {
            await connection.OpenAsync();
            Console.WriteLine("資料庫連線成功！");
            Console.WriteLine($"資料庫版本: {connection.ServerVersion}");
        }
        catch (SqlException ex)
        {
            Console.WriteLine($"連線失敗: {ex.Message}");
        }
    }
}

基本 CRUD 操作

查詢資料 (SELECT)

基本查詢

public async Task<List<User>> GetUsersAsync(int minAge = 0)
{
    var users = new List<User>();
    string sql = @"
        SELECT Id, Name, Email, Age, CreateDate 
        FROM Users 
        WHERE Age >= @MinAge 
        ORDER BY Name";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@MinAge", 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.IsDBNull("Email") ? null : reader.GetString("Email"),
                        Age = reader.GetInt32("Age"),
                        CreateDate = reader.GetDateTime("CreateDate")
                    });
                }
            }
        }
    }
    
    return users;
}

查詢單筆資料

public async Task<User> GetUserByIdAsync(int userId)
{
    string sql = "SELECT Id, Name, Email, Age, CreateDate FROM Users WHERE Id = @UserId";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@UserId", userId);
            
            using (var reader = await command.ExecuteReaderAsync())
            {
                if (await reader.ReadAsync())
                {
                    return new User
                    {
                        Id = reader.GetInt32("Id"),
                        Name = reader.GetString("Name"),
                        Email = reader.IsDBNull("Email") ? null : reader.GetString("Email"),
                        Age = reader.GetInt32("Age"),
                        CreateDate = reader.GetDateTime("CreateDate")
                    };
                }
            }
        }
    }
    
    return null;
}

新增資料 (INSERT)

基本新增

public async Task<int> CreateUserAsync(User user)
{
    string sql = @"
        INSERT INTO Users (Name, Email, Age, CreateDate) 
        VALUES (@Name, @Email, @Age, @CreateDate);
        SELECT CAST(SCOPE_IDENTITY() AS INT);";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@Name", user.Name);
            command.Parameters.AddWithValue("@Email", user.Email ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Age", user.Age);
            command.Parameters.AddWithValue("@CreateDate", DateTime.Now);
            
            // 取得新增記錄的 ID
            var result = await command.ExecuteScalarAsync();
            return Convert.ToInt32(result);
        }
    }
}

批次新增

public async Task<bool> CreateUsersAsync(List<User> users)
{
    string sql = @"
        INSERT INTO Users (Name, Email, Age, CreateDate) 
        VALUES (@Name, @Email, @Age, @CreateDate)";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                foreach (var user in users)
                {
                    using (var command = new SqlCommand(sql, connection, transaction))
                    {
                        command.Parameters.AddWithValue("@Name", user.Name);
                        command.Parameters.AddWithValue("@Email", user.Email ?? (object)DBNull.Value);
                        command.Parameters.AddWithValue("@Age", user.Age);
                        command.Parameters.AddWithValue("@CreateDate", DateTime.Now);
                        
                        await command.ExecuteNonQueryAsync();
                    }
                }
                
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }
    }
}

更新資料 (UPDATE)

public async Task<bool> UpdateUserAsync(User user)
{
    string sql = @"
        UPDATE Users 
        SET Name = @Name, Email = @Email, Age = @Age, UpdateDate = @UpdateDate
        WHERE Id = @Id";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@Id", user.Id);
            command.Parameters.AddWithValue("@Name", user.Name);
            command.Parameters.AddWithValue("@Email", user.Email ?? (object)DBNull.Value);
            command.Parameters.AddWithValue("@Age", user.Age);
            command.Parameters.AddWithValue("@UpdateDate", DateTime.Now);
            
            int rowsAffected = await command.ExecuteNonQueryAsync();
            return rowsAffected > 0;
        }
    }
}

刪除資料 (DELETE)

軟刪除（推薦）

public async Task<bool> SoftDeleteUserAsync(int userId)
{
    string sql = @"
        UPDATE Users 
        SET IsDeleted = 1, DeleteDate = @DeleteDate 
        WHERE Id = @UserId AND IsDeleted = 0";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@UserId", userId);
            command.Parameters.AddWithValue("@DeleteDate", DateTime.Now);
            
            int rowsAffected = await command.ExecuteNonQueryAsync();
            return rowsAffected > 0;
        }
    }
}

實體刪除

public async Task<bool> DeleteUserAsync(int userId)
{
    string sql = "DELETE FROM Users WHERE Id = @UserId";
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@UserId", userId);
            
            int rowsAffected = await command.ExecuteNonQueryAsync();
            return rowsAffected > 0;
        }
    }
}

進階操作技巧

使用 DataTable 和 DataAdapter

當需要處理大量資料或進行複雜的資料操作時，DataTable 是很好的選擇：

public async Task<DataTable> GetUsersDataTableAsync(string searchTerm = "")
{
    string sql = @"
        SELECT Id, Name, Email, Age, CreateDate 
        FROM Users 
        WHERE (@SearchTerm = '' OR Name LIKE '%' + @SearchTerm + '%' OR Email LIKE '%' + @SearchTerm + '%')
        ORDER BY CreateDate DESC";
    
    using (var connection = new SqlConnection(connectionString))
    {
        using (var adapter = new SqlDataAdapter(sql, connection))
        {
            adapter.SelectCommand.Parameters.AddWithValue("@SearchTerm", searchTerm);
            
            var dataTable = new DataTable();
            await Task.Run(() => adapter.Fill(dataTable));
            
            return dataTable;
        }
    }
}

預存程序操作

呼叫預存程序

public async Task<List<User>> GetUsersByAgeRangeAsync(int minAge, int maxAge)
{
    var users = new List<User>();
    
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var command = new SqlCommand("sp_GetUsersByAgeRange", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            
            // 輸入參數
            command.Parameters.AddWithValue("@MinAge", minAge);
            command.Parameters.AddWithValue("@MaxAge", maxAge);
            
            // 輸出參數
            var totalCountParam = new SqlParameter("@TotalCount", SqlDbType.Int)
            {
                Direction = ParameterDirection.Output
            };
            command.Parameters.Add(totalCountParam);
            
            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    users.Add(new User
                    {
                        Id = reader.GetInt32("Id"),
                        Name = reader.GetString("Name"),
                        Email = reader.IsDBNull("Email") ? null : reader.GetString("Email"),
                        Age = reader.GetInt32("Age"),
                        CreateDate = reader.GetDateTime("CreateDate")
                    });
                }
            }
            
            // 取得輸出參數值
            int totalCount = Convert.ToInt32(totalCountParam.Value);
            Console.WriteLine($"總共找到 {totalCount} 筆符合條件的資料");
        }
    }
    
    return users;
}

交易處理

基本交易

public async Task<bool> TransferUserDataAsync(int fromUserId, int toUserId, decimal amount)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                // 扣除來源帳戶金額
                string deductSql = "UPDATE UserAccounts SET Balance = Balance - @Amount WHERE UserId = @UserId";
                using (var deductCommand = new SqlCommand(deductSql, connection, transaction))
                {
                    deductCommand.Parameters.AddWithValue("@Amount", amount);
                    deductCommand.Parameters.AddWithValue("@UserId", fromUserId);
                    await deductCommand.ExecuteNonQueryAsync();
                }
                
                // 增加目標帳戶金額
                string addSql = "UPDATE UserAccounts SET Balance = Balance + @Amount WHERE UserId = @UserId";
                using (var addCommand = new SqlCommand(addSql, connection, transaction))
                {
                    addCommand.Parameters.AddWithValue("@Amount", amount);
                    addCommand.Parameters.AddWithValue("@UserId", toUserId);
                    await addCommand.ExecuteNonQueryAsync();
                }
                
                // 記錄交易歷史
                string logSql = @"
                    INSERT INTO TransactionLog (FromUserId, ToUserId, Amount, TransactionDate) 
                    VALUES (@FromUserId, @ToUserId, @Amount, @TransactionDate)";
                using (var logCommand = new SqlCommand(logSql, connection, transaction))
                {
                    logCommand.Parameters.AddWithValue("@FromUserId", fromUserId);
                    logCommand.Parameters.AddWithValue("@ToUserId", toUserId);
                    logCommand.Parameters.AddWithValue("@Amount", amount);
                    logCommand.Parameters.AddWithValue("@TransactionDate", DateTime.Now);
                    await logCommand.ExecuteNonQueryAsync();
                }
                
                // 提交交易
                transaction.Commit();
                Console.WriteLine("轉帳成功");
                return true;
            }
            catch (Exception ex)
            {
                // 回滾交易
                transaction.Rollback();
                Console.WriteLine($"轉帳失敗，已回滾：{ex.Message}");
                return false;
            }
        }
    }
}

Bulk Copy 大量資料操作

對於大量資料的插入，SqlBulkCopy 是最有效的方式：

public async Task<bool> BulkInsertUsersAsync(DataTable usersData)
{
    using (var connection = new SqlConnection(connectionString))
    {
        await connection.OpenAsync();
        
        using (var bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "Users";
            bulkCopy.BatchSize = 1000;
            bulkCopy.BulkCopyTimeout = 300;
            
            // 對應欄位
            bulkCopy.ColumnMappings.Add("Name", "Name");
            bulkCopy.ColumnMappings.Add("Email", "Email");
            bulkCopy.ColumnMappings.Add("Age", "Age");
            bulkCopy.ColumnMappings.Add("CreateDate", "CreateDate");
            
            try
            {
                await bulkCopy.WriteToServerAsync(usersData);
                Console.WriteLine($"成功批次插入 {usersData.Rows.Count} 筆資料");
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"批次插入失敗：{ex.Message}");
                return false;
            }
        }
    }
}

連線池和效能優化

連線池設定

public class DatabaseConfig
{
    public static string GetOptimizedConnectionString()
    {
        return @"
            Server=localhost;
            Database=TestDB;
            Integrated Security=true;
            Pooling=true;
            Min Pool Size=5;
            Max Pool Size=100;
            Connection Lifetime=300;
            Command Timeout=30;
            Application Name=MyApp;";
    }
}

使用 SqlConnectionStringBuilder

public static string BuildConnectionString(string server, string database, bool useWindowsAuth = true)
{
    var builder = new SqlConnectionStringBuilder
    {
        DataSource = server,
        InitialCatalog = database,
        IntegratedSecurity = useWindowsAuth,
        Pooling = true,
        MinPoolSize = 5,
        MaxPoolSize = 100,
        ConnectionTimeout = 30,
        CommandTimeout = 60,
        ApplicationName = "MyApplication"
    };
    
    if (!useWindowsAuth)
    {
        builder.UserID = "username";
        builder.Password = "password";
    }
    
    return builder.ConnectionString;
}

非同步最佳實務

public class UserService
{
    private readonly string _connectionString;
    
    public UserService(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public async Task<List<User>> GetUsersWithPagingAsync(int pageNumber, int pageSize)
    {
        var users = new List<User>();
        
        string sql = @"
            SELECT Id, Name, Email, Age, CreateDate
            FROM Users
            WHERE IsDeleted = 0
            ORDER BY CreateDate DESC
            OFFSET @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Offset", (pageNumber - 1) * pageSize);
                command.Parameters.AddWithValue("@PageSize", pageSize);
                
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        users.Add(MapUserFromReader(reader));
                    }
                }
            }
        }
        
        return users;
    }
    
    private static User MapUserFromReader(SqlDataReader reader)
    {
        return new User
        {
            Id = reader.GetInt32("Id"),
            Name = reader.GetString("Name"),
            Email = reader.IsDBNull("Email") ? null : reader.GetString("Email"),
            Age = reader.GetInt32("Age"),
            CreateDate = reader.GetDateTime("CreateDate")
        };
    }
}

錯誤處理和日誌記錄

完整的錯誤處理

public async Task<OperationResult<User>> CreateUserSafeAsync(User user)
{
    try
    {
        // 驗證輸入
        if (string.IsNullOrWhiteSpace(user.Name))
        {
            return OperationResult<User>.Failure("使用者姓名不能為空");
        }
        
        if (user.Age < 0 || user.Age > 150)
        {
            return OperationResult<User>.Failure("年齡必須在 0-150 之間");
        }
        
        // 檢查重複
        if (await IsEmailExistsAsync(user.Email))
        {
            return OperationResult<User>.Failure("電子信箱已存在");
        }
        
        // 執行新增
        int userId = await CreateUserAsync(user);
        user.Id = userId;
        
        return OperationResult<User>.Success(user, "使用者建立成功");
    }
    catch (SqlException ex) when (ex.Number == 2) // Timeout
    {
        return OperationResult<User>.Failure("資料庫連線逾時，請稍後再試");
    }
    catch (SqlException ex) when (ex.Number == 2547) // Primary key violation
    {
        return OperationResult<User>.Failure("資料已存在");
    }
    catch (SqlException ex)
    {
        // 記錄詳細錯誤但回傳簡化訊息
        Console.WriteLine($"SQL 錯誤 {ex.Number}: {ex.Message}");
        return OperationResult<User>.Failure("資料庫操作失敗");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"未預期的錯誤: {ex}");
        return OperationResult<User>.Failure("系統發生錯誤，請聯絡管理員");
    }
}

private async Task<bool> IsEmailExistsAsync(string email)
{
    if (string.IsNullOrWhiteSpace(email)) return false;
    
    string sql = "SELECT COUNT(1) FROM Users WHERE Email = @Email AND IsDeleted = 0";
    
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        using (var command = new SqlCommand(sql, connection))
        {
            command.Parameters.AddWithValue("@Email", email);
            var count = await command.ExecuteScalarAsync();
            return Convert.ToInt32(count) > 0;
        }
    }
}

操作結果封裝

public class OperationResult<T>
{
    public bool IsSuccess { get; set; }
    public string Message { get; set; }
    public T Data { get; set; }
    public List<string> Errors { get; set; } = new List<string>();
    
    public static OperationResult<T> Success(T data, string message = "操作成功")
    {
        return new OperationResult<T>
        {
            IsSuccess = true,
            Data = data,
            Message = message
        };
    }
    
    public static OperationResult<T> Failure(string message, List<string> errors = null)
    {
        return new OperationResult<T>
        {
            IsSuccess = false,
            Message = message,
            Errors = errors ?? new List<string>()
        };
    }
}

實體類別設計

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
    public int Age { get; set; }
    public DateTime CreateDate { get; set; }
    public DateTime? UpdateDate { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeleteDate { get; set; }
    
    // 驗證方法
    public List<string> Validate()
    {
        var errors = new List<string>();
        
        if (string.IsNullOrWhiteSpace(Name))
            errors.Add("姓名不能為空");
            
        if (Name?.Length > 100)
            errors.Add("姓名長度不能超過 100 字元");
            
        if (!string.IsNullOrWhiteSpace(Email) && !IsValidEmail(Email))
            errors.Add("電子信箱格式不正確");
            
        if (Age < 0 || Age > 150)
            errors.Add("年齡必須在 0-150 之間");
            
        return errors;
    }
    
    private bool IsValidEmail(string email)
    {
        try
        {
            var addr = new System.Net.Mail.MailAddress(email);
            return addr.Address == email;
        }
        catch
        {
            return false;
        }
    }
}

完整的資料存取層範例

public interface IUserRepository
{
    Task<List<User>> GetAllAsync();
    Task<User> GetByIdAsync(int id);
    Task<int> CreateAsync(User user);
    Task<bool> UpdateAsync(User user);
    Task<bool> DeleteAsync(int id);
    Task<List<User>> SearchAsync(string searchTerm);
    Task<(List<User> Users, int TotalCount)> GetPagedAsync(int pageNumber, int pageSize);
}

public class UserRepository : IUserRepository
{
    private readonly string _connectionString;
    
    public UserRepository(string connectionString)
    {
        _connectionString = connectionString;
    }
    
    public async Task<List<User>> GetAllAsync()
    {
        var users = new List<User>();
        string sql = @"
            SELECT Id, Name, Email, Age, CreateDate, UpdateDate, IsDeleted 
            FROM Users 
            WHERE IsDeleted = 0 
            ORDER BY CreateDate DESC";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(sql, connection))
            using (var reader = await command.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                    users.Add(MapUserFromReader(reader));
                }
            }
        }
        
        return users;
    }
    
    public async Task<User> GetByIdAsync(int id)
    {
        string sql = @"
            SELECT Id, Name, Email, Age, CreateDate, UpdateDate, IsDeleted 
            FROM Users 
            WHERE Id = @Id AND IsDeleted = 0";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Id", id);
                using (var reader = await command.ExecuteReaderAsync())
                {
                    if (await reader.ReadAsync())
                    {
                        return MapUserFromReader(reader);
                    }
                }
            }
        }
        
        return null;
    }
    
    public async Task<int> CreateAsync(User user)
    {
        string sql = @"
            INSERT INTO Users (Name, Email, Age, CreateDate, IsDeleted) 
            VALUES (@Name, @Email, @Age, @CreateDate, 0);
            SELECT CAST(SCOPE_IDENTITY() AS INT);";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Name", user.Name);
                command.Parameters.AddWithValue("@Email", user.Email ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Age", user.Age);
                command.Parameters.AddWithValue("@CreateDate", DateTime.Now);
                
                var result = await command.ExecuteScalarAsync();
                return Convert.ToInt32(result);
            }
        }
    }
    
    public async Task<bool> UpdateAsync(User user)
    {
        string sql = @"
            UPDATE Users 
            SET Name = @Name, Email = @Email, Age = @Age, UpdateDate = @UpdateDate
            WHERE Id = @Id AND IsDeleted = 0";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Id", user.Id);
                command.Parameters.AddWithValue("@Name", user.Name);
                command.Parameters.AddWithValue("@Email", user.Email ?? (object)DBNull.Value);
                command.Parameters.AddWithValue("@Age", user.Age);
                command.Parameters.AddWithValue("@UpdateDate", DateTime.Now);
                
                int rowsAffected = await command.ExecuteNonQueryAsync();
                return rowsAffected > 0;
            }
        }
    }
    
    public async Task<bool> DeleteAsync(int id)
    {
        string sql = @"
            UPDATE Users 
            SET IsDeleted = 1, DeleteDate = @DeleteDate 
            WHERE Id = @Id AND IsDeleted = 0";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Id", id);
                command.Parameters.AddWithValue("@DeleteDate", DateTime.Now);
                
                int rowsAffected = await command.ExecuteNonQueryAsync();
                return rowsAffected > 0;
            }
        }
    }
    
    public async Task<List<User>> SearchAsync(string searchTerm)
    {
        var users = new List<User>();
        string sql = @"
            SELECT Id, Name, Email, Age, CreateDate, UpdateDate, IsDeleted 
            FROM Users 
            WHERE IsDeleted = 0 
            AND (Name LIKE @SearchTerm OR Email LIKE @SearchTerm)
            ORDER BY CreateDate DESC";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            using (var command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@SearchTerm", $"%{searchTerm}%");
                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        users.Add(MapUserFromReader(reader));
                    }
                }
            }
        }
        
        return users;
    }
    
    public async Task<(List<User> Users, int TotalCount)> GetPagedAsync(int pageNumber, int pageSize)
    {
        var users = new List<User>();
        int totalCount = 0;
        
        // 先取得總數
        string countSql = "SELECT COUNT(*) FROM Users WHERE IsDeleted = 0";
        
        // 分頁查詢
        string dataSql = @"
            SELECT Id, Name, Email, Age, CreateDate, UpdateDate, IsDeleted 
            FROM Users 
            WHERE IsDeleted = 0 
            ORDER BY CreateDate DESC
            OFFSET @Offset ROWS
            FETCH NEXT @PageSize ROWS ONLY";
        
        using (var connection = new SqlConnection(_connectionString))
        {
            await connection.OpenAsync();
            
            // 取得總數
            using (var countCommand = new SqlCommand(countSql, connection))
            {
                totalCount = Convert.ToInt32(await countCommand.ExecuteScalarAsync());
            }
            
            // 取得分頁資料
            using (var dataCommand = new SqlCommand(dataSql, connection))
            {
                dataCommand.Parameters.AddWithValue("@Offset", (pageNumber - 1) * pageSize);
                dataCommand.Parameters.AddWithValue("@PageSize", pageSize);
                
                using (var reader = await dataCommand.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        users.Add(MapUserFromReader(reader));
                    }
                }
            }
        }
        
        return (users, totalCount);
    }
    
    private static User MapUserFromReader(SqlDataReader reader)
    {
        return new User
        {
            Id = reader.GetInt32("Id"),
            Name = reader.GetString("Name"),
            Email = reader.IsDBNull("Email") ? null : reader.GetString("Email"),
            Age = reader.GetInt32("Age"),
            CreateDate = reader.GetDateTime("CreateDate"),
            UpdateDate = reader.IsDBNull("UpdateDate") ? null : reader.GetDateTime("UpdateDate"),
            IsDeleted = reader.GetBoolean("IsDeleted")
        };
    }
}

使用 Entity Framework Core（替代方案）

如果您偏好使用 ORM，Entity Framework Core 是很好的選擇：

設定 DbContext

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
    
    public DbSet<User> Users { get; set; }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).HasMaxLength(100).IsRequired();
            entity.Property(e => e.Email).HasMaxLength(200);
            entity.Property(e => e.CreateDate).HasDefaultValueSql("GETDATE()");
            entity.HasQueryFilter(e => !e.IsDeleted); // 全域軟刪除過濾器
        });
        
        base.OnModelCreating(modelBuilder);
    }
}

EF Core 服務

public class UserServiceEF
{
    private readonly ApplicationDbContext _context;
    
    public UserServiceEF(ApplicationDbContext context)
    {
        _context = context;
    }
    
    public async Task<List<User>> GetAllUsersAsync()
    {
        return await _context.Users
            .OrderByDescending(u => u.CreateDate)
            .ToListAsync();
    }
    
    public async Task<User> GetUserByIdAsync(int id)
    {
        return await _context.Users.FindAsync(id);
    }
    
    public async Task<User> CreateUserAsync(User user)
    {
        _context.Users.Add(user);
        await _context.SaveChangesAsync();
        return user;
    }
    
    public async Task<bool> UpdateUserAsync(User user)
    {
        _context.Users.Update(user);
        var result = await _context.SaveChangesAsync();
        return result > 0;
    }
    
    public async Task<bool> DeleteUserAsync(int id)
    {
        var user = await _context.Users.FindAsync(id);
        if (user != null)
        {
            user.IsDeleted = true;
            user.DeleteDate = DateTime.Now;
            var result = await _context.SaveChangesAsync();
            return result > 0;
        }
        return false;
    }
}

常見問題與最佳實務

1. SQL 注入防護

錯誤示範：

// 危險！容易遭受 SQL 注入攻擊
string sql = $"SELECT * FROM Users WHERE Name = '{userName}'";

正確做法：

// 安全：使用參數化查詢
string sql = "SELECT * FROM Users WHERE Name = @UserName";
command.Parameters.AddWithValue("@UserName", userName);

2. 連線管理

錯誤示範：

// 錯誤：沒有正確釋放連線
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();
// 忘記關閉連線

正確做法：

// 正確：使用 using 語句自動釋放資源
using (var connection = new SqlConnection(connectionString))
{
    await connection.OpenAsync();
    // 自動關閉連線
}

3. 非同步程式設計

錯誤示範：

// 錯誤：在非同步方法中使用同步操作
public async Task<List<User>> GetUsersAsync()
{
    // 這會阻塞執行緒
    var result = command.ExecuteReader();
    return users;
}

正確做法：

// 正確：使用非同步方法
public async Task<List<User>> GetUsersAsync()
{
    using (var reader = await command.ExecuteReaderAsync())
    {
        // 非阻塞操作
    }
}

4. 大量資料處理

對於大量資料，使用適當的技術：

// 對於大量查詢，使用 IAsyncEnumerable
public async IAsyncEnumerable<User> GetAllUsersStreamAsync()
{
    using (var connection = new SqlConnection(_connectionString))
    {
        await connection.OpenAsync();
        
        string sql = "SELECT Id, Name, Email, Age FROM Users WHERE IsDeleted = 0";
        using (var command = new SqlCommand(sql, connection))
        using (var reader = await command.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                yield return MapUserFromReader(reader);
            }
        }
    }
}

5. 連線字串安全

// 使用設定檔而非硬編碼
public class DatabaseSettings
{
    public string ConnectionString { get; set; }
    
    public static DatabaseSettings FromConfiguration(IConfiguration config)
    {
        return new DatabaseSettings
        {
            ConnectionString = config.GetConnectionString("DefaultConnection")
        };
    }
}

// appsettings.json
{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=TestDB;Integrated Security=true;"
  }
}

效能監控和調優

1. 查詢執行時間監控

public async Task<T> ExecuteWithLoggingAsync<T>(Func<Task<T>> operation, string operationName)
{
    var stopwatch = Stopwatch.StartNew();
    try
    {
        var result = await operation();
        stopwatch.Stop();
        
        if (stopwatch.ElapsedMilliseconds > 1000) // 超過 1 秒的查詢
        {
            Console.WriteLine($"慢查詢警告：{operationName} 執行時間 {stopwatch.ElapsedMilliseconds}ms");
        }
        
        return result;
    }
    catch (Exception ex)
    {
        stopwatch.Stop();
        Console.WriteLine($"查詢失敗：{operationName}，執行時間 {stopwatch.ElapsedMilliseconds}ms，錯誤：{ex.Message}");
        throw;
    }
}

2. 連線池監控

public class ConnectionPoolMonitor
{
    public static void LogConnectionPoolStats(string connectionString)
    {
        var builder = new SqlConnectionStringBuilder(connectionString);
        
        // 這些資訊在生產環境中很有用
        Console.WriteLine($"應用程式名稱: {builder.ApplicationName}");
        Console.WriteLine($"連線逾時: {builder.ConnectTimeout}");
        Console.WriteLine($"命令逾時: {builder.CommandTimeout}");
        Console.WriteLine($"最小連線池大小: {builder.MinPoolSize}");
        Console.WriteLine($"最大連線池大小: {builder.MaxPoolSize}");
    }
}

單元測試

為您的資料存取層撰寫單元測試：

[TestClass]
public class UserRepositoryTests
{
    private string _testConnectionString;
    private UserRepository _userRepository;
    
    [TestInitialize]
    public void Setup()
    {
        _testConnectionString = "Server=(localdb)\\mssqllocaldb;Database=TestDB;Integrated Security=true;";
        _userRepository = new UserRepository(_testConnectionString);
    }
    
    [TestMethod]
    public async Task CreateUser_ShouldReturnUserId_WhenUserIsValid()
    {
        // Arrange
        var user = new User
        {
            Name = "測試使用者",
            Email = "test@example.com",
            Age = 25
        };
        
        // Act
        int userId = await _userRepository.CreateAsync(user);
        
        // Assert
        Assert.IsTrue(userId > 0);
        
        // Cleanup
        await _userRepository.DeleteAsync(userId);
    }
    
    [TestMethod]
    public async Task GetUserById_ShouldReturnNull_WhenUserNotExists()
    {
        // Act
        var result = await _userRepository.GetByIdAsync(99999);
        
        // Assert
        Assert.IsNull(result);
    }
}

結論

SQL Server 與 C# 的整合提供了強大且靈活的資料處理能力。透過本文介紹的技術和最佳實務，您可以：

關鍵要點總結：

  • 使用 Microsoft.Data.SqlClient 獲得最新功能和效能
  • 始終使用參數化查詢防止 SQL 注入
  • 善用 using 語句管理資源生命週期
  • 採用非同步程式設計提升應用程式回應性
  • 實作適當的錯誤處理和日誌記錄
  • 使用交易確保資料一致性
  • 針對大量資料使用 SqlBulkCopy
  • 適當設定連線池參數優化效能

進階建議：

  • 考慮使用 Entity Framework Core 簡化開發
  • 實作 Repository 模式提高程式碼可測試性
  • 監控查詢效能並優化慢查詢
  • 在 CI/CD 流程中包含資料庫相關測試
  • 定期審查和更新連線字串安全性

掌握這些技能後，您就能夠開發出高效、安全且可維護的資料存取層。記住，良好的資料庫設計和適當的索引策略同樣重要，這些將直接影響您的應用程式效能。

繼續學習和實踐，您將成為 C# 資料庫操作的專家！

Last modification：July 29, 2025
© Allow specification reprint
If you think my article is useful to you, please feel free to appreciate