Testing Database Connectivity in C# Console Application

Question

I have a C# Console Application that is essentially a long batch process that processes data on a nightly basis across many different databases. What is the proper or preferred way to test basic database connectivity at the beginning of this type of program? I ran into the issue of having an expired database password for one of my connections that was caught by exception handling but I want to test for basic connectivity at the very beginning.

Would a simple SELECT query suffice or is there a more efficient way of doing this for numerous databases?

Marco · Accepted Answer · 2012-04-13 12:53:06Z

3

IMHO the simplest way is trying to connect to database and, if you have a failure, you give up.
As you're running a night batch, it's not important to understand immediately the reason and solve it.
So something like this

using(SqlConnection conn = new SqlConnection(connectionString))
{
    try
    {
        conn.Open();
        // Do what you please here        
    }
    catch (Exception ex)
    {
        // Write error to file
        File.Append(..., 
            DateTime.Now.ToString("yyyy-MM-dd hh:mm:ss") + " " + 
            ex.Message);
    }
    finally 
    { 
        conn.Close();
    } 
}

Next morning you can check file for errors...

edited Apr 13, 2012 at 12:53

answered Apr 13, 2012 at 12:47

Marco

57.7k15 gold badges135 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Marco Over a year ago

@OlivierJacot-Descombes: I don't understand... what's wrong with my answer?

Shawn H. Over a year ago

The answer looks fine to me. Side-note: Do you need to use the conn.Close() when enclosed in a using statement?

Marco Over a year ago

@ShawnH.: I'm not sure, I should test it. I was wondering right when posting answer, but because of I'm not sure... well, it's better to be sure it's closed!! :)

Marco Over a year ago

@OlivierJacot-Descombes: I was editing my answer, but I did it six minutes before you posted your comment! ;)

Olivier Jacot-Descombes Over a year ago

Never mind. For some reason it's the version a saw when I posted my comment. I deleted my comments.

Sandeep · Accepted Answer · 2012-04-13 12:52:31Z

0

'Connection.open`

is the simple way to determine if you can connect to db or not.

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
}

If you get a SqlException with number 18487 or 18488 it indicates the password has been changed.

Connection.changePassword

answered Apr 13, 2012 at 12:52

Sandeep

7,34213 gold badges47 silver badges57 bronze badges

Comments

Orlando Perri · Accepted Answer · 2012-04-13 12:52:50Z

0

You don't need to run any query. If you use SqlConnection passing the connection string, you can just try to Open() the connection and you'll get an exception if you cannot connect Something like:

try
{
    var cnn = new SqlConnection(connectionString);
    cnn.Open();
}
catch
{
   // connection failed, do something
}

answered Apr 13, 2012 at 12:52

Orlando Perri

402 bronze badges

Comments

Olivier Jacot-Descombes · Accepted Answer · 2012-04-13 12:53:57Z

0

Opening (and then closing) a connection should be sufficient to test the password. however, this does not tell you , if a db-user has permissions to access specific tables.

answered Apr 13, 2012 at 12:53

Olivier Jacot-Descombes

114k14 gold badges149 silver badges202 bronze badges

Collectives™ on Stack Overflow

Testing Database Connectivity in C# Console Application

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related