開發 C# WinForm/WPF KTV 後台管理系統，需要一個通用的數據庫訪問封裝類 DBHelper，簡化 ADO.NET 繁瑣代碼。這裡提供一個生產可用的模板，支持 SQL Server / MySQL / PostgreSQL（只需換 Provider）。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Configuration;
public static class DBHelper {
// 連接字符串從配置文件讀取
private static readonly string ConnStr = ConfigurationManager.ConnectionStrings["KtvDb"].ConnectionString;
private static readonly string ProviderName = ConfigurationManager.ConnectionStrings["KtvDb"].ProviderName;
private static readonly DbProviderFactory Factory = DbProviderFactories.GetFactory(ProviderName);
// 獲取連接
public static DbConnection GetConnection() {
var conn = Factory.CreateConnection();
conn.ConnectionString = ConnStr;
return conn;
}
// 執行非查詢（增刪改），返回影響行數
public static int ExecuteNonQuery(string sql, params DbParameter[] parameters) {
using (var conn = GetConnection())
using (var cmd = Factory.CreateCommand()) {
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null) cmd.Parameters.AddRange(parameters);
conn.Open();
return cmd.ExecuteNonQuery();
}
}
// 執行查詢返回單值
public static object ExecuteScalar(string sql, params DbParameter[] parameters) {
using (var conn = GetConnection())
using (var cmd = Factory.CreateCommand()) {
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null) cmd.Parameters.AddRange(parameters);
conn.Open();
return cmd.ExecuteScalar();
}
}
// 執行查詢返回 DataTable
public static DataTable ExecuteDataTable(string sql, params DbParameter[] parameters) {
using (var conn = GetConnection())
using (var cmd = Factory.CreateCommand())
using (var adapter = Factory.CreateDataAdapter()) {
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null) cmd.Parameters.AddRange(parameters);
adapter.SelectCommand = cmd;
var dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
// 執行查詢返回強類型列表
public static List Query(string sql, Func mapper, params DbParameter[] parameters)
where T : new() {
var list = new List();
using (var conn = GetConnection())
using (var cmd = Factory.CreateCommand()) {
cmd.Connection = conn;
cmd.CommandText = sql;
if (parameters != null) cmd.Parameters.AddRange(parameters);
conn.Open();
using (var reader = cmd.ExecuteReader()) {
while (reader.Read()) {
list.Add(mapper(reader));
}
}
}
return list;
}
// 參數創建輔助
public static DbParameter CreateParameter(string name, object value) {
var p = Factory.CreateParameter();
p.ParameterName = name;
p.Value = value ?? DBNull.Value;
return p;
}
}
<connectionStrings>
<add name="KtvDb"
connectionString="Server=.;Database=KtvManagement;User Id=sa;Password=123456;"
providerName="System.Data.SqlClient" />
<!-- MySQL 示例
<add name="KtvDb"
connectionString="Server=localhost;Database=KtvManagement;Uid=root;Pwd=123456;Charset=utf8;"
providerName="MySql.Data.MySqlClient" /> -->
</connectionStrings>
<system.data>
<DbProviderFactories>
<add name="SqlClient" invariant="System.Data.SqlClient"
description=".NET Framework Data Provider for SQL Server"
type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
<!-- MySQL 需安裝 MySql.Data 包並註冊 -->
</DbProviderFactories>
</system.data>
public class RoomService {
// 查詢所有包廂
public List GetAllRooms() {
return DBHelper.Query("SELECT RoomId, RoomName, RoomType, Price, Status FROM Rooms",
r => new Room {
RoomId = r.GetInt32(0),
RoomName = r.GetString(1),
RoomType = r.GetString(2),
Price = r.GetDecimal(3),
Status = r.GetInt32(4)
});
}
// 開啟包廂（修改狀態）
public bool OpenRoom(int roomId, int userId) {
string sql = "UPDATE Rooms SET Status=1, CurrentUserId=@uid, StartTime=GETDATE() WHERE RoomId=@rid AND Status=0";
int rows = DBHelper.ExecuteNonQuery(sql,
DBHelper.CreateParameter("@rid", roomId),
DBHelper.CreateParameter("@uid", userId));
return rows > 0;
}
// 結帳結算（事務示例）
public bool Checkout(int roomId) {
using (var conn = DBHelper.GetConnection()) {
conn.Open();
using (var tran = conn.BeginTransaction()) {
try {
var cmd = DBHelper.Factory.CreateCommand();
cmd.Connection = conn;
cmd.Transaction = tran;
// 1. 計算消費
cmd.CommandText = "SELECT SUM(Amount) FROM Consumption WHERE RoomId=@rid AND IsPaid=0";
cmd.Parameters.Add(DBHelper.CreateParameter("@rid", roomId));
decimal total = (decimal)(cmd.ExecuteScalar() ?? 0);
// 2. 標記消費已付款
cmd.CommandText = "UPDATE Consumption SET IsPaid=1, PayTime=GETDATE() WHERE RoomId=@rid AND IsPaid=0";
cmd.Parameters.Clear();
cmd.Parameters.Add(DBHelper.CreateParameter("@rid", roomId));
cmd.ExecuteNonQuery();
// 3. 釋放包廂
cmd.CommandText = "UPDATE Rooms SET Status=0, CurrentUserId=NULL, StartTime=NULL WHERE RoomId=@rid";
cmd.Parameters.Clear();
cmd.Parameters.Add(DBHelper.CreateParameter("@rid", roomId));
cmd.ExecuteNonQuery();
tran.Commit();
return true;
} catch {
tran.Rollback();
return false;
}
}
}
}
}
conn.Query(sql) 一行搞定映射，性能接近原生 ADO.NETMin Pool Size=5;Max Pool Size=100;ExecuteNonQueryAsync 等異步方法，提升 UI 響應這個 DBHelper 結合 DbProviderFactory 實現了數據庫無關性，換數據庫只改配置文件不動代碼，是中小項目的實用選擇。
