SQL 建表語句轉 C# 代碼：ADO.NET 參數化執行與實體類映射完整方案

需求澄清

提供的 SQL 為 **DDL 建表語句**（`CREATE TABLE`），非 DML 操作語句。C# 無法直接「執行建表」作為業務邏輯，通常有兩種場景：

1. 程序化建表：安裝/升級腳本執行 DDL。
2. 實體類映射：根據表結構生成 C# 實體類，配合 ORM (EF/Dapper) 操作數據。

方案一：ADO.NET 執行建表腳本（參數化、事務、異常處理）

using System;
using System.Data.SqlClient;
using System.IO;

public class DbInitializer {
    private readonly string _connStr = "Server=.;Database=ExamDB;Trusted_Connection=True;";
    
    public void ExecuteSchemaScript(string sqlFilePath) {
        string script = File.ReadAllText(sqlFilePath);
        // 簡單按 GO 分割 (SQL Server 批次分隔符)，實際建議用 Microsoft.SqlServer.TransactSql.ScriptDom 解析
        string[] batches = script.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries);
        
        using (var conn = new SqlConnection(_connStr)) {
            conn.Open();
            foreach (var batch in batches) {
                string trimmed = batch.Trim();
                if (string.IsNullOrWhiteSpace(trimmed)) continue;
                
                using (var cmd = new SqlCommand(trimmed, conn)) {
                    try {
                        cmd.ExecuteNonQuery(); // DDL 不返回行數
                        Console.WriteLine($"執行成功: {trimmed.Substring(0, Math.Min(50, trimmed.Length))}...");
                    } catch (SqlException ex) {
                        // 記錄錯誤但繼續執行後續批次 (視業務決定)
                        Console.Error.WriteLine($"執行失敗: {ex.Message}\nSQL: {trimmed}");
                    }
                }
            }
        }
    }
}

方案二：實體類定義 (對應 4 張表)

// 考卷內容表
public class ExamContent {
    public int Pid { get; set; }
    public int? Tid { get; set; }
    public int? Type { get; set; }
    public int? Source { get; set; }
    public string Answer { get; set; }
    public decimal? FullMark { get; set; }
    public decimal? Score { get; set; }
}

// 考試匯總信息表
public class TestInfo {
    public int ExamID { get; set; }       // 自增主鍵，建議 [Key, DatabaseGenerated(Identity)]
    public int CourseId { get; set; }
    public int TimeLast { get; set; } = 120;
    public int TotalScore { get; set; } = 100;
    public decimal AverScore { get; set; } = 0;
    public int? TotalStu { get; set; }
    public int? RealStu { get; set; }
    public int? TeachID { get; set; }
    public string StuType { get; set; } = "3+1";
}

// 考卷信息表
public class PaperInfo {
    public int PaperID { get; set; }      // 自增主鍵
    public int ExamID { get; set; }
    public int? CourseID { get; set; }
    public DateTime? StartTime { get; set; }
    public DateTime? EndTime { get; set; }
    public int? Flag { get; set; }
    public string StuID { get; set; }
    public long? StuPwd { get; set; }
    public string TeachName { get; set; }
    public decimal Score { get; set; } = 0;
}

// 考生信息表
public class StuInfo {
    public string StuID { get; set; }     // 主鍵 varchar(8)
    public string StuName { get; set; }
    public string Sex { get; set; }
    public DateTime? Birthday { get; set; }
    public DateTime? EnrollDay { get; set; }
    public string NativePlc { get; set; }
    public int? DelSign { get; set; }
}

方案三：Dapper 參數化增刪改查示例 (高性能、輕量)

using Dapper;
using System.Data.SqlClient;
using System.Collections.Generic;

public class ExamRepository {
    private readonly string _connStr;
    
    public ExamRepository(string connStr) { _connStr = connStr; }
    
    // 新增考生
    public int InsertStudent(StuInfo stu) {
        const string sql = @"INSERT INTO StuInfo (StuID, StuName, Sex, Birthday, EnrollDay, NativePlc, DelSign)
                             VALUES (@StuID, @StuName, @Sex, @Birthday, @EnrollDay, @NativePlc, @DelSign)";
        using (var conn = new SqlConnection(_connStr)) {
            return conn.Execute(sql, stu); // 自動映射屬性到參數
        }
    }
    
    // 查詢考卷詳情 (多表連接)
    public IEnumerable GetPaperDetails(int examId) {
        const string sql = @"
            SELECT p.PaperID, p.StartTime, p.EndTime, c.CourseName, t.TotalScore
            FROM PaperInfo p
            JOIN TestInfo t ON p.ExamID = t.ExamID
            JOIN Course c ON t.Course_id = c.CourseID
            WHERE p.ExamID = @ExamID";
        using (var conn = new SqlConnection(_connStr)) {
            return conn.Query(sql, new { ExamID = examId });
        }
    }
}

方案四：Entity Framework Core Code First (推薦新項目)

// DbContext
public class ExamContext : DbContext {
    public DbSet ExamContents { get; set; }
    public DbSet TestInfos { get; set; }
    public DbSet PaperInfos { get; set; }
    public DbSet StuInfos { get; set; }
    
    protected override void OnConfiguring(DbContextOptionsBuilder options) {
        options.UseSqlServer("Server=.;Database=ExamDB;Trusted_Connection=True;");
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        // 流式配置主鍵、自增、默認值、索引等
        modelBuilder.Entity()
            .Property(t => t.TimeLast).HasDefaultValue(120);
        modelBuilder.Entity()
            .Property(p => p.Score).HasDefaultValue(0m);
        // 複合主鍵、外鍵關係等...
    }
}

// 使用
using (var db = new ExamContext()) {
    var student = new StuInfo { StuID = "20230001", StuName = "張三", Sex = "男" };
    db.StuInfos.Add(student);
    db.SaveChanges(); // 自動生成參數化 SQL
}

關鍵最佳實踐總結

總結

SQL 建表語句本身不直接「轉 C# 代碼」。**DDL 用腳本執行**，**DML 用實體類 + ORM/微 ORM 參數化操作**。掌握「實體類映射表結構」「參數化命令防注入」「事務保證一致性」是 C# 數據訪問核心技能。