ADO.NET 與 ORM 實戰對比 (一)：ADO.NET 手工實作 CRUD 基準測試

本系列以 SQL Server 2005 UserInfo 資料表為基準，分別用 ADO.NET、NHibernate、Linq to SQL、Entity Framework 實作 CRUD，作為後續 ORM 對比基準。

資料表結構

CREATE TABLE [dbo].[UserInfo](
    [UserID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [UserName] [varchar](20) NOT NULL,
    [RealName] [nvarchar](8) NOT NULL,
    [Age] [tinyint] NOT NULL,
    [Sex] [bit] NOT NULL,
    [Mobile] [char](11) NULL,
    [Phone] [char](11) NULL,
    [Email] [varchar](50) NOT NULL
)

App.config 連線字串

<connectionStrings>
  <add name="Conn" connectionString="Data Source=zhou;Initial Catalog=AspNetStudy;User ID=sa;Password=jerry" />
</connectionStrings>

實體類別 (POCO)

public class UserInfo
{
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string RealName { get; set; }
    public byte Age { get; set; }
    public bool Sex { get; set; }
    public string Email { get; set; }
    public string Mobile { get; set; }
    public string Phone { get; set; }
}

通用資料存取類 (SqlDbHelper)

封裝 ExecuteScalar、ExecuteNonQuery、ExecuteDataTable，支援參數化 SQL 與預存程序。(代碼略，見附件)

CRUD 核心程式碼 (ADODotNetCRUD)

public class ADODotNetCRUD
{
    public int Count()
    {
        return int.Parse(new SqlDbHelper().ExecuteScalar("select count(1) from UserInfo").ToString());
    }

    public bool Create(UserInfo info)
    {
        string sql = @"insert UserInfo(UserName,RealName,Age,Sex,Mobile,Email,Phone)
                       values(@UserName,@RealName,@Age,@Sex,@Mobile,@Email,@Phone)";
        var p = new[] {
            new SqlParameter("@UserName", info.UserName),
            new SqlParameter("@RealName", info.RealName),
            new SqlParameter("@Age", info.Age),
            new SqlParameter("@Sex", info.Sex),
            new SqlParameter("@Mobile", info.Mobile),
            new SqlParameter("@Email", info.Email),
            new SqlParameter("@Phone", info.Phone)
        };
        return new SqlDbHelper().ExecuteNonQuery(sql, CommandType.Text, p) > 0;
    }

    public UserInfo Read(int userId)
    {
        var dt = new SqlDbHelper().ExecuteDataTable("select * from UserInfo Where UserId=" + userId);
        if (dt.Rows.Count == 0) return null;
        var r = dt.Rows[0];
        return new UserInfo {
            UserId = int.Parse(r["UserId"].ToString()),
            UserName = r["UserName"].ToString(),
            Age = byte.Parse(r["Age"].ToString()),
            Email = r["Email"].ToString(),
            Mobile = r["Mobile"].ToString(),
            Phone = r["Phone"].ToString(),
            RealName = r["RealName"].ToString(),
            Sex = bool.Parse(r["Sex"].ToString())
        };
    }

    public bool Update(UserInfo info)
    {
        string sql = @"update UserInfo set UserName=@UserName,RealName=@RealName,Age=@Age,Sex=@Sex,
                       Mobile=@Mobile,Email=@Email,Phone=@Phone where UserID=@UserID";
        var p = new[] {
            new SqlParameter("@UserName", info.UserName),
            new SqlParameter("@RealName", info.RealName),
            new SqlParameter("@Age", info.Age),
            new SqlParameter("@Sex", info.Sex),
            new SqlParameter("@Mobile", info.Mobile),
            new SqlParameter("@Email", info.Email),
            new SqlParameter("@Phone", info.Phone),
            new SqlParameter("@UserID", info.UserId)
        };
        return new SqlDbHelper().ExecuteNonQuery(sql, CommandType.Text, p) > 0;
    }

    public bool Delete(int userId)
    {
        return new SqlDbHelper().ExecuteNonQuery("delete from UserInfo where UserId=" + userId) > 0;
    }

    public int GetMaxUserId()
    {
        return int.Parse(new SqlDbHelper().ExecuteScalar("select max(userId) from UserInfo").ToString());
    }
}

NUnit 測試代碼

涵蓋 Count、Create、Read、Update、Delete (ID/實體)、GetMaxUserId 等測試方法，驗證基準功能正確。(代碼略，見附件)

ADO.NET 優缺點總結

• 優點：完全掌控 SQL (統計/分組/複雜查詢優勢)、效能最高 (無轉譯層)、為所有 ORM 底層基礎
• 缺點：大量樣板代碼 (SQL 字串、參數、DataReader/DataTable 映射)、資料表結構變更需同步修改實體與映射邏輯、維護成本高

適合：高效能關鍵路徑、複雜報表查詢、團隊具深厚 SQL 功力、專案規模較小或資料庫結構穩定的情境。