This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.
How to test if a SqlConnection object is open
How can I test if the SqlConnection object is open?
My code looks similar to this; except that an exception will be thrown on line 2
1 Using connectionObject As New SqlCommand( ........)
2 connectionObject.Open()
3 If (connectionObject.State == ConnectionState.Open)
4 Then
End If
My code looks similar to this; except that an exception will be thrown on line 2
1 Using connectionObject As New SqlCommand( ........)
2 connectionObject.Open()
3 If (connectionObject.State == ConnectionState.Open)
4 Then
End If
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why are you maintaining a reference to a connection object? If you're worried about the overhead of creating a connection, then you should have connection pooling enabled.
I am not worried about maintaining a reference to a connection object. Let me explain this at a high level. If my application has a database connection, then data will be displayed in a GridView control. If it does not have a database connection, (The network is down for example) then I need to prevent my application from crashing.
You see if the network connection to my database is down then the statement :connectionObject.Open()
will fail. It will fail miserably by throwing an exception. I would like to test if the connection exists before I attempt to open it. I don't give a hoot about connection pooling.
You see if the network connection to my database is down then the statement :connectionObject.Open()
will fail. It will fail miserably by throwing an exception. I would like to test if the connection exists before I attempt to open it. I don't give a hoot about connection pooling.
Wrap it in a try/catch. To my knowledge, that is standard practice for database connections. My usual pattern:
try
{
using (SqlConnection con = new SqlConnection("connection string"))
{
con.Open();
using (SqlCommand cmd = new SqlCommand("query", con))
{
SqlDataReader reader = cmd.ExecuteReader(); // For example
// get data out of reader and put in domain object
}
}
}
catch (SqlException)
{
// Do something (maybe logging)
}
With the proper right, you could check if the service is running through a ServiceController object and check its Status property. But in most environmnents, the user won't have the needed rights.
And it will tell you nothing about the availability of the connection. There are to many things to consider.
- The server might be down
- The network connection might not work
- There might be too many connections running on the server to enable a new one
- The user might not have the rights for the parameters that you are requesting
So, trapping the Exception is the best way to go as far as I am concerned.
If it's the time it takes to get the error that bothers you, most of that time is not consumed by preparing the SqlException object itself, but by the connection TimeOut that that is set to 15 seconds by default. Unless you are working in a situation where the server is very slow to answer, you can usually lower down that value in the Connect Timeout property of the ConnectionString.
And it will tell you nothing about the availability of the connection. There are to many things to consider.
- The server might be down
- The network connection might not work
- There might be too many connections running on the server to enable a new one
- The user might not have the rights for the parameters that you are requesting
So, trapping the Exception is the best way to go as far as I am concerned.
If it's the time it takes to get the error that bothers you, most of that time is not consumed by preparing the SqlException object itself, but by the connection TimeOut that that is set to 15 seconds by default. Unless you are working in a situation where the server is very slow to answer, you can usually lower down that value in the Connect Timeout property of the ConnectionString.
I have tried the technique of catching the exception, however the application seems to stall and lockup for a while, which is a very irritating end user experience. I need to test beforehand if the database connection exists. Possibly some other Expert might have some good recommendations.
You may want to consider writing async code then. It's a topic that too vast for a comment, but the gist of it is that you sleep threads that are waiting for I/O so that your UI does not lock up whilst the application is doing nothing more than waiting for a response from the network. Have a look at the Async/Await documentation as well as the Channel 9 videos on the topic.
You could make the SqlConnection a private member of the class (or ASP page), then make a function to open the connection and return a Boolean to indicate status.
private class ClassName
{
private SqlConnection m_oConn = null;
private bool OpenDB(string connstr)
{
if(string.IsNullOrEmpty(connstr) return false;
if(m_oConn == null) {
m_oConn = new SqlConnection(connstr);
m_oConn.Timeout = 10; // for connection timeout
}
if(m_oConn.state == ConnectionState.Open)
return true;
try {
m_oConn.Open();
return true;
} catch(Exception ex) {
// log error here
return false;
}
}
private void DoSomething() {
if(!OpenDB()) {
// DB Connection failed to open. Handle the scenario
}
// all AOK. continue with the connection
}
}
If you look at any big commercial software that works with a database in the background (ACCPAC and Sage for instance), or have ever seen work done at any big company, they all rely on the exception or error thrown after a preset timeout in order to detect if a connection is available. They would do otherwise if they could.
In a proper environment, unavailable connections are almost always detected that way, because servers typically run 24/7. So connections problems are usually not related to the unavailability of the server, but to other problems that can be detected only by trying to open a connection.
If your problem is an intermittend network connection to the server, then look at what happens if you try to open a file on the network from Windows Explorer. Do you think that you can to better than the OS? In such a situation, the problem is solved at the server side, not in the application.
Same thing if you have problems connecting because you have used up all the connections permitted by your licence. Either pay to get more licences, or check at the server level to see if connections are used properly. Some applications can keep connections opened when they no longer use them. In .NET, I still see a lot of programmers who open a connection when they start an application and close it only when the application closes. This is not the way to go. This can lead to connection problems for other applications.
Solve the problem at its root, and a missed connection trapped in a Try...Catch will be so rare that it won't be a problem anymore.
In a proper environment, unavailable connections are almost always detected that way, because servers typically run 24/7. So connections problems are usually not related to the unavailability of the server, but to other problems that can be detected only by trying to open a connection.
If your problem is an intermittend network connection to the server, then look at what happens if you try to open a file on the network from Windows Explorer. Do you think that you can to better than the OS? In such a situation, the problem is solved at the server side, not in the application.
Same thing if you have problems connecting because you have used up all the connections permitted by your licence. Either pay to get more licences, or check at the server level to see if connections are used properly. Some applications can keep connections opened when they no longer use them. In .NET, I still see a lot of programmers who open a connection when they start an application and close it only when the application closes. This is not the way to go. This can lead to connection problems for other applications.
Solve the problem at its root, and a missed connection trapped in a Try...Catch will be so rare that it won't be a problem anymore.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial