插入事务和参数? [英] INSERT with transaction and parameters?

问题描述

我正在学习VB.Net，需要使用开源System.Data.SQLite ADO.Net解决方案使用SQLite数据库

I'm learning about VB.Net and need to work with an SQLite database using the open-source System.Data.SQLite ADO.Net solution

我在HOWTO部分中找到的示例仅在C#中.有人在VB.Net中有一个简单的示例，我可以研究该示例以了解在插入多个参数时如何使用事务吗?

The examples I found in the HOWTO section are only in C#. Would someone have a simple example in VB.Net that I could study to understand how to use transactions when INSERTing multiple parameters?

FWIW，这是我正在处理的代码:

FWIW, here's the code I'm working on:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim SQLconnect As New SQLite.SQLiteConnection()
    Dim SQLcommand As SQLite.SQLiteCommand
    Dim SQLtransaction As SQLite.SQLiteTransaction

    SQLconnect.ConnectionString = "Data Source=test.sqlite;"
    SQLconnect.Open()

    SQLcommand = SQLconnect.CreateCommand

    SQLcommand.CommandText = "CREATE TABLE IF NOT EXISTS files (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, hash TEXT);"
    SQLcommand.ExecuteNonQuery()

        '================ INSERT starts here
    SQLtransaction = SQLconnect.BeginTransaction()
    Dim myparam As New SQLite.SQLiteParameter()

    SQLcommand.CommandText = "INSERT INTO [files] ([name],[hash]) VALUES(?,?)"

    SQLcommand.Parameters.Add(myparam)

    'How to set all parameters? myparam.Value

    SQLcommand.ExecuteNonQuery()
    SQLtransaction.Commit()
        '================ INSERT ends here

    SQLcommand.CommandText = "SELECT id,name,hash FROM files"
    'How to tell if at least one row?
    Dim SQLreader As SQLite.SQLiteDataReader = SQLcommand.ExecuteReader()
    While SQLreader.Read()
        ListBox1.Items.Add(SQLreader(1))
    End While

    SQLcommand.Dispose()
    SQLconnect.Close()
End Sub

谢谢.

对于那些感兴趣的人，这里是一些有效的代码:

For those interested, here's some working code:

SQLtransaction = SQLconnect.BeginTransaction()
SQLcommand.CommandText = "INSERT INTO files (name,hash) VALUES(@name,@hash)"
SQLcommand.Parameters.AddWithValue("@name", "myfile")
SQLcommand.Parameters.AddWithValue("@hash", "123456789")
SQLcommand.ExecuteNonQuery()
SQLtransaction.Commit()

推荐答案

事务处理方法应该相同(前提是SQLite API支持事务处理.)对于多个参数，您需要为每个参数声明一个SqlParameter类实例，然后将每个添加到查询中.

The transaction approach should be the same (providing the SQLite API supports transactions.) As for multiple parameters, you need to declare a SqlParameter class instance for each parameter, then add each one to the query.

Dim myparam As New SQLite.SQLiteParameter()
myparam.Value = "Parameter 1's value"

Dim myparam2 As New SQLite.SQLiteParameter()
myparam2.Value = "Parameter 2's value"

SQLcommand.Parameters.Add(myparam)
SQLcommand.Parameters.Add(myparam2)

对于您的问题如何分辨至少一行"，标准.NET SQLReader具有"HasRows"属性.即

As for your question "How to tell if at least one row" the standard .NET SQLReader's have a "HasRows" property. i.e.

If SQLreader.HasRows Then
    While SQLreader.Read()
        ListBox1.Items.Add(SQLreader(1))
    End While
End If

我想也应该使用SQLlite驱动程序.

I presume the SQLlite driver should as well.

很抱歉，如果这段代码不是干净的VB，那么我已经有5年没有碰过它了！

Sorry if this code isn't clean VB, I haven't touched it in about 5 years!

这篇关于插入事务和参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！