92

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.

PiotrWolkowski
  • 8,408
  • 6
  • 48
  • 68
agnieszka
  • 14,897
  • 30
  • 95
  • 113

4 Answers4

165

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

    DbConnectionStringBuilder csb = new DbConnectionStringBuilder();
    csb.ConnectionString = "rubb ish"; // throws

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();
    }
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • 1
    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. – dlopezgonzalez Apr 15 '12 at 10:49
  • @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 – Marc Gravell Apr 15 '12 at 11:00
  • @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 :) – bernie2436 Jul 02 '12 at 22:15
  • You might want to make sure you follow `conn.Open()` with `conn.Close()` too – Geoff May 02 '13 at 18:22
  • 1
    @MarcGravell - my comment was based on the [docs](http://msdn.microsoft.com/en-us/library/vstudio/system.data.sqlclient.sqlconnection.open(v=vs.100).aspx) saying `If the SqlConnection goes out of scope, it is not closed. Therefore, you must explicitly close the connection by calling Close.` – Geoff May 02 '13 at 19:55
  • 3
    @MarcGravell - I should search harder before commenting :) I see that `Dispose()` calls `Close()` – Geoff May 02 '13 at 19:57
  • @MarcGravell `DbConnectionStringBuilder` ConnectionString property doesn't throw an exception for `"rubb=ish;"` – Zein Makki Jul 08 '16 at 19:52
  • @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. – Marc Gravell Jul 09 '16 at 07:33
  • @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. – RAM May 17 '18 at 08:24
17

Try this. 

    try 
    {
        using(var connection = new OleDbConnection(connectionString)) {
        connection.Open();
        return true;
        }
    } 
    catch {
    return false;
    }
Rashad Valliyengal
  • 3,132
  • 1
  • 25
  • 39
  • 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? – Alican Uzun Jan 10 '17 at 12:55
8

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;
CBlafer
  • 171
  • 1
  • 2
  • 6
0

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

GGSoft
  • 439
  • 6
  • 15