SqlConnection woes on the Compact Framework over WiFi
Last night at the Toronto .NET Users Group talk I did on mobility, a gentleman had a question about directly connecting to an Enterprise SqlServer database from a Pocket PC using the Compact Framework. His users run on a shop floor and some times they lose their wifi signal. While he doesn't keep the SqlConnection open the whole time, it seems that when a wifi signal is back alive his application can't connect to the database using a SqlConnection.Open anymore - despite that he can still ping the server.
He was correctly doing a SqlConnection.Close in a finally clause around his data access, but I suspect that even though pooling is not supported on the compact framework, this is not doing a proper disconnect of the physical connection (a Sql Profiler session would probably tell you that for sure). So a using block in C# will ensure that the SqlConnection is disposed of immediately and will properly terminate the connection. So if between calls you lose and reaquire your wifi connection you'll be starting a bran new connection the next time. It's still a good idea to put all of your actual work in a try/catch/finally block with a SqlConnection Close in the finally.
using
(SqlConnection cn = newSqlConnection(CONNECTION_STRING ))
{
try
{
cn.Open();
//do some work with the connection
}
catch (SqlException ex)
{
//error handling
}
finally
{
cn.Close();
}
}