在 SQL Server 数据库中查找元素

• 下载源代码 - 110.42 KB

引言

我正在开发这个简单的应用程序，它可以在 SQL Server 数据库的所有表和视图中搜索元素，只需配置连接字符串到连接 textbox 字段即可。我配置此应用程序仅使用 SqlClient 提供程序，但在后续版本中希望使用一些通用的提供程序或 Oracle 提供程序。如果您想自行升级此代码，如果您能考虑将您的贡献发给我以升级原始代码，我将不胜感激。

在这个应用程序中，我使用了两个后台工作线程来执行递归查询中的艰巨任务：BackgroundWorker、BackgroundWorkerSQL。

BackgroundWorker 事件	BackgroundWorkerSQL 事件
BackgroundWorker_DoWork	BackgroundWorkerSQL_DoWork
BackgroundWorker_ProgressChanged	BackgroundWorkerSQL_ProgressChanged
BackgroundWorker_RunWorkerCompleted	BackgroundWorkerSQL_RunWorkerCompleted

要使用窗体控件，需要创建一些委托来调用控件。

委托	Procedure
EditaTextoCallback	EditaTexto
HabilitaBusquedaCallback	HabilitaBusqueda
ValorProgressBarCallback	ValorProgressBar
ItemCallback	项目
AsignaDTCallback	AsignaDT

要激活后台工作线程中的异步进程，请使用 RunWorkerAsync() 方法。如果想取消该进程，则使用 CancelAsync() 方法。当您停止该进程时，它不会立即停止，您需要等待进程完成，然后才能再次执行该进程。

代码示例

Imports System.Data.SqlClient

Public Class Main

    Delegate Sub EditaTextoCallback(ByVal Objeto As Object, _
	ByVal Concatena As Boolean, ByVal Texto As String)
    Delegate Sub HabilitaBusquedaCallback(ByVal boton As Button, _
	ByVal Habilita As Boolean, ByVal Texto As String)
    Delegate Sub ValorProgressBarCallback(ByVal Barra As ProgressBar, _
	ByVal Valor As Integer)
    Delegate Sub ItemCallback(ByVal Limpia As Boolean, ByVal Texto As String)
    Delegate Sub AsignaDTCallback(ByVal dt As DataTable)

    Private Sub EditaTexto(ByVal Objeto As Object, ByVal Concatena As Boolean, _
	ByVal Texto As String)
        If Objeto.InvokeRequired Then
            Dim d As New EditaTextoCallback(AddressOf EditaTexto)
            Me.Invoke(d, New Object() {Objeto, Concatena, Texto})
        Else
            Select Case Concatena
                Case True
                    Objeto.Text += Texto
                Case False
                    Objeto.Text = Texto
            End Select
        End If
    End Sub

    Private Sub HabilitaBusqueda(ByVal boton As Button, _
	ByVal Habilita As Boolean, ByVal Texto As String)
        If boton.InvokeRequired Then
            Dim d As New HabilitaBusquedaCallback(AddressOf HabilitaBusqueda)
            Me.Invoke(d, New Object() {boton, Habilita, Texto})
        Else
            boton.Enabled = Habilita
            boton.Text = Texto
        End If
    End Sub

    Private Sub ValorProgressBar(ByVal Barra As ProgressBar, ByVal Valor As Integer)
        If Barra.InvokeRequired Then
            Dim d As New ValorProgressBarCallback(AddressOf ValorProgressBar)
            Me.Invoke(d, New Object() {Barra, Valor})
        Else
            Barra.Value = Valor
        End If
    End Sub

    Private Sub Item(ByVal Limpia As Boolean, ByVal Texto As String)
        If chkLista.InvokeRequired Then
            Dim d As New ItemCallback(AddressOf Item)
            Me.Invoke(d, New Object() {Limpia, Texto})
        Else
            Select Case Limpia
                Case True
                    chkLista.Items.Clear()
                Case False
                    chkLista.Items.Add(Texto)
            End Select
        End If
    End Sub

    Private Sub AsignaDT(ByVal dt As DataTable)
        If dgv.InvokeRequired Then
            Dim d As New AsignaDTCallback(AddressOf AsignaDT)
            Me.Invoke(d, New Object() {dt})
        Else
            dgv.DataSource = dt
            dgv.Refresh()
        End If
    End Sub

    Private Sub btnBuscar_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles btnBuscar.Click
        BackgroundWorker.RunWorkerAsync()
    End Sub

    Private Sub BackgroundWorker_ProgressChanged(ByVal sender As Object, _
	ByVal e As System.ComponentModel.ProgressChangedEventArgs)
        Handles BackgroundWorker.ProgressChanged

        ValorProgressBar(ProgressBar, e.ProgressPercentage)
        EditaTexto(valPercent, False, e.ProgressPercentage & "%")
    End Sub

    Private Sub BackgroundWorker_RunWorkerCompleted(ByVal sender As Object, _
	ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs)
        Handles BackgroundWorker.RunWorkerCompleted

        EditaTexto(txtResultado, True, "Operación Terminada.......")
        HabilitaBusqueda(btnBuscar, True, "Buscar")
        HabilitaBusqueda(btnCancelar, False, "Cancelar")
        'ValorProgressBar(0)
    End Sub

    Private Sub BackgroundWorker_DoWork(ByVal sender As Object, _
	ByVal e As System.ComponentModel.DoWorkEventArgs)
        Handles BackgroundWorker.DoWork

        RecorreTablas()
    End Sub

    Private Sub btnCancelar_Click(ByVal sender As System.Object, _
	ByVal e As System.EventArgs) Handles btnCancelar.Click
        If BackgroundWorker.IsBusy Then
            HabilitaBusqueda(btnCancelar, False, "Cancelar")
            BackgroundWorker.CancelAsync()
            EditaTexto(txtResultado, True, "Operación Cancelada.......")
            EditaTexto(valPercent, False, "")
            ValorProgressBar(ProgressBar, 0)
        End If
    End Sub

    Private Sub RecorreTablas()
        EditaTexto(txtResultado, False, "Ejecutando...")
        EditaTexto(valPercent, False, "")
        HabilitaBusqueda(btnBuscar, False, "Trabajando...")
        HabilitaBusqueda(btnCancelar, True, "Cancelar")
        Item(True, "")
        ValorProgressBar(ProgressBar, 0)
        Dim qry As String
        'Query de extracción de las tablas de la base de datos
        qry = " sp_tables @table_type = " & Chr(34) & Chr(39) & _
	"TABLE" & Chr(39) & "," & Chr(39) & "VIEW" & Chr(39) & Chr(34)
        'qry += " WAITFOR DELAY '0:0:10'; "
        Dim da As New Data.SqlClient.SqlDataAdapter(qry, txtConnectionString.Text)
        Dim dtTablas As New Data.DataTable("Tablas")
        Try
            'Lleno el datatable dtTablas con las Tablas de la Base de datos
            da.Fill(dtTablas)
            Dim irow As Data.DataRow = Nothing
            Dim i As Integer = 0
            For Each irow In dtTablas.Rows
                If BackgroundWorker.CancellationPending Then
                    Exit Sub
                Else
                    BackgroundWorker.ReportProgress(i / (dtTablas.Rows.Count / 100))
                    BuscaEnTabla(irow.Item(2).ToString())
                    'BuscaEnTabla("catalogo_ciudad")
                End If
                i = i + 1
            Next
            BackgroundWorker.ReportProgress(dtTablas.Rows.Count / _
		(dtTablas.Rows.Count / 100))
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Private Sub BuscaEnTabla(ByVal Tabla As String)
        Dim qry As String
        'Query de extracción de las columnas de la tabla
        qry = " sp_columns " & Tabla
        Dim da As New Data.SqlClient.SqlDataAdapter(qry, txtConnectionString.Text)
        Dim dtColumnas As New Data.DataTable("Columnas")
        Try
            'Lleno el datatable dtColumnas con las columnas de la Tabla
            da.Fill(dtColumnas)
            Dim irow As Data.DataRow = Nothing
            For Each irow In dtColumnas.Rows
                Try
                    qry = " SELECT * FROM " & Tabla & " WHERE " & _
			irow.Item(3) & " LIKE '%" & txtBusqueda.Text & "%' "
                    Dim dr As SqlDataReader
                    Dim cmd As New SqlCommand(qry)
                    Dim conn As New SqlConnection(txtConnectionString.Text)
                    cmd.Connection = conn
                    cmd.Connection.Open()
                    dr = cmd.ExecuteReader()
                    If dr.HasRows = True Then
                        'EditaTexto(txtResultado, True, irow.Item(0).ToString() & _
			"." & irow.Item(1).ToString() & 
                        ' "." & irow.Item(2).ToString() & "." & _
			irow.Item(3).ToString() & vbCrLf)
                        Item(False, irow.Item(0).ToString() & "." & _
			irow.Item(1).ToString() & "." & irow.Item(2).ToString() &
                             "." & irow.Item(3).ToString())
                    End If
                    cmd.Connection.Close()
                    cmd = Nothing
                    dr = Nothing
                Catch ex As Exception
                    Exit Try
                End Try
            Next
        Catch ex As Exception
            Exit Sub
        End Try
    End Sub

    Private Sub btnQuery_Click(ByVal sender As System.Object, _
		ByVal e As System.EventArgs) Handles btnQuery.Click
        BackgroundWorkerSQL.RunWorkerAsync()
    End Sub

    Private Sub chkLista_SelectedIndexChanged(ByVal sender As System.Object, _
	ByVal e As System.EventArgs)
        Handles chkLista.SelectedIndexChanged

        Dim Cadena As String = chkLista.SelectedItem
        Dim arrCadena As String()
        arrCadena = Cadena.Split(".")
        txtquery.Text = "SELECT * FROM " & arrCadena(0) & "." & _
		arrCadena(1) & "." & arrCadena(2) &
            " WHERE " & arrCadena(3) & " LIKE '%" & txtBusqueda.Text & "%' "
    End Sub

    Private Sub BackgroundWorkerSQL_DoWork(ByVal sender As System.Object, _
		ByVal e As System.ComponentModel.DoWorkEventArgs)
        Handles BackgroundWorkerSQL.DoWork

        HabilitaBusqueda(btnQuery, False, "Ejecutar")
        Dim qry As String
        Try
            qry = txtquery.Text
            Dim conn As New SqlConnection(txtConnectionString.Text)
            Dim da As New SqlClient.SqlDataAdapter(qry, conn)
            Dim dt As New Data.DataTable
            da.Fill(dt)
            AsignaDT(dt)
            For i As Integer = 0 To 1000
                BackgroundWorkerSQL.ReportProgress(i / 10)
            Next
            da = Nothing
        Catch ex As Exception
            Exit Try
        End Try
    End Sub

    Private Sub BackgroundWorkerSQL_ProgressChanged(ByVal sender As Object, _
		ByVal e As System.ComponentModel.ProgressChangedEventArgs)
        Handles BackgroundWorkerSQL.ProgressChanged

        ValorProgressBar(ProgressBarSQL, e.ProgressPercentage)
    End Sub

    Private Sub BackgroundWorkerSQL_RunWorkerCompleted(ByVal sender As Object, _
        ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) _
	Handles BackgroundWorkerSQL.RunWorkerCompleted

        HabilitaBusqueda(btnQuery, True, "Ejecutar")
    End Sub

End Class

历史

• 2011 年 5 月 2 日：初始发布