This is NOT another "I don't understand you!" or "Refer to Million Rows sample" type of post. This is real code and fetches millions of data from a SQL Server and binds to UltraDataSource :)
P.S. If anyone has a better way of doing this, please share it...
Although the following was said to be required, one can easily change the code and requirements.
A SQL Server (or any other database assuming you know how to fetch rows) database called "AuditDB"
A table called "Events" with the following columns:
ID (biging - auto increment)
EventID (int)
Source (varchar(50))
TimeGenerated (Date)
ReplacementStrings (varchar(MAX)) - fulltext enabled
A non-clustered index on TimeGenerated (optional)
Imports Infragistics.Shared
Imports Infragistics.Win
Imports Infragistics.Win.UltraWinDataSource
Imports Infragistics.Win.UltraWinGrid
Imports System.Collections.Generic
Imports System.Data.SqlClient
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
MyBase.Dispose(disposing)
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents ultraGrid1 As Infragistics.Win.UltraWinGrid.UltraGrid
Friend WithEvents ultraDataSource1 As Infragistics.Win.UltraWinDataSource.UltraDataSource
Friend WithEvents bgw As System.ComponentModel.BackgroundWorker
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents Button2 As System.Windows.Forms.Button
Friend WithEvents dtFrom As System.Windows.Forms.DateTimePicker
Friend WithEvents dtTo As System.Windows.Forms.DateTimePicker
Friend WithEvents Label1 As System.Windows.Forms.Label
Friend WithEvents Label2 As System.Windows.Forms.Label
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.components = New System.ComponentModel.Container()
Me.ultraGrid1 = New Infragistics.Win.UltraWinGrid.UltraGrid()
Me.ultraDataSource1 = New Infragistics.Win.UltraWinDataSource.UltraDataSource(Me.components)
Me.bgw = New System.ComponentModel.BackgroundWorker()
Me.Button1 = New System.Windows.Forms.Button()
Me.Button2 = New System.Windows.Forms.Button()
Me.TextBox1 = New System.Windows.Forms.TextBox()
Me.dtFrom = New System.Windows.Forms.DateTimePicker()
Me.dtTo = New System.Windows.Forms.DateTimePicker()
Me.Label1 = New System.Windows.Forms.Label()
Me.Label2 = New System.Windows.Forms.Label()
CType(Me.ultraGrid1, System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.ultraDataSource1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'ultraGrid1
Me.ultraGrid1.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
Or System.Windows.Forms.AnchorStyles.Left) _
Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
Me.ultraGrid1.Cursor = System.Windows.Forms.Cursors.Hand
Me.ultraGrid1.DataSource = Me.ultraDataSource1
Me.ultraGrid1.DisplayLayout.Override.AllowAddNew = Infragistics.Win.UltraWinGrid.AllowAddNew.No
Me.ultraGrid1.DisplayLayout.Override.AllowDelete = Infragistics.Win.DefaultableBoolean.[False]
Me.ultraGrid1.DisplayLayout.Override.AllowUpdate = Infragistics.Win.DefaultableBoolean.[False]
Me.ultraGrid1.DisplayLayout.Override.CellClickAction = Infragistics.Win.UltraWinGrid.CellClickAction.RowSelect
Me.ultraGrid1.DisplayLayout.Override.ColumnAutoSizeMode = Infragistics.Win.UltraWinGrid.ColumnAutoSizeMode.AllRowsInBand
Me.ultraGrid1.DisplayLayout.Override.HeaderClickAction = Infragistics.Win.UltraWinGrid.HeaderClickAction.SortMulti
Me.ultraGrid1.DisplayLayout.Override.SelectTypeCell = Infragistics.Win.UltraWinGrid.SelectType.None
Me.ultraGrid1.DisplayLayout.Override.SelectTypeCol = Infragistics.Win.UltraWinGrid.SelectType.None
Me.ultraGrid1.DisplayLayout.Override.SelectTypeRow = Infragistics.Win.UltraWinGrid.SelectType.[Single]
Me.ultraGrid1.DisplayLayout.ScrollBounds = Infragistics.Win.UltraWinGrid.ScrollBounds.ScrollToFill
Me.ultraGrid1.DisplayLayout.ScrollStyle = Infragistics.Win.UltraWinGrid.ScrollStyle.Immediate
Me.ultraGrid1.DisplayLayout.TabNavigation = Infragistics.Win.UltraWinGrid.TabNavigation.NextControl
Me.ultraGrid1.DisplayLayout.ViewStyle = Infragistics.Win.UltraWinGrid.ViewStyle.SingleBand
Me.ultraGrid1.Location = New System.Drawing.Point(12, 85)
Me.ultraGrid1.Name = "ultraGrid1"
Me.ultraGrid1.Size = New System.Drawing.Size(733, 285)
Me.ultraGrid1.TabIndex = 4
'ultraDataSource1
'bgw
Me.bgw.WorkerReportsProgress = True
Me.bgw.WorkerSupportsCancellation = True
'Button1
Me.Button1.Location = New System.Drawing.Point(541, 11)
Me.Button1.Name = "Button1"
Me.Button1.Size = New System.Drawing.Size(97, 23)
Me.Button1.TabIndex = 2
Me.Button1.Text = "Start"
Me.Button1.UseVisualStyleBackColor = True
'Button2
Me.Button2.Enabled = False
Me.Button2.Location = New System.Drawing.Point(644, 11)
Me.Button2.Name = "Button2"
Me.Button2.Size = New System.Drawing.Size(102, 23)
Me.Button2.TabIndex = 3
Me.Button2.Text = "Cancel"
Me.Button2.UseVisualStyleBackColor = True
'TextBox1
Me.TextBox1.Location = New System.Drawing.Point(12, 14)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(523, 20)
Me.TextBox1.TabIndex = 1
'dtFrom
Me.dtFrom.Location = New System.Drawing.Point(55, 44)
Me.dtFrom.Name = "dtFrom"
Me.dtFrom.Size = New System.Drawing.Size(200, 20)
Me.dtFrom.TabIndex = 5
'dtTo
Me.dtTo.Location = New System.Drawing.Point(330, 44)
Me.dtTo.Name = "dtTo"
Me.dtTo.Size = New System.Drawing.Size(200, 20)
Me.dtTo.TabIndex = 6
'Label1
Me.Label1.AutoSize = True
Me.Label1.Location = New System.Drawing.Point(16, 48)
Me.Label1.Name = "Label1"
Me.Label1.Size = New System.Drawing.Size(33, 13)
Me.Label1.TabIndex = 7
Me.Label1.Text = "From:"
'Label2
Me.Label2.AutoSize = True
Me.Label2.Location = New System.Drawing.Point(301, 48)
Me.Label2.Name = "Label2"
Me.Label2.Size = New System.Drawing.Size(23, 13)
Me.Label2.TabIndex = 8
Me.Label2.Text = "To:"
'Form1
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(757, 382)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.dtTo)
Me.Controls.Add(Me.dtFrom)
Me.Controls.Add(Me.ultraGrid1)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.TextBox1)
Me.Name = "Form1"
Me.Text = "Virtual Mode-Extended"
CType(Me.ultraGrid1, System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.ultraDataSource1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
Me.PerformLayout()
#End Region
Public Class AuditRecord
Public Property ID As Long
Public Property EventID As Integer
Public Property Source As String
Public Property TimeGenerated As DateTime
Public Property ReplacementString As String
Public Sub New(ByVal ID As Long, ByVal EventID As Integer, ByVal Source As String, ByVal TimeGenerated As DateTime, ByVal ReplacementString As String)
Me.ID = ID
Me.EventID = EventID
Me.Source = Source
Me.TimeGenerated = TimeGenerated
Me.ReplacementString = ReplacementString
End Class
Private externalData As New List(Of AuditRecord)
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
Me.dtFrom.Value = Now.AddDays(-1)
Me.dtTo.Value = Today.AddDays(1).Subtract(New TimeSpan(0, 0, 1))
Me.ultraDataSource1.Band.Columns.Add("ID", GetType(Long))
Me.ultraDataSource1.Band.Columns("ID").ReadOnly = DefaultableBoolean.True
Me.ultraDataSource1.Band.Columns.Add("EventID", GetType(Integer))
Me.ultraDataSource1.Band.Columns.Add("Source", GetType(String))
Me.ultraDataSource1.Band.Columns.Add("TimeGenerated", GetType(DateTime))
Me.ultraDataSource1.Band.Columns.Add("ReplacementString", GetType(String))
Me.ultraDataSource1.Rows.SetCount(0)
Me.ultraGrid1.DisplayLayout.LoadStyle = LoadStyle.LoadOnDemand
' Miscellaneous settings on UltraGrid.
Me.ultraGrid1.DisplayLayout.ScrollStyle = ScrollStyle.Immediate
Private Sub UltraDataSource1_InitializeDataRow(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinDataSource.InitializeDataRowEventArgs) Handles ultraDataSource1.InitializeDataRow
' You can use the Tag off the row to store a piece of data that will
' make it easy to identify the row later on in CellDataRequested and
' other events.
e.Row.Tag = DirectCast(Me.externalData(e.Row.Index), AuditRecord)
Private Sub UltraDataSource1_CellDataRequested(ByVal sender As Object, ByVal e As Infragistics.Win.UltraWinDataSource.CellDataRequestedEventArgs) Handles ultraDataSource1.CellDataRequested
' We set the Tag of the row to a record object in InitializeDataRow.
' Here we can use that to get the associated Record object.
Dim r As AuditRecord = DirectCast(e.Row.Tag, AuditRecord)
' Typically since there are more than 1 columns, you can check which column the
' data is being requested for using the Column property off the event args.
Select Case e.Column.Key
Case "ID" : e.Data = r.ID
Case "EventID" : e.Data = r.EventID
Case "Source" : e.Data = r.Source
Case "TimeGenerated" : e.Data = r.TimeGenerated
Case "ReplacementString" : e.Data = r.ReplacementString
End Select
' Set CacheData to false to prevent UltraDataSource from caching the
' data. Default value of CacheData property is true.
e.CacheData = False
Private cmd As SqlCommand
Private con As SqlConnection
Private dr As SqlDataReader
Private Sub bgw_DoWork(ByVal sender As System.Object, ByVal e As System.ComponentModel.DoWorkEventArgs) Handles bgw.DoWork
Try
Me.externalData.Clear()
Dim i As Integer = 0
Using con As SqlConnection = New SqlConnection("data source=CHANGE_ME;initial catalog=auditdb;user id=sa;password=CHANGE_ME")
cmd = New SqlCommand("SELECT * from Events WHERE (TimeGenerated >= @d1 and TimeGenerated <= @d2) AND CONTAINS(*,@p)", con)
cmd.Parameters.AddWithValue("@p", TextBox1.Text)
cmd.Parameters.AddWithValue("@d1", dtFrom.Value)
cmd.Parameters.AddWithValue("@d2", dtTo.Value)
con.Open()
dr = cmd.ExecuteReader
While dr.Read
i += 1
Dim ar As New AuditRecord(dr("ID"), dr("EventID"), dr("Source"), dr("TimeGenerated"), dr("ReplacementStrings"))
bgw.ReportProgress(i, ar)
If bgw.CancellationPending Then
e.Cancel = True
Return
End While
con.Close()
End Using 'con
Catch ex As Exception
MsgBox(ex.Message)
Finally
End Try
Private Sub bgw_ProgressChanged(ByVal sender As System.Object, ByVal e As System.ComponentModel.ProgressChangedEventArgs) Handles bgw.ProgressChanged
Me.Text = e.ProgressPercentage
externalData.Add(DirectCast(e.UserState, AuditRecord))
Me.ultraDataSource1.Rows.SetCount(Me.externalData.Count)
Private Sub bgw_WorkerCompleted(ByVal sender As System.Object, ByVal e As System.ComponentModel.RunWorkerCompletedEventArgs) Handles bgw.RunWorkerCompleted
If e.Cancelled Then
MsgBox("Cancelled")
Button1.Enabled = True
Button2.Enabled = False
ultraGrid1.UseWaitCursor = False
Else
MsgBox("Completed!")
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
If Not bgw.IsBusy Then
Me.Text = "Searching, please wait..."
ultraGrid1.UseWaitCursor = True
bgw.RunWorkerAsync()
Button1.Enabled = False
Button2.Enabled = True
MsgBox("Please wait. BGW is not ready")
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
If cmd IsNot Nothing Then
cmd.Cancel()
bgw.CancelAsync()
Me.Text = "Ready (cancelled)"