无法将多个项目插入SQLITE数据库中[英] Cannot insert multiple items into SQLite database

本文是小编为大家收集整理的关于无法将多个项目插入SQLITE数据库中的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

DB具有用于批处理数据的主表,每个批次可以具有零或更多样本.它们在batch.batchid == samples.fk_batchid上链接.这些表的类别如下所示.

我可以在批处理中添加一个值 - 预期的自动批量更新. 我可以为样本添加一个值. 我无法在样本表中添加多个值,并获得

的异常

附加信息:无法添加已使用的密钥的实体.

如果我设置为" 1",那么DB将获得一个新批次,并带有正确引用的新样本.我必须做什么才能允许添加多个样本以进行一批.此外,我理想地喜欢使用相同的上下文和一个" subbitchanges()"操作 - 但是让我在运行前走路.

这是我尝试过的代码:

 Int64 newbatchID = 0;
 using (var context = new Data.BatchContext(connection))
 {    // This 'chunk' work fine and the newbatchID gets the new value
      context.Log = Console.Out;
      Data.Batches newBatch = new Data.Batches {
           Created = System.DateTime.Now.ToString("u"),
           Title = "New Title",
           Varietal = "Waltz"
      };

      // Return the Batch Id if necessary...
      var qs = from c in context.sqlite_sequence
               where c.name == "Batches"
               select c.seq;
      context.Batches.InsertOnSubmit(newBatch);
      context.SubmitChanges();
      newbatchID = qs.ToList().First();
 }

 // Use the new batch ID to submit a load of new samples
 int STOP = 2;     // PROBLEM. If Stop is not 1 the following fails
 using (var context = new Data.BatchContext(connection))
 {
      context.Log = Console.Out;
      List<Data.Samples> samplelist = new List<Data.Samples>();
      for (var i = 0; i < STOP; ++i)
      {    // Just to get different time values
           System.Threading.Thread.Sleep(500);
           samplelist.Add(
                new Data.Samples {
                     // Commenting out the FK_BatchID doesn't help
                     FK_BatchID = newbatchID,
                     Created = System.DateTime.Now.ToString("u"),
                     ImageURI = String.Format("./Path/Img_{0}.jpg", i)
                });
           }
           context.Samples.InsertAllOnSubmit(samplelist);
           context.SubmitChanges();
      }

数据基类

[Table(Name = "Batches")]
public class Batches
{
    public virtual ICollection<Batches> batches { get; set; }
    public Batches()
    {
        batches = new HashSet<Batches>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "BatchID", IsPrimaryKey = true)]
    public Int64? BatchID { get; set; }

    // Batch creation date
    [Column(Name = "Created")]
    public String Created { get; set; }

    // Other String columns the same as Created
    ...
}

[Table(Name = "Samples")]
public class Samples
{
    public virtual ICollection<Samples> samples { get; set; }
    public Samples()
    {
        samples = new HashSet<Samples>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "SampleID", IsPrimaryKey = true)]
    public Int64? SampleID { get; set; }

    // Foreign key to the Batches Table
    private EntityRef<Batches> _batch = new EntityRef<Batches>();
    [Association(Name = "FK_BatchID", IsForeignKey = true, 
                 Storage = "_batch", ThisKey = "FK_BatchID", 
                  OtherKey = "BatchID")]
    public Batches Batches
    {
        get { return _batch.Entity; }
        set { _batch.Entity = value; }
    }
    // Foreign key table entry
    [Column(Name = "FK_BatchID")]
    public Int64? FK_BatchID { get; set; }

    // Date the image was processed by the batch
    [Column(Name = "Created")]
    public String Created { get; set; }
    
    // Other String columns etc
    ...
 }

编辑:

在未成功尝试对列装饰(即ISDBGenerated和/或dbtype等)中进行修改后,我使用下面显示的SQLITE_SECERESS实施了工作.这用于查找各种表ID的值,然后在创建新对象时使用它们.然后可以将这些都包裹在单个交易中

 // Start by getting the latest autoincrement values
 var aiQuery = (from c in context.sqlite_sequence
                select c).ToList();

 Int64 batchId = 1 + aiQuery.Find(q => q.name == @"Batches").seq;
 Int64 sampleId = 1 + aiQuery.Find(q => q.name == @"Samples").seq;
 
 // Create objects etc
 ...

 // Then update the db once only
 context.Batches.InsertOnSubmit(newBatch);
 context.SubmitChanges();

如果没有更好的解决方案,那么我将接受此答案

推荐答案

在没有适当解决方案的情况下,我的解决方法.

使用SQL_Sequence建立自动启动ID值,然后在所创建的对象中明确使用.

var aiQuery = (from sequence in sqlite_sequence
               select sequence).ToList();

BatchId = 1 + aiQuery.Find(q => q.name == @"Batches").seq;
SampleId = 1 + aiQuery.Find(q => q.name == @"Samples").seq;

为了方便起见,我将其放在从linq.datacontext派生的班级中


然后,根据以下伪代码,可以在单个事务中添加多个插入.

Batch batch = new Batch { id = BatchId, ... }
foreach (int i=0; i<n; ++i)
{
   batch.AddSample(new Sample { id = SampleId+i, batchid = BatchId, ... });
}

// Update db
context.Batches.InsertOnSubmit(batch);
context.SubmitChanges();

本文地址:https://www.itbaoku.cn/post/1556923.html

问题描述

The DB has a primary table for batch data, each Batch can have zero or more Samples. They are linked on Batch.BatchID == Samples.FK_BatchID. The Classes for these tables are shown below.

I can add a value to Batches - the autoincrement BatchID updates as expected. I can add a single value to the Samples. I cannot add multiple values to the Samples table and get an exception with

Additional information: Cannot add an entity with a key that is already in use.

If I set STOP to '1' then the db gets a new Batch with a properly referenced new Sample. What must I do to allow multiple Samples to be added for a single Batch. In addition, I'd ideally like to use the same context and a single 'SubmitChanges()' operation - but let me walk before I run.

Here is the code I've attempted:

 Int64 newbatchID = 0;
 using (var context = new Data.BatchContext(connection))
 {    // This 'chunk' work fine and the newbatchID gets the new value
      context.Log = Console.Out;
      Data.Batches newBatch = new Data.Batches {
           Created = System.DateTime.Now.ToString("u"),
           Title = "New Title",
           Varietal = "Waltz"
      };

      // Return the Batch Id if necessary...
      var qs = from c in context.sqlite_sequence
               where c.name == "Batches"
               select c.seq;
      context.Batches.InsertOnSubmit(newBatch);
      context.SubmitChanges();
      newbatchID = qs.ToList().First();
 }

 // Use the new batch ID to submit a load of new samples
 int STOP = 2;     // PROBLEM. If Stop is not 1 the following fails
 using (var context = new Data.BatchContext(connection))
 {
      context.Log = Console.Out;
      List<Data.Samples> samplelist = new List<Data.Samples>();
      for (var i = 0; i < STOP; ++i)
      {    // Just to get different time values
           System.Threading.Thread.Sleep(500);
           samplelist.Add(
                new Data.Samples {
                     // Commenting out the FK_BatchID doesn't help
                     FK_BatchID = newbatchID,
                     Created = System.DateTime.Now.ToString("u"),
                     ImageURI = String.Format("./Path/Img_{0}.jpg", i)
                });
           }
           context.Samples.InsertAllOnSubmit(samplelist);
           context.SubmitChanges();
      }

Data base classes

[Table(Name = "Batches")]
public class Batches
{
    public virtual ICollection<Batches> batches { get; set; }
    public Batches()
    {
        batches = new HashSet<Batches>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "BatchID", IsPrimaryKey = true)]
    public Int64? BatchID { get; set; }

    // Batch creation date
    [Column(Name = "Created")]
    public String Created { get; set; }

    // Other String columns the same as Created
    ...
}

[Table(Name = "Samples")]
public class Samples
{
    public virtual ICollection<Samples> samples { get; set; }
    public Samples()
    {
        samples = new HashSet<Samples>();
    }

    // Primary key - nullable to allow Autoincrement
    [Column(Name = "SampleID", IsPrimaryKey = true)]
    public Int64? SampleID { get; set; }

    // Foreign key to the Batches Table
    private EntityRef<Batches> _batch = new EntityRef<Batches>();
    [Association(Name = "FK_BatchID", IsForeignKey = true, 
                 Storage = "_batch", ThisKey = "FK_BatchID", 
                  OtherKey = "BatchID")]
    public Batches Batches
    {
        get { return _batch.Entity; }
        set { _batch.Entity = value; }
    }
    // Foreign key table entry
    [Column(Name = "FK_BatchID")]
    public Int64? FK_BatchID { get; set; }

    // Date the image was processed by the batch
    [Column(Name = "Created")]
    public String Created { get; set; }
    
    // Other String columns etc
    ...
 }

Edit:

After unsuccessfully trying modifications to the Column adornment (i.e. IsDbGenerated and/or DbType etc) I've implemented a work around using sqlite_sequence shown below. This is used to find values for the various table ID's which are then used when creating the new objects. These can then all be wrapped up in a single transaction

 // Start by getting the latest autoincrement values
 var aiQuery = (from c in context.sqlite_sequence
                select c).ToList();

 Int64 batchId = 1 + aiQuery.Find(q => q.name == @"Batches").seq;
 Int64 sampleId = 1 + aiQuery.Find(q => q.name == @"Samples").seq;
 
 // Create objects etc
 ...

 // Then update the db once only
 context.Batches.InsertOnSubmit(newBatch);
 context.SubmitChanges();

If there are no better solutions then I'll accept this as the answer

推荐答案

In the absence of a proper solution here is my workaround.

Use sql_sequence to establish autoincrement Id values which are then used explicitly in the objects being created.

var aiQuery = (from sequence in sqlite_sequence
               select sequence).ToList();

BatchId = 1 + aiQuery.Find(q => q.name == @"Batches").seq;
SampleId = 1 + aiQuery.Find(q => q.name == @"Samples").seq;

For convenience I placed this in my class deriving from Linq.DataContext


Then multiple inserts can be added in a single transaction as per the following pseudo code.

Batch batch = new Batch { id = BatchId, ... }
foreach (int i=0; i<n; ++i)
{
   batch.AddSample(new Sample { id = SampleId+i, batchid = BatchId, ... });
}

// Update db
context.Batches.InsertOnSubmit(batch);
context.SubmitChanges();