West Wind Client Tools
sql server connection and private datasession
Gravatar is a globally recognized avatar based on your email address. sql server connection and private datasession
  Julio Cesar Santone
  All
  Jun 22, 2017 @ 04:44am

Hi, I have an app that runs every form under private datasession. I'm connecting to SQL Server every time a form is loaded and the performance is poor. Is anyway I can connect once to sql server (Default Datasession) and use that conection in every private datasession or this is impossible and I have to connect on every private datasession ?

Regards

Gravatar is a globally recognized avatar based on your email address. re: sql server connection and private datasession
  Rick Strahl
  Julio Cesar Santone
  Jun 22, 2017 @ 10:44am

You can create explicit data session objects, attach them and then assign a form to that datasession which effectively lets you cache it.

However, the SQL Server ODBC driver (or oledb) use connection pooling unless explicitly turned off or if the timeout for the pool is too short. So in theory you should be getting cached connection handles for sql requests.

I don't use DataAdapters so not sure of the exact semantics of that, but i know this works with wwSql and raw SQL Passthrough. Using that approach it's easy to open a connection and holding the connection open either by storing the wwSql instance or the SQL connection handle on a global object somewhere and then reusing that.

Gravatar is a globally recognized avatar based on your email address. re: sql server connection and private datasession
  Julio Cesar Santone
  Rick Strahl
  Jun 28, 2017 @ 08:17am

Rick,

Thank you for taking a time to answer my question. I'll try your suggestion

Regards

Gravatar is a globally recognized avatar based on your email address. re: sql server connection and private datasession
  Steve
  Julio Cesar Santone
  Jun 28, 2017 @ 07:45pm

Hi Julio,
The short answer is, yes you can use private data sessions in your forms and also use a single connection to SQLServer. I do this in my desktop app. It's been quite a while since I implemented this and it's difficult to recall the exact details. You have to setup the connection to be shared. If I recall, I use essentially two SQL Connections, one for SQL Passthru and another for Views. I use Remote Views in most of my forms. All of my views are kept in a separate database container, and I also setup a SQLConnection within that container that all of the views use/share. It's basically from techniques outlined in the Client Server Applications with VFP and SQLServer book. This is a very good book with tons of great info. The separate DBC for Remote Views is a technique I learned in a September 2001 FoxTalk Article by Jim Falinok, Turn your VFP App into Client/Server, a 12 Step Program. Thank you Jim! You may be able to find this article on-line somewhere. I highly recommend it. The techniques outlined in this article have worked out great. I will say that turning my desktop app into a client server app took a while, but it was worth it. I have one set of views that access VFP Data, and another set of views that access SQLServer data. The forms don't really know or care where the data is coming from. I do have some blocked code for the differences in SQL syntax between VFP and SQL, but this is no big deal and a small price to pay for being able to support multiple backends.

Anyway, hope this helps.
Steve

© 1996-2017