SQL Server单连接多命令批量插入代码是否正确关闭连接？求优化建议

Hey Daniel, let's break this down clearly for you—first addressing your connection closure concern, then diving into better patterns for your bulk insert scenario.

Can Your Current Connection Close Properly? #

Looking at the code snippet you shared:

var conn = new SqlConnection(ConnString); 
conn.Open(); 
var data = new Dictionary<string, List<object>>(); 
foreach (var h in hours) {
    // ... your insertion logic here
}
// I assume you have a conn.Close() somewhere later?

The short answer is: probably not, unless you're wrapping everything in a try/finally block. Here's why:

• If any exception gets thrown inside that foreach loop (like a SQL constraint violation, bad parameter value, or network blip), your code will jump straight to exception handling, skipping the conn.Close() call entirely. That leaves the connection hanging until SQL Server's connection pool times out, which can lead to pool exhaustion, slowdowns, or outright errors.
• Even if you remember to call Close() manually, it's error-prone—any future code changes that add conditional branches could accidentally skip that line, creating a silent resource leak.

Optimized Solutions #

1. Use using Statements (Non-Negotiable for Connection Management) #

SqlConnection implements the IDisposable interface, which means the using statement will automatically call Dispose() when the block exits—whether it exits normally or due to an exception. Dispose() handles closing the connection and returning it to the connection pool, which is the reliable, industry-standard way to manage SQL connections in .NET.

Here's how to refactor your code with using:

// The using block ensures the connection is closed/released no matter what
using (var conn = new SqlConnection(ConnString))
{
    conn.Open();
    var data = new Dictionary<string, List<object>>();
    
    foreach (var h in hours)
    {
        // Wrap your SqlCommand in a using too—commands are disposable too!
        using (var insertCmd = new SqlCommand("INSERT INTO YourTable (Col1, Col2) VALUES (@Val1, @Val2)", conn))
        {
            // Set your parameters here to avoid SQL injection
            insertCmd.Parameters.AddWithValue("@Val1", h.SomeProperty);
            insertCmd.Parameters.AddWithValue("@Val2", h.AnotherProperty);
            
            insertCmd.ExecuteNonQuery();
        }
    }
}
// Connection is already closed and back in the pool by this point

2. Upgrade to SqlBulkCopy for Bulk Inserts #

Since you're inserting multiple records at once, looping through individual INSERT statements is inefficient—especially as your hours list grows. SqlBulkCopy is built specifically for this scenario, letting you insert hundreds/thousands of records in a single round-trip to the database, which drastically improves performance.

Here's a quick example (assuming you can map your hours data to a DataTable):

// First, convert your hours collection to a DataTable matching your target DB table
var bulkTable = new DataTable();
bulkTable.Columns.Add("HourId", typeof(int));
bulkTable.Columns.Add("HourValue", typeof(decimal));
// Add other columns as needed

foreach (var h in hours)
{
    var row = bulkTable.NewRow();
    row["HourId"] = h.Id;
    row["HourValue"] = h.Value;
    bulkTable.Rows.Add(row);
}

// Use SqlBulkCopy to push all rows at once
using (var conn = new SqlConnection(ConnString))
{
    conn.Open();
    using (var bulkCopy = new SqlBulkCopy(conn))
    {
        bulkCopy.DestinationTableName = "YourTargetDatabaseTable";
        // If your DataTable column names match the DB table, no need for mappings
        // If not, add mappings like: bulkCopy.ColumnMappings.Add("DataTableCol", "DBTableCol");
        
        bulkCopy.WriteToServer(bulkTable);
    }
}

3. Small Tweak for Repeated Commands #

If you stick with individual inserts (for small datasets), avoid recreating the SqlCommand every loop—reuse it and just update parameter values to cut down on object creation overhead:

using (var conn = new SqlConnection(ConnString))
{
    conn.Open();
    // Create the command once outside the loop
    using (var insertCmd = new SqlCommand("INSERT INTO YourTable (Col1, Col2) VALUES (@Val1, @Val2)", conn))
    {
        // Define parameters once
        insertCmd.Parameters.Add("@Val1", typeof(int));
        insertCmd.Parameters.Add("@Val2", typeof(decimal));
        
        foreach (var h in hours)
        {
            // Just update parameter values each loop
            insertCmd.Parameters["@Val1"].Value = h.Id;
            insertCmd.Parameters["@Val2"].Value = h.Value;
            
            insertCmd.ExecuteNonQuery();
        }
    }
}

Final Takeaways #

• Your original code won't reliably close connections unless wrapped in try/finally or using—always use using for disposable resources like SqlConnection and SqlCommand.
• For bulk inserts, SqlBulkCopy is the way to go for performance and clean code.
• Reusing commands instead of recreating them saves small overhead, which adds up for large datasets.

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