After executing the following code, is there a way to immediately test the connection to make sure my settings are correct before running the app?

Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder()

builder("Data Source") = My.Settings.DataSource

builder("Initial Catalog") = My.Settings.Database

builder("User ID") = My.Settings.UserID

builder("Password") = My.Settings.Password

DataLayer.DataSources.Add(New SqlDataSourceItem("", builderCMPR.ConnectionString))

"Test connection here...............If fails, possibly using a try/catch show a user friendly error message and gracefully exit the app." 

Thanks for the help!

Hi Jeff,

i use this to test the database connection:

and this to build the string:

DBEinstellung.DatenbankName/SystemBenutzer/Login are part of my Settings Class.

Hi Philipp,

Thanks for the tip.  That led me down the road where I needed to go.  What I really wanted to do was test the complete connection string.  So what I end up doing was the following:

Dim serverSchema As New MicroFour.StrataFrame.DBEngine.SQL.SQLServerSchema

Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder()

'-- Initialize builder

builder.DataSource = MySqlServerName

builder.InitialCatalog = MyDatabaseName

builder.UserID = MyUserID

builder.Password = MyPassword

'-- Try to connect to SQL Server

serverSchema.Connect(builder.DataSource, builder.UserID, builder.Password, False)

'-- Is Database valid

If serverSchema.IsDatabase(builder.InitialCatalog) Then

      '-- Add datasource

     DataLayer.DataSources.Add(New SqlDataSourceItem("", builder.ConnectionString))

Else

     '-- Show failure message

     MessageBox.Show("Unable to connect to '" & builder.DataSource & "\" & builder.InitialCatalog & "' database!",    "System Notification", MessageBoxButtons.OK)

    End

End If

'-- Clean up

serverSchema.Disconnect()

serverSchema.Dispose()

SF Team,

What would really be nice is after excecuting the DataLayer.DataSources.Add(New SqlDataSourceItem("", MyConnectionString)) is to test the DataLayer.DataSource object to determine a connection success/failure status.  Is that possible?

Thanks,

Jeff

Would this do the trick?

Bill

Hi Bill,

Using the logic below incorporating the DataLayer.DataSources("").IsAvailable method did not work.  This method always returned 'True' even though I put in invalid information.  Is there something I have to do before calling .IsAvailable method to get it to return false?

Thanks for your help!

'-- Initialize builder

builder.DataSource = My_Invalid_DataSource

builder.InitialCatalog = My_Invalid_DatabaseName

builder.UserID = MyUserID

builder.Password = MyPassword

'-- Add datasource

DataLayer.DataSources.Add(New SqlDataSourceItem("", builder.ConnectionString))

If Not DataLayer.DataSources("").IsAvailable Then

'-- Show failure message

MessageBox.Show("Unable to connect to '" & builder.DataSource & "\" & builder.InitialCatalog & "' database!", "System Notification", MessageBoxButtons.OK)

End

End If

IsAvailable is used internally for something entirely different. Your best option will be to execute a command like "select * from myTable where 1=0" and set the command timeout to 1, then wrap it in a try catch. The command shouldn't return anything and shouldn't take long to execute, but will throw the exception if the connection isn't valid.

Hi Dustin,

I am doing that now by attempting to fill BO.  I thought I might be able to take advantage of a built-in method within the DataLayer object that I might not be aware of. Hopefully these answers will help others with similar questions.  Thanks for the info anyway. 

Jeff