Introduction
Solve the ability to configure connection to SQL Server by using app.config or any other means.
Using the code
For use only need to import System.Configuration
.
This example gets the connectionstring of app.config for viewing and editing, creating a method in the client form.
Add section connectionStrings
in app.config
<connectionStrings>
<add name="conn"
connectionString="Data Source=127.0.0.1;Initial Catalog=master; User Id=sa; Password=123456;Integrated Security=false"
providerName="System.Data.sqlclient" />
</connectionStrings>
Opens the specified client configuration file as a Configuration
object.
Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
Gets the ConnectionStringsSection data for the current application's default configuration.
Dim connectionString as String = config.ConnectionStrings.ConnectionStrings("conn").Connection
Save and refresh ConnectionString
in current application's default configuration (app.config).
config.ConnectionStrings.ConnectionStrings("conn").ConnectionString = CS
config.Save(ConfigurationSaveMode.Modified)
ConfigurationManager.RefreshSection("connectionStrings")
Complete code form method client.
Public Function ConnectionDialog() As Boolean
Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)
Dim dialog As New SQLServerConnectionDialog()
dialog.ConnectionString = config.ConnectionStrings.ConnectionStrings("conn").ConnectionString
If dialog.ShowDialog = Windows.Forms.DialogResult.Cancel Then Return False
Dim CS As String = dialog.ConnectionString
config.ConnectionStrings.ConnectionStrings("conn").ConnectionString = CS
config.Save(ConfigurationSaveMode.Modified)
ConfigurationManager.RefreshSection("connectionStrings")
Return True
End Function
To get the list of SQL Server instances registered on the network:
Dim sqlSources As DataTable = SqlDataSourceEnumerator.Instance.GetDataSources
For Each datarow As DataRow In sqlSources.Rows
Dim datasource As String = datarow("ServerName").ToString
If Not datarow("InstanceName") Is DBNull.Value Then
datasource &= String.Format("\{0}", datarow("InstanceName"))
End If
cbServer.Items.Add(datasource)
Next
To get the list of databases on SQL Server:
Using cn As SqlConnection = New SqlConnection(connString)
cn.Open()
Using cmd As SqlCommand = New SqlCommand()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp_databases"
Using myReader As SqlDataReader = cmd.ExecuteReader()
While (myReader.Read())
cbDataBase.Items.Add(myReader.GetString(0))
End While
End Using
End Using
End Using
We build a connectionstring via object SqlConnectionStringBuilder
.
Dim conn As New SqlConnectionStringBuilder()
conn.DataSource = "ServerName"
conn.IntegratedSecurity = False
conn.UserID = "sa"
conn.Password = "123456"
conn.InitialCatalog = "DBName"