473,387 Members | 1,416 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Change Connection String during runtime

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
1 6028
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Tim Nelson | last post by:
I am building a data bound application for SQL server which all the forms I use sqlconnections generated by VS. I want to have a login form that changes the connection depending upon login...
5
by: Markus Stehle | last post by:
Hi all! I have asp.net web application that uses static impersonation. Is it possible to change the impersonated user during runtime? Within some parts of my application I would like to...
2
by: Balaji | last post by:
Hi All, Can I use more than one membership provider for a given website? I understand only one of them could be default one. If yes, then how to programmatically access the other membership...
37
by: sam44 | last post by:
Hi, At startup the user log on and chooses the name of a client from a dropdownlist, which then changes dynamically the connection string (the name of the client indicates which database to use)....
1
by: Randy | last post by:
I just had to rebuild my computer and have reorganized my file structure. I have a dataset established at design time with many tables, etc. I have moved my mdf folder to a new location and now I...
6
by: little83 | last post by:
Dear all i wrote one class to deal with sql database to insert and read data from that class only...and based on that one of the parmeters or the class is the sqlconnection SqlConnection...
0
by: thomasbihn | last post by:
I'm new to C# development. I am familiar with setting up connection strings and working with databases in ADO in VB6, but this is a bit more involved. I have a very basic application in which I...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.