How to create Paging using SQLDataAdapter

We can use SQLDataAdapter to create Paging, which can be useful to access Large Data. For Details:

SQLDataAdapter Paging

How to create Paging for Large Data in SQL Server 2005

In SQL Server 2005 we can create paging, which can be useful to access the Large Data. I have discussed this in my previous post.

Paging in SQL Server 2005

Comparing DateTime Column in SQL Server

Introduction: This article addresses the following issues:

1) Time also stores in DateTime column therefore Time is also required while comparing
2) How to extract only Date (excluding the Time) from DateTime column

Level: Beginner

Knowledge Required:

• T-SQL
• SQL Server 2005

Description:
While working with Database we usually face an issue where we need to compare the given Date with SQL Server DateTime column. For example:

SELECT * FROM Orders WHERE Order_Date = '5-May-2008'

As we can see in above example we are comparing our date i.e. '5-May-2008' with the DateTime column Order_Date. But it is NOT sure that all the Rows of 5-May-2008 will be returned. This is because Time also stores here. So if we look into the Table we will find that,

Order_Date = '5-May-2008 10:30'
Order_Date = '5-May-2008 11:00'
Order_Date = '5-May-2008 14:00'

So when we give '5-May-2008' to SQL Server then it automatically converts it into:

Order_Date = '5-May-2008 12:00'

Therefore this date will NOT be equal to any of the dates above.

There are several techniques to handle this issue, I will discuss some here:

1) Compare all the 3 parts (day, month, year)
2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function
3) Use Date Range

1) Compare all the 3 parts:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   Day(Order_Date) = Day(@Given_Date) AND
        Month(Order_Date) = Month(@Given_Date) AND
        Year(Order_Date) = Year(@Given_Date);

2) Convert both (the given Date and the DateTime Column) into some predefined format using CONVERT function:
Example:

DECLARE @Given_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';

SELECT *
FROM Orders 
WHERE   CONVERT(varchar(100), Order_Date, 112) = CONVERT(varchar(100), @Given_Date, 112);

Note that CONVERT function will work as:

Print CONVERT(varchar(100), CAST('5-May-2008' AS DateTime), 112);

Output:

20080505

But we have limitation i.e. cannot use '<' and '>' operators here.

3) Use Date Range
Example:

DECLARE @Given_Date DateTime;
DECLARE @Start_Date DateTime;
DECLARE @End_Date DateTime;

SET @Given_Date = '5-May-2008 12:00';
SET @Start_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) As DateTime);
SET @End_Date = CAST(CAST(Day(@Given_Date) As varchar(100)) + '-' + DateName(mm, @Given_Date) + '-' + CAST(Year(@Given_Date) As varchar(100)) + ' 23:59:59' As DateTime);

SELECT *
FROM Order
WHERE Order_Date Between @Start_Date AND @End_Date;

In my opinion the last example is the fastest, since in all the previous examples SQL Server has to perform some extraction/conversion each time while extracting the Rows. But in the last method SQL Server will convert the Given Date only once, and in SELECT statement each time it is comparing the Dates, which is obviously faster than comparing a Date after converting it into VarChar or comparing each part of Date. Also in previous 2 methods we have limitation i.e. we cannot use the Range.

Creating Generic Range Class

Level: Beginner

Knowledge Required:
Generics in Visual Basic

Description:
In this article we will create a Generic Range Class, which can be further used in different types of Applications like Database Applications.

Generic Range Class:

Public Class GenericRange(Of T)
    Private _Start As T
    Private _End As T
    Private _HasStart As Boolean
    Private _HasEnd As Boolean

    Public Sub New()
        Call Me.SetStartNull()
        Call Me.SetEndNull()
    End Sub

    Public Sub New(ByVal Start As T, ByVal [End] As T)
        Me.Start = Start
        Me.End = [End]
    End Sub

    Public Property Start() As T
        Get
            If Me.HasStart Then
                Return Me._Start
            Else
                Throw New Exception("Start Value is NOT Set")
            End If
        End Get
        Set(ByVal value As T)
            If value Is Nothing Then
                Me.SetStartNull()
            Else
                Me._Start = value
                Me._HasStart = True
            End If
        End Set
    End Property

    Public Property [End]() As T
        Get
            If Me.HasEnd Then
                Return Me._End
            Else
                Throw New Exception("End Value is NOT Set")
            End If
        End Get
        Set(ByVal value As T)
            If value Is Nothing Then
                Me.SetEndNull()
            Else
                Me._End = value
                Me._HasEnd = True
            End If
        End Set
    End Property

    Public ReadOnly Property HasStart() As Boolean
        Get
            Return Me._HasStart
        End Get
    End Property

    Public ReadOnly Property HasEnd() As Boolean
        Get
            Return Me._HasEnd
        End Get
    End Property

    Public Sub SetStartNull()
        Me._HasStart = False
    End Sub

    Public Sub SetEndNull()
        Me._HasEnd = False
    End Sub
End Class

Usage:

Private Function GetOrderQueryByDate(ByRef GR As GenericRange(Of Date)) As String
    Dim sQuery As String
    Dim sWhere As String = ""

    sQuery = "SELECT * FROM Orders"

    If GR.HasStart Then
        sWhere = "OrderDate >= '" & GR.Start & "'"
    End If
    If GR.HasEnd Then
        If sWhere <> "" Then sWhere &= " AND "
        sWhere &= "OrderDate <= '" & GR.End & "'"
    End If
    If sWhere <> "" Then
        sQuery &= " WHERE " & sWhere
    End If
    Return sQuery
End Function

How to Create Generic Function

Level: Beginner

Knowledge Required:
Generics in Visual Basic

Introduction:
This article discusses how to create a Generic Function. Example: Create a Generic Function which is used to Find a Form in Open Forms. Useful for:

1) Preventing to create another instance of a Form
2) Finding a De-Activated / NOT Focused Window and set Focus on it

Description:
In my starting Post I discussed a situation where we require to find a Form which is opened. Now we will create a Generic Function which will return that particular type of Form.

Public Function GetWindow(Of T As Form)() As Form
    For Each frmEach As Form In My.Application.OpenForms
        If TypeOf frmEach Is T Then
            Return frmEach
        End If
    Next
    Return Nothing
End Function

Usage:

Public Sub ShowForm2()
    ' We will create a new instance of Form2
    Dim frmNew As Form2
    ' Call GetWindow() function which returns that type of Window if open
    frmNew = GetWindow(Of Form2)()
    ' GetWindow() returns NOTHING if window NOT found
    If frmNew IsNot Nothing Then
        frmNew.Show()
        frmNew.Focus()
    Else
        ' OK it means Window NOT Opened so create new instance
        frmNew = New Form2
        frmNew.Show()
    End If
End Sub

Post Updated: Using Data Across Multiple Forms

An issue is found in previous post,

Using Data Across Muiltiple Forms with OK and Cancel Feature

See post for Details.

How to Iterate TreeView Nodes Recursively

Level: Intermediate
Knowledge Required:

• Recursion
• TreeView Control

Description:
In this article we will use a code which Recursively Iterates through each TreeView Node.

Private Sub IterateTreeViewNodesRecursively(Optional ByRef ParentNode As TreeNode = Nothing)
    Dim objNodes As TreeNodeCollection

    ' if parentnode is NOT given then use treeview's nodes
    If ParentNode Is Nothing Then
        objNodes = Me.TreeView1.Nodes
    Else ' else it means parentnode is mentioned so use it's nodes
        objNodes = ParentNode.Nodes
    End If

    For Each n As TreeNode In objNodes
        ' perform your checking here
        'E.g.:
        'If n.Checked Then
        '    ' perform your operation here
        'End If
        'If n.Tag = "FOLDER" Then
        '    ' perform your operation here
        'End If
        If n.Nodes.Count > 0 Then        ' if this node has children
            ' iterate each children
            Call IterateTreeViewNodesRecursively(n)
        End If
    Next
End Sub

Note that if you want to access the checked nodes only you can use the above code but I have discussed another approach in the earlier post you can also see it.

How to Add SQL Server Built-in/User Defined Function in Typed DataSet as Queries

Issue: SQL Server Function in Typed DataSet does NOT return value
Level: Intermediate
Knowledge Required:

• T-SQL
• SQL User Defined Function
• Typed DataSet
• TableAdapter

Introduction:
This article explains how to add the SQL Server’s User Defined or Built-in Function in Typed DataSet. Note that if we do NOT follow the proper way then Function might NOT return any value in Visual Basic.

Description:
As we have used the Typed DataSets in VB 2005, which are primarily used to store the data, loaded from Database. We can also use the Typed DataSets to execute SQL Server functions (either built-in or user Defined).

For this purpose first I will show you the normal procedure:

1) Add any User Defined Function in the Database
e.g.: The following function returns the server date

CREATE FUNCTION [dbo].[GetServerDate] ()
RETURNS DateTime
AS
BEGIN
    DECLARE @Result AS DateTime;

    SELECT @Result = GetDate();

    RETURN @Result;
END

2) In Visual Studio 2005, create a new Windows Application Project
3) Add a New Empty DataSet (Data->Add New Data Source) (DO NOT select any tables/procedures/etc.)
4) Open the DataSet in Designer
5) Right Click in the Designer and Click on Add->Query


6) In the TableAdapter Query Configuration Wizard, select the "Use existing stored procedure" option (on page 2)


7) In the next step select the GetServerDate procedure from given List


8) Click Finish

This will add a Query TableAdapter in our DataSet and have added a Function GetServerDate which can be called using Code as,

Dim adp As MyDataSetTableAdapters.QueriesTableAdapter
Dim o As Object

adp = New MyDataSetTableAdapters.QueriesTableAdapter
o = adp.GetServerDate()

But here adp.GetServerDate() will always return Nothing. I think this is because internally when TableAdapter executes the Procedure it does NOT pass the parameters properly.

To overcome this issue we will use a slightly different approach.

Proceed the above given steps up to Step 5, then:

6) In the TableAdapter Query Configuration Wizard, this time select "Use SQL Statements" option, click Next
7) Select "SELECT which returns a single value" option, click Next


8) In the next step type the following SQL Query and click Next

SELECT dbo.GetServerDate()

9) Next we will supply the Function Name (which will be used in coding), type GetServerDate here
10) Click Finish

Now we will again test the function with same code given above and you will notice it returns a Value i.e. DateTime on Server.

In the above example we have used the SQL Server User Defined Function, we can also use the SQL Server's Built-in Function. For Example in the above steps, replace the SQL Statement in Step 8 to the following SQL Statement.

SELECT Is_Member(@Role)

Above function will check the Current Login in the Particular Role.

Working with String.Trim() Function

Level: Beginner

Introduction:
This article discusses the main functionality of String.Trim() function and also explains how to trim other characters along with White Spaces.

Description:
As we all know that Trim() function is used to remove the Spaces from Left and Right of a String. E.g.:

Dim SomeText As String
SomeText = "    Text with some spaces on Left and Right         "
SomeText = SomeText.Trim()
Debug.Print(SomeText)

Output will be

Text with some spaces on Left and Right

But Trim() actually removes the White Space Characters (White Space = Space, Tab, Enter, etc.)

Example:

Dim SomeText As String
SomeText = vbTab & vbCrLf " Text with some spaces on Left and Right " & vbTab & vbCrLf
SomeText = SomeText.Trim()
Debug.Print(SomeText)

Still output will be the same.

Sometimes we also require to remove the other characters along with White Spaces.

For Example: We have taken a File Name from some other string and it contains Tab, Enter or Spaces in its Start and/or End plus the File Name will be enclosed in Apostrophe or Quotation (' or "). So we want to remove the White Spaces, Apostrophe/Quotation from Start/End. In this way we will get the plain file name only which we can use for other purposes. To check I am creating a file name here as,

FileName = vbTab & vbCrLf & "'C:\ABC\Def.txt'    "

As you can see I have added the Tab, Enter and Apostrophe in the start. Now to Trim it properly we will use the following code,

FileName = FileName.Trim() ' first remove the White Spaces
FileName = FileName.Trim("'"c, """"c) ' now remove the Apostrophe/Quotation

As you can see we have used a simple technique, i.e.

• first we have removed the White Space Characters by calling the simple Trim() function
• then we have used its overloaded definition i.e. Trim(ParamArray CharArray() as Char)

How to Get Multiple Rows from Database using Comma Separated IDs (Primary Keys in Delimited String)

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server Stored Procedure
• SQL Server User Defined Table Functions

Description:
Sometimes it is required that we need to send more than 1 primary keys to the Database and get the Rows from Table. For example, we want to get all the Rows in which Primary Key = 1, 23, 66 and 99. For this purpose we can create a Dynamic SQL Query in our Application and then execute it as,

SELECT * FROM tbl_SomeTable WHERE PrimaryKey IN (1, 23, 66, 99)

But if we have done all our work using Stored Procedures then we need to execute the same stored procedure 4 times.

CREATE PROCEDURE dbo.GetSomeTableRow
    @PrimaryKey int
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey = @PrimaryKey;
End

Now we want to send the IDs to this Stored Procedure in one go. We can achieve this by creating a Delimited String, e.g.: Dim sIDs As String sIDs = "1, 22, 33, 99" Then we will create a stored procedure which will accept this Delimited String and return rows. But before creating this Stored Procedure we will first create a User Defined Table Function which will accept the Delimited String and Return the Table having 1 int Field/Column. This function will extract out each integer value from String and add it in a Table then return that Table.

Following is the Script of this function:

CREATE FUNCTION [dbo].GetIntTableFromDelimitedString
( 
    @DelimitedString varchar(max), 
    @Delimiter varchar(10)
)
RETURNS
@ReturnTable TABLE(
    IntValue int
)
AS
Begin
    DECLARE @EachItem varchar(255);
    DECLARE @DelimiterPos int;
    DECLARE @DelimiterPosPrv int;

    SET @DelimitedString = @DelimitedString + ',';
    SET @DelimiterPosPrv = 1;
    SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, 1);

    WHILE @DelimiterPos > 0
    BEGIN
        SET @EachItem = LTRIM(RTRIM(SUBSTRING(@DelimitedString, @DelimiterPosPrv, @DelimiterPos - @DelimiterPosPrv)));
        IF @EachItem <> ''
            INSERT INTO @ReturnTable(IntValue)
                VALUES(CAST(@EachItem AS int));  
        SET @DelimiterPosPrv = @DelimiterPos + 1;
        SET @DelimiterPos = CHARINDEX(@Delimiter, @DelimitedString, @DelimiterPosPrv);
    END

    Return;
End

Now we can change our Stored Procedure as

CREATE PROCEDURE dbo.GetSomeTableRows
    @PrimaryKeys varchar(max)
AS
Begin
    SET NOCOUNT ON;

    SELECT *
    FROM tbl_SomeTable
    WHERE PrimaryKey IN (
                        SELECT IntValue 
                        FROM dbo.GetIntTableFromDelimitedString(@PrimaryKeys, ',')
                        );
End

How to Get Image from Internet / How To Load Online Image into Image Class

Level: Intermediate

Knowledge Required:

• WebClient Class
• MemoryStream Class
• Image Class

Description:
We have used Image class to create images. One of its Shared member is

Image.FromFile(filename)

Which we can use as,

Dim i As Image
i = Image.FromFile("C:\MyTest.Jpg")

This will create a New instance of Image Class with MyTest.Jpg Loaded. We can then use this image in different controls like PictureBox.

But this method does NOT support URI. For example we have an image at:

http://www.google.com/intl/en_ALL/images/logo.gif

We cannot use as,

Dim i As Image
i = Image.FromFile("http://www.google.com/intl/en_ALL/images/logo.gif")

This will through an exception. So to Load images (programitically) that are stored online we will use the following code,

Private Function GetOnlineImage(ByVal URL As String) As Image
    Dim i As Image
    Dim w As New Net.WebClient
    Dim b() As Byte
    Dim m As System.IO.MemoryStream

    ' download the Image Data in a Byte array
    b = w.DownloadData(URL)

    ' create a memory stream from that Byte array
    m = New System.IO.MemoryStream(b)
    ' now create an Image from Memory Stream
    i = Image.FromStream(m)

    ' release the WebClient
    w.Dispose()

    ' return image
    Return i
End Function

' Usage
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim i As Image
    i = Me.GetOnlineImage("http://www.google.com/intl/en_ALL/images/logo.gif")
    Me.PictureBox1.Image = i
End Sub

How To Increase Performance of Loading Large Data from Database (Part-3)

Level: Advanced

Knowledge Required:
Part-3:

• Typed DataSet
• TableAdapter
• DataGridView

Description:
So far we have discussed the 2 better ways of loading large data,

Implement Paging in SQL Server 2005
Implement Paging in Application using SQLDataAdapter

Now the last one is to use to Virtual Mode of DataGridView control. Here we will NOT use the Data Binding since I have already discussed in Part-1 that Data Binding also slows down the process.

When DataGridView control is in Virtual Mode (VirtualMode=True) then CellValueNeeded Event is triggered for Unbound Columns. In the same event we receive the Row and Column Index, in which DataGridView is needing the Value. Therefore we just go in the same Row and Column of our loaded DataTable, get the Value and give to the DataGridView control.

So what we will do is,

1. Create a DataGridView Control NOT bound to anything
2. Add the Columns (manually) equal to number of Columns in our DataTable
3. Set the property of DataGridView Control i.e. VirtualMode = True
4. Load the Data in our DataTable which is also NOT bound to anything
5. Add the same number Rows in the DataGridView
6. And finally in the CellValueNeeded event you can use the following code

Private Sub grdLargeData_CellValueNeeded(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellValueEventArgs) Handles grdLargeData.CellValueNeeded
    Dim rowLargeData As LargeDataDataSet.LargeDataRow
    rowLargeData = Me.dsLargeData.LargeData(e.RowIndex)
    e.Value = rowLargeData(e.ColumnIndex)
End Sub

This approach is a bit faster as compare to the Data Bound version. But has limitations i.e. Ordering and Filtering cannot be performed.

Source Code:
LargeDataWithVirtualDGV.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

How To Increase Performance of Loading Large Data from Database (Part-2)

Level: Advanced

Knowledge Required:
Part-2:

• Typed DataSet
• TableAdapter
• DataAdapter
• Partial Class
• Data Binding

Description:
In my previous post, I showed you how to Implement Paging in SQL Server 2005 (Database Level). In this post I will discuss How To Implement Paging Using DataAdapter (Application Level). This is a simple technique,

SQLDataAdapter gives us an option to load the Data from Particular Row and to Limit the Loading of Data by providing the Maximum Number of Records to be Loaded. The Fill Method of SQLDataAdapter has 5 Declarations, one of them is: SQLDataAdapter.Fill(StartRecord, MaxNumberOfRecordsToLoad, ParamArray DataTables()) We will use the above Fill Method to start loading Data from Particular Record Number and Limit the SQLDataAdapter to load only 20 or 30 Records, as per our Page Size.

To implement it professionaly, we will create another Fill Method in our TableAdapter Class (using Partial Class) as,

Partial Class LargeDataTableAdapter
    Public Function FillByDataAdapterPaging(ByRef LargeDataDataTable As LargeDataSet.LargeDataDataTable, ByVal PageNumber As Integer, ByVal PageSize As Integer) As Integer
        Dim StartRow As Integer
        StartRow = ((PageNumber - 1) * PageSize)
        Me.Adapter.SelectCommand = Me.CommandCollection(0)
        If (Me.ClearBeforeFill = True) Then
            LargeDataDataTable.Clear()
        End If
        Dim returnValue As Integer = Me.Adapter.Fill(StartRow, PageSize, LargeDataDataTable)
        Return returnValue
    End Function
End Class

Source Code:
LargeDataWithDataAdapterPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

How To Increase Performance of Loading Large Data from Database (Part-1)

Issue: While loading Large amount of Data from Database:

• Connection Timeout occurs
• Application gets Hang/Stuck
• Application takes too much time to load data

Level: Advanced

Knowledge Required:
Part-1:
SQL Server 2005
Stored Procedures

Description:
First of all this is NOT a good practice to load ALL THE DATA from a Table into memory. A Human being cannot process/analyse all this huge data in a glance, instead we usually interested in a short information. For example we have a Table that contains all the Contact Numbers living in the City along with there Addresses and Names. Now we usually want to extract out one particular number or one particular name, NOT all the names and numbers.

But still we (developers) are forced to create such applications which give the user to access all the data by SCROLLING up or down.

So in this Article I will show you how to increase this performance by the following ways:

1. Implement Paging in SQL Server 2005 (Database Level)
2. Implement Paging using DataAdapter (Application Level)
3. Use the Virtual Mode of DataGridView Control

In this part I will discuss the 1st one.
1) Paging in SQL Server 2005
Paging means we divide our huge data into number of small chunks. We will display 1 page at a time and will provide next previous buttons so user can navigate forward and backword. I think this is the fastest way to load data. We will send the Page Number and Number of Records Per Page to Stored Procedure, which will return only that part of data. For example: I have created a Table tbl_LargeData in which there are 3 Fields:

• Row_ID [primary key, int, Identity Column]
• SomeData [varchar(255)]
• InsertDateTime [DateTime, Default = GetDate()]

In this table I have put some fake data with Total 1,000,000 Rows.

Now this is a bit Large Data (NOT that much Large). Now to test, I created a project in VB and simply loaded the Data (in a DataTable) by executing the Query

SELECT * FROM tbl_LargeData

I have 1 GB RAM and AMD Athlon 64 Processor 3500+.

Unbound DataTable: It took 28 Seconds to fill the DataTable.
Bound DataTable: I bind that DataTable to BindingSource, it took 51 seconds, almost double.

BindingSource also increases time to Fill a DataTable, becuase BindingSource itself keeps another Cache of Data for Sorting and Filtering Purpose.

The Loading of Data can significantly increase Time, if:

• System has low RAM and Processing Speed
• Other Applications are also running on Client PC
• Database Server is NOT on same machine, it is somewhere on the LAN
• Client PC is connected to server using low Band Width

Therefore to Implement Paging I have created 2 Stored Procedures in Database:

1. GetLargeDataPageInfo
2. GetLargeDataWithPaging

The GetLargeDataPageInfo stored procedure has a Parameter @PageSize int. User has to provide the Page Size (i.e. Number of Records Per Page) then this stored procedure will return

Total Records	Total Pages
1000000	50000

GetLargeDataWithPaging stored procedure is the main stored procedure which Returns the Particular Page of Data. The script is:

CREATE PROCEDURE [dbo].[GetLargeDataWithPaging]
    @PageNumber int,
    @PageSize int
AS
BEGIN
    SET NOCOUNT ON;

    -- For Paging we have used the ROW_NUMBER() function
    -- which operates on Ordering of Column

    DECLARE @RowStart int;
    DECLARE @RowEnd int;

    -- Calculate the first row's Index
    -- and Last Row's Index
    SET @RowStart = ((@PageNumber - 1) * @PageSize) + 1;
    SET @RowEnd = @RowStart + @PageSize - 1;
 
    SELECT Row_ID, SomeData, InsertDateTime
    FROM (
        SELECT    ROW_NUMBER()
                OVER (
                    ORDER BY Row_ID
                    ) AS Row_Num,
                *
        FROM    tbl_LargeData
        ) AS DerivedTable
    WHERE Row_Num Between @RowStart AND @RowEnd;
END

Source Code:
LargeDataWithSQLPaging.rar
Database:
TemporaryDB.rar
Note: Execute the ScriptToInsertBulkData.sql file (ziped inside the TemporaryDB.rar) to add the Fake Data

Update (12-May-2008): Point #2 changed to "Implement Paging using DataAdapter" which was "Implement Paging using DataReader"

How to make a ComboBox Read Only

Issue: ComboBox Control does NOT have a ReadOnly Property
Level: Intermediate
Knowledge Required: To understand the following solution you must have the knowledge of:

• ComboBox


• AddHandler


• RemoveHandler

Description:
Sometimes it is required to have the ComboBox control to be Read Only i.e. it should just be like a TextBox, from which we can Select and Copy Text but cannot change value and cannot open Drop Down List. For this purpose you can call the following public method ToggleComobBoxReadonly.

For Example:

' This will make the comobbox readonly
ToggleComobBoxReadonly(Me.ComboBox1, True)

Public Sub ToggleComboBoxReadonly(ByRef cmbToUse As ComboBox, ByVal bValue As Boolean)
    If bValue Then
        Dim intHeight As Integer
        intHeight = cmbToUse.Height
        cmbToUse.Tag = cmbToUse.DropDownStyle
        cmbToUse.DropDownStyle = ComboBoxStyle.Simple
        cmbToUse.Height = intHeight
        AddHandler cmbToUse.KeyDown, AddressOf Common.ComboBox_KeyDown
        AddHandler cmbToUse.KeyPress, AddressOf Common.ComboBox_KeyPress
    Else
        If cmbToUse.Tag IsNot Nothing Then
            cmbToUse.DropDownStyle = cmbToUse.Tag
            RemoveHandler cmbToUse.KeyDown, AddressOf Common.ComboBox_KeyDown
            RemoveHandler cmbToUse.KeyPress, AddressOf Common.ComboBox_KeyPress
        End If
    End If
End Sub

Private Sub ComboBox_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs)
    Select Case e.KeyCode
        Case Keys.Delete, Keys.Down, Keys.Up, Keys.PageDown, Keys.PageUp
            e.Handled = True
    End Select
End Sub

Private Sub ComboBox_KeyPress(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
    e.Handled = True
End Sub

Filtering the Data Using BindingSource

Level: Beginner
Knowledge Required: To understand the following solution you must have knowledge of:

• Data Binding

Description:
We usually use BindingSource to bind the Data with Controls. BindingSource is a powerful tool which not only used for binding but can also be used to quickly Filter and Sort the Data.
We use Filter property of BindingSource to filter the data,

KeywordBindingSource.Filter = "Keyword='Computer'"

In the above statement, word "Keyword" is the Column Name. Therefore the typical syntax for Filtering the Data is

ColumnName (comparison operator) Value

For String and Date Columns we close the Value in Single Quotes and also we can use LIKE operator with String Columns to partially search the value. We can group together multiple Column Filtering by using AND and/or OR operators.

KeywordBindingSource.Filter = "Keyword LIKE 'C%' AND IsSelected=True"

This statement will display those rows in which Keyword starts with "C" and only selected ones.

Other Filtering examples:

MyBindingSource.Filter = "Qty >= 50 AND Order_ID <= 20"
MyBindingSource.Filter = "OrderDate = #1-Jan-2008 3:23:00 PM#"

Please note that in above statement we used "#" sign for Date column therefore Date column can be closed with either Single Quotes or hash (#) sign.

Also consider this:

MyBindingSource.Filter = "CompName='Ahmed''s Bakery'"

In the above example notice the 2 Single Quotes after Ahmed. The actual String Value is Ahmed's Bakery but whenever in string, a Single Quote comes we must replace it with 2 Single Quotes otherwise an exception will be thrown by BindingSource, that's how BindingSource recognizes that String is closed in Single Quotes and any 2 Single Quotes within String is actually part of that String.

Here I have created a simple project which displays a list of Keywords and user has choice to select the Keywords. I have given an option to filter out the Keywords by typing the initial letters and can also filter down the Selected Keywords only by click on the "Display Selected Keywords only" check box. Here is the picture of the Form1




I have created a single method to set filtering:

Private Sub SetFilter()
    Dim sFilter As String = ""
    ' if Display Only Selected Keywords is checked
    If Me.chkIsSelected.Checked Then
        sFilter = "IsSelected=True";
    End If
    ' if some text is given to filter Keywords
    If Me.txtKeywordFilter.Text <> "" Then
        If sFilter <> "" Then sFilter &= " AND "
        sFilter &= "Keyword LIKE '" & Me.txtKeywordFilter.Text.Replace("'", "''") & "%'"
    End If
    ' If NO Filter is found to be created
    If sFilter = "" Then
        ' remove current filter
        Me.bsKeyword.RemoveFilter()
    Else
        ' set that filter
        Me.bsKeyword.Filter = sFilter
    End If
End Sub

This method is called when chkIsSelected CheckBox's Checked State is changed or whenever txtKeywordFilter TextBox's Text property is changed (See Project for detail).

Download the full source code from here

KeywordSelector.rar

Creating a Friendly User Interface for Many to Many Relationship Scenario

Level: Advanced
Knowledge Required: To understand the following solution you must have the knowledge of:

• Typed DataSets
• DataTables
• Data Binding
• DataGridView Control

Description:

In our daily life development, we usually face a scenario where we need to create a user interface for many to many relationship scenario. For example I will discuss here a scenario where we require to store the Shops along with the Products they deal in.

We have created 2 Typed DataSets here:

• Product DataSet
• Shop DataSet

Product DataSet contains 1 Table i.e. Product. We will load products in this Table. Note that I haven’t created any Physical Database here, so I will be manually filling some products in this table at runtime.

In the Shop DataSet we have 2 Tables:

• Shop Table
• ShopProducts Table

ShopProducts Table is a Junction Table, which means a shop can have multiple Products. One way to create this interface is to place a DataGridView control for entering the Shop’s Products as I have shown in the following figure:

As you can see user can add multiple Products in this list by opening the ComboBox and selecting a Product. This way of creating user interface is fine but NOT friendly. As you can see whenever user opens the ComboBox all the Products display no matter how many products user has already selected. Therefore whenever user tries to duplicate a product the Primary Key Violation exception will occur which we have handled in the DataError event of the DataGridView control. You can note that this Exception which user sees is also NOT Friendly.

So to make it simple we can add a Checked List Box here, in which all the products are displayed and user just has to check the products which he/she wants to be added with Shop. Since Checked List Box control is a bit old fashioned and NOT a good looking control so we will use the same DataGridView control but in a different way.

We will now add another DataSet here i.e. ProductSelectionDataSet. This DataSet is same as ProductDataSet except that it’s Product Table contains a field Boolean field IsSelected, which will be used to check whether a product is selected or NOT.

Next we will replace current DataGridView control with another DataGridView control having 2 Columns

• IsSelected Column
• Product Name Column

We will setup this DataGridView in the following manner:

• AllowUserToAddRows = False
• AllowUserToDeleteRows = False
• RowHeadersVisible = False
• ReadOnly = True

Also we will set the 1st Column i.e. IsSelected column as:

• HeaderText = “” (empty string, we don’t want its header to be displayed)
• Resizable = False
• Width = 32

And for the 2nd column (Product Name):

• HeaderText = “Product”
• AutoSizeMode = Fill

This will make our DataGridView a bit like Checked ListBox. Now we just have to handle some of the events. First we will create a method SelectCurrentShopProducts; this method will select the Products according the given Products in the ShopProduct Table (of ShopDataSet). This method will be executed each time when Binding Source’s Position is changed.

Private Sub SelectCurrentShopProducts()
    ' first we will clear the currently selected shops
    Call Me.ClearAllSelection()

    Dim drvShop As DataRowView
    ' get current shop
    drvShop = Me.ShopBindingSource.Current
    ' if there is some shop selected
    If drvShop IsNot Nothing Then
        Dim rowShop As ShopDataSet.ShopRow
        Dim rowsProduct() As ShopDataSet.ShopProductsRow
        ' get the row from shop table
        rowShop = CType(drvShop.Row, ShopDataSet.ShopRow)
        ' get the current selected products of that shop
        rowsProduct = rowShop.GetShopProductsRows()
        ' for each selected product
        For Each r As ShopDataSet.ShopProductsRow In rowsProduct
           Dim rowProdSel As ProductSelectionDataSet.ProductWithSelectionRow
           ' get the product row from ProductWithSelection table
            rowProdSel =Me.ProductSelectionDataSet.ProductWithSelection.FindByProd_ID(r.Prod_ID)
           ' and mark it as selected
            rowProdSel.IsSelected = True
        Next
    End If
End Sub

Private Sub ClearAllSelection()
    For Each r As ProductSelectionDataSet.ProductWithSelectionRow In Me.ProductSelectionDataSet.ProductWithSelection
        r.IsSelected = False
    Next
End Sub

Next we will handle the CellContentClick event of DataGridView control. This event will be triggered whenever the CheckBox is checked or unchecked. In this event we will first check: If CheckBox is NOT selected then we will add this Product in our ShopProduct table otherwise if CheckBox is selected then it means we have already added this Product in ShopProduct table so now we will remove it.

Private Sub ProductWithSelectionDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ProductWithSelectionDataGridView.CellContentClick
    If e.RowIndex >= 0 Then
        ' if checkbox is clicked
        If e.ColumnIndex = Me.ColumnIsSelected.Index Then
           Try
               Me.ShopBindingSource.EndEdit()

               Dim rowProdSel As ProductSelectionDataSet.ProductWithSelectionRow
                rowProdSel = CType(Me.ProductWithSelectionDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView).Row
                   ' if currently selected then we will de-select it
                   If rowProdSel.IsSelected Then
                   Try
                       Call Me.RemoveProductFromCurrentShop(rowProdSel.Prod_ID)
                        rowProdSel.IsSelected = False
                   Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "De-Select Shop")
                   End Try
               Else ' if NOT selected then we will select it
                   Try
                       Call Me.AddProductInCurrentShop(rowProdSel.Prod_ID)
                        rowProdSel.IsSelected = True
                   Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Select Shop")
                   End Try
                End If
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Edit Shop")
           End Try
        End If
    End If
End Sub

Private Sub AddProductInCurrentShop(ByVal Prod_ID As Integer)
    Dim drvShop As DataRowView
    Dim rowNew As ShopDataSet.ShopProductsRow
    ' get the current shop
    drvShop = Me.ShopBindingSource.Current
    ' create new ShopProduct Row
    rowNew = Me.ShopDataSet.ShopProducts.NewShopProductsRow()
    ' set values
    With rowNew
        .Shop_ID = drvShop("Shop_ID")
        .Prod_ID = Prod_ID
    End With
    ' add it in DataTable
    Me.ShopDataSet.ShopProducts.AddShopProductsRow(rowNew)
End Sub

Private Sub RemoveProductFromCurrentShop(ByVal Prod_ID As Integer)
    Dim drvShop As DataRowView
    Dim rowShopProduct As ShopDataSet.ShopProductsRow
    ' get the current shop
    drvShop = Me.ShopBindingSource.Current
    ' get the ShopProductRow
    rowShopProduct = Me.ShopDataSet.ShopProducts.FindByShop_IDProd_ID(drvShop("Shop_ID"), Prod_ID)
    rowShopProduct.Delete()
End Sub

NOTE: We have used the READONLY version of DataGridView that means when user clicks on the CheckBox in first column, then it does NOT get checked or un-checked Automatically. We handle this in the CellContentClick event of the DataGridView control. In this event we set the IsSelected = True or False which automatically updates the CheckBox in DataGridView control since it is binded to this table.

Download the full code from here:

JunctionTable.rar

How To Get the Selected/Checked Nodes of TreeView Control

Issue: TreeView Control does NOT have the Selected/Checked Nodes property
Level: Intermediate
Knowledge Required:

• TreeView Control

Description:
TreeView control is a powerfull tool to display the hierarchical data. We can use its CheckBoxes property to display the CheckBoxes against each node and user is allowed to select/de-select the nodes.

Sometimes it is required to get the nodes which are checked. For this purpose we can iterate through Nodes using Recursion but this process sometimes makes significant delay if there are many items in the Tree.

Another way of getting the selected/checked nodes is that

We create a List of Nodes and each time when user clicks on a node we check if user has Checked the node then we add it in our list and if he/she has unchecked the node then we remove it from our list. And finally we will use this List of Nodes to see which one is selected/checked

Following is the Form1 Code. I have put a TreeView and a Button Control on this form to test:


Dim CheckedNodes As List(Of TreeNode)

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Me.CheckedNodes = New List(Of TreeNode)
End Sub

Private Sub TreeView1_BeforeCheck(ByVal sender As Object, ByVal e As System.Windows.Forms.TreeViewCancelEventArgs) Handles TreeView1.BeforeCheck
    ' if NOT checked and going to be checked
    If Not e.Node.Checked Then
        Me.CheckedNodes.Add(e.Node)
    Else ' else (if checked and going to be un-chekced)
        Me.CheckedNodes.Remove(e.Node)
    End If
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    MsgBox(Me.CheckedNodes.Count & " Nodes are selected", MsgBoxStyle.Information, "Form1")
End Sub

Advanced Usage of Select Case

Recently I have discovered an advance way of performing a SELECT...CASE on vbforums.com

Here is the link to that post

http://www.vbforums.com/showthread.php?p=3217499&posted=1#post3217499