CODEWITHSUNDEEP
c#.netsql-server-2008ado.netconnection-string
Jitender Mahlawat
Jitender Mahlawat

Reputation: 3102

How to check is connection string valid?

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?

Upvotes: 2

Views: 26694

Answers (7)

Rashad Valliyengal
Rashad Valliyengal

Reputation: 3162

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

Upvotes: 1

Peter
Peter

Reputation: 14508

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

Upvotes: 2

vwvolodya
vwvolodya

Reputation: 2344

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.

Upvotes: 7

Tim Schmelter
Tim Schmelter

Reputation: 460038

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

Upvotes: 12

Rustem
Rustem

Reputation: 326

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

Upvotes: 3

Rui Jarimba
Rui Jarimba

Reputation: 17944

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

Upvotes: 0

m4ngl3r
m4ngl3r

Reputation: 560

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)
{

}

Upvotes: 0

Related Questions