Sharing DB connection between multiples DataSnap Server Modules
When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will generate a lot of connections on the database, and depending on the situation it will became a problem.
In Delphi XE, DataSnap introduce the Session Management that will make easy to implement the control on the server side to manage the database connection for the clients. The client application won’t know anything about that, the server will do the magic.
When we create a DataSnap Server it is best practice to define a Server Container (Data Module), which contains the DataSnap Server component and registers all the server classes required by the application. In this container we will define the method responsible for dealing with the database connections for each Server Class.
As an example, I have implemented a GetConnection method on the Server Container. This method is responsible for assigning the connection looking in to the connection pool, which will a have list of connections per client.
private
{ Private declarations }
ListofConnection : TDictionary;
public
function GetConnection : TSQLConnection;
In case the server receives new requests from new clients the GetConnection will create a new connection and add to the pool list. If the client already have a connection associated, the GetConnection will just return a instance of SQLConnection. The pool uses the Thread ID to control the unique connection per client. If you use DataSnap 2010 you have to use the method GetThreadSession for this purpose.
function TServerContainer1.GetConnection: TSQLConnection;
var
dbconn : TSQLConnection;
begin
if ListofConnection.ContainsKey(TDSSessionManager.GetThreadSession.Id) then
Result := ListofConnection[TDSSessionManager.GetThreadSession.Id]
else
begin
dbconn := TSQLConnection.Create(nil);
dbconn.Params.Clear;
dbconn.LoadParamsOnConnect := true;
dbconn.ConnectionName := 'DS Employee';
ListofConnection.Add(TDSSessionManager.GetThreadSession.Id, dbconn);
Result := dbconn;
end;
end;
Since the connection is defined we need to update all datasets to use this connection, as well the server methods that create and execute SQL queries at run-time will have to invoke the GetConnection.
If you are using the VCL Data components (TSQLQuery, TSQLStoredProc, etc…) on your Server DataModules, the onCreate event is a good place to associate the DataSets with the connection, using the following code.
procedure TServerContainer1.SetConnection(Conn: TSqlConnection);
var
i: integer;
begin
if Conn = nil then
Conn := GetConnection;
else
Conn := Sender;
for i := 0 to ComponentCount - 1 do
if Components[i] is TSQLQuery then
TSQLQuery(Components[i]).SQLConnection := Conn;
end;
To avoid connection leaks on the database, we implement the onDisconnect event from DSServer. This will be executed when the client disconnects.
if GetConnection <> nil then
GetConnection.Close;
The source code is available for download at Code Central.
Do you know that your code won’t work properly unless all client calls are serialized somehow, because of transactions? For example, a client has form A and form B. Each form performs separate database operations. I start editing some data in A, but I do not commit. Then I switch to B and do someting and then commit. When I return to A, if the session is the same, A operations have been committed as well. Unless you work in the dreadful “autocommit” mode, you probably need a session for each separate operation the client is performing. Only operations that should happen in the same session (because they need to see uncommited data, for exampe) should reuse it. If you’re going to pool connection you should get an unused connection for each datamodule needing one, and reuse a session already in use when you really need it.
For the scenario you expose yes, but not necessarily for everything, It will depended. For example if the developer use separated transaction on the server side for each Server Module or Server Method. Also, he can implementing a method on the server side forcing the server to give a different connection, your comment about unused connection is valid as well.
I just implemented the example on this way, because it’s apply for the majority of scenarios developer has been asking me.
@LDS : I think your approach is difficult (on not) to mantain in a multitier scenario with independence of what Andreano is exposing here.
Also I think you are using ADO. In DBX you can control this behavior using the ApplyUpdates method.
Oh, it seems only adding element to pool, but, when to removing the element?