SQL Cache Dependency Issue!!
After implementing the SQL cache dependency logic, SQL connection with the client remains open till the application stop because in this case we can only use Application_Start and Application_Stop methods of Global.asax for the creation of connection. This will ultimately decrease the performance of the website. Performance issues will occur when millions of users hit the hosted application in which sql connection is open . Is there any way to create SQL cache connection only when any changes occur into the table in the database.
Consider a scenario:-
1. Data fetch from database and inserted into the cache.
2. Now connection close.
3. When data changes in the database. connection opens and cache goes null.
4. Data fetched and inserted into the cache.
5. Connection of client and SQL closed.
Is it possible to create this scenario because client will never approve a code in which SQL and client connection remain open because it leads to security issues?