CODEWITHSUNDEEP
c#.netvb.netwinformsvisual-studio-2008
Farhan Mukadam
Farhan Mukadam

Reputation: 470

Raise event upon population of DataTable

I have a Form in which I have to populate DataGrid. The source for this DataGrid comes from another class.

I need to populate the DataGrid through Raising Event in that class in which i'm getting Datatable.

Imports System.IO

Public Class ExcelReader

    Private WithEvents tmrRead As New Timer

    Dim fullpath As String = ""

    Public Sub ExcelReader()
        tmrRead.Interval = 2000
        tmrRead.Start()
    End Sub


    Public Sub TimerTick(ByVal sender As Object, ByVal e As System.EventArgs) Handles tmrRead.Tick
        Dim DT As New DataTable

        Dim path As String = Directory.GetParent(Directory.GetParent(Directory.GetCurrentDirectory).ToString).ToString + "\ExcelHotReader\"

    Dim file1 As String() = System.IO.Directory.GetFiles(path, "*.xls")
    Dim file2 As String() = System.IO.Directory.GetFiles(path, "*.xlsx")
    If file1.Count <> 0 Or file2.Count <> 0 Then
        tmrRead.Stop()
    End If

        If file1.Count <> 0 Then
            fullpath = file1(0).ToString
        End If

        If file2.Count <> 0 Then
            fullpath = file2(0).ToString
        End If

        Dim DT As New DataTable
        Dim cn As System.Data.OleDb.OleDbConnection
        Dim cmd As System.Data.OleDb.OleDbDataAdapter
        cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;" & "data source=" & fullpath & ";Extended Properties=Excel 8.0;")

        cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)
        cn.Open()
        cmd.Fill(DT)
        cn.Close()

    End Sub
End Class

After cn.Close() the event should be raised with Datatable. This event need to be captured by the form to populate DataGrid.

Upvotes: 1

Views: 289

Answers (1)

Steve
Steve

Reputation: 216323

Add the declaration of the Event inside the ExcelReader class and call it at the end of your code using the RaiseEvent

Public Class ExcelReader
    Public Event DataTableLoaded(ByVal dt As DataTable)
    .......

        Using cn = New System.Data.OleDb.OleDbConnection("provider=Microsoft.ACE.12.0;.......")
        Using cmd = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", cn)
            cn.Open()
            cmd.Fill(DT)
            RaiseEvent DataTableLoaded(DT)
        End Using
        End Using
    ....
End Class

Then the calling code declares (globally) an instance of the ExcelReader class with the keyword WithEvents

Public Dim WithEvents readerTableFromExcel = New ExcelReader()

Finally the declaration of the methods that handle the event (the event sink)

Public Sub DataTableFromExcel(ByVal dt As System.Data.DataTable) _
           Handles readerTableFromExcel.DataTableLoaded
    MsgBox("Table loaded")
End Sub

As a final note, I would use a different method to start the reading of the Excel file (for example a BackgroundWorker class seems more appropriate than a TimerTick event)

If you need to deepen your knowledge of events, this article seems a good summary of what is important to know

Upvotes: 2

Related Questions