CODEWITHSUNDEEP
c#
Ayush
Ayush

Reputation: 42440

Check if connection to database can be established

I need to write a C# program that is scheduled to run everyday and runs a series of tests to ensure everything's running good. It checks to see for net connection, server connection, database connection etc.

The part I'm confused about it checking connection to database. Should I establish a connection with the db and then disconnect? Or is there a way to just poll the database without having to pass credentials (don't actually need to log in).

Upvotes: 1

Views: 19392

Answers (7)

ShakirAhmad
ShakirAhmad

Reputation: 191

We can either check if the connection is open or not.

if (conn.State == ConnectionState.Open)
            {
                return true;
            }
            else {
                return false;
            }

Don't forget using System.Data;

Upvotes: 0

Stephen
Stephen

Reputation: 3084

I don't see why you couldn't do something like this? Obviously this isn't as specific, but you could fill in the blanks.

using (SqlConnection con = new SqlConnection(connectionString))
{
   try
   {
      con.Open();
   }
   catch (Exception)
   {
      // Cant Connect
   }
}

Upvotes: 0

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31723

MySql Offers a Connection.Ping() method that returns true or false even if you haven't called Connection.Open() before.

However, I prefer to not perform such kind of preflight checks but rather handle exceptions if something goes wrong (Even if Connection.Ping() returns true you can't be sure that the server is still available during the next command)

Upvotes: 2

Simon Mourier
Simon Mourier

Reputation: 138776

It really depends on what do you exactly mean by "checking connection to the database". Problems can arise at many levels. For example in the case of SQL Server, if you read this article, there are many ways something can fail: http://support.microsoft.com/kb/827422/en

The best is really to connect, do a SELECT 1 or alike, and check the return.

Upvotes: 1

Mark 909
Mark 909

Reputation: 1835

You could use the ServiceController class in System.Service process to check that the DB service is running.

But then you could still get an instance where the service is running but the DB is not accepting connections. So, for me the only sure way would be to connect and run a simple query

Upvotes: 0

Peter
Peter

Reputation: 38455

Im no expert but i assume you mean MS Sql server? Database server could be more or less any thing...

If the program is running on the same server or has access to it you could check if the database service is up and running but im not 100% sure if that gives you the info you need..

Edit:

You could also try to use the "SqlDataSourceEnumerator" to find the instance

Upvotes: 0

Matti Virkkunen
Matti Virkkunen

Reputation: 65116

You could try to connect to the database using invalid credentials and then examine the error code to see if you got an "access denied" error as opposed to "connection failed" or something else. Whether this is reliably doable depends on your database server of choice, which you failed to mention.

The easiest way would be to just use the correct credentials, though.

Upvotes: 2

Related Questions