ADO.NET 自訂類別 MyDBase 連接 SQL Server 完整程式碼

封裝 SqlConnection、SqlCommand、SqlDataAdapter 等核心物件，提供資料查詢、純量執行、交易控制、DataReader 等常用方法的 C# 工具類別。

完整原始碼

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

public class MyDBase
{
    private bool ECode = false;
    private string ES = "";
    private SqlConnection cn = new SqlConnection();
    private DataSet Rs;

    // Windows 驗證建構子
    public MyDBase(string MyDBServerName, string MyDataBaseName)
    {
        ECode = false;
        cn.ConnectionString = "workstation id=" + MyDBServerName +
            ";packet size=4096;integrated security=SSPI;data source=" + MyDBServerName +
            ";persist security info=False;initial catalog=" + MyDataBaseName;
        try { cn.Open(); }
        catch (Exception e) { ES = e.Message; ECode = true; }
    }

    // SQL 驗證建構子
    public MyDBase(string MyDBServerName, string MyDataBaseName, string sUerName, string sPasswd)
    {
        ECode = false;
        string sConn = "workstation id=" + MyDBServerName + ";packet size=4096;user id=" + sUerName +
            ";pwd=" + sPasswd + ";data source=" + MyDBServerName +
            ";persist security info=False;initial catalog=" + MyDataBaseName;
        cn.ConnectionString = sConn;
        try { cn.Open(); }
        catch (Exception e) { ES = e.Message; ECode = true; }
    }

    // 傳回 DataSet
    public DataSet GetRecordset(string Sqls)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(Sqls, cn);
            SqlDataAdapter adp = new SqlDataAdapter(sqlCmd);
            Rs = new DataSet();
            adp.Fill(Rs);
        }
        catch (Exception e) { ES = e.Message; ECode = true; return null; }
        return Rs;
    }

    // 執行純量查詢回傳 int
    public int ExecuteSQLScalar(string Sqls)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(Sqls, cn) { CommandType = CommandType.Text };
            string s = sqlCmd.ExecuteScalar().ToString();
            return int.Parse(s);
        }
        catch (Exception e) { ES = e.Message; ECode = true; return -1; }
    }

    // 執行純量查詢回傳 string
    public string ExecuteSQLScalarTOstring(string Sqls)
    {
        try
        {
            SqlCommand sqlCmd = new SqlCommand(Sqls, cn) { CommandType = CommandType.Text };
            return sqlCmd.ExecuteScalar().ToString();
        }
        catch (Exception e) { ES = e.Message; ECode = true; return "-1"; }
    }

    // 交易執行並回傳純量
    public string ExecuteSQLWithTrans(string Sqls)
    {
        SqlTransaction myTrans = cn.BeginTransaction();
        SqlCommand sqlCmd = new SqlCommand(Sqls, cn, myTrans) { CommandType = CommandType.Text };
        try
        {
            sqlCmd.ExecuteNonQuery();
            string s = sqlCmd.ExecuteScalar().ToString();
            myTrans.Commit();
            return s;
        }
        catch (Exception e) { ES = e.Message; ECode = true; myTrans.Commit(); return ""; }
    }

    // 執行無回傳 SQL
    public void ExecuteSQL(string Sqls)
    {
        try
        {
            new SqlCommand(Sqls, cn) { CommandType = CommandType.Text }.ExecuteNonQuery();
        }
        catch (Exception e) { ES = e.Message; ECode = true; }
    }

    // 傳回 DataReader (關閉連線模式)
    public SqlDataReader DBDataReader(string Sqls)
    {
        try
        {
            return new SqlCommand(Sqls, cn) { CommandType = CommandType.Text }
                .ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch (Exception e) { ES = e.Message; ECode = true; return null; }
    }

    public void DBClose()
    {
        try { cn.Close(); }
        catch (Exception e) { ES = e.Message; ECode = true; }
    }

    public bool ErrorCode() => ECode;
    public string ErrMessage() => ES;

    ~MyDBase() { /* 解構子可選擇關閉連線 */ }
}

使用範例

var db = new MyDBase("localhost", "MyDB", "sa", "pwd");
DataSet ds = db.GetRecordset("SELECT * FROM Users");
if (!db.ErrorCode()) { /* 處理 ds */ }
db.DBClose();

注意： 現代開發建議使用 using 區塊確保連線釋放，或採用 Dapper/EF Core 等微型 ORM 簡化資料存取。