C#调用sp_executesql实现批量插入的问题咨询

Hey there! I’ve dealt with this exact head-scratcher before—when your bulk insert runs perfectly in SSMS but does nothing (no errors!) from your C# WinForm app. Let’s break down the most likely culprits and how to fix them:

Common Causes & Fixes #

1. Uncommitted Transactions #

This is the #1 suspect. If your C# code is using a transaction but forgetting to commit it, all your inserts will silently roll back.

Check if you’re doing something like this (and missing the commit):

using (var conn = new SqlConnection(yourConnString))
{
    conn.Open();
    var tran = conn.BeginTransaction(); // Transaction started
    try
    {
        var cmd = new SqlCommand("sp_executesql", conn, tran);
        // ... set up parameters and execute ...
        // Oops! No tran.Commit(); here
    }
    catch
    {
        tran.Rollback();
        throw;
    }
}

Fix: Add tran.Commit(); after executing your command (inside the try block, right after ExecuteNonQuery()).

2. Incorrect sp_executesql Parameter Binding #

If you’re concatenating your bulk insert SQL directly into the command string instead of passing it as a parameter to sp_executesql, you might be hitting syntax issues (like unescaped quotes) that don’t throw errors but result in a no-op.

❌ Wrong approach (string concatenation):

var cmd = new SqlCommand($"exec sp_executesql {bulkInsertSql}", conn);
cmd.ExecuteNonQuery();

✅ Correct approach (parameterized sp_executesql call):

using (var cmd = new SqlCommand("sp_executesql", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;
    // Use MAX-length type to avoid truncating long SQL strings
    cmd.Parameters.Add(new SqlParameter("@stmt", SqlDbType.NVarChar, -1) 
    { 
        Value = bulkInsertSql 
    });
    cmd.ExecuteNonQuery();
}

3. Mismatched Connection Strings #

Double-check that your C# app’s connection string is pointing to the exact same database you’re testing in SSMS. It’s easy to accidentally connect to a different instance (e.g., local vs. remote) or a staging database instead of production.

Quick test: Add a query to verify the current database:

using (var cmd = new SqlCommand("SELECT DB_NAME()", conn))
{
    var currentDb = cmd.ExecuteScalar().ToString();
    Console.WriteLine($"Connected to: {currentDb}");
}

Compare this to the database you’re querying in SSMS.

4. Silent Permission Issues #

Even if you don’t get an error, the account your WinForm app is using (e.g., Windows auth for the current user, or a service account) might lack INSERT permissions on the target table.

• Check the database’s error logs for any denied access entries.
• Wrap your execution code in a try-catch to capture hidden SqlExceptions (sometimes exceptions are swallowed in your code):

try
{
    // Your sp_executesql call here
}
catch (SqlException ex)
{
    foreach (var error in ex.Errors)
    {
        Console.WriteLine($"SQL Error: {error.Message} (State: {error.State})");
    }
}

5. Truncated Bulk Insert SQL #

If your generated bulk insert string is extremely long, the default parameter length might truncate it. This results in a partial (or invalid) SQL statement that executes without error but doesn’t insert any data.

Fix: Ensure your @stmt parameter uses a MAX-length type (like SqlDbType.NVarChar, -1 as shown in the correct parameter binding example above).

Step-by-Step Troubleshooting Checklist #

1. Log/print your bulkInsertSql string from C#, then run it directly in SSMS. If it doesn’t insert data here, the problem is with your generated SQL—not the C# call.
2. Verify transactions are being committed properly.
3. Check connection string alignment between C# and SSMS.
4. Capture all SQL exceptions to uncover hidden errors.

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