{"id":396,"date":"2010-12-10T11:12:54","date_gmt":"2010-12-10T19:12:54","guid":{"rendered":"http:\/\/www.andreanolanusse.com\/en\/?p=396"},"modified":"2011-06-23T14:42:31","modified_gmt":"2011-06-23T22:42:31","slug":"sharing-db-connection-between-multiples-datasnap-server-modules","status":"publish","type":"post","link":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/","title":{"rendered":"Sharing DB connection between multiples DataSnap Server Modules"},"content":{"rendered":"

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.<\/p>\n

In Delphi XE<\/a>, 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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

  private\r\n    { Private declarations }\r\n    ListofConnection : TDictionary;\r\n  public\r\n    function GetConnection : TSQLConnection;<\/pre>\n
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.<\/p>\n
function TServerContainer1.GetConnection: TSQLConnection;\r\nvar\r\n  dbconn : TSQLConnection;\r\nbegin\r\n\r\n  if ListofConnection.ContainsKey(TDSSessionManager.GetThreadSession.Id) then\r\n     Result := ListofConnection[TDSSessionManager.GetThreadSession.Id]\r\n  else\r\n  begin\r\n    dbconn := TSQLConnection.Create(nil);\r\n    dbconn.Params.Clear;\r\n    dbconn.LoadParamsOnConnect := true;\r\n    dbconn.ConnectionName := 'DS Employee';\r\n\r\n    ListofConnection.Add(TDSSessionManager.GetThreadSession.Id, dbconn);\r\n    Result := dbconn;\r\n  end;\r\n\r\nend;<\/pre>\n
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.<\/p>\n
If you are using the VCL Data components (TSQLQuery, TSQLStoredProc, etc\u2026) on your Server DataModules, the onCreate event is a good place to associate the DataSets with the connection, using the following code.<\/p>\n
procedure TServerContainer1.SetConnection(Conn: TSqlConnection);\r\nvar\r\n  i: integer;\r\nbegin\r\n  if Conn = nil then\r\n     Conn := GetConnection;\r\n  else\r\n    Conn := Sender;\r\n\r\n  for i := 0 to ComponentCount - 1 do\r\n    if Components[i] is TSQLQuery then\r\n       TSQLQuery(Components[i]).SQLConnection := Conn;\r\nend;<\/pre>\n
To avoid connection leaks on the database,\u00a0we implement the onDisconnect event from DSServer. This will be executed when the client disconnects.<\/p>\n
  if GetConnection <> nil then\r\n     GetConnection.Close;<\/pre>\n
The source code is available for download at Code Central<\/a>.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_s2mail":"","footnotes":""},"categories":[10],"tags":[21,90,13],"class_list":["post-396","post","type-post","status-publish","format-standard","hentry","category-delphi","tag-datasnap","tag-delphi","tag-featured"],"yoast_head":"\nSharing DB connection between multiples DataSnap Server Modules | Andreano Lanusse | Technology and Software Development<\/title>\n<meta name=\"description\" content=\"When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Sharing DB connection between multiples DataSnap Server Modules | Andreano Lanusse | Technology and Software Development\" \/>\n<meta property=\"og:description\" content=\"When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\" \/>\n<meta property=\"og:site_name\" content=\"Andreano Lanusse | Technology and Software Development\" \/>\n<meta property=\"article:published_time\" content=\"2010-12-10T19:12:54+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2011-06-23T22:42:31+00:00\" \/>\n<meta name=\"author\" content=\"Andreano Lanusse\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Andreano Lanusse\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#article\",\"isPartOf\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\"},\"author\":{\"name\":\"Andreano Lanusse\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\"},\"headline\":\"Sharing DB connection between multiples DataSnap Server Modules\",\"datePublished\":\"2010-12-10T19:12:54+00:00\",\"dateModified\":\"2011-06-23T22:42:31+00:00\",\"mainEntityOfPage\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\"},\"wordCount\":346,\"commentCount\":6,\"publisher\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\"},\"keywords\":[\"DataSnap\",\"Delphi\",\"Featured\"],\"articleSection\":[\"Delphi\"],\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\",\"url\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\",\"name\":\"Sharing DB connection between multiples DataSnap Server Modules | Andreano Lanusse | Technology and Software Development\",\"isPartOf\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#website\"},\"datePublished\":\"2010-12-10T19:12:54+00:00\",\"dateModified\":\"2011-06-23T22:42:31+00:00\",\"description\":\"When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will\",\"breadcrumb\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#breadcrumb\"},\"inLanguage\":\"en\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"http:\/\/www.andreanolanusse.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Sharing DB connection between multiples DataSnap Server Modules\"}]},{\"@type\":\"WebSite\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#website\",\"url\":\"http:\/\/www.andreanolanusse.com\/en\/\",\"name\":\"Andreano Lanusse | Technology and Software Development\",\"description\":\"Where Andreano Lanusse talk about technology, software development, programming techniques, databases, games and more through articles, tutorials and videos\",\"publisher\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"http:\/\/www.andreanolanusse.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b\",\"name\":\"Andreano Lanusse\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en\",\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g\",\"caption\":\"Andreano Lanusse\"},\"logo\":{\"@id\":\"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/\"},\"description\":\"Andreano Lanusse is an expert and enthusiastic on software development industry, at Embarcadero he is focused on helping to make sure the products being developed meet the expectations of Embarcadero's customers, as well as defining market strategies for Latin America. Today as Latin Lead Evangelist he spends great deal of time in developer conferences, tradeshows, user group, and visiting customers throughout Latin America. Before Embarcadero, he worked 13 years for Borland, Andreano has worked as Support Coordinator, Engineer, Product Manager, including Product Line Sales Manager, where was responsible to manage the relationship with Brazil developer community, also has worked as Principal Consultant for Borland Consulting Services on the development and management of critical applications. He previously served as Chief Architect for USS Solu\u00e7\u00f5es Gerenciadas (now USS Tempo). Andreano holds a bachelor's degree in Business Administration Marketing Emphasis from Sumare Institute, MBA in Project Management from FGV, certification in Microsoft products, all Borland ALM products, and all CodeGear product line.\",\"sameAs\":[\"http:\/\/www.andreanolanusse.com\",\"https:\/\/x.com\/andreanolanusse\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Sharing DB connection between multiples DataSnap Server Modules | Andreano Lanusse | Technology and Software Development","description":"When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/","og_locale":"en_US","og_type":"article","og_title":"Sharing DB connection between multiples DataSnap Server Modules | Andreano Lanusse | Technology and Software Development","og_description":"When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will","og_url":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/","og_site_name":"Andreano Lanusse | Technology and Software Development","article_published_time":"2010-12-10T19:12:54+00:00","article_modified_time":"2011-06-23T22:42:31+00:00","author":"Andreano Lanusse","twitter_misc":{"Written by":"Andreano Lanusse","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#article","isPartOf":{"@id":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/"},"author":{"name":"Andreano Lanusse","@id":"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b"},"headline":"Sharing DB connection between multiples DataSnap Server Modules","datePublished":"2010-12-10T19:12:54+00:00","dateModified":"2011-06-23T22:42:31+00:00","mainEntityOfPage":{"@id":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/"},"wordCount":346,"commentCount":6,"publisher":{"@id":"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b"},"keywords":["DataSnap","Delphi","Featured"],"articleSection":["Delphi"],"inLanguage":"en","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#respond"]}]},{"@type":"WebPage","@id":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/","url":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/","name":"Sharing DB connection between multiples DataSnap Server Modules | Andreano Lanusse | Technology and Software Development","isPartOf":{"@id":"http:\/\/www.andreanolanusse.com\/en\/#website"},"datePublished":"2010-12-10T19:12:54+00:00","dateModified":"2011-06-23T22:42:31+00:00","description":"When developers starts creating Delphi DataSnap application it is very common to see the database connection defined per Data Module. Doing this will","breadcrumb":{"@id":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#breadcrumb"},"inLanguage":"en","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.andreanolanusse.com\/en\/sharing-db-connection-between-multiples-datasnap-server-modules\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"http:\/\/www.andreanolanusse.com\/en\/"},{"@type":"ListItem","position":2,"name":"Sharing DB connection between multiples DataSnap Server Modules"}]},{"@type":"WebSite","@id":"http:\/\/www.andreanolanusse.com\/en\/#website","url":"http:\/\/www.andreanolanusse.com\/en\/","name":"Andreano Lanusse | Technology and Software Development","description":"Where Andreano Lanusse talk about technology, software development, programming techniques, databases, games and more through articles, tutorials and videos","publisher":{"@id":"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"http:\/\/www.andreanolanusse.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en"},{"@type":["Person","Organization"],"@id":"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/b51fdf99c01fcd6ae0a5ae894c23837b","name":"Andreano Lanusse","image":{"@type":"ImageObject","inLanguage":"en","@id":"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/49ab23ef70c249c0cb3469f14ef07edc?s=96&d=mm&r=g","caption":"Andreano Lanusse"},"logo":{"@id":"http:\/\/www.andreanolanusse.com\/en\/#\/schema\/person\/image\/"},"description":"Andreano Lanusse is an expert and enthusiastic on software development industry, at Embarcadero he is focused on helping to make sure the products being developed meet the expectations of Embarcadero's customers, as well as defining market strategies for Latin America. Today as Latin Lead Evangelist he spends great deal of time in developer conferences, tradeshows, user group, and visiting customers throughout Latin America. Before Embarcadero, he worked 13 years for Borland, Andreano has worked as Support Coordinator, Engineer, Product Manager, including Product Line Sales Manager, where was responsible to manage the relationship with Brazil developer community, also has worked as Principal Consultant for Borland Consulting Services on the development and management of critical applications. He previously served as Chief Architect for USS Solu\u00e7\u00f5es Gerenciadas (now USS Tempo). Andreano holds a bachelor's degree in Business Administration Marketing Emphasis from Sumare Institute, MBA in Project Management from FGV, certification in Microsoft products, all Borland ALM products, and all CodeGear product line.","sameAs":["http:\/\/www.andreanolanusse.com","https:\/\/x.com\/andreanolanusse"]}]}},"_links":{"self":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/posts\/396","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/comments?post=396"}],"version-history":[{"count":0,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/posts\/396\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/media?parent=396"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/categories?post=396"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.andreanolanusse.com\/en\/wp-json\/wp\/v2\/tags?post=396"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}