Private Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long
Dim oDB As Object
Public dbPath As String '存储 数据库路径
Private Sub Class_Initialize()
Dim lb As Long
lb = LoadLibrary(App.Path & "\sqlite3.dll")
Set oDB = CreateObject("LiteX.LiteConnection")
End Sub Private Sub openDB()
oDB.Open (dbPath)
End Sub Public Function Insert(ByVal tableName As String, ByRef arr() As String) As String '插入 数据库的操作，返回受影响的行数,tableName为表名,arr为插入的字段名和值的二维数组
Dim str_Fields As String, str_Values As String, strSql As String
strSql = "insert into " & tableName
For I = To UBound(arr)
str_Fields = str_Fields & "'" & arr(I, ) & "',"
str_Values = str_Values & "'" & Trim(Replace(arr(I, ), "'", "''")) & "',"
Next I
str_Fields = Mid(str_Fields, , Len(str_Fields) - )
str_Values = Mid(str_Values, , Len(str_Values) - )
strSql = strSql & "(" & str_Fields & ") values (" & str_Values & ")"
openDB (dbPath)
oDB.Execute (strSql)
Insert = oDB.changes
oDB.Close
End Function Public Function Query(ByVal tableName As String, Optional ByVal Fields As String = "", Optional Condition As String = "", Optional ByVal Exp As String = "") As String() '查询数据。返回结果数组，tableName 表名，fields是你要查询哪些字段,condition是查询条件也就是where， 为其它条件。例如order by ，group by，limit，offset等
Dim arr() As String, sqlStr As String, K As Integer
Dim odbRS As Object
Set odbRS = CreateObject("LiteX.LiteStatement")
Fields = IIf(Fields = "", "*", Fields)
odbRS.ActiveConnection = oDB
sqlStr = "select " & Fields & " from " & tableName & IIf(Condition = "", "", " where " & Condition & " ") & IIf(Exp = "", "", " " & Exp & " ")
Debug.Print sqlStr
openDB (dbPath)
d = odbRS.prepare(sqlStr)
If odbRS.Rows.Count = Then
Query = arr
Else
ReDim arr(odbRS.Rows.Count - , odbRS.Columns.Count - )
For Each Row In odbRS.Rows
For I = To odbRS.Columns.Count -
arr(K, I) = Row(I)
Next I
K = K +
Next
End If
Query = arr
odbRS.Close
oDB.Close
End Function Public Function Modify(ByVal tableName As String, ByRef arr() As String, Optional Condition As String = "") As Integer'实现数据修改功能。tableName表名，arr和insert方法一样的。Condition 为条件，返回受影响的行数
Dim str_ As String, strSql As String
strSql = "update " & tableName & " set "
For I = To UBound(arr)
str_ = str_ & "`" & Trim(arr(I, )) & "`='" & Trim(Replace(arr(I, ), "'", "''")) & "',"
Next I
strSql = strSql & Mid(str_, , Len(str_) - ) & IIf(Condition = "", "", " where " & Condition & " ")
Debug.Print strSql
openDB (dbPath)
oDB.Execute (strSql)
Modify = oDB.changes
oDB.Close
End Function Public Function Delete(ByVal tableName As String, Optional ByVal Condition As String = "") As Integer'删除数据的功能。tableName为表名，condition为条件，返回受影响的行数。
Dim str_ As String
strSql = "delete from " & tableName & IIf(Condition = "", "", " where " & Condition)
Debug.Print strSql
openDB (dbPath)
oDB.Execute (strSql)
Delete = oDB.changes
oDB.Close
End Function
查询
Dim sqlite As New ClsSqlite
sqlite.dbPath = 这里搞上你的数据库的路径
arr = sqlite.Query("这里是表名", "", "`username`='小松'")
'将返回查询结果数组
添加
Private Function makeArr() As String()'本函数为制作一个要插入数据的数组。
Dim arr() As String
ReDim arr(, )
arr(, ) = "name" '这个是字段名
arr(, ) = txtName.Text '这个是字段值，下同
arr(, ) = "oldname"
arr(, ) = txtOldname.Text
arr(, ) = "sex"
arr(, ) = cmbSex.Text
makeArr = arr
End Function
Private Sub cmdAdd_Click()
Dim pSqlite As New ClsSqlite
arr = makeArr '这里调用makeArr得到要插入的数据的数组
pSqlite.Insert("populations", arr) '执行插入
End Sub
3、修改
修改和添加记录基本上差不多。也需要makeArr，唯一不同的是一个调用类里面的insert方法，一个调用类里面的modify方法，还有就是既然你要修改数据，需要指定条件，要修改哪个ID的数据。
pSqlite.Modify("populations", arr, "`uid`='" & Uid & "'") 'uid就是指定要修改uid为UID的这一条数据。
4、删除
删除最简单。提供一个表名，和一个条件即可。
|
利用本人提供的这个sqlite3的dll文件，你用vb6就不用复杂的操作，直接用LoadLibrary函数就可以直接操作sqlite数据库了。 sqlite3 litex 版请下载此附件： 附操作数据库的类模块代码以及使用方法。 类模块代码【添加一个类，比如我们取名为ClsSqlite】 |