将指定OleDb参数化插入语句改造为通用插入函数的需求

Alright, let's convert that hardcoded INSERT operation into a flexible, reusable function that works with any table, any set of columns, and their corresponding values. Here's a clean, safe implementation using OleDb:

First, we'll create a function that accepts the table name, list of column names, and list of parameter values. We'll use parameterized queries to avoid SQL injection and ensure compatibility with OleDb's positional parameter matching.

public void InsertIntoTable(OleDbConnection connection, string tableName, IEnumerable<string> columnNames, IEnumerable<object> parameterValues)
{
    // Validate input to avoid mismatched columns/values
    if (columnNames == null || parameterValues == null)
        throw new ArgumentNullException("Column names and parameter values cannot be null");
    
    var columnsList = columnNames.ToList();
    var valuesList = parameterValues.ToList();
    
    if (columnsList.Count != valuesList.Count)
        throw new ArgumentException("Number of columns must match number of parameter values");

    // Build the INSERT SQL statement
    var quotedColumns = string.Join(", ", columnsList.Select(col => $"[{col}]"));
    var parameterPlaceholders = string.Join(", ", Enumerable.Range(0, columnsList.Count).Select(i => $"@p{i}"));
    var sql = $"INSERT INTO {tableName} ({quotedColumns}) VALUES ({parameterPlaceholders})";

    using (var cmd = new OleDbCommand(sql, connection))
    {
        // Add parameters to the command
        for (int i = 0; i < columnsList.Count; i++)
        {
            cmd.Parameters.Add(new OleDbParameter($"@p{i}", valuesList[i] ?? DBNull.Value));
        }

        try
        {
            connection.Open();
            cmd.ExecuteNonQuery();
        }
        finally
        {
            if (connection.State == ConnectionState.Open)
                connection.Close();
        }
    }
}

Key Details & Notes: #

• Input Validation: We check for null inputs and ensure the number of columns matches the number of values to prevent runtime errors.
• Parameterized Queries: Instead of concatenating values directly into the SQL string, we use placeholders (@p0, @p1, etc.) which protects against SQL injection and handles data type conversion properly.
• Resource Management: The using statement ensures the OleDbCommand is disposed correctly, and we use a finally block to guarantee the connection is closed even if an exception occurs.
• Null Handling: We replace null values with DBNull.Value since OleDb can't handle .NET null directly for database parameters.

How to Use the Function #

Using your original example (inserting into the Panel table), you'd call it like this:

// Assuming you have your connection object ready (conn2)
var tableName = "Panel";
var columns = new List<string> { "Symbol Name SE", "Symbol Name EP" };
var values = new List<object> { variable1, variable2 };

InsertIntoTable(conn2, tableName, columns, values);

Bonus: Connection String Best Practice #

For even better reusability, you could modify the function to accept a connection string instead of an existing connection object. This way, the function manages the connection lifecycle entirely:

public void InsertIntoTable(string connectionString, string tableName, IEnumerable<string> columnNames, IEnumerable<object> parameterValues)
{
    using (var connection = new OleDbConnection(connectionString))
    {
        // Reuse the same validation, SQL building, parameter adding, and execution logic here
        var columnsList = columnNames.ToList();
        var valuesList = parameterValues.ToList();
        
        if (columnsList.Count != valuesList.Count)
            throw new ArgumentException("Number of columns must match number of parameter values");

        var quotedColumns = string.Join(", ", columnsList.Select(col => $"[{col}]"));
        var parameterPlaceholders = string.Join(", ", Enumerable.Range(0, columnsList.Count).Select(i => $"@p{i}"));
        var sql = $"INSERT INTO {tableName} ({quotedColumns}) VALUES ({parameterPlaceholders})";

        using (var cmd = new OleDbCommand(sql, connection))
        {
            for (int i = 0; i < columnsList.Count; i++)
            {
                cmd.Parameters.Add(new OleDbParameter($"@p{i}", valuesList[i] ?? DBNull.Value));
            }

            connection.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

This version is even more self-contained—you just pass in your connection string, table name, columns, and values, and it handles the rest.

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