How can maintain a SqlConnection open always

Question

How can maintain a SqlConnection (or using another component) open (connected) always during the execution of my .Net app?

I need this because my app needs to detect using this commnad

 exec sp_who2 

how many instances of my app are connected to mydatabase, to restrict the access (license control).

example

A) my app executed from location1

1. check the number of my apps connected to the sql server using exec sp_who2
2. if the number of my applications < MaxLicencesConnected then start my app and open a sqlconnection

B) my app executed from location2

1. check the number of my apps connected to the sql server using exec sp_who2
2. if the number of my applications >= MaxLicencesConnected then close my application

sorry for my english.

thanks in advance.

Answer 1

Wouldn't it be easier to simply have a table where you add a record when the program starts and remove it when the program exits? Keeping a connection to SQL Server open all the time is quite expensive.

Technically, someone could come along a delete the record and start up a new instance, but obviously if they do that then the first instance is going to stop working (assuming it checks for the presense of it's own record every now and then).

Answer 2

Myabe you can set a connection object, open it and store in a global (static) variable. I beleive, you would also have to set the timeout of the connection, otherwise it will be closed automaticaly if let idle.

Franly speaking, I dont think keeping a connection open is a good idea, as they are expensive.

Answer 3

Connection pooling keeps the connection alive:

http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx

Answer 4

Use connection pooling. Set Max Pool Size and Min Pool Size and Pooling in your connection string.