程序中的临时缓存

最新推荐文章于 2023-04-13 10:42:51 发布

lmdcszh

最新推荐文章于 2023-04-13 10:42:51 发布

阅读量1k

点赞数

分类专栏： VB 文章标签： integer string date 数据库网格

本文链接：https://blog.csdn.net/lmdcszh/article/details/6678940

版权

VB 专栏收录该内容

17 篇文章 0 订阅

订阅专栏

临时缓存是一种思想，在程序中应用很广泛，由于有时候要操作的数据量很大，或者需要借助一个临时的容器来盛放一些数据，那么在这种时候就用到了临时缓存的思想，也就是建立一个临时容器。

第一次接触是在做分页显示那个小例子的时候，当时就觉得很经典，现在回头再看，仍有新的感触，尤其是对自定义过程方面又增加了一些新的认识。

   '此过程实现分页显示记录功能
   '建立一个临时缓存（新的记录集），然后从中获取数据
    '用到了记录集的pagesize和absolutepage属性
    '基本实现思路：intPage作为自定义过程的数据传入接口，
    '记录要显示的当前页，另外内部须重新定义一个记录集，
    '具有和数据源，相同的字段名、字段类型及字段大小，
    '当然也少不了一个循环变量，用于控制添加新的记录

Public Sub ShowData(ByVal intPage As Integer)
    '实现分页记录功能
    Dim intRecord As Integer
    Dim objDataSource As ADODB.Recordset
    '创建一个局部记录集，保存源数据记录集（objRs）的当前记录
    For intRecord = 0 To objRs.Fields.Count - 1
        objDataSource.Fields.Append objRs.Fields(intRecord).Name, adVarChar, objRs.Fields(intRecord).DefinedSize
    Next
    '打开局部记录集对象
    objDataSource.Open
    
    '重置objRs每页显示的记录条数
    objRs.PageSize = Val(txtPageSize)
    
    '重置objRs当前记录页
    objRs.AbsolutePage = intPage
    
    '将源记录中当前页的数据写入临时缓存objDataSource
    For intRecord = 1 To objRs.PageSize
        objDataSource.AddNew
        objDataSource!用户名 = objRs!用户名
        objDataSource!口令 = objRs!口令
        objDataSource!身份 = objRs!身份
        objRs.MoveNext
        If objRs.EOF = True Then Exit Sub
    Next
    
    '设置为数据网格的数据源
    Set DataGrid.DataSource = objDataSource
    '刷新当前记录页信息
    txtPageMsg = intPage & "/" & objRs.PageCount
    
End Sub

在机房收费系统的强制所有学生下机的窗体中也遇到了临时缓存的问题。它涉及到了数据库中多表的嵌套操作，还用到了双重循环。大致的要求是获取在线信息表中的全部数据，并从基本数据表中动态获取基本信息，主要是针对不同用户的不同单价，进行相关的消费金额、余额、上下机时间等计算后，将数据显示到MSFlexgrid控件中，然后再将数据添加到上机记录表中，同时还要更新学生信息表的余额和时间等信息。代码实现如下：

Private Sub cmdOk_Click()
 
    '将online_info表中的内容，保存到myflexgrid中，
    '作为一个临时缓存，然后经计算后再写到line_info表中
    Dim txtSQLA As String
    Dim strA As String
    Dim RstA As ADODB.Recordset
    
    Dim ShangDate As Date
    Dim ShangTime As Date
    Dim startTime As Date
    Dim endTime As Date
    Dim xiaTime As Date
    Dim xiaDate As Date
    Dim durTime As Long
    
    Dim myMbookMark As Variant
    
    xiaTime = Format(Now, "hh:mm")
    xiaDate = Format(Date, "yyyy-MM-dd")
    '获取基本数据
    Dim txtSQL As String
    Dim MsgText As String
    Dim Rst As ADODB.Recordset
    txtSQL = "select * from BasicData_Info"
    Set Rst = ExecuteSQL(txtSQL, MsgText)
        UnitTime = Rst.Fields(2).Value
        LeastTime = Rst.Fields(3).Value
        PrepareTime = Rst.Fields(4).Value
        LeastCash = Rst.Fields(5).Value
    Rst.Close
    
    '从在线表中读取信息到myflexgrid控件中，并进行计算，
    '将所有要处理的数据计算出来，呈现到临时缓存中
    txtSQLA = "select * from online_info"
    Set RstA = ExecuteSQL(txtSQLA, strA)
    If RstA.EOF = True And RstA.BOF = True Then
        MsgBox "没有学生上机", vbOKOnly, "提示'"
        Exit Sub
    Else
        Dim i As Integer
        Dim Count As Integer
        Count = RstA.RecordCount
        '将数据写入到缓存中，并进行相关计算
        With myFlexGrid
            .CellAlignment = 4
            myFlexGrid.TextMatrix(0, 0) = "卡号"
            myFlexGrid.TextMatrix(0, 1) = "学号"
            myFlexGrid.TextMatrix(0, 2) = "姓名"
            myFlexGrid.TextMatrix(0, 3) = "院系"
            myFlexGrid.TextMatrix(0, 4) = "性别"
            myFlexGrid.TextMatrix(0, 5) = "上机日期"
            myFlexGrid.TextMatrix(0, 6) = "上机时间"
            myFlexGrid.TextMatrix(0, 7) = "下机日期"
            myFlexGrid.TextMatrix(0, 8) = "下机时间"
            myFlexGrid.TextMatrix(0, 9) = "消费时间"
            myFlexGrid.TextMatrix(0, 10) = "消费金额"
            myFlexGrid.TextMatrix(0, 11) = "上期卡内余额"
            myFlexGrid.TextMatrix(0, 12) = "卡内最新余额"
            myFlexGrid.TextMatrix(0, 13) = "用户类型"
            myFlexGrid.TextMatrix(0, 14) = "状态"
            myFlexGrid.TextMatrix(0, 15) = "机器号"
            .Rows = 1
            For i = 1 To RstA.RecordCount
                .Rows = .Rows + 1
                myFlexGrid.TextMatrix(.Rows - 1, 1) = RstA!studentNo
                Dim txtSQL2 As String
                Dim MsgText2 As String
                Dim Rst2 As ADODB.Recordset
                Dim j As Integer
                txtSQL2 = "select * from student_info"
                Set Rst2 = ExecuteSQL(txtSQL2, MsgText2)
                    For j = 1 To Rst2.RecordCount
                       If myFlexGrid.TextMatrix(.Rows - 1, 1) = Rst2!studentNo Then
                            myFlexGrid.TextMatrix(.Rows - 1, 11) = Trim(Rst2!Cash)
                            myFlexGrid.TextMatrix(.Rows - 1, 14) = Trim(Rst2!status)
                            Rst2.MoveFirst
                            Exit For
                       Else
                            Rst2.MoveNext
                       End If
                    Next j
                '为什么没有这句话也行啊？
                Rst2.Close
                myFlexGrid.TextMatrix(.Rows - 1, 0) = RstA!cardno
                myFlexGrid.TextMatrix(.Rows - 1, 2) = RstA!studentName
                myFlexGrid.TextMatrix(.Rows - 1, 3) = RstA!department
                myFlexGrid.TextMatrix(.Rows - 1, 4) = RstA!sex
                myFlexGrid.TextMatrix(.Rows - 1, 5) = RstA!onDate
                myFlexGrid.TextMatrix(.Rows - 1, 6) = RstA!onTime
                myFlexGrid.TextMatrix(.Rows - 1, 7) = Trim(xiaDate)
                myFlexGrid.TextMatrix(.Rows - 1, 8) = Trim(xiaTime)
                '获取用户类型
                'myFlexGrid.TextMatrix(.Rows - 1, 13) = Trim(RstA!Type)
                myFlexGrid.TextMatrix(.Rows - 1, 13) = "固定用户"
                      
               '获取上机时间和日期，以及开始的具体时间，本次上机消费时间
               ShangDate = Trim(RstA!onDate)
               ShangTime = Trim(RstA!onTime)
               startTime = ShangDate + ShangTime
               endTime = Now
               durTime = DateDiff("n", startTime, endTime)
              
               '确定最低消费时间
               If durTime > 0 Then
                  If durTime < PrepareTime Then
                      durTime = 0
                  Else
                      If durTime < LeastTime Then
                          durTime = LeastTime
                      End If
                  End If
               End If
                myFlexGrid.TextMatrix(.Rows - 1, 9) = durTime
                 '动态获取不同类型用户的单价,计算本次消费金额
                Dim txtSQL1 As String
                Dim MsgText1 As String
                Dim Rst1 As ADODB.Recordset
                Dim k As Integer
                txtSQL1 = "select * from BasicData_Info"
                Set Rst1 = ExecuteSQL(txtSQL1, MsgText1)
                    If myFlexGrid.TextMatrix(.Rows - 1, 13) = "固定用户" Then
                        DanJia = Val(Rst1.Fields(0).Value)
                        myFlexGrid.TextMatrix(.Rows - 1, 10) = Val(DanJia * Val(myFlexGrid.TextMatrix(.Rows - 1, 9)))
                    ElseIf myFlexGrid.TextMatrix(.Rows - 1, 13) = "临时用户" Then
                        DanJia = Val(Rst1.Fields(1).Value)
                        myFlexGrid.TextMatrix(.Rows - 1, 10) = Val(DanJia * Val(myFlexGrid.TextMatrix(.Rows - 1, 9)))
                    End If
                Rst1.Close
                '显示最新余额
                myFlexGrid.TextMatrix(.Rows - 1, 12) = Val(myFlexGrid.TextMatrix(.Rows - 1, 11)) - Val(myFlexGrid.TextMatrix(.Rows - 1, 10))
                '显示机器号
                myFlexGrid.TextMatrix(.Rows - 1, 15) = RstA!computer
                 RstA.MoveNext
            Next i
        End With
        RstA.Close
    End If
    
    '将数据写入到上机记录表中
    Dim txtSQLB As String
    Dim strB As String
    Dim RstB As ADODB.Recordset
    txtSQLB = "select * from line_info"
    Set RstB = ExecuteSQL(txtSQLB, strB)
            With myFlexGrid
                For i = 1 To Count
                    RstB!cardno = Trim(myFlexGrid.TextMatrix(i, 0))
                    RstB!studentNo = Trim(myFlexGrid.TextMatrix(i, 1))
                    RstB!studentName = Trim(myFlexGrid.TextMatrix(i, 2))
                    RstB!sex = Trim(myFlexGrid.TextMatrix(i, 4))
                    RstB!onDate = Trim(myFlexGrid.TextMatrix(i, 5))
                    RstB!onTime = Trim(myFlexGrid.TextMatrix(i, 6))
                    RstB!offdate = Trim(myFlexGrid.TextMatrix(i, 7))
                    RstB!offtime = Trim(myFlexGrid.TextMatrix(i, 8))
                    RstB!consume = Trim(myFlexGrid.TextMatrix(i, 10))
                    RstB!department = Trim(myFlexGrid.TextMatrix(i, 3))
                    RstB!consumetime = Trim(myFlexGrid.TextMatrix(i, 9))
                    RstB!Cash = Trim(myFlexGrid.TextMatrix(i, 12))
                    RstB!status = Trim(myFlexGrid.TextMatrix(i, 14))
                    RstB!computer = Trim(myFlexGrid.TextMatrix(i, 15))
                    RstB.MoveNext
                    i = i + 1
                Next i
            End With
            RstB.Update
     RstB.Close
     
    '更新学生表中卡内余额及日期
    Dim txtSQLC As String
    Dim strC As String
    Dim RstC As ADODB.Recordset
    Dim m As Integer
    Dim n As Integer
    txtSQLC = "select * from student_info"
    Set RstC = ExecuteSQL(txtSQLC, strC)
        With myFlexGrid
             For n = 1 To Count
                For m = 1 To RstC.RecordCount
                    If Trim(RstC!studentNo) = Trim(myFlexGrid.TextMatrix(n, 1)) Then
                        RstC!Date = Trim(myFlexGrid.TextMatrix(n, 7))
                        RstC!Time = Trim(myFlexGrid.TextMatrix(n, 8))
                        RstC!Cash = Trim(myFlexGrid.TextMatrix(n, 11))
                        RstC.MoveFirst
                        Exit For
                    Else
                        RstC.MoveNext
                        If RstC.EOF = True Then
                            RstC.MoveFirst
                        End If
                    End If
                Next m
            Next n
        End With
       RstC.Update
    RstC.Close

    '清空在线信息表
    Dim txtSQLD As String
    Dim strD As String
    Dim RstD As ADODB.Recordset
    txtSQLD = "select * from online_info"
    Set RstD = ExecuteSQL(txtSQLD, strD)
    Dim h As Integer
    myBookMark = RstD.Bookmark
    For h = 1 To Count
        RstD.Delete myBookMark
        If RstD.EOF = True Then
            Exit Sub
        Else
            RstD.MoveNext
        End If
    Next h
    RstD.Close
    
    frmShangXiaJi.txtCardNo.Text = ""
    frmShangXiaJi.txtStudentNo.Text = ""
    frmShangXiaJi.txtName.Text = ""
    frmShangXiaJi.txtSex.Text = ""
    frmShangXiaJi.txtXi.Text = ""
    frmShangXiaJi.txtType.Text = ""
    frmShangXiaJi.txtShangDate.Text = ""
    frmShangXiaJi.txtShangTime.Text = ""
    frmShangXiaJi.txtXiaDate.Text = ""
    frmShangXiaJi.txtXiaTime.Text = ""
    frmShangXiaJi.txtCash.Text = ""
    frmShangXiaJi.txtSpentTime.Text = ""
    frmShangXiaJi.txtSpent.Text = ""

End Sub

曾经尝试过利用动态数组的方法，但是由于水平显示没有实现，希望通过以后的学习，还可以用动态数组的方法解决。或者探索一种新的方法。