将记录集存储为类的实例？

发布于 2024-11-06 18:30:38 字数 630 浏览 4 评论 0原文

有以下场景。我有一些表单，本质上有一些保管箱、列表等。我用 ms sql 数据库中的记录填充它们。但是，是否有一种方法只查询数据库一次，并在应用程序的整个生命周期中将记录存储为类的实例，而不是每次用户打开表单时都进行查询？

连接是这样的：

Sub connection_test()

    Dim Cn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim stSQL As String

    stSQL = "SELECT * FROM dbo.Client"

    Set Cn = New ADODB.Connection

    With Cn
        .CursorLocation = adUseClient
        .Open CONNECTION_STRING
        .CommandTimeout = 0
        Set Rs = .Execute(stSQL)
    End With

    Rs.Close
    Cn.Close
    Set Rs = Nothing
    Set Cn = Nothing
End Sub

有人能为我建议一个解决方案吗？我可以看到交通拥堵带来的所有问题，而且大部分都是不必要的。

原文

Have the following scenario. I have a few form, which essentially have a few dropboxes, lists etc. I populate them with records from a ms sql db. However, is there a way to query the database only once and store the records as an instance of a class throughout the life of the application rather than querying each time the user opens the form?

Connection is as this:

Sub connection_test()

    Dim Cn As ADODB.Connection
    Dim Rs As ADODB.Recordset
    Dim stSQL As String

    stSQL = "SELECT * FROM dbo.Client"

    Set Cn = New ADODB.Connection

    With Cn
        .CursorLocation = adUseClient
        .Open CONNECTION_STRING
        .CommandTimeout = 0
        Set Rs = .Execute(stSQL)
    End With

    Rs.Close
    Cn.Close
    Set Rs = Nothing
    Set Cn = Nothing
End Sub

Can someone suggest a solution for me? I can see all the problems from heavy traffic and mostly unnecessary.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

海螺姑娘 2024-11-13 18:30:38

如果您只希望记录集可用，请将标准模块中的变量调暗为公共

Public Rs As ADODB.Recordset

Sub connection_test()

    Dim Cn As ADODB.Connection
    Dim sSQL As String

    If Rs.State = adStateClosed Then
        sSQL = "SELECT * FROM dbo.Client"

        Set Cn = New ADODB.Connection

        With Cn
            .CursorLocation = adUseClient
            .Open CONNECTION_STRING
            .CommandTimeout = 0
            Set Rs = .Execute(sSQL)
        End With
    End If

End Sub

，现在 Rs 将在项目中的任何位置可用。您可以在需要时运行connection_test，如果记录集关闭，它将创建它。如果没有，你就可以走了。

一般来说，我的方法是创建自定义类。我将创建一个 CClient 类，从记录集（或其他地方）填充它，使用业务逻辑操作对象，然后将新值写回数据库。这样，我的业务逻辑就不会依赖于我使用 ado 的事实。我可以切换到文本文件或 Excel 工作表作为数据存储，而不必担心代码中各处的依赖关系。

例如，假设我有一个 Access 表：

 ClientID, Autonumber
 ContactFirst, String
 ContactLast, String
 Company, String
 CityState, String
 Volume, Double

我创建一个 CClient 类，其中包含表中每个字段的属性。我还创建了一个 CClients 类来保存所有 CClient 实例。在标准模块中，您可能会遇到类似这样的情况

Public gclsClients As CClients

Sub Main()

    Set gclsClients = New CClients

    'Fill the class
    gclsClients.FillFromRS

    'change some value
    gclsClients.Client(1).Volume = 100

    'write back to the database
    gclsClients.WriteToDB

End Sub

，在我更改一个客户端的音量的情况下，您将有更多的代码调用您的用户表单等。基础知识是，加载类，执行您需要的任何操作，然后编写类数据返回到数据库。我不会向你展示所有的类代码，但是在 CClients

Public Sub FillFromRS()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim clsClient As CClient

    Const sSQL As String = "SELECT * FROM tblClient"

    Set cn = New ADODB.Connection

    cn.Open msCON
    Set rs = cn.Execute(sSQL)

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            Set clsClient = New CClient
            With clsClient
                .ClientID = rs.Fields("ClientID").Value
                .ContactFirst = rs.Fields("ContactFirst").Value
                .ContactLast = rs.Fields("ContactLast").Value
                .Company = rs.Fields("Company").Value
                .CityState = rs.Fields("CityState").Value
                .Volume = rs.Fields("Volume").Value
            End With
            Me.Add clsClient
            rs.MoveNext
        Loop
    End If

End Sub

方法中从数据库获取数据并填充一堆 CClient 实例。同样在 CClients 中，

Public Sub WriteToDB()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim clsClient As CClient
    Dim sSQL As String

    Set cn = New ADODB.Connection

    cn.Open msCON

    For Each clsClient In Me
        sSQL = BuildUpdateSQL(clsClient)
        cn.Execute sSQL
    Next clsClient

End Sub

Private Function BuildUpdateSQL(clsClient As CClient)

    Dim sReturn As String

    With clsClient
        sReturn = "UPDATE tblClient SET ContactFirst = '" & .ContactFirst & "',"
        sReturn = sReturn & " ContactLast = '" & .ContactLast & "',"
        sReturn = sReturn & " Company = '" & .Company & "',"
        sReturn = sReturn & " CityState = '" & .CityState & "',"
        sReturn = sReturn & " Volume = " & .Volume
        sReturn = sReturn & " WHERE ClientID = " & .ClientID & ";"
    End With

    BuildUpdateSQL = sReturn

End Function

该方法循环遍历所有 CClient 实例，创建 UPDATE sql 语句并执行它。您需要在 CClient 中实现某种 IsDirty 属性，以便仅更新发生更改的客户端。其余的 CClient 和 CClient 是基本的类模块内容。

您可以多次或少量调用 WriteToDB。在某些应用程序中，每当发生变化时我都会编写它。在其他情况下，我仅在工作簿关闭时写回数据库。这在某种程度上取决于您的应用程序的流程。真正的美妙之处在于，如果您从 Access 数据库更改为用于数据存储的文本文件，则只需更改为 CClient 中的方法即可。所有其余代码都使用 CClient，并且不关心数据所在的位置。

您可以在此处查看工作簿和 Access 数据库 http://www.dailydoseofexcel.com/excel/ClientClassExample.zip

If you just want the recordset available, dim the variable as public in a standard module

Public Rs As ADODB.Recordset

Sub connection_test()

    Dim Cn As ADODB.Connection
    Dim sSQL As String

    If Rs.State = adStateClosed Then
        sSQL = "SELECT * FROM dbo.Client"

        Set Cn = New ADODB.Connection

        With Cn
            .CursorLocation = adUseClient
            .Open CONNECTION_STRING
            .CommandTimeout = 0
            Set Rs = .Execute(sSQL)
        End With
    End If

End Sub

Now Rs will be available anywhere in the project. You can run connection_test whenever you need to and, if the recordset it closed, it will create it. If not, you're good to go.

Generally my approach to this is to create custom classes. I would create a CClient class, fill it from a recordset (or somewhere else), manipulate the objects with the business logic, then write the new values back to the database. That way, none of my business logic relies on the fact that I'm using ado. I could switch to a text file or an Excel worksheet as a data store and wouldn't have to worry about dependencies everywhere in the code.

For instance, suppose I have an Access table:

 ClientID, Autonumber
 ContactFirst, String
 ContactLast, String
 Company, String
 CityState, String
 Volume, Double

I create a CClient class with a property for each field in my table. I also create a CClients class to hold all of the CClient instances. In a standard module, you might have something like this

Public gclsClients As CClients

Sub Main()

    Set gclsClients = New CClients

    'Fill the class
    gclsClients.FillFromRS

    'change some value
    gclsClients.Client(1).Volume = 100

    'write back to the database
    gclsClients.WriteToDB

End Sub

Where I change the volume of one client, you would have a lot more code calling your userform, etc. The basics are, load up the class, do whatever you need, then write the class data back to the db. I won't show you all the class code, but in CClients

Public Sub FillFromRS()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim clsClient As CClient

    Const sSQL As String = "SELECT * FROM tblClient"

    Set cn = New ADODB.Connection

    cn.Open msCON
    Set rs = cn.Execute(sSQL)

    If Not rs.BOF And Not rs.EOF Then
        rs.MoveFirst
        Do While Not rs.EOF
            Set clsClient = New CClient
            With clsClient
                .ClientID = rs.Fields("ClientID").Value
                .ContactFirst = rs.Fields("ContactFirst").Value
                .ContactLast = rs.Fields("ContactLast").Value
                .Company = rs.Fields("Company").Value
                .CityState = rs.Fields("CityState").Value
                .Volume = rs.Fields("Volume").Value
            End With
            Me.Add clsClient
            rs.MoveNext
        Loop
    End If

End Sub

This method gets the data from the database and fills a bunch of CClient instances. Also in CClients

Public Sub WriteToDB()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim clsClient As CClient
    Dim sSQL As String

    Set cn = New ADODB.Connection

    cn.Open msCON

    For Each clsClient In Me
        sSQL = BuildUpdateSQL(clsClient)
        cn.Execute sSQL
    Next clsClient

End Sub

Private Function BuildUpdateSQL(clsClient As CClient)

    Dim sReturn As String

    With clsClient
        sReturn = "UPDATE tblClient SET ContactFirst = '" & .ContactFirst & "',"
        sReturn = sReturn & " ContactLast = '" & .ContactLast & "',"
        sReturn = sReturn & " Company = '" & .Company & "',"
        sReturn = sReturn & " CityState = '" & .CityState & "',"
        sReturn = sReturn & " Volume = " & .Volume
        sReturn = sReturn & " WHERE ClientID = " & .ClientID & ";"
    End With

    BuildUpdateSQL = sReturn

End Function

This method loops through all of the CClient instances, creates an UPDATE sql statement and executes it. You'll want to implement some sort of IsDirty property in CClient so that you only update those client where something is changed. The rest of CClients and CClient are basic class module stuff.

You can call WriteToDB a lot or a little. In some apps, I write it whenever something changes. In others, I only write back to the database when the workbook is closed. It kind of depends on the flow of your application. The real beauty is that if you change from, say, an Access database to a text file for data storage, you only have to change to methods in CClients. All of the rest of your code consumes CClients and doesn't care where the data lives.

You can see the workbook and Access database here http://www.dailydoseofexcel.com/excel/ClientClassExample.zip

回复收藏 0 原文

长发绾君心 2024-11-13 18:30:38

请在此处阅读有关断开连接的记录集的信息。

本文确实包含（在示例代码中）但没有强调的一件事是您必须使用 adLockBatchOptimistic。您不必必须像他们一样使用adOpenForwardOnly。当然，您的 Recordset 对象必须具有子对象之外的范围。我会这样做：

Function connection_test() as ADODB.Recordset

Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset

Dim stSQL As String

    stSQL = "SELECT * FROM dbo.Client"
    Set Cn = New ADODB.Connection

    With Cn
        .Open CONNECTION_STRING
        .CommandTimeout = 0
    End With

    With Rs
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open stSQL
        Set .ActiveConnection = Nothing 
    End With

    Set connection_test = Rs

    'DON'T Close the Recordset
    'Rs.Close
    Cn.Close
    'Destroying the local instance is fine, though
    Set Rs = Nothing
    Set Cn = Nothing
End Function

当然你想添加错误处理；）。然后在调用代码中声明一个 Recordset 对象，并通过调用 Function 来实例化它。

Read up on Disconnected Recordsets here.

One thing that the article does include (in the sample code), but doesn't emphasize is that you have to use the adLockBatchOptimistic. You do not have to use adOpenForwardOnly, as they do. And, of course, your Recordset object has to have a scope outside the sub. I'd do it this way:

Function connection_test() as ADODB.Recordset

Dim Cn As ADODB.Connection
Dim Rs As ADODB.Recordset

Dim stSQL As String

    stSQL = "SELECT * FROM dbo.Client"
    Set Cn = New ADODB.Connection

    With Cn
        .Open CONNECTION_STRING
        .CommandTimeout = 0
    End With

    With Rs
        .CursorLocation = adUseClient
        .LockType = adLockBatchOptimistic
        .Open stSQL
        Set .ActiveConnection = Nothing 
    End With

    Set connection_test = Rs

    'DON'T Close the Recordset
    'Rs.Close
    Cn.Close
    'Destroying the local instance is fine, though
    Set Rs = Nothing
    Set Cn = Nothing
End Function

And of course you want to add error handling ;). Then have a Recordset object declared in the calling code, and instantiate it by calling the Function.

回复收藏 0 原文

~没有更多了~