I am about to start my very first SQL Server based Application. I would like to get your suggestion on what is a good approach for connectivity?
? Should I have a exclusive connection for every table?
? Should I have a connection per form?
? Should I open a connection, fetch the data/write data and close the connection?
In short I would like to know a good approach towards using SQL Server Optimally. At a given time I will be updating about 5 tables for transactions and fetching read only data from about 15 tables. The trasactions would occur at interval of, anywhere from 30 seconds to 5 minutes. I will have to have remote views, as well as SQL Views (some information from multiple tables need to consolidated and summed up as one record/row), to present appropriate information to the user.
Thank you for your valued input.
Yashkamal Patel