sqlite 运行两个示例时,自动增量不正确

0ve6wy6x  于 2023-03-13  发布在  SQLite
关注(0)|答案(4)|浏览(156)

我有下面代码的2个示例正在运行,它连接到System.Data.SQLite数据库。当我使用任何一个示例向数据库中插入一行时,从其他示例读取时,自动递增的值(ID)不正确。这背后的原因是什么?

Imports System.Data.SQLite
Public Class Form1
    Public cnn As SQLiteConnection
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        cnn = New SQLiteConnection("Data Source=\\abc\xx\x_backup.db;Password=password;Connect Timeout=55;FailIfMissing=True")
        cnn.ParseViaFramework = True
        cnn.Open()
    End Sub
    Public Function inserttoTable(ByVal sql As String) As DataTable

         Try
            sql = "SELECT max(ID) FROM joblog;"
            Dim mycommand As SQLiteCommand = New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            MsgBox(mycommand.ExecuteScalar)
            sql = "INSERT INTO joblog (jobid) VALUES (123);"

            mycommand = New SQLiteCommand(cnn)
            mycommand.CommandText = sql
            MsgBox(mycommand.ExecuteNonQuery())
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
ulydmbyx

ulydmbyx1#

当我使用任何一个示例向数据库中插入行时,从其他示例读取时,自动递增的值(ID)不正确
我 * 怀疑这是因为你的连接和命令没有关闭/处理 *,所以它们是打开的。
连接的最佳建议是使用它,然后关闭/释放它。命令也是如此,确保它们被释放。
这里有一个方法可以做到这一点:

Dim intReturn As Integer
 sql = "SELECT max(ID) FROM joblog;"

 ' First query to get your scalar return value
 Using cnn As New New SQLiteConnection("Data Source=\\abc\xx\x_backup.db;Password=password;Connect Timeout=55;FailIfMissing=True")
    Using mycommand As New SQLiteCommand(sql, cnn)
       cnn.ParseViaFramework = True
       cnn.Open()

       intReturn = mycommand.ExecuteScalar
       MessageBox.Show(intReturn)

    End Using
 End Using

 ' Second query to actually do the insert
 sql = "INSERT INTO joblog (jobid) VALUES (123);"
 Using cnn As New New SQLiteConnection("Data Source=\\abc\xx\x_backup.db;Password=password;Connect Timeout=55;FailIfMissing=True")
    Using mycommand As New SQLiteCommand(sql, cnn)
       cnn.ParseViaFramework = True
       cnn.Open()

       intReturn = mycommand.ExecuteNonQuery()
       MessageBox.Show("Records affected " & intReturn)

    End Using
 End Using

另一方面,请考虑使用SQL Parameters,否则您可能很容易受到SQL注入攻击。

tzdcorbm

tzdcorbm2#

我使用的函数在某种程度上等同于Sql Server中的Scope_Identity。我没有SqLite数据库,所以无法进行测试。我不确定是否可以在SqLite中链接命令,但如果不能,则只需进行2个单独的查询。ExecuteScalar返回结果集中第一行的第一列,这就是您想要的数字。

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    Dim NewID As Long
    Using cn As New SQLiteConnection("Your connection string")
        Using cmd As New SQLiteCommand("INSERT INTO joblog (jobid) VALUES (123); Select last_insert_rowid()")
            NewID = CLng(cmd.ExecuteScalar())
        End Using
    End Using
    MessageBox.Show($"The new ID is {NewID}")
End Sub
wr98u20j

wr98u20j3#

简单的答案是你不应该使用共享的sqlite数据库文件,它不稳定,而且数据库文件很容易损坏,使用mariadb/postgresql是一个更好的主意。
我认为你的问题是关于事务的,你应该手动提交你的更改,以便在更新后立即更新数据库文件。

1. SQLiteTransaction trans = conn.BeginTransaction();
2. select max(id), insert...
3. trans.commit();
dojqjjoe

dojqjjoe4#

是的,我发现了同样的问题。下面是我解决这个问题的方法。我在MS SQL Server中创建了一个名为“SeqNo”的表。下面是创建该表的代码:

CREATE TABLE [dbo].[SeqNo](
    [SeqNoCode] [varchar](50) NOT NULL,
    [SeqNo] [bigint] NOT NULL,
 CONSTRAINT [PK_SeqNo] PRIMARY KEY CLUSTERED 
(
    [SeqNoCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

然后使用下面的VB.Net函数检索下一个序号。

Public Function NextSeqNo(ByVal SeqNoCode As String) As Long
        Dim strSQL As String = "DECLARE @SeqNo bigint;" & vbCrLf &
            "SET NOCOUNT ON;" & vbCrLf &
            "BEGIN TRANSACTION;" & vbCrLf &
            "IF (SELECT COUNT(*) FROM SeqNo WHERE SeqNoCode = '" & SeqNoCode & "') = 0" & vbCrLf &
                "INSERT INTO SeqNo (SeqNoCode, SeqNo) VALUES ('" & SeqNoCode & "', 0);" & vbCrLf &
            "SELECT @SeqNo = SeqNo FROM SeqNo WITH (TABLOCK, HOLDLOCK) WHERE SeqNoCode = '" & SeqNoCode & "'" & vbCrLf &
            "SET @SeqNo = @SeqNo + 1" & vbCrLf &
            "UPDATE SeqNo SET SeqNo = @SeqNo WHERE SeqNoCode = '" & SeqNoCode & "'" & vbCrLf &
            "COMMIT TRANSACTION" & vbCrLf &
            "SELECT @SeqNo"
        Dim scmdNextSeqNo As New SqlCommand(strSQL, GlobalVars.SQLConnection)
        Dim objNextSeqNo As Object = scmdNextSeqNo.ExecuteScalar
        Return CLng(objNextSeqNo)
    End Function

请注意,它包含了一个事务,该事务锁定表,递增序列号,然后解锁表。

相关问题