[ตัวอย่าง] หน้าจอสำหรับตั้งค่าการเชื่อมต่อฐานข้อมูล MS SQL Server (VB.Net)

Public Class frmConnectionSetup

    Private _IsFirstLoadServer As Boolean = True
    Private _lastServerName As String = ""
    Private _isTestPass As Boolean = False
    Private _conString As String = ""

    Private Sub getInstanceServer()
        Me.Cursor = Cursors.WaitCursor
        Dim sv As New DataTable
        sv = System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources
        For Each dr As DataRow In sv.Rows
            If dr("InstanceName").ToString = "" Then
                Me.cboServer.Items.Add(dr("ServerName"))
            Else
                Me.cboServer.Items.Add(dr("ServerName").ToString & "\" & dr("InstanceName").ToString)
            End If
        Next

        sv.Dispose()
        GC.SuppressFinalize(sv)
        sv = Nothing
       
        Me.Cursor = Cursors.Default
        _IsFirstLoadServer = False

    End Sub

    Private Sub cboServer_MouseClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles cboServer.MouseClick
        If Me.cboServer.Items.Count = 0 Then
            getInstanceServer()
        End If
    End Sub

    Private Sub frmConnectionSetup_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.cboAuthenType.Items.Add("Windows Authentication")
        Me.cboAuthenType.Items.Add("SQL Server Authentication")
        Me.cboAuthenType.SelectedIndex = 0
    End Sub

    Private Sub cboAuthenType_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboAuthenType.SelectedIndexChanged
        Dim SQLAuthen As Boolean = True
        If Me.cboAuthenType.SelectedIndex <> 1 Then
            SQLAuthen = False
        End If

        Me.lblUserName.Enabled = SQLAuthen
        Me.txtUserName.Enabled = SQLAuthen
        Me.lblPassword.Enabled = SQLAuthen
        Me.txtPassword.Enabled = SQLAuthen
        _isTestPass = False
    End Sub

    Private Sub cboServer_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cboServer.SelectedIndexChanged
        If _lastServerName.ToLower <> Me.cboServer.Text.ToLower Then
            _lastServerName = Me.cboServer.Text
        Else
            Exit Sub
        End If

        Me.cboDatabase.Items.Clear()

        If Me.cboServer.Text.Length = 0 Then
            Exit Sub
        End If

        Try
            _isTestPass = False
            Dim server As New Microsoft.SqlServer.Management.Smo.Server(Me.cboServer.Text)

            For i As Integer = 0 To server.Databases.Count - 1
                Me.cboDatabase.Items.Add(server.Databases.Item(i).Name)
            Next
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "Error")
        End Try

    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        If _isTestPass = False Then
            MsgBox("กรุณาทดสอบการเชื่อมต่อก่อนกดบันทึก", MsgBoxStyle.Exclamation + MsgBoxStyle.OkOnly, "")
            Exit Sub
        End If

        Dim dt As New DataTable
        dt.TableName = "ConnParam"
        dt.Columns.Add("Server")
        dt.Columns.Add("Database")
        dt.Columns.Add("AuthenType")
        dt.Columns.Add("UserName")
        dt.Columns.Add("Password")

        Dim dr As DataRow
        dr = dt.NewRow

        dr("Server") = Me.cboServer.Text
        dr("Database") = Me.cboDatabase.Text
        dr("AuthenType") = Me.cboAuthenType.SelectedIndex
        dr("UserName") = Me.txtUserName.Text
        dr("Password") = Me.txtPassword.Text

        dt.Rows.Add(dr)

        dt.WriteXml(Application.StartupPath & "\ConnParam.xml")
        ' บันทึกการเชื่อมต่อไว้ที่เดียวกับ exe ไฟล์

        dt.Dispose()
        dt = Nothing

        gConnentionString = _conString

        If Not StartConnection() Then
            MessageBox.Show("เชื่อมต่อฐานข้อมูลไม่ได้", "ผิดพลาด", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End If

        GC.Collect()

        Me.Close()

    End Sub

    Private Sub btnTestConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTestConnect.Click
        _conString = ""
        _conString &= "Data Source = " & Me.cboServer.Text & ";"
        _conString &= "Database = " & Me.cboDatabase.Text & ";"
        If Me.cboAuthenType.SelectedIndex = 0 Then
            _conString &= "Integrated Security = True; Persist Security Info = True"
        Else
            _conString &= "User Id= " & Me.txtUserName.Text & "; Password=" & Me.txtPassword.Text & ";"
        End If

        Dim conn As New SqlClient.SqlConnection(_conString)
        Try
            conn.Open()
        Catch ex As Exception
        End Try
        If conn.State = ConnectionState.Open Then
            conn.Close()
            MsgBox("เชื่อมต่อสำเร็จ", MsgBoxStyle.Information + MsgBoxStyle.OkOnly, "")
            _isTestPass = True
        Else
            MsgBox("เชื่อมต่อล้มเหลว", MsgBoxStyle.Critical + MsgBoxStyle.OkOnly, "")
            _isTestPass = False
        End If

        conn.Dispose()
        conn = Nothing
        GC.Collect()
    End Sub
End Class

    '=====  
    '   gConnentionString เป็นตัวแปร Global  สร้างไว้ใน Module สำหรับเรียกใช้ได้ทั้งโปรแกรม


    Public Sub getConString()
        Try
            Dim dt As New DataTable
            dt.TableName = "ConnParam"
            dt.ReadXmlSchema(Application.StartupPath & "\ConnParam.xml")
            dt.ReadXml(Application.StartupPath & "\ConnParam.xml")

            If dt IsNot Nothing Then
                If dt.Rows.Count > 0 Then
                    gConnentionString = ""
                    gConnentionString &= "Data Source = " & dt.Rows(0)("Server").ToString & ";"
                    gConnentionString &= "Database = " & dt.Rows(0)("Database").ToString & ";"
                    If CInt(dt.Rows(0)("AuthenType").ToString) = 0 Then
                        gConnentionString &= "Integrated Security = True; Persist Security Info = True"
                    Else
                        gConnentionString &= "User Id= " & dt.Rows(0)("UserName").ToString & "; Password=" & dt.Rows(0)("Password").ToString & ";"
                    End If
                End If
            End If

            dt.Dispose()
            dt = Nothing

            GC.Collect()
        Catch ex As Exception

        End Try

    End Sub