connectionpoolingform.vb

讲解如何实现从数据库同步下载数据以及在窗口上显示数据

Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.Drawing
Imports System.Text
Imports System.Windows.Forms
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.Common
Imports System.Diagnostics

Public Class ConnectionPoolingForm
    Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        'Force app to be available for SqlClient perf counting
        Using cn As New SqlConnection()

        End Using

        InitializeMinSize()
        InitializePerfCounters()
    End Sub

    Sub InitializeMinSize()
        Me.MinimumSize = Me.Size
    End Sub

    Dim _SelectedConnection As DbConnection = Nothing

    Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged
        _SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)
        EnableOrDisableButtons(_SelectedConnection)
    End Sub

    Sub DisableAllButtons()
        btnAdd.Enabled = False
        btnOpen.Enabled = False
        btnQuery.Enabled = False
        btnClose.Enabled = False
        btnRemove.Enabled = False
        btnClearPool.Enabled = False
        btnClearAllPools.Enabled = False
    End Sub

    Sub EnableOrDisableButtons(ByVal cn As DbConnection)
        btnAdd.Enabled = True
        If cn Is Nothing Then
            btnOpen.Enabled = False
            btnQuery.Enabled = False
            btnClose.Enabled = False
            btnRemove.Enabled = False
            btnClearPool.Enabled = False
        Else
            Dim connectionState As ConnectionState = cn.State
            btnOpen.Enabled = (connectionState = connectionState.Closed)
            btnQuery.Enabled = (connectionState = connectionState.Open)
            btnClose.Enabled = btnQuery.Enabled
            btnRemove.Enabled = True
            If Not (TryCast(cn, SqlConnection) Is Nothing) Then
                btnClearPool.Enabled = True
            End If
        End If
        btnClearAllPools.Enabled = True
    End Sub

    Sub StartWaitUI()
        Me.Cursor = Cursors.WaitCursor
        DisableAllButtons()
    End Sub

    Sub EndWaitUI()
        Me.Cursor = Cursors.Default
        EnableOrDisableButtons(_SelectedConnection)
    End Sub

    Sub SetStatus(ByVal NewStatus As String)
        RefreshPerfCounters()
        Me.statusStrip.Items(0).Text = NewStatus
    End Sub

    Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click
        Dim strConn As String = txtConnectionString.Text
        Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()
        Try
            bldr.ConnectionString = strConn
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return
        End Try

        Dim dlg As New ConnectionStringBuilderDialog()
        If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then
            txtConnectionString.Text = dlg.ConnectionString
            SetStatus("Ready")
        Else
            SetStatus("Operation cancelled")
        End If
    End Sub

    Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
        Dim blnError As Boolean = False
        Dim strErrorMessage As String = ""
        Dim strErrorCaption As String = "Connection attempt failed"

        StartWaitUI()
        Try
            Dim cn As DbConnection = _ProviderFactory.CreateConnection()
            cn.ConnectionString = txtConnectionString.Text
            cn.Open()
            lstConnections.SelectedIndex = lstConnections.Items.Add(cn)
        Catch ex As Exception
            blnError = True
            strErrorMessage = ex.Message
        End Try
        EndWaitUI()

        If blnError Then
            SetStatus(strErrorCaption)
            MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            SetStatus("Connection opened succesfully")
        End If
    End Sub

    Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click
        StartWaitUI()
        Try
            _SelectedConnection.Open()
            EnableOrDisableButtons(_SelectedConnection)
            SetStatus("Connection opened succesfully")
            EndWaitUI()
        Catch ex As Exception
            EndWaitUI()
            Dim strErrorCaption As String = "Connection attempt failed"
            SetStatus(strErrorCaption)
            MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try
    End Sub

    Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click
        Dim queryDialog As New QueryDialog()
        If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
            Me.Cursor = Cursors.WaitCursor
            DisableAllButtons()
            Try
                Dim cmd As DbCommand = _SelectedConnection.CreateCommand()
                cmd.CommandText = queryDialog.txtQuery.Text
                Using rdr As DbDataReader = cmd.ExecuteReader()
                    If rdr.HasRows Then
                        Dim resultsForm As New QueryResultsForm()
                        resultsForm.ShowResults(cmd.CommandText, rdr)
                        SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))
                    Else
                        SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))
                    End If
                    Me.Cursor = Cursors.Default
                    EnableOrDisableButtons(_SelectedConnection)
                End Using
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                EnableOrDisableButtons(_SelectedConnection)
                Dim strErrorCaption As String = "Query attempt failed"
                SetStatus(strErrorCaption)
                MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        Else
            SetStatus("Operation cancelled")
        End If
    End Sub

    Sub btnClose_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClose.Click
        _SelectedConnection.Close()
        SetStatus("Connection closed")
        EnableOrDisableButtons(_SelectedConnection)
    End Sub

    Sub btnRemove_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnRemove.Click
        _SelectedConnection.Close()
        _SelectedConnection.Dispose()
        lstConnections.Items.RemoveAt(lstConnections.SelectedIndex)
        lstConnections.SelectedIndex = lstConnections.Items.Count - 1
        SetStatus("Connection removed")
    End Sub

    Sub btnClearPool_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClearPool.Click
        StartWaitUI()
        Dim cn As SqlConnection = TryCast(_SelectedConnection, SqlConnection)
        If Not cn Is Nothing Then
            SqlConnection.ClearPool(cn)
            SetStatus("Pool cleared")
            EndWaitUI()
        Else
            SetStatus("Expecting SqlConnections")
            EndWaitUI()
        End If
    End Sub

    Sub btnClearAllPools_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClearAllPools.Click
        StartWaitUI()
        SqlConnection.ClearAllPools()
        RefreshPerfCounters()
        SetStatus("All SqlConnection pools cleared")
        EndWaitUI()
    End Sub

    Dim perfActiveConnections As PerformanceCounter
    Dim perfActiveConnectionPools As PerformanceCounter
    Dim perfActiveConnectionPoolGroups As PerformanceCounter
    Dim perfFreeConnections As PerformanceCounter
    Dim perfInactiveConnectionPools As PerformanceCounter
    Dim perfInactiveConnectionPoolGroups As PerformanceCounter
    Dim perfNonPooledConnections As PerformanceCounter
    Dim perfPooledConnections As PerformanceCounter

    Sub InitializePerfCounters()
        Dim strInstanceName As String = GetInstanceName()
        Dim strCategoryName As String = ".NET Data Provider for SqlServer"
        perfActiveConnections = New PerformanceCounter(strCategoryName, "NumberOfActiveConnections", strInstanceName, True)
        perfActiveConnectionPools = New PerformanceCounter(strCategoryName, "NumberOfActiveConnectionPools", strInstanceName, True)
        perfActiveConnectionPoolGroups = New PerformanceCounter(strCategoryName, "NumberOfActiveConnectionPoolGroups", strInstanceName, True)
        perfFreeConnections = New PerformanceCounter(strCategoryName, "NumberOfFreeConnections", strInstanceName, True)
        perfInactiveConnectionPools = New PerformanceCounter(strCategoryName, "NumberOfInactiveConnectionPools", strInstanceName, True)
        perfInactiveConnectionPoolGroups = New PerformanceCounter(strCategoryName, "NumberOfInactiveConnectionPoolGroups", strInstanceName, True)
        perfNonPooledConnections = New PerformanceCounter(strCategoryName, "NumberOfNonPooledConnections", strInstanceName, True)
        perfPooledConnections = New PerformanceCounter(strCategoryName, "NumberOfPooledConnections", strInstanceName, True)
        RefreshPerfCounters()
    End Sub

    Sub btnPerfCounters_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnPerfRefresh.Click
        'Calling SetStatus implicitly refreshes perf counters
        SetStatus("Performance counters refreshed")
    End Sub

    Sub RefreshPerfCounters()
        'Connections
        txtPerfConnectionsPooled.Text = perfPooledConnections.NextValue().ToString()
        txtPerfConnectionsActive.Text = perfActiveConnections.NextValue().ToString()
        txtPerfConnectionsNonPooled.Text = perfNonPooledConnections.NextValue().ToString()
        txtPerfConnectionsFree.Text = perfFreeConnections.NextValue().ToString()

        'Connection Pools
        txtPerfConnectionPoolsActive.Text = perfActiveConnectionPools.NextValue().ToString()
        txtPerfConnectionPoolsInActive.Text = perfInactiveConnectionPools.NextValue().ToString()

        'Connection Pool Groups
        txtConnectionPoolGroupsActive.Text = perfActiveConnectionPoolGroups.NextValue().ToString()
        txtlblConnectionPoolGroupsInActive.Text = perfInactiveConnectionPoolGroups.NextValue().ToString()
    End Sub

    Private Declare Function GetCurrentProcessId Lib "kernel32.dll" () As Integer

    Private Function GetInstanceName() As String
        'This works for Winforms apps. 
        Dim instanceName As String = _
           System.Reflection.Assembly.GetEntryAssembly.GetName.Name

        ' Must replace special characters like (, ), #, /, \\ 
        Dim instanceName2 As String = _
           AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
           .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")

        'For ASP.NET applications your instanceName will be your CurrentDomain's 
        'FriendlyName. Replace the line above that sets the instanceName with this: 
        'instanceName = AppDomain.CurrentDomain.FriendlyName.ToString.Replace("(", "[") _
        '    .Replace(")", "]").Replace("#", "_").Replace("/", "_").Replace("\\", "_")

        Dim pid As String = GetCurrentProcessId.ToString
        instanceName = (instanceName + ("[" & (pid & "]")))
        Console.WriteLine("Instance Name: {0}", instanceName)
        Console.WriteLine("---------------------------")
        Return instanceName
    End Function
End Class