CODEWITHSUNDEEP
c#sql-serverdatabase
Shawn H.
Shawn H.

Reputation: 789

Testing Database Connectivity in C# Console Application

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?

Upvotes: 0

Views: 5564

Answers (4)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112682

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.

Upvotes: 0

Marco
Marco

Reputation: 57593

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...

Upvotes: 3

Orlando Perri
Orlando Perri

Reputation: 40

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
}

Upvotes: 0

Sandeep
Sandeep

Reputation: 7334

'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

Upvotes: 0

Related Questions