连接到此应用程序的数据库的最佳方法

I have a Delphi application which hits a database (usually MySql) every 60 seconds through a TTimer. The application is more or less an unattended bulletin board. If the network drops the application needs to continue running and connect back to the database when the connection is back. Often it might be over broadband, so chances are the connection is not always the best.

我有一个Delphi应用程序,它通过TTimer每隔60秒就会访问一个数据库(通常是MySql)。该应用程序或多或少是一个无人看管的公告板。如果网络中断,则应用程序需要继续运行并在连接恢复时连接回数据库。通常它可能是通过宽带,所以连接并不总是最好的。

I am using the TAdoConnection component. This is opened at application startup and remains open. Whenever I need to make a new query I set the Connection to the open TAdoConnection. But I am finding this is not very reliable if there is a network drop.

我正在使用TAdoConnection组件。这在应用程序启动时打开并保持打开状态。每当我需要创建一个新查询时,我将Connection设置为打开的TAdoConnection。但我发现如果网络丢失,这不是很可靠。

What is the best way to connect to the database in this instance? I have seen ways where you can build the connection string directly into the TAdoQuery. Would this be the proper way? Or is this excessively resource intensive? Sometimes I need to open 5-10 queries to get all the information.

在这个实例中连接数据库的最佳方法是什么?我已经看到了可以直接在TAdoQuery中构建连接字符串的方法。这是正确的方法吗?或者这是否过度耗费资源?有时我需要打开5-10个查询来获取所有信息。

Or how about doing this in the TTimer.OnTimer event:

或者如何在TTimer.OnTimer事件中执行此操作:

Create TAdoConnection

Do All Queries

做所有查询

Free TAdoConnection

Thanks.

1 个解决方案

#1

You should use single TAdoConnection object to avoid setting connection string to each component. Keep your connection object closed and open it when you need to access data. Something like this:

您应该使用单个TAdoConnection对象来避免为每个组件设置连接字符串。保持连接对象关闭,并在需要访问数据时将其打开。像这样的东西:

procedure OnTimer;
begin
  MyAdoConnection.Open;
  try
    // Data access code here
    ...
  finally
     MyAdoConnection.Close;
  end; 
end;

You can additionally put another try/except block around MyAdoConnection.Open to catch situation where network is not available.

您还可以在MyAdoConnection.Open周围添加另一个try / except块来捕获网络不可用的情况。

About second part of your question, best would be to put all your data access components in data module that you will create when you need to run data access procedures. Then you can put all your data access code in that data module and separate it from rest of the code.

关于问题的第二部分,最好将所有数据访问组件放在您需要运行数据访问过程时创建的数据模块中。然后,您可以将所有数据访问代码放在该数据模块中,并将其与其余代码分开。

You could try to open connection in OnCreate event of datamodule, but be careful to handle possible exceptions when opening connection. Close connection in OnDestroy event. Then you can use that datamodule like this:

您可以尝试在数据模块的OnCreate事件中打开连接,但在打开连接时要小心处理可能的异常。在OnDestroy事件中关闭连接。然后你可以像这样使用那个数据模块:

procedure OnTimer;
var myDataModule : TMyDataModule;
begin
  myDataModule  := TMyDataModule.Create;
  try
    // Data access code here
    myDataModule.DoSomeDatabaseWork;
  finally
     myDataModule.Free;
  end; 
end;

#1