Global SqlConnection

Question

I have a Web Service that requires an SqlConnection. There are 5 web methods that use this SqlConnection, but presently they are declared (locally) in each of the methods. Would it be acceptable to make it a global variable in this case?

Thanks

Answer 1

What if you ever need a second connection ?

I'd suggest passing the variable around between classes/functions optionally putting it as class members.

Answer 2

No.

According to the documentation for SqlConnection:

Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe.

Since your different web methods could be called simultaneously from different threads, you'd better not share the same connection instance between them.

The typical pattern is that every time you need a database connection, you create a new instance, and Dispose it as soon as you're done. Threading is one of the reasons. Connection pooling makes sure it's not too expensive to create all those connection instances.

Answer 3

Unless you need very high performance, open and close each connection at each use. The IIS or system does the rest for you. Simple and efficient.