C# 使用主键自动增量将数据表添加到 SQL

Question

我有一个数据库，我正在尝试向其中添加一些行。这些行来自一个数据表，问题是我的 SQL Server 表有一个自动增量的主键，我不知道如何完成这个所以我所做的是总是发送一个 1 认为数据库会忽略并放置正确的值但没有't。

我遇到了这个错误

Violation of PRIMARY KEY constraint 'PK_NWActivity'. Cannot insert duplicate key in object 'dbo.Activity'. The duplicate key value is (1).

我的代码是这样的

if (owner_response != null)
{
            DataTable periodData = new DataTable();
            periodData.Columns.Add("idActivity", typeof(int));
            periodData.Columns.Add("eventSubType", typeof(String));
            periodData.Columns.Add("antena", typeof(String));
            periodData.Columns.Add("economico", typeof(String));
            periodData.Columns.Add("Latitude", typeof(float));
            periodData.Columns.Add("Longitud", typeof(float));
            periodData.Columns.Add("Location", typeof(String));
            periodData.Columns.Add("site", typeof(String));
            periodData.Columns.Add("IgnitionOn", typeof(Boolean));
            periodData.Columns.Add("speed", typeof(float));
            periodData.Columns.Add("activityDate", typeof(DateTime));
            periodData.Columns.Add("sensor1", typeof(Boolean));
            periodData.Columns.Add("sensor2", typeof(Boolean));

            foreach (var owner in owner_response.Owners)
            {
                var period_response = new GetPeriodActivityResponse();

                do
                {
                    period_response = proxy.GetActivityByOwner(owner.OwnerId,startTimer , (period_response.Version != 0) ? period_response.Version : 0);

                    if (period_response != null)
                    {
                        periodData.Clear();

                        foreach (var activity in period_response.Activities)
                        {
                            DataRow[] vehiculo = vehicleData.Select("[VehicleId] = '" + activity.VehicleID +"'");
                            DataRow[] sitio = SiteTable.Select("[SiteId] = '" + activity.SiteID + "'");
                            periodData.Rows.Add(
                                1,
                                activity.EventSubType,
                                vehiculo[0][15],
                                vehiculo[0][4],
                                activity.Latitude,
                                activity.Longitude,
                                activity.Location,
                                (sitio.Length > 1) ? sitio[4].ToString() : "",
                                activity.IgnitionOn,
                                activity.Speed,
                                activity.ActivityDateTime.ToLocalTime(),
                                false,
                                false);

                        }
                    }
                } while (period_response.MoreItemsAvailable);

            }

            if (periodData.Rows.Count > 0)
            {
                SqlBulkCopy bc = new SqlBulkCopy(con.ConnectionString, SqlBulkCopyOptions.TableLock);
                bc.DestinationTableName = "Activity";
                bc.BatchSize = periodData.Rows.Count;

                if (con.State != ConnectionState.Open)
                {
                    con.Close();
                    con.Open();
                }

                bc.WriteToServer(periodData);
                bc.Close();
                con.Close();
            }
        }

Answer 1

只需忽略主键列。不要将它添加到 DataTable，服务器应该处理该值。

DataTable periodData = new DataTable();
//periodData.Columns.Add("idActivity", typeof(int));
periodData.Columns.Add("eventSubType", typeof(String));
periodData.Columns.Add("antena", typeof(String));
periodData.Columns.Add("economico", typeof(String));
periodData.Columns.Add("Latitude", typeof(float));
periodData.Columns.Add("Longitud", typeof(float));
periodData.Columns.Add("Location", typeof(String));
periodData.Columns.Add("site", typeof(String));
periodData.Columns.Add("IgnitionOn", typeof(Boolean));
periodData.Columns.Add("speed", typeof(float));
periodData.Columns.Add("activityDate", typeof(DateTime));
periodData.Columns.Add("sensor1", typeof(Boolean));
periodData.Columns.Add("sensor2", typeof(Boolean));
...