When you’re designing applications that work with databases over the Internet, perhaps the largest problem you face is ensuring that everything can actually connect to everything else. You need to ensure that your users have a valid connection to the Net, and you need to ensure that your database server is up and running.
It’s not a typically easy process—and it’s a topic that attracts an unusually large amount of error handling code.
However, why not take the simple route and avoid potential big errors later on by checking whether your SQL Server is available to the user before starting your database code?
The following function does just that for you. Simply call IsSQLServerAvailable, passing in your server or IP address. It attempts a connection and returns a True if everything seems fine. Here’s the code:
Public Function IsSQLServerAvailable( _
ByVal ServerAddress _
As String) As Boolean
' Tests an SQL Server connection by
' name or IP address
Try
' Attempt to get server address
Dim objIPHost As New System.Net.IPHostEntry()
objIPHost = _
System.Net.Dns.Resolve(ServerAddress)
Dim objAddress As System.Net.IPAddress
objAddress = objIPHost.AddressList(0)
' Connect to port 1433, most common SQL Server
' port. If your target is different, change here
Dim objTCP As System.Net.Sockets.TcpClient = _
New System.Net.Sockets.TcpClient()
objTCP.Connect(objAddress, 1433)
' No problems (hurrah!)
' Close and cleanup
objTCP.Close()
objTCP = Nothing
objAddress = Nothing
objIPHost = Nothing
' Return success
Return True
Catch ex As Exception
' Server unavailable, return fail value
Return False
End Try
End Function
And here’s how you might call this function:
Dim blnCanConnect As Boolean
blnCanConnect = IsSQLServerOnline("maxsql001.maximumasp.com")
About the Author
Karl Moore (MCSD, MVP) is an experience author living in Yorkshire, England. He is author of numerous technology books, including the new Ultimate VB .NET and ASP.NET Code Book, plus regularly features at industry conferences and on BBC radio. Moore also runs his own creative consultancy, White Cliff Computing Ltd. Visit his official Web site at www.karlmoore.com.
# # #
