Post #4 : Programming "How to Create SQL Server Connection String Dynamic"

Hi Jack.

We meet again, today we will try to make SQL Server Connection String which we can change without changing the script.

Let's Start

1. Open your project, right click on Solution Explorer click properties, see picture below 

2. Project properties windows will appear, and select setting tab, see picture below 

3. Create some settings as shown below
 

4. Close properties windows.
5. Right click on Solution Explorer click Add, Click Class


6. name it "koneksi_server.vb", see picture below


7. In class that we created earlier, typewritten script as below

    Public Shared koneksi As String = "Server=" & My.Settings.server_instance & ";Database=" _
                                      & My.Settings.database & ";User Id =" & My.Settings.usr & ";Password=" & My.Settings.pwd & ";"

8. Create as form as shown below, to change value in the setting which we have previously made.


9. last but most importantly, create a script to save the setting and to test connection are made.

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        get_connection_info()
    End Sub
    Private Sub get_connection_info()
        tserver_instance.Text = My.Settings.server_instance
        tdatabase.Text = My.Settings.database
        tusr.Text = My.Settings.usr
        tpwd.Text = My.Settings.pwd
    End Sub
    Private Sub test_connection()
        Try
            'uji coba masuk ke database
            Using iKon = New SqlConnection(koneksi_server.koneksi)
                If iKon.State = ConnectionState.Open Then iKon.Close()
                iKon.Open()
                MsgBox("Koneksi Berhasil dibuat", MsgBoxStyle.Information)
            End Using
        Catch ex As Exception
            'untuk menangkap error yang terjadi
            MsgBox("Koneksi Error" & vbNewLine & ex.Message, MsgBoxStyle.Critical)
        End Try
    End Sub
    Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click
        My.Settings.server_instance = tserver_instance.Text
        My.Settings.database = tdatabase.Text
        My.Settings.usr = tusr.Text
        My.Settings.pwd = tpwd.Text
        My.Settings.Save()
        test_connection()
    End Sub
End Class

CONGRATULATIONS !!!

Sample Project : Download Here

Next : "How to Detect a list of SQL Server instance are active"