I have documented almost every lines of our codes (FrmSearchDialog.vb). I hope you get the idea behind the SearchDialog. Here they are:
Imports System.Data
Imports System.Data.SqlServerCe
Public Class FrmSearchDialog
''' <summary>
''' Connection with connection string retrieved from a dictionary.
''' The dictionary values are loaded from a RSA encrypted text file.
''' The connection must be always opened for browsing SqlCeResultSet.
''' </summary>
Protected CconCurrent As New SqlCeConnection(DictConnectionString("SqlCeConnectionString"))
''' <summary>
''' Command used for searching.
''' </summary>
Protected CcmdSearch As New SqlCeCommand("", CconCurrent)
''' <summary>
''' ResultSet for storing select result.
''' </summary>
Protected CrsSearch As SqlCeResultSet
''' <summary>
''' HashTable for storing search result row.
''' </summary>
Protected HashSearchResult As Hashtable = Nothing
''' <summary>
''' String for storing SQL passed by user.
''' </summary>
Protected strSelect As String = ""
''' <summary>
''' String for storing Criteria passed by user.
''' </summary>
Protected strCriteria As String = ""
''' <summary>
''' String for storing strSelect + strCriteriaClause + strCritera + incremental search criteria.
''' </summary>
Protected strSearchSelect As String = ""
''' <summary>
''' List for storing ColumnTypes string for each column.
''' </summary>
Protected strColumnTypes As New List(Of String)
''' <summary>
''' Boolean for storing whether strSelect is a group by SQL.
''' </summary>
Protected boolGroupBy As Boolean = False
''' <summary>
''' String for storing criteria clause where for ordinary select and having for boolGroupBy=true.
''' </summary>
Protected strCriteriaClause As String = " where "
''' <summary>
''' A static function for opening a SearchDialog for the select statement and Criteria passed by user.
''' </summary>
''' <param name="SelectSql">The select statement.</param>
''' <param name="HashSearchResult">The search result row HashTable reference.</param>
''' <param name="DefaultCriteria">The select criteria.</param>
''' <param name="AllowSearch">if set to <c>true</c> allow search.</param>
''' <returns>True if OK is clicked and false if Cancel is clicked.</returns>
Shared Function Execute(ByVal SelectSql As String, ByRef HashSearchResult As Hashtable, Optional ByVal DefaultCriteria As String = "", Optional ByVal AllowSearch As Boolean = True) As Boolean
'Create an instance of FrmSearchDialog because this function is static.
Dim aFrmSearchDialog As New FrmSearchDialog
With aFrmSearchDialog
'Initializing allow search state
.MnuSearch.Enabled = AllowSearch
.CmbColumn.Enabled = AllowSearch
.TxtSearch.Enabled = AllowSearch
'Initializing the select statement and populating the grid.
.Initialize(SelectSql, DefaultCriteria)
'Showing modally and checking for dialog result
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
HashSearchResult = .HashSearchResult
Return True
Else
Return False
End If
End With
End Function
''' <summary>
''' A procedure for initializing the specified select statement and default criteria.
''' </summary>
''' <param name="SelectSql">The select statement.</param>
''' <param name="DefaultCriteria">The default criteria.</param>
Public Sub Initialize(ByVal SelectSql As String, Optional ByVal DefaultCriteria As String = "")
'Initializing the DialogResult to None
DialogResult = Windows.Forms.DialogResult.None
'Storing the SelectSql and DefaultCriteria to data members
Me.strSelect = SelectSql
Me.strCriteria = DefaultCriteria
'Open the Connection if it is not opened yet.
If CconCurrent.State <> ConnectionState.Open Then
CconCurrent.Open()
End If
'There are DefaultCriteria.
If strCriteria.Length > 0 Then
'Initializing the boolGroupBy by searching for group by clause
boolGroupBy = strSelect.ToLower.IndexOf("group by") >= 0
'It is a group by SelectSql
If boolGroupBy Then
'Initializing the strCriteriaClause as having for group by select.
strCriteriaClause = " having "
End If
'Constructing the initial Search SQL.
strSearchSelect = strSelect + strCriteriaClause + strCriteria
Else 'There are no DefaultCriteria
'Constructing the initial Search SQL without critera.
strSearchSelect = strSelect
End If
'Setting the CommandText to strSearchSelect and execute a scrollable ResultSet from it.
CcmdSearch.CommandText = strSearchSelect
CrsSearch = CcmdSearch.ExecuteResultSet(ResultSetOptions.Scrollable)
'There are row(s) returned.
If CrsSearch.Read Then
'Iterating all columns of ResultSet.
For i As Integer = 0 To CrsSearch.FieldCount - 1
'Storing columns names to combo box items.
CmbColumn.Items.Add(CrsSearch.GetName(i))
'Storing columns type names
strColumnTypes.Add(CrsSearch.GetProviderSpecificFieldType(i).ToString)
Next
End If
'Setting the combo box search column to first column.
If CmbColumn.Items.Count > 0 Then
CmbColumn.SelectedIndex = 0
End If
'Binding DataGrid to ResultSet returned.
DgSearch.DataSource = CrsSearch
'There are no incremental search yet, so empty the strSearchSelect.
strSearchSelect = ""
End Sub
''' <summary>
''' A procedure for storing a row of ResultSet in certain position to the HashTable.
''' </summary>
''' <param name="RowPosition">The row position.</param>
Sub ResultSetRowToHashTable(ByVal RowPosition As Integer)
'The row position is valid.
If RowPosition >= 0 Then
'Scrolling the ResultSet to RowPosition.
CrsSearch.ReadAbsolute(RowPosition)
'Creating an instance of HashTable.
HashSearchResult = New Hashtable
'Iterating all columns of ResultSet and storing their values to HashTable.
For i As Integer = 0 To CrsSearch.FieldCount - 1
HashSearchResult.Add(CrsSearch.GetName(i), CrsSearch.GetValue(i))
Next
'Closing and disposing the ResultSet.
If Not CrsSearch.IsClosed Then
CrsSearch.Close()
End If
CrsSearch.Dispose()
Else 'The row position is invalid.
'Triggering a cancel click.
MnuCancel_Click(Nothing, Nothing)
End If
End Sub
''' <summary>
''' Handles the DoubleClick event of the GridSearch control for storing current row to HashTable and closing the dialog.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub GridSearch_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DgSearch.DoubleClick
'The grid current row index is valid.
If DgSearch.CurrentRowIndex >= 0 Then
Try
'Storing ResultSet on current row index to HashTable
ResultSetRowToHashTable(DgSearch.CurrentRowIndex)
'Returning OK dialog result and close the form.
DialogResult = Windows.Forms.DialogResult.OK
Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub
''' <summary>
''' Handles the Click event of the MnuOK control for storing current row to HashTable and closing the dialog.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub MnuOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuOK.Click
'The grid current row index is valid.
If DgSearch.CurrentRowIndex >= 0 Then
DialogResult = Windows.Forms.DialogResult.OK
Try
'Storing ResultSet on current row index to HashTable
ResultSetRowToHashTable(DgSearch.CurrentRowIndex)
'Returning OK dialog result and close the form.
DialogResult = Windows.Forms.DialogResult.OK
Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End If
End Sub
''' <summary>
''' Handles the Click event of the MnuCancel control for closing the dialog.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub MnuCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuCancel.Click
'Closing and disposing the ResultSet.
If Not CrsSearch.IsClosed Then
CrsSearch.Close()
End If
CrsSearch.Dispose()
'Returning Cancel dialog result and close the form.
DialogResult = Windows.Forms.DialogResult.Cancel
Close()
End Sub
''' <summary>
''' Handles the Click event of the MnuSearch control for constructing incremental search.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub MnuSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuSearch.Click
'There is no incremental search on going.
If strSearchSelect.Length = 0 Then
'Constructing initial select and concating with and for adding a new search criterion.
strSearchSelect = strSelect
If strCriteria.Length > 0 Then
strSearchSelect += strCriteriaClause + strCriteria + " and "
Else
strSearchSelect += strCriteriaClause
End If
Else 'There is incremental search on going.
'Concating with and for adding a new search criterion.
strSearchSelect += " and "
End If
'Current search column in combo box's type is a string.
If strColumnTypes(CmbColumn.SelectedIndex).EndsWith("String") Then
strSearchSelect += String.Format("{0} like '%{1}%'", CmbColumn.Text, TxtSearch.Text)
'Current search column in combo box's type is a DateTime.
ElseIf strColumnTypes(CmbColumn.SelectedIndex).EndsWith("DateTime") Then
strSearchSelect += String.Format("convert(nvarchar,{0},3) like '%{1}%'", CmbColumn.Text, TxtSearch.Text)
Else 'The other type of current search column in combo box.
strSearchSelect += String.Format("convert(nvarchar,{0}) like '%{1}%'", CmbColumn.Text, TxtSearch.Text)
End If
'Closing and disposing the ResultSet before retrieving new ResultSet.
If Not CrsSearch.IsClosed Then
CrsSearch.Close()
End If
CrsSearch.Dispose()
'Setting the CommandText to strSearchSelect and execute a scrollable ResultSet from it.
CcmdSearch.CommandText = strSearchSelect
CrsSearch = CcmdSearch.ExecuteResultSet(ResultSetOptions.Scrollable)
'Binding DataGrid to ResultSet returned.
DgSearch.DataSource = CrsSearch
'Focusing to search column combo box for next search.
CmbColumn.Focus()
End Sub
''' <summary>
''' Handles the Click event of the MnuReset control for resetting incremental search to initial state.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub MnuReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuReset.Click
'Closing and disposing the ResultSet before retrieving new ResultSet.
If Not CrsSearch.IsClosed Then
CrsSearch.Close()
End If
CrsSearch.Dispose()
'There are DefaultCriteria.
If strCriteria.Length > 0 Then
'Constructing the initial Search SQL.
strSearchSelect = strSelect + strCriteriaClause + strCriteria
Else
'Constructing the initial Search SQL.
strSearchSelect = strSelect
End If
'Setting the CommandText to strSearchSelect and execute a scrollable ResultSet from it.
CcmdSearch.CommandText = strSearchSelect
CrsSearch = CcmdSearch.ExecuteResultSet(ResultSetOptions.Scrollable)
'Binding DataGrid to ResultSet returned.
DgSearch.DataSource = CrsSearch
'There are no incremental search yet, so empty the strSearchSelect.
strSearchSelect = ""
'Empty the search text.
TxtSearch.Text = ""
End Sub
''' <summary>
''' Handles the LostFocus event of the TxtSearch control for triggering Search click.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub TxtSearch_LostFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtSearch.LostFocus
'Triggering search click.
MnuSearch_Click(Nothing, Nothing)
End Sub
''' <summary>
''' Handles the Deactivate event of the FrmSearchDialog control for triggering dialog close.
''' </summary>
''' <param name="sender">The source of the event.</param>
''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
Private Sub FrmSearchDialog_Deactivate(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Deactivate
'The DialogResult is OK and no search result HashTable
If DialogResult = Windows.Forms.DialogResult.OK AndAlso HashSearchResult Is Nothing Then
'The grid current row index is valid.
If DgSearch.CurrentRowIndex >= 0 Then
'Triggering OK click.
MnuOK_Click(Nothing, Nothing)
Else 'Setting Cancel DialogResult.
DialogResult = Windows.Forms.DialogResult.Cancel
End If
End If
End Sub
End Class
...Read more
