Click here to Skip to main content
Click here to Skip to main content

Check that Sql Server exists prior to connection

, 27 Jun 2013
Rate this:
Please Sign up or sign in to vote.
Validate existence of Sql Server

Introduction 

At times a Sql Server database server service is not available for use. In this article I will offer one way for applications to validate the existence of a Sql Service service prior to connection.

Background  

Failures happen. Fact of life. Deal with it!

Seriously, at times our applications can fail to connect to a Sql Server service for any number of reasons. Waiting for connection timeout and handling that gracefully is always a good option, but your timeout may be excessive due to various reasons, and having the users wait for an extended period of time for a connection that is going to fail anyway is frustrating to them. It would be nice (and slightly productive) for a user to know that that they can't do what they were going to do a little bit sooner.   

Here's what I've come up with to combat this simple but frustrating issue. 

Using the code

Sql Server communicates via a TCP connection to a client. The code below simply attempts to open a socket connection to the specified TCP port of a specified machine. If it fails, it returns false. Sql Server's default port is 1433, but the method accepts a port number as a parameter. You can also use an IP address as the address parameter, or the machine name, but DNS must be able to locate the machine.

using System.Configuration;
using System.Net.Sockets;
private bool TestForServer(string address, int port)
{  
	int timeout = 500;
	if(ConfigurationManager.AppSettings["RemoteTestTimeout"] != null)
		timeout = int.Parse(ConfigurationManager.AppSettings["RemoteTestTimeout"]);
	var result = false;
	try
	{
		using(var socket = new Socket(AddressFamily.InterNetwork, SocketType.Stream, ProtocolType.Tcp))
		{
			IAsyncResult asyncResult = socket.BeginConnect(address, port, null, null);
			result = asyncResult.AsyncWaitHandle.WaitOne(timeout, true);
			socket.Close();
		}
		return result;
	}
	catch { return false; }
}

Note the timeout variable. Because TCP connections are not immediate because network traffic, speed, etc., the connection attempt waits for this specified number of milliseconds before returning. The IAsyncResult.AsncyWaitHandle.WaitOne returns true or false depending on if a connection is made or not. The timeout value is stored in a config file for easy manipulation as you may have to change this depending on your own local network speeds. 

Simply call the code as follows:  

if(!TestForServer("MySqlServer", 1433))
	throw new ApplicationException("Cannot connection to the Sql Server service on MySqlServer");

Points of Interest  

The method above is not just limited to Sql Server. It can also be used to verify the existence of other database platforms utilizing TCP sockets. I have used this to successfully check for existence of FireBird servers. Another interesting use of the method would be to check to see if the user is running in disconnected mode, thus switching to a local database that would then be synchronized upon re-connection to the network. 

History

6/27/2013 Original submission

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Greg Osborne

United States United States
Visual Basic Developer since version 1.0
Java web developer
Currently developing in vb and c#

Comments and Discussions

 
QuestionWhat if you do not know the port and/or you're looking on the local machine PinmemberMember 43533715-Jun-14 13:21 
AnswerRe: What if you do not know the port and/or you're looking on the local machine PinpremiumGreg Osborne24-Jun-14 3:25 
GeneralThoughts PinpremiumPIEBALDconsult18-Apr-14 7:15 
AnswerRe: Thoughts PinpremiumGreg Osborne24-Jun-14 3:26 
GeneralMy vote of 5 PinmemberJohn B Oliver16-Jul-13 11:33 
GeneralMy vote of 5 PinprofessionalMihai MOGA13-Jul-13 20:47 
QuestionConnection Timeout Pinmemberjfos28-Jun-13 7:16 
AnswerRe: Connection Timeout [modified] PinmemberGreg Osborne28-Jun-13 7:39 
Questionasync or sync Pinmembergiammin27-Jun-13 6:38 
AnswerRe: async or sync PinmemberGreg Osborne27-Jun-13 6:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 27 Jun 2013
Article Copyright 2013 by Greg Osborne
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid