c# - 从 C# 代码插入 SQL 数据库时出现重复键

Question

我正在向最初由另一位程序员编写的程序添加功能，现在我遇到了这个难题。当我向数据库发送此查询时，

INSERT INTO AQS_STATION_Road_Influences (STA_SerialCode,
TangentStreetNumber, TangentStreetName, RoadTypeCode,
TrafficCountValue, TrafficCountYear, DirectionToStreetCode,
TrafficCountSourceCode) Values (4, '45', '56', '3', '365', '2016',
'NE', '54'),  (141, '45', '56', '3', '365', '2016', 'NE', '54')

我明白了

Violation of PRIMARY KEY constraint 'PK_AQS_STATION_Road_Influences'. Cannot insert duplicate key in object 'dbo.AQS_STATION_Road_Influences'. The duplicate key value is (4, 45).

The statement has been terminated.

我已经检查了查询中的表，它没有这样的主键。当我从表中选择 * 时，我得到了我手动放入其中的 3 个条目。具体如下:

These are the current rows in the road influences table

此表的主键是一个复合键，由 STA_SerialCode 和 TangentStreetNumber 组成。令人困惑的是，如果我将我的插入查询直接粘贴到 SQL 管理器中，它运行得很好。我试过一次插入一行，但它仍然抛出同样的错误。我还逐步检查了我的代码，以确保我认为正在传递的查询实际上是我想要的查询。为了便于引用，这是我认为相关的 C# 代码:

public int DbUpdateTable(string strQuery, DataTable dtData)
        {
            int nRows;

            try
            {
                DbProviderFactory factoryProvider = DbProviderFactories.GetFactory(_strDbProvider);
                using (DbConnection connDb = factoryProvider.CreateConnection())
                {
                    try
                    {
                        connDb.ConnectionString = _strDbConnection;
                        connDb.Open();

                        using (DbTransaction dbTrans = connDb.BeginTransaction())
                        {
                            using (DbDataAdapter adapterDb = factoryProvider.CreateDataAdapter())
                            {
                                using (DbCommand cmdDb = connDb.CreateCommand())
                                {
                                    cmdDb.Transaction = dbTrans;
                                    cmdDb.CommandText = strQuery;

                                    if (strQuery.Contains("INSERT INTO"))
                                    {
                                        adapterDb.InsertCommand = cmdDb;
                                    }
                                    else if (strQuery.Contains("DELETE FROM"))
                                    {
                                        adapterDb.DeleteCommand = cmdDb;
                                    }
                                    else
                                    {
                                        adapterDb.SelectCommand = cmdDb;
                                    }

                                    using (DbCommandBuilder bldDb = factoryProvider.CreateCommandBuilder())
                                    {
                                        try
                                        {
                                            bldDb.DataAdapter = adapterDb;
                                            nRows = adapterDb.Update(dtData);
                                            dbTrans.Commit();
                                            return (nRows);
                                        }
                                        catch (Exception dbex)//DbException dbex)
                                        {
                                            Debug.Assert(false);
                                            dbTrans.Rollback();
                                            throw new Exception(dbex.Message);
                                        }
                                    }
                                }
                            }
                        }
                    }
                    catch (DbException ex)
                    {
                        Debug.Assert(false);
                        throw new Exception(ex.Message);
                    }
                    finally
                    {
                        if (connDb.State != ConnectionState.Closed)
                        {
                            connDb.Close();
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Debug.Assert(false);
                Trace.WriteLine("Excpt: " + e.Message, this.ToString());
                throw new CybernetException("QUERY ERROR: " + strQuery);
            }
            finally
            {
            }

        }

如有任何帮助，我们将不胜感激。

编辑:为了便于阅读，我在此处添加了我评论中的主键 SQL 代码。

ALTER TABLE [dbo].[AQS_STATION_Road_Influences] ADD CONSTRAINT
[PK_AQS_STATION_Road_Influences] PRIMARY KEY CLUSTERED (
[STA_SerialCode] ASC, [TangentStreetNumber] ASC )WITH (PAD_INDEX =
OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

编辑 2:

SQL Profiler displaying what appears to be two sent queries.

Answer 1

所以在找了几天之后仍然无法弄清楚为什么插入查询被发送了两次，我最终只是重写了函数，现在它工作得很好。

public int SimpleDBUpdateTable(string strQuery)
    {
        int nRows;

        try
        {
            DbProviderFactory factoryProvider = DbProviderFactories.GetFactory(_strDbProvider);
            using (DbConnection connDb = factoryProvider.CreateConnection())
            {
                try
                {
                    connDb.ConnectionString = _strDbConnection;
                    connDb.Open();
                    using (DbCommand cmdDb = connDb.CreateCommand())
                    {
                        cmdDb.CommandText = strQuery;
                        nRows = cmdDb.ExecuteNonQuery();

                        return nRows;
                    }
                }
                catch (DbException ex)
                {
                    throw;
                }
                finally
                {
                    if (connDb.State != ConnectionState.Closed)
                    {
                        connDb.Close();
                    }
                }
            }
        }
        catch (Exception e)
        {
            throw;
        }
    }

我猜重复与适配器和命令对象有关，但正如我所说，我找不到原因。