How to check is connection string valid?

Question

I have to save data and I have to test connection before to save it. How can I test that this connection string is valid for a particular connection?

My code is like this:

static public bool TestConnString(string connectionString)
{
    bool returnVal = true;
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        try
        {
            conn.Open();

            if (conn.State != ConnectionState.Open)
                returnVal = false;
            else
                returnVal = true;
        }
        catch (Exception ex)
        {
            returnVal = false;
        }
    }

    return returnVal;
}

Connection string is:

Data Source=testSvr03\SQLEXPRESS;Initial Catalog=Test; Connection Timeout=600; Persist Security Info=True;User ID=Test; password=test

If I give wrong data source in connection String then it never returns in this function after conn.open() .I put catch block but it is coming in it

Can anyone Tell me what is solution?

a connect call would anyways validate/fail if your connection string is invalid. — Prabhu Murthy, Nov 16 '12 at 08:45
@CodeIgnoto I want to test that is there a data connection for a particular connection string — Jitender Mahlawat, Nov 16 '12 at 08:56
@m4ngl3r I didnot get solution from these answer .problem was due to connection timeout — Jitender Mahlawat, Nov 21 '12 at 08:19
Possible duplicate of [How to check if connection string is valid?](http://stackoverflow.com/questions/434864/how-to-check-if-connection-string-is-valid) — Luke Girvin, Oct 31 '16 at 11:31

Tim Schmelter · Answer 1 · 2012-11-16T09:24:18.207

You can let the SqlConnectionStringBuilder constructor check it:

bool isValidConnectionString = true;
try{
    var con = new SqlConnectionStringBuilder("ABC");
}catch(Exception)
{
    // can be KeyNotFoundException, FormatException, ArgumentException
    isValidConnectionString = false;
}

Here's an overview of the ConnectionStringBuilders for the different data providers:

Provider                   ConnectionStringBuilder 
System.Data.SqlClient      System.Data.SqlClient.SqlConnectionStringBuilder
System.Data.OleDb          System.Data.OleDb.OleDbConnectionStringBuilder
System.Data.Odbc           System.Data.Odbc.OdbcConnectionStringBuilder
System.Data.OracleClient   System.Data.OracleClient.OracleConnectionStringBuilder

score 7 · Answer 2 · edited Jul 07 '13 at 19:48

7

You can put the return statement just in the catch block like this

static bool TestConnectionString(string connectionString)
{
    using (MySqlConnection conn = new MySqlConnection(connectionString))
    {
        try
        {
            conn.Open();

            return (conn.State == ConnectionState.Open);
        }
        catch
        {
            return false;
        }
    }
    return false;
}

I have just tried this. It works correctly (returns false value) if you call this function with empty string.

edited Jul 07 '13 at 19:48

abatishchev

98,240
88
296
433

answered Jul 07 '13 at 19:21

vwvolodya

2,324
2
20
30

1

But this needs to open a pysical connection to the database even if you just want to know if the format is correct. Also, if you have problems with the network it'll also return `false` even if the format is correct as opposed to the [`...ConnectionStringBuilder`](http://stackoverflow.com/a/13413610/284240) approach. – Tim Schmelter Jul 07 '13 at 20:35
1

But the task was to get known if the connection is ok. So, there is no other way to discover whether there is a connection to DB unless trying to open it. – vwvolodya Jul 14 '13 at 19:32

score 3 · Answer 3 · answered Nov 16 '12 at 08:53

You can just try to open connection

SqlConnection myConnection = new SqlConnection(myConnString); 
try
{
   myConnection.Open();
}
catch(SqlException ex)
{
   //Failure to open
}
finally
{
   myConnection.Dispose();
}

You can do it in background thread and you can set Timeout, If you don't want waiting long

score 2 · Answer 4 · answered Mar 11 '14 at 11:58

This is what I ended up using:

private bool validateConnectionString(string connString)
{
    try
    {
        var con = new SqlConnectionStringBuilder(connString);
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            return (conn.State == ConnectionState.Open);
        }
    }
    catch
    {
        return false;
    }
}

score 1 · Answer 5 · answered Mar 28 '14 at 09:09

1

Try this. This is the easiest way to check a connection.

    try 
    {
        using(var connection = new OleDbConnection(connectionString)) {
        connection.Open();
        return true;
        }
    } 
    catch {
    return false;
    }

answered Mar 28 '14 at 09:09

Rashad Valliyengal

3,132
1
25
39

score 0 · Answer 6 · answered Nov 16 '12 at 08:46

0

you mean connection string?

well, something like this maybe...

try
{
//....try to connect and save it here, if connection can not be made it will throw an exception
}
catch(Exception ex)
{

}

answered Nov 16 '12 at 08:46

m4ngl3r

552
2
17

score 0 · Answer 7 · answered Nov 16 '12 at 08:49

0

Create a connection object and try to open it.

An exception will be throw if the connection string is invalid. Something like this:

using(var connection = New SqlConnection("..."))
{
     connection.Open();
}

answered Nov 16 '12 at 08:49

Rui Jarimba

11,166
11
56
86

I am also using same code to check connection if I am giving datasource wrong in connectionSting It never comes in catch block or on code next line after connection.open() Statement Data Source=testSvr03\SQLEXPRESS;Initial Catalog=Test; Connection Timeout=600; Persist Security Info=True;User ID=Test; password=test – Jitender Mahlawat Nov 16 '12 at 12:40

How to check is connection string valid?

7 Answers7

Linked