By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,070 Members | 1,718 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,070 IT Pros & Developers. It's quick & easy.

Change Connection String during runtime

P: n/a
Hi,
I am using VB 2005. My application has many data bound controls. The
connection is stored in the app.config file.

I want the application to start with a default connection string and
while during the runtime, the user can click on a button and change the
connection string without exiting the application.
I would really appreciate any sort of help.

I have already something working but this requires the application to
close and restart. ( If i dont close and restart the application dosent
change the connection string) . Here is the code
locatedatabase.vb

Imports System.Xml
Public Class LocateDatabase
Private Const AuthenticationTypeWindows As Integer = 1
Private Const AuthenticationTypeSQL As Integer = 2
Dim ProgramTitle As String = "BDM Security"

Public Function PromptUser() As String
' ----- Prompt the user for database connection details. Return
a
' valid ADO.NET connection string for SQL Server, or a
blank
' string if the user clicks Cancel.

Dim newConnection As String
' ----- Prompt the user.
Try
Me.ShowDialog()
Catch ex As Exception

End Try
' ----- Build the new connection string.
If (Me.DialogResult = Windows.Forms.DialogResult.OK) Then

newConnection = "Data Source=" & Trim(server.Text) & _
";Initial Catalog=" & Trim(database_name.Text)
If authentication.SelectedIndex = 0 Then
' ----- Use Windows security.
newConnection &= ";Integrated Security=true"
Else
' ----- Use SQL Server security.
newConnection &= ";User ID=" & Trim(user_id.Text) & _
";Password=" & Trim(password.Text)
End If
Return newConnection
Else
Return ""
End If
End Function

Private Sub LocateDatabase_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
' ----- Prepare the form.
Dim counter As Integer
Dim connectionString As String
Dim oneKey As String
Dim oneValue As String
On Error Resume Next
' ----- Load in the existing data.

connectionString = My.Settings.con & ""
For counter = 1 To CountSubStr(connectionString, ";") + 1
' ----- Each comma-delimited part has the format
"key=value".
oneKey = GetSubStr(connectionString, ";", counter)
oneValue = Trim(GetSubStr(oneKey, "=", 2))
oneKey = Replace(UCase(Trim(GetSubStr(oneKey, "=", 1))), "
", "")

' ----- Process each part.
Select Case oneKey
Case "DATASOURCE"
' ----- Show the server host.
server.Text = oneValue
Case "INITIALCATALOG"
' ----- Show the default database name.
database_name.Text = oneValue
Case "INTEGRATEDSECURITY"
' ----- Only check for "true". False is assumed.
If (UCase(oneValue) = "TRUE") Then _
authentication.SelectedIndex = 0
Case "USERID"
' ----- A user ID forces SQL authentication.
authentication.SelectedIndex = 1
user_id.Text = oneValue
Case "PASSWORD"
' ----- A password forces SQL authentication.
authentication.SelectedIndex = 1
password.Text = oneValue
End Select
Next counter
End Sub

Private Sub RecordDatabase_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles database_name.Enter
' ----- Highlight the entire text.
database_name.SelectAll()
End Sub

Private Sub RecordPassword_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles password.Enter
' ----- Highlight the entire text.
password.SelectAll()
End Sub

Private Sub RecordServer_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles server.Enter
' ----- Highlight the entire text.
server.SelectAll()
End Sub

Private Sub RecordUser_Enter(ByVal sender As Object, ByVal e As
System.EventArgs) Handles user_id.Enter
' ----- Highlight the entire text.
user_id.SelectAll()
End Sub

Private Sub RecordAuthentication_SelectedIndexChanged(ByVal sender
As Object, ByVal e As System.EventArgs) Handles
authentication.SelectedIndexChanged
' ----- Enable dependent fields as needed.
If authentication.SelectedIndex = 0 Then
' ----- Windows authentication requires no user/password.
Label4.Enabled = False
user_id.Enabled = False
Label5.Enabled = False
password.Enabled = False
Else
' ----- Requires specific SQL Server user and password.
Label4.Enabled = True
user_id.Enabled = True
Label5.Enabled = True
password.Enabled = True
End If
End Sub
Private Sub ActOK_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles ActOK.Click
'Clear the previous connection string

Dim con_Z As String
con_Z = My.Settings.con
My.Settings.con = ""
If (ValidateFormData() = False) Then Return
Me.DialogResult = Windows.Forms.DialogResult.OK
If Form1.ConnectDatabase() = False Then
My.Settings.con = con_Z
Me.Close()
Exit Sub
End If
Form1.Close()

Me.Close()
End Sub
Public Function CountSubStr(ByVal mainText As String, ByVal subText
As String) As Integer
' ----- Return a count of the number of times that a subText
occurs in
' a string (mainText).
Dim totalTimes As Integer
Dim startPos As Integer
Dim foundPos As Integer

totalTimes = 0
startPos = 1

' ----- Keep searching until we don't find it no more!
Do
' ----- Search for the subText.
foundPos = InStr(startPos, mainText, subText)
If (foundPos = 0) Then Exit Do
totalTimes = totalTimes + 1

' ----- Move to just after the occurrence.
startPos = foundPos + Len(subText)
Loop

' ----- Return the count.
Return totalTimes
End Function

Public Function GetSubStr(ByVal origString As String, ByVal delim
As String, _
ByVal whichField As Integer) As String
' ----- Extracts a delimited string from another larger string.
Dim stringParts() As String

' ----- Handle some errors.
If (whichField < 0) Then Return ""
If (Len(origString) < 1) Then Return ""
If (Len(delim) = 0) Then Return ""

' ----- Break the string up into delimited parts.
stringParts = Split(origString, delim)

' ----- See if the part we want exists and return it.
If (whichField UBound(stringParts) + 1) Then Return "" _
Else Return stringParts(whichField - 1)
End Function

Private Function ValidateFormData() As Boolean
' ----- Check the supplied data, and return True if it is all
valid.
On Error Resume Next

' ----- Server name is required.
If (Trim(server.Text) = "") Then
MsgBox("The Server or Host name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
server.Focus()
Return False
End If
' ----- Database name is required.
If (Trim(database_name.Text) = "") Then
MsgBox("The Database Name is required.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
database_name.Focus()
Return False
End If
' ----- For SQL Server authentication, the User ID is required.
If authentication.SelectedIndex = 1 Then
If (Trim(user_id.Text) = "") Then
MsgBox("The User Name is required for SQL Server
authentication.", _
MsgBoxStyle.OkOnly Or MsgBoxStyle.Exclamation,
ProgramTitle)
user_id.Focus()
Return False
End If
End If
' ----- Success.
Return True
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Me.Close()
End Sub
Private Sub unit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles unit.Click
server.Text = "SQLDEV2\SQL_DEV2"
database_name.Text = "fgb01q_unit"
authentication.SelectedIndex = 0
user_id.Text = ""
password.Text = ""

End Sub

Private Sub demo_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub

Private Sub demo1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles demo1.Click
server.Text = "BUF53460"
database_name.Text = "fgb01q_demo1"
authentication.SelectedIndex = 1
user_id.Text = "sa"
password.Text = "blues"
End Sub
End Class
Main Form (Form1)

Private Sub ChangeDatabase_Click(ByVal sender As System.Object, ByVal e
As System.EventArgs) Handles ChangeDatabase.Click
LocateDatabase.Show()

End Sub
Public Function ConnectDatabase() As Boolean
' ----- Connect to the database. Return True on success.
Dim connectionString As String
Dim configChanged As Boolean
Dim ProgramTitle As String = "BDM Security "
' ----- Initialize.
HoldTransaction = Nothing
configChanged = False
' ----- Obtain the connection string.
If (Trim(My.Settings.con & "") = "") Then
' ----- Inform the user about the need to configure the
database.
If (MsgBox("The application will exit for the new
connection. Please restart the application. Would you like to
proceed?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
<MsgBoxResult.Yes) Then Return False
' ----- Prompt for the new connection details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
Else
connectionString = My.Settings.con

End If

TryConnectingAgain:
' ----- Attempt to open the database.
Try
DB = New SqlClient.SqlConnection(connectionString)
DB.Open()
Catch ex As Exception
' ----- Some database failure.
MsgBox("Database Connection Error ")

' ----- Perhaps it is just a configuration issue.
If (MsgBox("The connection to the database may have failed
due to " & _
"invalid configuration settings. Would you like to
change the " & _
"database configuration at this time?", _
MsgBoxStyle.YesNo Or MsgBoxStyle.Question,
ProgramTitle) _
<MsgBoxResult.Yes) Then Return False

' ----- Prompt for new details.
connectionString = LocateDatabase.PromptUser()
If (connectionString = "") Then Return False
configChanged = True
GoTo TryConnectingAgain
End Try

' ----- Save the udpated configuration if needed.
If (configChanged = True) Then _
My.Settings.con = connectionString
' ----- Success.
Return True
End Function

End Class

Dec 28 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Sankalp" <sa**********@gmail.comwrote in news:1167320785.025493.102960
@h40g2000cwb.googlegroups.com:
I want the application to start with a default connection string and
while during the runtime, the user can click on a button and change the
connection string without exiting the application.
Load the connection string into a static (shared) variable. Initialize the
variable with the connection string in the app.config.

If the connection string changes during runtime, you'll need to
reinitialize all your database stuff/
Dec 28 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.