Datagrid real time update
I have an Excel workbook that receives real time data (updates every second) via a DDE link. I am trying to import this data into a vb.net datagrid. I can do that ok, except that my datagrid is static and the only way I can update it is to reload the workbook and refresh the datagrid. Since my worbook contains 1600 real time cells, it takes too long... maybe 5-10 seconds to read the data. Can someone give me some ideas on how to achieve a real time update in my datagrid as well?
This is my present code; I am refreshing it with a timer control:
Code:
Private Sub OpenDataGrid()
Dim dt As New DataTable
Dim dc As New DataColumn
Dim dr As DataRow
' Set up column headers
dt.Columns.Clear()
dt.Columns.Add("Symbol", Type.GetType("System.String"))
dt.Columns.Add("Date", Type.GetType("System.String"))
dt.Columns.Add("Time", Type.GetType("System.String"))
dt.Columns.Add("PrevClose", Type.GetType("System.String"))
dt.Columns.Add("Open", Type.GetType("System.String"))
dt.Columns.Add("High", Type.GetType("System.String"))
dt.Columns.Add("Low", Type.GetType("System.String"))
dt.Columns.Add("Last", Type.GetType("System.String"))
dt.Columns.Add("OI", Type.GetType("System.String"))
dt.Columns.Add("Change", Type.GetType("System.String"))
dt.Columns.Add("TradeVol", Type.GetType("System.String"))
dt.Columns.Add("DailyVol", Type.GetType("System.String"))
dt.Columns.Add("BidSize", Type.GetType("System.String"))
dt.Columns.Add("Bid", Type.GetType("System.String"))
dt.Columns.Add("Ask", Type.GetType("System.String"))
dt.Columns.Add("AskSize", Type.GetType("System.String"))
' Add Excel data to datatable row
Dim RowIndex As Integer = 0
Dim ColIndex As Integer = 0
For RowIndex = 2 To 101 ' In the future, read upper boundary
dr = dt.NewRow
ColIndex += 1
dr("Symbol") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Date") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Time") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("PrevClose") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Open") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("High") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Low") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Last") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("OI") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Change") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("TradeVol") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("DailyVol") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("BidSize") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Bid") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("Ask") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex += 1
dr("AskSize") = CStr(CType(xlWS.Cells(RowIndex, ColIndex), Excel.Range).Value)
ColIndex = 0
' Add row to datatable
dt.Rows.Add(dr)
' Bind datatable to the datagrid
dg.DataSource = dt
Next RowIndex
' Upon exit, release controls
dt = Nothing
dc = Nothing
dr = Nothing
End Sub
Re: Datagrid real time update
Ok, I've changed to trying to do this with ADO.NET as a connected database. It loads much quicker, but I still can't figure out how to get the updates automatically. Somehow, I will need the reader to remain open or be triggered on change... something. Any ideas?
Code:
Dim conStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\DDE1.xls;" & "Extended Properties=""Excel 8.0;HDR=YES"""
Dim conn As New OleDbConnection(conStr)
conn.Open()
' Create query command
Dim sqlStr As New OleDbCommand("Select * From [Sheet1$]", conn)
' Open reader
Dim Reader As OleDbDataReader = sqlStr.ExecuteReader()
' Define data table
Dim dt As New DataTable
Dim dc As New DataColumn
Dim dr As DataRow
' Label column headers
' Count Columns
Dim ColCount As Integer = Reader.FieldCount
' Clear existing column headers
dt.Columns.Clear()
Dim Counter As Integer
' Add new column headers to datagrid
For Counter = 0 To ColCount - 1
dt.Columns.Add(Reader.GetName(Counter))
Next
' Assign column index
' Note: these names must match column headers in WB exactly
Dim idxSymbol As Integer = Reader.GetOrdinal("Symbol")
Dim idxLastDate As Integer = Reader.GetOrdinal("Date")
Dim idxLastTime As Integer = Reader.GetOrdinal("Time")
Dim idxPrevClose As Integer = Reader.GetOrdinal("PrevClose")
Dim idxOpen As Integer = Reader.GetOrdinal("Open")
Dim idxHigh As Integer = Reader.GetOrdinal("High")
Dim idxLow As Integer = Reader.GetOrdinal("Low")
Dim idxLast As Integer = Reader.GetOrdinal("Last")
Dim idxOI As Integer = Reader.GetOrdinal("OI")
Dim idxChange As Integer = Reader.GetOrdinal("Change")
Dim idxTradeVol As Integer = Reader.GetOrdinal("TradeVol")
Dim idxDailyVol As Integer = Reader.GetOrdinal("DailyVol")
Dim idxBidSize As Integer = Reader.GetOrdinal("BidSize")
Dim idxBid As Integer = Reader.GetOrdinal("Bid")
Dim idxAsk As Integer = Reader.GetOrdinal("Ask")
Dim idxAskSize As Integer = Reader.GetOrdinal("AskSize")
'Do While ExitFlag = False
' Populate datagrid
While Reader.Read
dr = dt.NewRow
dr(idxSymbol) = Reader.GetString(idxSymbol)
dr(idxLastDate) = Format(Reader.GetDateTime(idxLastDate), "dd-MMM-yyyy")
dr(idxLastTime) = Format(Reader.GetDateTime(idxLastTime), "H:mm:ss")
dr(idxPrevClose) = Format(Reader.GetValue(idxPrevClose), "n2")
dr(idxOpen) = Format(Reader.GetValue(idxOpen), "n2")
dr(idxHigh) = Format(Reader.GetValue(idxHigh), "n2")
dr(idxLow) = Format(Reader.GetValue(idxLow), "n2")
dr(idxLast) = Format(Reader.GetValue(idxLast), "n2")
dr(idxOI) = Format(Reader.GetValue(idxOI), "n0")
dr(idxChange) = Format(Reader.GetValue(idxChange), "n2")
dr(idxTradeVol) = Format(Reader.GetValue(idxTradeVol), "n0")
dr(idxDailyVol) = Format(Reader.GetValue(idxDailyVol), "n0")
dr(idxBidSize) = Format(Reader.GetValue(idxBidSize), "n0")
dr(idxBid) = Format(Reader.GetValue(idxBid), "n2")
dr(idxAsk) = Format(Reader.GetValue(idxAsk), "n2")
dr(idxAskSize) = Format(Reader.GetValue(idxAskSize), "n0")
' Add data to table
dt.Rows.Add(dr)
' Bind datatable to the datagrid
dg.DataSource = dt
End While
'Loop
' Upon exit, release controls
dt = Nothing
dc = Nothing
dr = Nothing
' Close connection
conn.Close()
End Sub
