將 SqlConnection、SqlCommand、SqlDataAdapter 等 ADO.NET 核心物件封裝為可重用的 MyDBase 類別,支援:
DataSet / SqlDataReaderExecuteScalar)ExecuteSQLWithTrans)ErrorCode, ErrMessage)public class MyDBase
{
private bool _errorCode = false;
private string _errorMessage;
private SqlConnection _cn = new SqlConnection();
private DataSet _rs;
// 建構子:Windows 驗證
public MyDBase(string server, string database) { ... }
// 建構子:SQL 驗證
public MyDBase(string server, string database, string uid, string pwd) { ... }
// 查詢回傳 DataSet
public DataSet GetRecordset(string sql) { ... }
// 執行非查詢
public void ExecuteSQL(string sql) { ... }
// 標量查詢 (回傳 int)
public int ExecuteSQLScalar(string sql) { ... }
// 標量查詢 (回傳 string)
public string ExecuteSQLScalarTOstring(string sql) { ... }
// 交易執行並回傳標量
public string ExecuteSQLWithTrans(string sql) { ... }
// 回傳 DataReader (自動關閉連線)
public SqlDataReader DBDataReader(string sql) { ... }
// 關閉連線
public void DBClose() { ... }
// 錯誤屬性
public bool ErrorCode() => _errorCode;
public string ErrMessage() => _errorMessage;
}
// Windows 整合驗證
_cn.ConnectionString = $@"workstation id={server};packet size=4096;integrated security=SSPI;data source={server};persist security info=False;initial catalog={database}";
// SQL Server 驗證
_cn.ConnectionString = $@"workstation id={server};packet size=4096;user id={uid};pwd={pwd};data source={server};persist security info=False;initial catalog={database}";
雖然範例類別未直接暴露參數化方法,但實際使用時建議擴充:
public DataSet GetRecordset(string sql, params SqlParameter[] parameters)
{
using (var cmd = new SqlCommand(sql, _cn))
{
cmd.Parameters.AddRange(parameters);
var da = new SqlDataAdapter(cmd);
var ds = new DataSet();
da.Fill(ds);
return ds;
}
}
public string ExecuteSQLWithTrans(string sql)
{
using (var trans = _cn.BeginTransaction())
using (var cmd = new SqlCommand(sql, _cn, trans))
{
try
{
cmd.ExecuteNonQuery();
// 範例:取回 SCOPE_IDENTITY()
cmd.CommandText = "SELECT SCOPE_IDENTITY()";
var result = cmd.ExecuteScalar()?.ToString() ?? "";
trans.Commit();
return result;
}
catch
{
trans.Rollback();
throw;
}
}
}
using (var db = new MyDBase(@".\SQLEXPRESS", "MyDB", "sa", "pwd"))
{
if (db.ErrorCode())
{
Console.WriteLine("連線失敗: " + db.ErrMessage());
return;
}
// 查詢
var ds = db.GetRecordset("SELECT * FROM Users WHERE Age > @Age",
new SqlParameter("@Age", 18));
// 新增
db.ExecuteSQL("INSERT INTO Log (Msg) VALUES (@Msg)",
new SqlParameter("@Msg", "測試"));
// 交易
var id = db.ExecuteSQLWithTrans("INSERT INTO Orders (Amt) VALUES (100); SELECT SCOPE_IDENTITY();");
}
using 區塊自動釋放連線。async/await 版本 (ExecuteNonQueryAsync 等)。MyDBase 展示了 ADO.NET 基礎封裝的核心概念:連線管理、命令執行、資料填充、交易控制與錯誤處理。雖然現代開發多改用 Dapper/EF Core,但理解底層封裝原理有助於排查效能問題與處理特殊場景。