如何分离SQL插入逻辑与DataAccess类：仅传SQL语句并处理参数

算力阿峰

2026-7-10

问题：分离DataAccess类的switch逻辑，实现无类型感知的参数插入

现有如下C#代码：

private class DataAccess
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["DatabaseServerInstance"].ConnectionString;
    public static void Push(object o)
    {
        SqlCommand command = new SqlCommand();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            switch (o)
            {
                case Billing billing:
                    command = new SqlCommand("INSERT INTO BILLINGS (Comission Sales, Takedown Fee, Extra Billings)" +
                                                     "VALUES(@ComissionSales,@TakedownFee,@BillingExtra)", connection);
                    command.Parameters.Add("@ComissionSales", SqlDbType.NVarChar).Value = billing.ComissionSales;
                    command.Parameters.Add("@TakedownFee", SqlDbType.Int).Value = billing.TakedownFee;
                    command.Parameters.Add("@BillingExtra", SqlDbType.NVarChar).Value = billing.BillingExtra;
                    command.ExecuteNonQuery();
                    break;
                case Renter renter:
                    command = new SqlCommand("INSERT INTO RENTERS (Name, Phone, Email, BankAccount)" +
                                                     "VALUES(@RenterName,@RenterPhone,@RenterEmail,@RenterPaymentinfo)", connection);
                    command.Parameters.Add("@RenterName", SqlDbType.NVarChar).Value = renter.RenterName;
                    command.Parameters.Add("@RenterPhone", SqlDbType.Int).Value = renter.RenterPhone;
                    command.Parameters.Add("@RenterEmail", SqlDbType.NVarChar).Value = renter.RenterEmail;
                    command.Parameters.Add("@RenterPaymentinfo", SqlDbType.Int).Value = renter.RenterPaymentinfo;
                    command.ExecuteNonQuery();
                    break;
                case Booking booking:
                    command = new SqlCommand("INSERT INTO BOOKINGS (Booking Paid, Weeks, Start Date, End Date)" +
                                                     "VALUES(@v,@IsPaid,@BookingStartdate,@BookingEnddate)", connection);
                    command.Parameters.Add("@IsPaid", SqlDbType.NVarChar).Value = booking.IsPaid;
                    command.Parameters.Add("@BookingWeeks", SqlDbType.Int).Value = booking.BookingWeeks;
                    command.Parameters.Add("@BookingStartdate", SqlDbType.NVarChar).Value = booking.BookingStartdate;
                    command.Parameters.Add("@BookingEnddate", SqlDbType.Int).Value = booking.BookingEnddate;
                    command.ExecuteNonQuery();
                    break;
                case Shelf shelf:
                    command = new SqlCommand("INSERT INTO SHELVES (Shelf ID, Rented)" +
                                                     "VALUES(@ShelfID,@IsBooked)", connection);
                    command.Parameters.Add("@ShelfID", SqlDbType.NVarChar).Value = shelf.ShelfID;
                    command.Parameters.Add("@IsBooked", SqlDbType.Int).Value = shelf.IsBooked;
                    command.ExecuteNonQuery();
                    break;
            }
        }
    }
}

当前DataAccess类的Push方法通过switch语句处理不同类型对象的SQL插入操作，希望将switch逻辑从DataAccess类中分离，仅向其传递SQL语句字符串，但需要在DataAccess不了解对象具体类型的情况下，完成参数绑定并执行插入。

方案一：定义数据映射接口，让实体类自行实现参数绑定

核心思路是让每个实体类负责自身的SQL语句和参数绑定逻辑，DataAccess只处理通用数据库操作。

定义通用接口：

public interface IDataEntity
{
    string GetInsertSql();
    void AddParameters(SqlCommand command);
}

让实体类实现接口，以Billing为例：

public class Billing : IDataEntity
{
    public string ComissionSales { get; set; }
    public int TakedownFee { get; set; }
    public string BillingExtra { get; set; }

    public string GetInsertSql()
    {
        return "INSERT INTO BILLINGS (Comission Sales, Takedown Fee, Extra Billings) VALUES(@ComissionSales,@TakedownFee,@BillingExtra)";
    }

    public void AddParameters(SqlCommand command)
    {
        command.Parameters.Add("@ComissionSales", SqlDbType.NVarChar).Value = ComissionSales;
        command.Parameters.Add("@TakedownFee", SqlDbType.Int).Value = TakedownFee;
        command.Parameters.Add("@BillingExtra", SqlDbType.NVarChar).Value = BillingExtra;
    }
}

修改DataAccess的Push方法为通用逻辑：

private class DataAccess
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["DatabaseServerInstance"].ConnectionString;
    public static void Push(IDataEntity entity)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(entity.GetInsertSql(), connection))
            {
                entity.AddParameters(command);
                command.ExecuteNonQuery();
            }
        }
    }
}

此方案彻底分离了数据库操作与实体映射逻辑，DataAccess无需知晓实体具体类型。

方案二：使用反射自动映射属性到参数

若不想修改实体类，可利用反射将对象属性自动绑定到SQL参数，前提是属性名与参数名保持一致（或遵循约定命名规则）。

修改后的DataAccess类：

private class DataAccess
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["DatabaseServerInstance"].ConnectionString;
    public static void Push(object entity, string insertSql)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(insertSql, connection))
            {
                var properties = entity.GetType().GetProperties();
                foreach (var prop in properties)
                {
                    string paramName = $"@{prop.Name}";
                    SqlDbType dbType = GetSqlDbType(prop.PropertyType);
                    command.Parameters.Add(paramName, dbType).Value = prop.GetValue(entity);
                }
                command.ExecuteNonQuery();
            }
        }
    }

    private static SqlDbType GetSqlDbType(Type clrType)
    {
        if (clrType == typeof(string))
            return SqlDbType.NVarChar;
        if (clrType == typeof(int) || clrType == typeof(int?))
            return SqlDbType.Int;
        // 可扩展支持更多类型
        throw new NotSupportedException($"类型 {clrType.Name} 未实现映射");
    }
}

调用示例：

var billing = new Billing { ComissionSales = "1000", TakedownFee = 50, BillingExtra = "无" };
DataAccess.Push(billing, "INSERT INTO BILLINGS (Comission Sales, Takedown Fee, Extra Billings) VALUES(@ComissionSales,@TakedownFee,@BillingExtra)");

此方案无需修改实体，但反射会带来轻微性能开销，适合简单场景。

方案三：使用表达式树预编译映射逻辑（性能优化版）

若担心反射性能问题，可通过表达式树预编译每个类型的参数绑定逻辑，避免重复反射开销。

修改后的DataAccess类：

private class DataAccess
{
    private static string connectionString = ConfigurationManager.ConnectionStrings["DatabaseServerInstance"].ConnectionString;
    private static readonly Dictionary<Type, Action<object, SqlCommand>> _parameterBinders = new Dictionary<Type, Action<object, SqlCommand>>();

    public static void Push(object entity, string insertSql)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand(insertSql, connection))
            {
                var entityType = entity.GetType();
                if (!_parameterBinders.TryGetValue(entityType, out var binder))
                {
                    binder = CreateParameterBinder(entityType);
                    _parameterBinders[entityType] = binder;
                }
                binder(entity, command);
                command.ExecuteNonQuery();
            }
        }
    }

    private static Action<object, SqlCommand> CreateParameterBinder(Type entityType)
    {
        var entityParam = Expression.Parameter(typeof(object), "entity");
        var commandParam = Expression.Parameter(typeof(SqlCommand), "command");
        var castEntity = Expression.TypeAs(entityParam, entityType);
        var expressions = new List<Expression>();

        foreach (var prop in entityType.GetProperties())
        {
            string paramName = $"@{prop.Name}";
            SqlDbType dbType = GetSqlDbType(prop.PropertyType);

            var addParam = Expression.Call(
                Expression.Property(commandParam, nameof(SqlCommand.Parameters)),
                nameof(SqlParameterCollection.Add),
                null,
                Expression.Constant(paramName),
                Expression.Constant(dbType)
            );
            var setValue = Expression.Assign(
                Expression.Property(addParam, nameof(SqlParameter.Value)),
                Expression.Property(castEntity, prop.Name)
            );
            expressions.Add(setValue);
        }

        var block = Expression.Block(expressions);
        return Expression.Lambda<Action<object, SqlCommand>>(block, entityParam, commandParam).Compile();
    }

    private static SqlDbType GetSqlDbType(Type clrType)
    {
        if (clrType == typeof(string))
            return SqlDbType.NVarChar;
        if (clrType == typeof(int) || clrType == typeof(int?))
            return SqlDbType.Int;
        // 扩展支持更多类型
        throw new NotSupportedException($"类型 {clrType.Name} 未实现映射");
    }
}

此方案第一次调用某类型时编译表达式树，后续复用缓存的委托，性能接近手写代码，兼顾灵活性与性能。

内容的提问来源于stack exchange，提问作者Aleksandar

如何分离SQL插入逻辑与DataAccess类：仅传SQL语句并处理参数

方案一：定义数据映射接口，让实体类自行实现参数绑定 #

方案二：使用反射自动映射属性到参数 #

方案三：使用表达式树预编译映射逻辑（性能优化版） #

方案一：定义数据映射接口，让实体类自行实现参数绑定

方案二：使用反射自动映射属性到参数

方案三：使用表达式树预编译映射逻辑（性能优化版）