How to test DataLayer.DataSources connection string

StrataFrame Forum

How to test DataLayer.DataSources connection string

http://forum.strataframe.net/Topic18828.aspx

By Jeff Pagley - 8/28/2008

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!

By Philipp Guntermann - 8/28/2008

Hi Jeff,

i use this to test the database connection:

public static bool PrfeSQLVerbindung(string Server, string Instanz, string Benutzername, string Login)
{
bool Result;
MicroFour.StrataFrame.DBEngine.SQL.SQLServerSchema ServerSchema = new MicroFour.StrataFrame.DBEngine.SQL.SQLServerSchema();
if (Instanz != null && Instanz != "")
{
Result = ServerSchema.IsValidConnection(Server + @"\" + Instanz, Benutzername, Login);
}
else
{
Result = ServerSchema.IsValidConnection(Server, Benutzername, Login);
}
ServerSchema.Dispose();
return Result;
}

and this to build the string:

public string GetVerbindungsString()
{
string DBString = "Server=" + DBEinstellungen.DatenbankServer;
if (DBEinstellungen.DatenbankInstanz != null && DBEinstellungen.DatenbankInstanz != "")
{
DBString += @"\" + DBEinstellungen.DatenbankInstanz;
}
DBString += ";" +
"Database=" + DBEinstellungen.DatenbankName + ";" +
"Uid=" + DBEinstellungen.SystemBenutzer + ";" +
"Pwd=" + DBEinstellungen.SystemLogin + ";";
return DBString;
}

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

By Jeff Pagley - 8/28/2008

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

By Jeff Pagley - 8/28/2008

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

By Bill Cunnien - 8/29/2008

Jeff Pagley (08/28/2008)

SF Team,

Thanks,

Jeff

Would this do the trick?

DataLayer.DataSources["KeyName"].IsAvailable

Bill

By Jeff Pagley - 9/2/2008

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

By Dustin Taylor - 9/2/2008

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.

By Jeff Pagley - 9/2/2008

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