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?

Solution 1

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

Solution 2

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.

Solution 3

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

Solution 4

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;
    }
}

Solution 5

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;
    }

View more solutions