StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » How to test DataLayer.DataSources connection string

How to test DataLayer.DataSources connection string

Jeff Pagley — Tue, 02 Sep 2008 14:34:50 GMT

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!

RE: How to test DataLayer.DataSources connection string

Jeff Pagley — Tue, 02 Sep 2008 14:34:50 GMT

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

RE: How to test DataLayer.DataSources connection string

Dustin Taylor — Tue, 02 Sep 2008 13:53:18 GMT

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.

RE: How to test DataLayer.DataSources connection string

Jeff Pagley — Tue, 02 Sep 2008 09:12:20 GMT

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

RE: How to test DataLayer.DataSources connection string

Bill Cunnien — Fri, 29 Aug 2008 10:39:13 GMT

[quote][b]Jeff Pagley (08/28/2008)[/b][hr]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

[/quote]

Would this do the trick?

[codesnippet]DataLayer.DataSources["KeyName"].IsAvailable[/codesnippet]

Bill

RE: How to test DataLayer.DataSources connection string

Jeff Pagley — Thu, 28 Aug 2008 11:41:13 GMT

SF Team,

Thanks,

Jeff

RE: How to test DataLayer.DataSources connection string

Jeff Pagley — Thu, 28 Aug 2008 11:29:55 GMT

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

RE: How to test DataLayer.DataSources connection string

Philipp Guntermann — Thu, 28 Aug 2008 08:42:24 GMT

Hi Jeff,

i use this to test the database connection:

[codesnippet]

public static bool PrfeSQLVerbindung(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;

}

[/codesnippet]

and this to build the string:

[codesnippet]

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;

}

[/codesnippet]

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