Find an Element in a SQL Server Database

• Download source - 110.42 KB

Introduction

I was working on this simple application that searches an element in all tables and views within SQL Server Database only configuring the Connection String in the connection textbox field. I configured this app to use only SqlClient provider, but I would like to use some generic provider or Oracle provider in the next releases. If you want to upgrade this code by yourself, I would appreciate if you could consider mailing me to upgrade the original code published with your contributions.

In this app, I used two Background workers to do the hard tasks in the recursive queries: BackgroundWorker, BackgroundWorkerSQL.

To use the form controls, it is necessary to create some delegates to invoke the controls.

To activate the async process in the background worker, use the RunWorkerAsync() method, if you want to cancel the process, then use CancelAsync() method, when you stop the process, this one doesn’t stop immediately, you need to wait until the process finishes and then you can execute the process again.

Code Example

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

History

• 2^nd May, 2011: Initial post