How to check if the connection string is correct?

Question

I am writing an application in which the user provides the connection string manually, and I wonder if there is a way to check the connection string - I mean checking the correctness and availability of the database.

Answer 1

Could you try to connect? For a quick (offline) check, use DbConnectionStringBuilder to parse it ...

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

But to check if db exists, you need to try connecting. The easiest way is if you know the supplier, of course:

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

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

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

Answer 2

Try it.

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

Answer 3

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

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

Answer 4

For sqlite, use this: suppose you have a connection string in the txtConnSqlite text box

  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 easily convert it to C # code