四大核心 SqlHelper 方法封裝：查詢、增刪改、存儲過程、標量查詢

設計理念

拒絕代碼生成器產出的臃腫 SqlHelper，專注實戰高頻 4 個核心方法，簡潔、高性能、易維護。

完整實現代碼

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

namespace SQL
{
    public static class SqlHelper
    {
        // 連接字符串從配置文件讀取
        private static readonly string ConnStr = 
            ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        #region 1. 執行查詢，返回 DataTable
        /// 

        /// 參數化查詢，返回 DataTable
        /// 
        public static DataTable ExecuteTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter(sql, ConnStr))
            {
                if (parameters != null && parameters.Length > 0)
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
        #endregion

        #region 2. 執行增刪改，返回影響行數
        /// 

        /// 參數化增刪改，返回影響行數
        /// 
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region 3. 執行存儲過程，返回影響行數
        /// 

        /// 執行存儲過程，支持輸出參數
        /// 
        public static int ExecuteProcedure(string procName, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            using (SqlCommand cmd = new SqlCommand(procName, conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                
                conn.Open();
                return cmd.ExecuteNonQuery();
            }
        }
        #endregion

        #region 4. 執行查詢，返回首行首列 (標量)
        /// 

        /// 返回 object，調用時轉型：Convert.ToInt32、ToString 等
        /// 
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(ConnStr))
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                
                conn.Open();
                return cmd.ExecuteScalar();
            }
        }
        #endregion
    }
}

使用示例

// 1. 查詢列表
var users = SqlHelper.ExecuteTable(
    "SELECT * FROM Users WHERE Status = @status AND CreateTime > @time",
    new SqlParameter("@status", 1),
    new SqlParameter("@time", DateTime.Now.AddDays(-30)));

// 2. 插入記錄
int rows = SqlHelper.ExecuteNonQuery(
    "INSERT INTO Logs (Message, Level, CreateTime) VALUES (@msg, @level, @time)",
    new SqlParameter("@msg", "用戶登錄"),
    new SqlParameter("@level", "Info"),
    new SqlParameter("@time", DateTime.Now));

// 3. 調用存儲過程 (含輸出參數)
var outParam = new SqlParameter("@NewId", SqlDbType.Int) { Direction = ParameterDirection.Output };
SqlHelper.ExecuteProcedure("sp_CreateOrder", 
    new SqlParameter("@UserId", 123),
    new SqlParameter("@Amount", 99.9m),
    outParam);
int newOrderId = (int)outParam.Value;

// 4. 統計查詢
int totalUsers = Convert.ToInt32(SqlHelper.ExecuteScalar("SELECT COUNT(*) FROM Users"));
string maxOrderNo = SqlHelper.ExecuteScalar("SELECT MAX(OrderNo) FROM Orders")?.ToString();

進階增強建議

現代化替代：Dapper 一行代碼搞定

using Dapper;

// 查詢列表
var users = conn.Query("SELECT * FROM Users WHERE Status = @status", new { status = 1 }).ToList();

// 單條查詢
var user = conn.QueryFirstOrDefault("SELECT * FROM Users WHERE Id = @id", new { id = 123 });

// 執行增刪改
int rows = conn.Execute("INSERT INTO Logs (Message) VALUES (@msg)", new { msg = "Test" });

// 存儲過程
var p = new DynamicParameters();
p.Add("@UserId", 123);
p.Add("@NewId", dbType: DbType.Int32, direction: ParameterDirection.Output);
conn.Execute("sp_CreateOrder", p, commandType: CommandType.StoredProcedure);
int newId = p.Get("@NewId");

四大核心方法覆蓋 90%+ 數據訪問場景，理解其原理與擴展點，是 .NET 開發者數據層功力的試金石。