Can Ado detect changes in a database table for automtic refresh ?

[nelson.enlis]

Hi,

I made a class for access (read and write) to SQL database Tables.

Each time, that mi class execute a command, this call to private method for "Refresh" a Internal DataTable that, this class have for access to info from the instance.

The question is: had the .net any way for detect that table in SQL change (another external access to DB is change the data) and .net catch this change for refresh the dataTable object ?

again,

DataTable Object is refreshed only after a command excecute inside of my class, but if the db table (SQL) change for external commands (not for my class), my dataTable is not refreshed.

So, Can I detect (in ADO.net) that db SQL table change, then with this event previously detected, refresh de data table?

Here part of my class:

Imports System.Data
Imports System.Data.SqlClient

Public Class SqlMethods

    Private con As New SqlConnection()
    Private adp As New SqlDataAdapter()
    Private cmd As New SqlCommand()
    Private dt As New DataTable()

    Private conStr As String = "Data Source=" + My.Computer.Name.ToString + _
        "\SQLEXPRESS;Initial Catalog=database;User Id=user;Password=pass;"

    Private adpStr As String
    Private cmdStr As String
    Private DefQuery As String


    Public Sub New(ByVal NomTabla As String, ByVal TieneAutoIndex As Boolean, Optional ByVal AutoRefresh As Boolean = False)
        If TieneAutoIndex Then dif = 1
        con.ConnectionString = conStr
        cmd.Connection = con
        dt.TableName = NomTabla
        DefQuery = "SELECT * FROM " + dt.TableName
        Me.load(DefQuery)
    End Sub


    Public ReadOnly Property CopyDataTable() As DataTable
        Get
            Return dt
        End Get
    End Property


    Function insert(ByVal valores() As String) As Integer
        Dim i As Integer
        cmdStr = "INSERT INTO " + dt.TableName + " VALUES ("

        If valores.Length <> dt.Columns.Count - dif Then
            Return 0
        End If
        For i = 0 To dt.Columns.Count - 1
            Select Case dt.Columns(i + dif).DataType.ToString
                Case "System.Int16"
                    cmdStr += valores(i)
                Case "System.Byte"
                    cmdStr += valores(i)
                Case "System.Single"
                    cmdStr += valores(i)
                Case "System.Int32"
                    cmdStr += valores(i)
                Case "System.String"
                    cmdStr += "'" + valores(i) + "'"
                Case "System.Boolean"
                    cmdStr += valores(i)
                Case "System.DateTime"
                    cmdStr += "'" + valores(i) + "'"
            End Select
            If dif <> 0 And i = dt.Columns.Count - 2 Then
                cmdStr += ")"
                Exit For
            ElseIf dif = 0 And i = dt.Columns.Count - 1 Then
                cmdStr += ")"
                Exit For
            Else
                cmdStr += ", "
            End If
        Next i
        SendCommand()
        refresh()
    End Function


    Public Function update(ByVal CampoBusqueda As String, ByVal cadenaBusqueda As String, ByVal cadenaCambio As String, _
                              Optional ByVal CampoCambiar As String = Nothing) As Integer

        If CampoCambiar = Nothing Then CampoCambiar = CampoBusqueda
        cmdStr = "UPDATE " + dt.TableName + " SET " + CampoCambiar + " = " + cadenaCambio + _
        " WHERE " + CampoBusqueda + " LIKE '" + cadenaBusqueda + "'"
        SendCommand()
        refresh()
    End Function


    Private Sub load(ByVal cadena As String)

        cmd.CommandText = cadena
        adp.SelectCommand = cmd

        Try
            con.Open()
            adp.FillSchema(dt, SchemaType.Source)
            adp.Fill(dt)
            con.Close()
        Catch ex As Exception
        Finally
            con.Close()
        End Try

    End Sub


    Public Sub refresh()

        cmd.CommandText = DefQuery

        Try
            con.Open()
            dt.Clear()
            adp.FillSchema(dt, SchemaType.Source)
            adp.Fill(dt)

            con.Close()
        Catch ex As Exception
        Finally
            con.Close()
        End Try

    End Sub


    Private Sub SendCommand()
        cmd.CommandText = cmdStr
        Try
            con.Open()
            cmd.ExecuteReader()
            con.Close()
        Catch ex As Exception
        Finally
            con.Close()
        End Try
    End Sub

End Class

If you see, each command call to refresh the datatable. But if the SQL Table is changed for external reasons, my datatable (dt) can't refreshed.

So, for last time, Can detect changes in Sql table, and with this event refresh my datatable ?

Please, any support is welcome.

Best Regards

 

[nelson.enlis]

Another sub-question inside the primary question.

Wich its the best way to refresh data from sql to a datatable (through dataadapter) ?

here my form (actually the best to the moment):

    Public Sub refresh()

        cmd.CommandText = DefQuery

        Try
            con.Open()
            dt.Clear()
            adp.FillSchema(dt, SchemaType.Source)
            adp.Fill(dt)

            con.Close()
        Catch ex As Exception
        Finally
            con.Close()
        End Try

    End Sub

Best Regards

 

[cjard]

So, Can I detect (in ADO.net) that db SQL table change, then with this event previously detected, refresh de data table?

So, for last time, Can detect changes in Sql table, and with this event refresh my datatable ?

No; DataSets do not become aware of changes to database schema at all during runtime, and only become aware of data changes upon refilling.

You may want to look into SQLServer Event Notification Services (i think that's what it's called) where SQLS will emit events when data changes