FaibClass.Data 是輕量級 ADO.NET 封裝框架，支援 SQL Server、Oracle、OleDb (Access)、MySQL 等，核心特色：
| 方法 | 用途 |
|---|---|
ExecuteNonQuery(sql, params) | 執行 INSERT/UPDATE/DELETE/DDL |
ExecuteScalar(sql, params) | 單一值查詢 |
ExecuteReader(sql, params) | 資料流讀取 |
ExecuteDataTable(sql, params) | 填充 DataTable（12 種多載） |
FillDataSet(sql, params) | 填充 DataSet |
FillModelList<T>(sql, params) | 直接映射實體列表（反射/Expression） |
Update(dt/ds) | DataTable/DataSet 批次更新回資料庫 |
Begin/Commit/RollbackTransaction() | 事務控制（支援分散式交易） |
CreateParameters() | 建立參數集合（自動處理 @ / : 前綴差異） |
Open/CloseUpdateBag() | 啟用/關閉 SQL 追蹤（供 Update 批次用） |
public interface IDataProvider
{
IDbConnection GetDbConnection();
IDbDataAdapter GetDataAdapter();
IDbDataParameter GetParameter();
IDbDataParameter[] GetParameters(int count);
IDbCommand GetCommand(IDbCommand cmd);
void GetCommandBuilder(IDbDataAdapter adapter, DataTable dt, DataViewRowState state);
}
// SQL Server 實作
public class SqlServer : DataHelper, IDataProvider
{
public IDbConnection GetDbConnection() => new SqlConnection(ConnectionString);
public IDbDataAdapter GetDataAdapter() => new SqlDataAdapter();
public IDbDataParameter GetParameter() => new SqlParameter();
public IDbDataParameter[] GetParameters(int count) => new SqlParameter[count];
public IDbCommand GetCommand(IDbCommand cmd) => cmd as SqlCommand;
public void GetCommandBuilder(IDbDataAdapter adapter, DataTable dt, DataViewRowState state)
=> new SqlCommandBuilder((SqlDataAdapter)adapter);
}[Serializable]
public abstract class BaseModel
{
public abstract string TableName { get; }
public abstract string PrimaryKey { get; }
public virtual DataColumn GetDataColumn(string columnName) => null; // 子類實作欄位中繼資料
}
// 具體實體範例
[Serializable]
public class TB_BUY_BILL : BaseModel
{
public override string TableName => "TB_BUY_BILL";
public override string PrimaryKey => "BUY_BILL_ID";
public static string _TableName = "TB_BUY_BILL";
public static string _PrimaryKey = "BUY_BILL_ID";
public static string _BUY_BILL_ID = "BUY_BILL_ID";
public static string _BILL_NO = "BILL_NO";
// ... 其他欄位常數
public string BUY_BILL_ID { get; set; }
public string BILL_NO { get; set; }
public BillType BILL_TYPE { get; set; }
public decimal SALED_AMOUNT { get; set; }
public override DataColumn GetDataColumn(string columnName)
{
return columnName switch
{
"BUY_BILL_ID" => new DataColumn { ColumnName = "BUY_BILL_ID", DbType = DbType.String, IsPrimaryKey = true, ColumnSize = 200 },
"BILL_NO" => new DataColumn { ColumnName = "BILL_NO", DbType = DbType.String, ColumnSize = 200 },
"BILL_TYPE" => new DataColumn { ColumnName = "BILL_TYPE", DbType = DbType.Int32 },
"SALED_AMOUNT" => new DataColumn { ColumnName = "SALED_AMOUNT", DbType = DbType.Decimal },
_ => null
};
}
}
[Serializable]
public class TB_BUY_BILLs : DataModelList { } // 僅繼承 List public class BaseDataAccess where TEntity : BaseModel, new()
where TList : IList, new()
{
protected DataHelper DataHelper { get; }
public PageArgs PageArgs { get; set; }
public BaseDataAccess(DataHelper helper) => DataHelper = helper;
public TEntity NewEntity => new TEntity();
public bool Create(TEntity entity) => DataHelper.ExecuteNonQuery(BuildInsertSql(entity), GetParameters(entity)) > 0;
public bool Update(TEntity entity) => DataHelper.ExecuteNonQuery(BuildUpdateSql(entity), GetParameters(entity)) > 0;
public bool Delete(TEntity entity) => DataHelper.ExecuteNonQuery($"DELETE FROM {entity.TableName} WHERE {entity.PrimaryKey}=@pk", new { pk = entity.GetPkValue() }) > 0;
public TEntity Get(QueryBuilder qb, string[] fields = null) => DataHelper.FillModelList(BuildSelectSql(qb, fields), qb.Parameters).FirstOrDefault();
public TList Select(QueryBuilder qb, string orderBy, string[] fields) => DataHelper.FillModelList(BuildSelectSql(qb, fields) + " ORDER BY " + orderBy, qb.Parameters);
public bool Exists(TEntity exclude, string field, object value, string where) { /* ... */ }
} public class Database : OleDb // 或 SqlServer, Oracle
{
public Database() => ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\test.mdb";
}
public class TB_BUY_BILL_DA : BaseDataAccess
{
public TB_BUY_BILL_DA() : base(new Database()) { }
} <!-- App.config -->
<configSections>
<section name="faibclass.data.instanceSection" type="FaibClass.Data.Configuration.InstanceSettingsSection, FaibClass.Data" />
</configSections>
<faibclass.data.instanceSection>
<faibclass.data.instanceSettings defaultInstance="Access">
<add name="SqlServer" type="FaibClass.Data.SqlServer, FaibClass.Data" connectionString="Data Source=.;Initial Catalog=MyDB;User ID=sa;Password=***" />
<add name="Access" type="FaibClass.Data.OleDb, FaibClass.Data" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={APP}\test.mdb" />
<add name="Sql2005" type="FaibClass.Data.SqlServer, FaibClass.Data" interfaceType="FaibClass.Data.Configuration.BinaryConnectionConfig, FaibClass.Data" interfaceParameters="{APP}\Connection.bin" interfaceKey="c" />
</faibclass.data.instanceSettings>
</faibclass.data.instanceSection>// 程式碼中建立連線
var helper = ConfigurationCreator.CreateInstance(); // 依設定檔自動建立對應 DataHelper
var dal = new TB_BUY_BILL_DA(helper); // 注入而非硬編碼var qb = new QueryBuilder();
qb.Append(QueryRelation.And, QueryCompare.Equal, "Status", 1);
qb.Append(QueryRelation.And, QueryCompare.Like, "Name", "%關鍵字%");
qb.BeginBracket();
qb.Append(QueryRelation.Or, QueryCompare.Equal, "Type", 1);
qb.Append(QueryRelation.Or, QueryCompare.Equal, "Type", 2);
qb.EndBracket();
// 產生: WHERE Status=1 AND Name LIKE '%關鍵字%' AND (Type=1 OR Type=2)
// 參數自動參數化：@p0=1, @p1='%關鍵字%', @p2=1, @p3=2public enum BillType
{
[EnumText("電子")] Ele = 0,
[EnumText("手工")] Man = 1,
}
// 使用
string text = EnumTextAttribute.GetText(BillType.Ele); // "電子"
BillType val = EnumTextAttribute.GetValue("手工"); // Man FaibClass.Data 以 DataHelper 統一 ADO.NET 操作 + IDataProvider 抽象提供者 + BaseModel 實體中繼資料 + BaseDataAccess 泛型 CRUD 封裝 + 配置驅動連線管理 構建完整資料存取框架。優點：無 ORM 開銷、SQL 完全可控、支援多資料庫熱切換、實體定義即文檔。適用於存儲過程多、效能敏感、需精確控制 SQL 的企業級 WinForms/WebForms 專案。
