无DataContext时如何实现参数化SQL查询的延迟执行？

Great question! When you need to batch parameterized SQL queries without a DataContext, the key is to separate storing the query templates and parameters from actually formatting them into valid SQL (since string.Format falls short on handling SQL-specific types like DateTime or escaped strings). Here's a practical approach to solve this:

1. 定义参数化查询容器类 #

First, create a simple class to hold each query's template and its associated parameters. This lets you collect queries without immediately resolving their parameter values into SQL strings:

public class ParameterizedSqlQuery
{
    public string SqlTemplate { get; set; }
    public object[] Parameters { get; set; }
}

2. 实现SQL安全的参数格式化方法 #

Next, build a helper method to safely format each parameter into a SQL-compatible string. This handles edge cases that string.Format can't, like escaping single quotes in strings, formatting dates correctly, and handling nulls:

private string FormatSqlParameter(object parameter)
{
    if (parameter == null)
        return "NULL";
    
    var typeCode = Type.GetTypeCode(parameter.GetType());
    switch (typeCode)
    {
        case TypeCode.String:
            // Escape single quotes and wrap in SQL string delimiters
            return $"'{((string)parameter).Replace("'", "''")}'";
        case TypeCode.DateTime:
            // Format dates into SQL-standard datetime string
            return $"'{((DateTime)parameter).ToString("yyyy-MM-dd HH:mm:ss")}'";
        case TypeCode.Boolean:
            // Adjust based on your database (SQL Server uses 1/0, PostgreSQL uses TRUE/FALSE)
            return ((bool)parameter) ? "1" : "0";
        case TypeCode.Byte:
        case TypeCode.Int16:
        case TypeCode.Int32:
        case TypeCode.Int64:
        case TypeCode.Single:
        case TypeCode.Double:
        case TypeCode.Decimal:
            // Numeric types can be directly converted to string
            return parameter.ToString();
        default:
            // Throw or handle custom types as needed for your use case
            throw new ArgumentException($"Unsupported parameter type: {parameter.GetType().Name}");
    }
}

3. 收集并批量生成SQL #

Now, collect all your queries into a list, then iterate through them to build the final batch SQL string. This ensures you only format parameters once, right before execution:

// Step 1: Collect all your parameterized queries
var pendingQueries = new List<ParameterizedSqlQuery>
{
    new ParameterizedSqlQuery
    {
        SqlTemplate = "UPDATE ForumUserStats SET Posts += {0} WHERE UserID = {1} AND ForumID = {2}",
        Parameters = new object[] { -1, post.AuthorID, post.Forum.ID }
    },
    // Add more queries as needed...
};

// Step 2: Build the final batch SQL
var sqlBuilder = new StringBuilder();
foreach (var query in pendingQueries)
{
    // Format each parameter safely
    var formattedParams = query.Parameters.Select(FormatSqlParameter).ToArray();
    // Replace placeholders in the template
    var formattedSql = string.Format(query.SqlTemplate, formattedParams);
    // Append to the batch (add a semicolon for most databases)
    sqlBuilder.AppendLine($"{formattedSql};");
}

// Step 3: Execute the batch SQL
db.ExecuteCommand(sqlBuilder.ToString());

Important Notes #

• SQL Injection Risk: Manual parameter formatting requires strict attention to escaping. The helper method above handles common cases, but always test with edge inputs (like strings containing single quotes) to avoid vulnerabilities.
• Database-Specific Adjustments: Adjust the FormatSqlParameter method for your database:
  • For PostgreSQL, use TRUE/FALSE instead of 1/0 for booleans.
  • For MySQL, date formatting might use a different string (e.g., yyyy-MM-dd HH:mm:ss still works, but verify your database's requirements).
• Alternative: Native Batch Parameterization: If your database supports it (e.g., SQL Server table-valued parameters, MySQL EXECUTE with multiple parameters), consider using ADO.NET's native parameterization instead of string formatting. This is safer and avoids manual escaping entirely.

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