ExcelVBA:创建SQLite数据库，以及CRUD基本操作函数(2/2)

继续上篇，以下是SQLite的CRUD基本操作：

1、Create（创建/增）

Sub InsertIntoSQLiteBySQL(conn As Object, strSQL As String)
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    
    ' 设置命令对象的连接
    cmd.ActiveConnection = conn
    
    ' 设置SQL命令文本
    cmd.CommandText = strSQL
    
    ' 执行SQL命令
    cmd.Execute
    
    ' 清理资源
    Set cmd = Nothing
End Sub



Sub InsertIntoSQLiteByRecordset(conn As Object, tableName As String, unitName As String, indexName As String, value As Double)
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 打开Recordset对象
    rs.Open "SELECT * FROM " & tableName, conn, 2, 3 ' adOpenDynamic, adLockOptimistic
    
    ' 添加新记录
    rs.AddNew
    rs.Fields("单位名称").value = unitName
    rs.Fields("指标名称").value = indexName
    rs.Fields("数值").value = value
    rs.Update
    
    ' 关闭Recordset对象
    rs.Close
    Set rs = Nothing
End Sub

2、Read（读取/查）

Function SelectFromSQLite(conn As Object, tableName As String, Optional condition As String = "") As Object

    Dim strSQL As String
    strSQL = "SELECT * FROM " & tableName
    
    If condition <> "" Then
        strSQL = strSQL & " WHERE " & condition
    End If
    
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn, 3, 1 ' adOpenStatic, adLockReadOnly
    
    Set SelectFromSQLite = rs
    
End Function

3、Update（更新/改）

Sub UpdateSQLiteBySQL(conn As Object, tableName As String, id As Long, unitName As String, indexName As String, value As Double)

    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    
    ' 设置命令对象的连接
    cmd.ActiveConnection = conn
    
    ' 构建SQL UPDATE语句
    Dim sql As String
    sql = "UPDATE " & tableName & " SET 单位名称 = ?, 指标名称 = ?, 数值 = ? WHERE ID = ?"
    
    ' 设置SQL命令文本
    cmd.CommandText = sql
    
    ' 添加参数
    cmd.Parameters.Append cmd.CreateParameter("单位名称", 200, 1, 255, unitName)
    cmd.Parameters.Append cmd.CreateParameter("指标名称", 200, 1, 255, indexName)
    cmd.Parameters.Append cmd.CreateParameter("数值", 131, 1, , value)
    cmd.Parameters.Append cmd.CreateParameter("ID", 3, 1, , id)
    
    ' 执行SQL命令
    cmd.Execute
    
    ' 清理资源
    Set cmd = Nothing
    
End Sub


Sub UpdateSQLiteByRecordset(conn As Object, tableName As String, id As Long, unitName As String, indexName As String, value As Double)
    Dim rs As Object
    Set rs = CreateObject("ADODB.Recordset")
    
    ' 打开Recordset对象
    rs.Open "SELECT * FROM " & tableName & " WHERE ID = " & id, conn, 2, 3 ' adOpenDynamic, adLockOptimistic
    
    ' 编辑记录
    If Not rs.EOF Then
    
        rs.Edit
        rs.Fields("单位名称").value = unitName
        rs.Fields("指标名称").value = indexName
 
        rs.Update
        
    End If
    
    ' 关闭Recordset对象
    rs.Close
    Set rs = Nothing

End Sub

4、Delete（删除/删）

Sub DeleteFromSQLiteWithCondition(conn As Object, tableName As String, condition As String)

    Dim strSQL As String
    strSQL = "DELETE FROM " & tableName & " WHERE " & condition
    
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    With cmd
        .ActiveConnection = conn
        .CommandText = strSQL
        .CommandType = 1 ' adCmdText
        .Execute
    End With
    Set cmd = Nothing
    
End Sub

Sub DeleteFromSQLiteBySQL(conn As Object, strSQL As String)

    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")
    
    ' 设置命令对象的连接
    cmd.ActiveConnection = conn
    
    ' 设置SQL命令文本
    cmd.CommandText = strSQL
    
    ' 执行SQL命令
    cmd.Execute
    
    ' 清理资源
    Set cmd = Nothing
    
End Sub

以上函数的名称，沿用了个人习惯，也可以和CRUD对应起来。