Check Remote sql connecton is exist or not? [modified]
-
Hello friends. I hv one application that runs continuously for Month or two.And the application is using any remote PC database. When i start the application i set all connection object required for that. In some of the cases When i go for insert/update/delete operation at that time connection is broken so application stops working.it goes into idle state.Nothing is happening over there. So i want to check that is there connection is exist or not with Remote PC in C#.NET? Please reply. Thanks in advance.
modified on Friday, February 18, 2011 2:25 AM
-
Hello friends. I hv one application that runs continuously for Month or two.And the application is using any remote PC database. When i start the application i set all connection object required for that. In some of the cases When i go for insert/update/delete operation at that time connection is broken so application stops working.it goes into idle state.Nothing is happening over there. So i want to check that is there connection is exist or not with Remote PC in C#.NET? Please reply. Thanks in advance.
modified on Friday, February 18, 2011 2:25 AM
I don't think you are doing this in the most efficient manner. Most guides agree that you should only open a connection to the database when you need to perform some function, and you should immediately close it afterwards. This should allow you to check whether the connection is available when you need it, rather than checking when your app starts, and then assuming it still exists at some later time.
I must get a clever new signature for 2011.
-
I don't think you are doing this in the most efficient manner. Most guides agree that you should only open a connection to the database when you need to perform some function, and you should immediately close it afterwards. This should allow you to check whether the connection is available when you need it, rather than checking when your app starts, and then assuming it still exists at some later time.
I must get a clever new signature for 2011.
Yes i am already doing it in this way. i am going to open the connection when i need to perform Insert/Update/Delete.
public void Insert(string str) { try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open();//Application execution stops here. cmd = new SqlCommand(str, con);//str contains query to execute cmd.ExecuteNonQuery(); } catch { } finally { con.Close(); } } -
Yes i am already doing it in this way. i am going to open the connection when i need to perform Insert/Update/Delete.
public void Insert(string str) { try { if (con.State == ConnectionState.Open) { con.Close(); } con.Open();//Application execution stops here. cmd = new SqlCommand(str, con);//str contains query to execute cmd.ExecuteNonQuery(); } catch { } finally { con.Close(); } }.Net uses a connection pool. So unless you explicitly turn it off connections are pooled. What this means is that you can see the following happen. - Do something with database (C#), connection is in pool. - database gets bounced. - Do something else with database, it pulls existing connection from pool. When you attempt to use the connection it blows up. The choices are 1. Use a connection string that prevents pooling. 2. Go through enough connection attempts that you get a good connection. 3. Catch and parse all exceptions and look for several specific ones. Reset the pool when you find those. The originating connection still failed though so you need to do something about that.
-
.Net uses a connection pool. So unless you explicitly turn it off connections are pooled. What this means is that you can see the following happen. - Do something with database (C#), connection is in pool. - database gets bounced. - Do something else with database, it pulls existing connection from pool. When you attempt to use the connection it blows up. The choices are 1. Use a connection string that prevents pooling. 2. Go through enough connection attempts that you get a good connection. 3. Catch and parse all exceptions and look for several specific ones. Reset the pool when you find those. The originating connection still failed though so you need to do something about that.
If your connection string is same, not integrated authentication, then you have 25 connections possible in your pool, unless u increase them manually. Otherwise, look for an open SqlDataReader which has caused exception and hence not got the connection close (you would have also missed the closing of connection in the finally block, for this to happen). Anyway, within a little time, the connection will timeout and return to your pool. So the issue cannot be continuous; if it is, then you need to look into the machine's event log to figure out the actual root cause.