Dim Nrsj As New Data.SQLite.SQLiteConnection
Nrsj.ConnectionString = "Data Source=" & _
"C:\SqliteDev\轉換"
Try
Nrsj.Open()'測試數據庫連接
Catch ex As Exception
MsgBox(ex.Message)
exit sub
End Try
Dim Nr As New Data.SQLite.SQLiteCommand’定義壹個SQL操作
Nr.Connection = Nrsj
Nr.CommandType = CommandType.Text
Dim iD_tJ As New System.Collections.SortedList
Nr.CommandText = " select id,途徑 from 文件 "
Dim Sj As Data.SQLite.SQLiteDataReader = Nr.ExecuteReader
While Sj.Read
iD_tJ.Add(Sj(0), Sj(1))
End While
Sj.Close()
' Nr.CommandText = " select Nr from 文件 WHERE 途徑=@途徑"
Nr.CommandText = " select Nr from 文件 WHERE ID=@ID"
Dim Xrsj As New Data.SQLite.SQLiteConnection
Xrsj.ConnectionString = "Data Source=" & _
System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\MB.Bmy"
Try
Xrsj.Open()
Catch ex As Exception
MsgBox(ex.Message)
End Try
Dim Xr As New Data.SQLite.SQLiteCommand
Xr.Connection = Xrsj
Xr.CommandType = CommandType.Text
Xr.CommandText = "select max(id) from 表格模版"
Dim MaxId As Int32 = Xr.ExecuteScalar
Xr.CommandText = "update 表格模版 set 表格=@表格 where id=@id "
' Xr.CommandText = "insert into 表格模版(fid,名稱,序號,目錄,表格) values (@fid,@名稱,@序號,@目錄,@表格)"
Dim Sw As Data.Common.DbTransaction = Xrsj.BeginTransaction‘開啟數據庫事務
For id As Int32 = 0 To MaxId - 1
Nr.Parameters.Clear()
Dim Tj As String = "C:\******\" & id & ".cll"
If iD_tJ.ContainsValue(Tj) Then
Nr.Parameters.Add("@id", DbType.Int64).Value = iD_tJ.GetKey(iD_tJ.IndexOfValue(Tj))
Xr.Parameters.Clear()
Xr.Parameters.Add("@表格", DbType.Binary).Value = Nr.ExecuteScalar
Xr.Parameters.Add("@id", DbType.Int64).Value = id
Xr.ExecuteNonQuery()
End If
Next
Sw.Commit()’提交事務。
MsgBox("ok")