How to check if connection string is valid?

Question

I'm writing an application where a user provides a connection string manually and I'm wondering if there is any way that I could validate the connection string - I mean check if it's correct and if the database exists.

Libava

Answer 1

You could try to connect? For quick (offline) validation, perhaps use DbConnectionStringBuilder to parse it...

    DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
    csb.ConnectionString = "rubb ish"; // throws
    if (string.IsNullOrEmpty(csb.ConnectionString))
    {
        throw new ArgumentException("Connection string is invalid");
    }

But to check whether the db exists, you'll need to try to connect. Simplest if you know the provider, of course:

    using(SqlConnection conn = new SqlConnection(cs)) {
        conn.Open(); // throws if invalid
    }

If you only know the provider as a string (at runtime), then use DbProviderFactories:

    string provider = "System.Data.SqlClient"; // for example
    DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
    using(DbConnection conn = factory.CreateConnection()) {
        conn.ConnectionString = cs;
        conn.Open();
    }

Granular

When it usign System.data.sqlite, this snippet doesnt work. Until dbcon doesnt execute a query, the user doesnt known if connection string are correct. – Brumaire

@videador do you mean "invalid syntax"? Or "valid syntax but wrong info?" - obviously if it looks sane but the server name or password is wrong, you have to try to connect to check that. If sqlite will "Open()" with an invalid string, then that sounds like a bug in SQLite – Granular

@MarcGravell I have heard that you should not use exceptions for flow control. Is there any way to do this without throwing and catching an exception? Or is this the best method available? Maybe an "exception" to the rule above :) – Coronado

You might want to make sure you follow conn.Open() with conn.Close() too – Semmes

@MarcGravell - my comment was based on the docs saying If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close. – Semmes

@MarcGravell - I should search harder before commenting :) I see that Dispose() calls Close() – Semmes

@MarcGravell DbConnectionStringBuilder ConnectionString property doesn't throw an exception for "rubb=ish;" – Sissy

@user3185569 why would it? It is semantically valid. It is up the the actual provider to decide what key/value pairs is wants to complain about. – Granular

@bernie2436: When I use exception for flow control I specify the type of exception in the catch (System.Data.SqlClient.SqlException) - that makes me feel a bit better about doing it anyway. – Gambrill

Answer 2

Try this.

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

Innards

I tried your code, it is working as expected but, it throws after connection timeout expires. I tried to set connection timeout to 1 sec in connection string, nothing changed. Is there a solution to this? – Prefect

Answer 3

If the goal is validity and not existence, the following will do the trick:

try
{
    var conn = new SqlConnection(TxtConnection.Text);
}
catch (Exception)
{
    return false;
}
return true;

Carmine

Answer 4

For sqlite use this: Suppose you have connection string in textbox txtConnSqlite

     Using conn As New System.Data.SQLite.SQLiteConnection(txtConnSqlite.Text)
            Dim FirstIndex As Int32 = txtConnSqlite.Text.IndexOf("Data Source=")
            If FirstIndex = -1 Then MsgBox("ConnectionString is incorrect", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
            Dim SecondIndex As Int32 = txtConnSqlite.Text.IndexOf("Version=")
            If SecondIndex = -1 Then MsgBox("ConnectionString is incorrect", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
            Dim FilePath As String = txtConnSqlite.Text.Substring(FirstIndex + 12, SecondIndex - FirstIndex - 13)
            If Not IO.File.Exists(FilePath) Then MsgBox("Database file not found", MsgBoxStyle.Exclamation, "Sqlite") : Exit Sub
            Try
                conn.Open()
                Dim cmd As New System.Data.SQLite.SQLiteCommand("SELECT * FROM sqlite_master WHERE type='table';", conn)
                Dim reader As System.Data.SQLite.SQLiteDataReader
                cmd.ExecuteReader()
                MsgBox("Success", MsgBoxStyle.Information, "Sqlite")
            Catch ex As Exception
                MsgBox("Connection fail", MsgBoxStyle.Exclamation, "Sqlite")
            End Try
          End Using

I think you can easilly convert it to c# code

Bartolemo