插入事务和参数?
我正在学习 VB.Net,需要使用开源 System.Data.SQLite ADO.Net 解决方案来处理 SQLite 数据库
我在 HOWTO 部分找到的示例仅采用 C# 语言。有人可以在 VB.Net 中提供一个简单的示例吗?我可以研究它来了解如何在插入多个参数时使用事务?
FWIW,这是我正在编写的代码:
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
谢谢。
编辑:对于那些感兴趣的人,这里有一些工作代码:
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()
I'm learning about VB.Net and need to work with an SQLite database using the open-source System.Data.SQLite ADO.Net solution
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, 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
Thank you.
Edit: 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()
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
![扫码二维码加入Web技术交流群](/public/img/jiaqun_03.jpg)
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
事务方法应该是相同的(前提是 SQLite API 支持事务)。对于多个参数,您需要为每个参数声明一个 SqlParameter 类实例,然后将每个参数添加到查询中。
至于你的问题“如何判断是否至少一行”,标准 .NET SQLReader 有一个“HasRows”属性。即
我认为 SQLlite 驱动程序也应该如此。
抱歉,如果这段代码不是干净的 VB,我已经大约 5 年没有碰过它了!
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.
As for your question "How to tell if at least one row" the standard .NET SQLReader's have a "HasRows" property. i.e.
I presume the SQLlite driver should as well.
Sorry if this code isn't clean VB, I haven't touched it in about 5 years!