Filtering Arrays Using LINQ

LINQ provide a fast and effective way to filter arrays.

Here is an example on how to get the unique/distinct content of an array:

'An example array
Dim dArray() As Integer = {4, 5, 6, 7, 8, 5}

'Filter by LINQ
'dResult will contain the result.
Dim dResult = (From dNumber As Integer In dArray
               Select dNumber).Distinct

'Display the number of results
MessageBox.Show(dResult.Count)

Using StringBuilder

StringBuilder is very useful if you are working on string with a lot of concatenation. String has its own limitation and is very slow when you do a lot of string manipulations.

Here is an example of using StringBuilder:

Import System.Text

Imports System.Text

Example using Append method.

Dim dString As New StringBuilder
dString.Append("This is a string. ")
dString.Append("This is another string.")

The output of this will be: "This is a string. This is another string."

Example using AppendFormat method

Dim dString As New StringBuilder
Dim dString1 As String = "Append"
Dim dString2 As String = "Format"
dString.AppendFormat("This is a string using {0}{1},", dString1, dString2)

Creating and Using Datatables in VB.net

DataTable is very useful in storing data in table format that can be use to display in DataGridView control or any data bound controls.

DataTable can be search/filtered using LINQ.

Here is how to create a datatable:

Dim dTable As New DataTable("TableName")

Add columns to the datatable

dTable.Columns.Add("Column1", GetType(Integer))
dTable.Columns.Add("Column2", GetType(String))
dTable.Columns.Add("Column3", GetType(Date))
dTable.Columns.Add("Column4", GetType(Double))

Add rows to the datatable

dTable.Rows.Add({1, "Hello", Now.Date, 5.6})
dTable.Rows.Add({2, "World", New Date(2011, 2, 1), Math.PI})

You can also add rows using the following code:

Dim dRow As DataRow
dRow = dTable.NewRow
dRow("Column1") = 3
dRow("Column1") = "Hello World"
dRow("Column1") = Now.Date
dRow("Column1") = 4.6
dTable.Rows.Add(dRow)

To bind the datatable to the DataGridView control:

'Change DataGridView1 to the name of your DataGridView control
DataGridView1.DataSource = dTable

To filter the datable, you can use any of the two codes.

'Filtering using DataTable.Select() method
'This method is very slow if you have a lot of rows.
Dim dResult1() As DataRow = dTable.Select("Column1 = 3")

'Filtering using LINQ.
'This is the recommended approach if you are working on large set of data.
Dim dResult2 = From dItem As DataRow In dTable.AsEnumerable
              Where dItem("Column1") = 3
              Select dItem

To convert DataTable to HTML you can read this article:
Convert DataTable to HTML Table

How To Capture Keys If KeyDown Event Is Not Firing

To force the KeyDown and KeyUp event to capture keys like left, right, up, and down keys, IsInputKey must be overriden.

Here is the code that will show you how to do it.

Protected Overrides Function IsInputKey(keyData As System.Windows.Forms.Keys) As Boolean
    Select Case keyData
        Case Keys.Left, Keys.Right, Keys.Up, Keys.Down
            Return True
        Case Else
            Return MyBase.IsInputKey(keyData)
    End Select
End Function

If there are keys that are not firing the KeyDown and KeyUp event, just place it after the Keys.Down in the select case statement.

Get the Exact Width of A String Using MeasureCharacterRanges

Getting the exact size of a string will not always work using MeasureString. Here is the remark coming the MSDN website:

The MeasureString method is designed for use with individual strings and includes a small amount of extra space before and after the string to allow for overhanging glyphs. Also, the DrawString method adjusts glyph points to optimize display quality and might display a string narrower than reported by MeasureString.

So to obtain the actual size, MSDN continues:
"...To obtain metrics suitable for adjacent strings in layout (for example, when implementing formatted text), use the MeasureCharacterRangesmethod or one of the MeasureString methods that takes a StringFormat, and pass GenericTypographic. Also, ensure the TextRenderingHint for the Graphics is AntiAlias.

Here is the code on how to obtain the exact size using MeasureCharacterRanges:

Public Function MeasureDisplayStringWidth(ByVal dGraphics As Graphics, ByVal dText As String, ByVal dFont As Font) As Integer	
	Dim dRect As New System.Drawing.RectangleF(0, 0, Integer.MaxValue, Integer.MaxValue)
    Dim dRange As System.Drawing.CharacterRange() = {New System.Drawing.CharacterRange(0, dText.Length)}
    Dim dRegion As System.Drawing.Region() = New System.Drawing.Region(0) {}
    Dim dFormat As New System.Drawing.StringFormat()

    dFormat.SetMeasurableCharacterRanges(dRange)

    dRegion = dGraphics.MeasureCharacterRanges(dText, dFont, dRect, dFormat)
    dRect = dRegion(0).GetBounds(dGraphics)
	Return CInt(Math.Truncate(dRect.Right + 1))
End Function

How to Use FillMode to Fill the Interior of a Path

The following example creates a path that has one open figure (an arc) and one closed figure (an ellipse). The FillPath method fills the path according to the default fill mode, which is Alternate. Note that the path is filled (according to Alternate) as if the open figure were closed by a straight line from its ending point to its starting point.

Dim path As New GraphicsPath()

' Add an open figure.
path.AddArc(0, 0, 150, 120, 30, 120)

' Add an intrinsically closed figure.
path.AddEllipse(50, 50, 50, 100)

Dim pen As New Pen(Color.FromArgb(128, 0, 0, 255), 5)
Dim brush As New SolidBrush(Color.Red)

' The fill mode is FillMode.Alternate by default.
e.Graphics.FillPath(brush, path)
e.Graphics.DrawPath(pen, path)

DashStyle and DashPattern Property Example

Below is a sample on how to use dashstyle property in a pen.

Private Sub Button3_Click(ByVal sender As System.Object, _
 ByVal e As System.EventArgs) Handles Button3.Click

    Dim buttonGraphics As Graphics = Button3.CreateGraphics()
    Dim myPen As Pen = New Pen(Color.ForestGreen, 4.0F)
    myPen.DashStyle = Drawing2D.DashStyle.DashDotDot

    Dim theRectangle As Rectangle = Button3.ClientRectangle
    theRectangle.Inflate(-2, -2)
    buttonGraphics.DrawRectangle(myPen, theRectangle)
    buttonGraphics.Dispose()
    myPen.Dispose()
End Sub

Connect to MySQL Server Using VB.Net

To connect to the MySQL Server using VB.Net, you need to install MySQL .Net Connector or a MySQL ODBC driver. Add reference to the MySQL .Net Connector, then use the following code to connect.

Using ODBC Driver

Imports Microsoft.VisualBasic
Imports System.Data.Odbc
Imports System.Data

Public Class Server
    Private MySQLDataReader As OdbcDataReader
    Private ConnectionString As String = "Driver={MySQL ODBC 5.1 Driver}; Server=localhost;     
                    User=user; Password=password; Database=database; Option=3;"
    
    Protected Function ExecuteQuery(Byval Query As String) As DataTable
        'Reference: http://msdn.microsoft.com/en-us/library/ms998569.aspx
        'When Using DataReaders, Specify CommandBehavior.CloseConnection
        Dim dReturnValue As New DataTable

        Try
            'Open and Close the Connection in the Method. See Reference.
            Dim MySQLAdapter As New OdbcDataAdapter(Query, ConnectionString)

            'Explicitly Close Connections. See Reference.
            MySQLAdapter.SelectCommand.Connection.Close()

            'Do Not Explicitly Open a Connection if You Use Fill or Update for a Single Operation
            MySQLAdapter.Fill(dReturnValue)
        Catch ex As Exception
        End Try

        Return dReturnValue
    End Function

    Protected Function ExecuteNonQuery(Byval Query As String) As Boolean
        Dim MySQLConnection As New OdbcConnection(ConnectionString)
        Try
            'Open and Close the Connection in the Method. See Reference.
            MySQLConnection.Open()
            Dim MySQLCommand As New OdbcCommand(Query, MySQLConnection)
            MySQLCommand.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            Return False
        Finally
            'Open and Close the Connection in the Method. See Reference.
            MySQLConnection.Close()
        End Try
    End Function
End Class

In the ConnectionString, replace the Server, Username, Password and Database with the actual value. In my example, my Server is localhost, Username is username, Password is password and Database is database.

To get data from the database, use the ExecuteQuery function. This will return a DataTable. To insert, update, delete or any query that requires data manipulation, use the ExecuteNonQuery function.


Using .Net Connector

Imports MySql.Data.MySqlClient

Public Class Server
    Private Shared Connection As New MySqlConnection
    Private Shared Connected As Boolean

    Public Shared Function Login(ByVal Server As String, ByVal User As String, ByVal Password As String, ByVal Database As String, Optional ByVal Port As Integer = 3306) As Boolean
        Connection.ConnectionString = String.Format("server={0}; user id={1}; password={2}; pooling=false; database={3}; port={4}", Server, User, Password, Database, Port)
        Try
            Connection.Open()
            Connected = True
            Return True
        Catch ex As MySqlException
            Connected = False
            Return False
        End Try
    End Function

    Public Shared Sub Logout()
        Try
            Connected = False
            Connection.Close()
        Catch ex As Exception
        End Try
    End Sub

    Public Shared ReadOnly Property IsConnected() As Boolean
        Get
            Return Connected
        End Get
    End Property

    ''' 

    ''' Executes a query (INSERT, UPDATE, DELETE).
    ''' 
    ''' SQL Command (INSERT, UPDATE, DELETE)    ''' Returns true if command is executed successfully
    ''' 
    Public Shared Function ExecuteNonQuery(ByVal Command As String) As Boolean
        Try
            Dim SQLCommand As New MySqlCommand(Command, Connection)
            SQLCommand.ExecuteNonQuery()
            Return True
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        End Try
    End Function

    ''' 

    ''' Returns the results of a query in Datatable.
    ''' 
    ''' SQL query    ''' Returns a datatable containing the data (SELECT)
    Public Shared Function ExecuteQuery(ByVal Query As String) As DataTable
        Dim Data As New DataTable
        Try
            Dim dAdapter As New MySqlDataAdapter(Query, Connection)
            Dim CommandBuilder As New MySqlCommandBuilder(dAdapter)
            dAdapter.Fill(Data)
        Catch ex As MySqlException
            MessageBox.Show(ex.Message)
        End Try
        Return Data
    End Function
End Class

The code for the .Net Connector is similar to the ODBC Driver, except that you have to call the Login function before calling the ExecuteQuery and ExecuteNonQuery. The Login function will return true if it was able to connect to the server. You can also check the IsConnected property to check if the program was able to connect to the database.

Convert DataTable to HTML Table

Below is the code to convert DataTable or Dataset as html table for ASP.net.
Call the function DataTableToHTMLTable as pass the DataTable as parameter. The return value would be a formatted HTML table.

Public Function DataTableToHTMLTable(ByVal inTable As DataTable) As String
 Dim dString As New StringBuilder
 dString.Append("<table>")
 dString.Append(GetHeader(inTable))
 dString.Append(GetBody(inTable))
 dString.Append("</table>")
 Return dString.ToString
End Function

Private Function GetHeader(ByVal dTable As DataTable) As String
 Dim dString As New StringBuilder

 dString.Append("<thead><tr>")
 For Each dColumn As DataColumn In dTable.Columns
  dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName)
 Next
 dString.Append("</tr></thead>")

 Return dString.ToString
End Function

Private Function GetBody(ByVal dTable As DataTable) As String
 Dim dString As New StringBuilder

 dString.Append("<tbody>")

 For Each dRow As DataRow In dTable.Rows
  dString.Append("<tr>")
  For dCount As Integer = 0 To dTable.Columns.Count - 1
   dString.AppendFormat("<td>{0}</td>", dRow(dCount))
  Next
  dString.Append("</tr>")
 Next
 dString.Append("</tbody>")

 Return dString.ToString()

End Function

To create DataTable, you can read this article:
Creating and Using Datatables in VB.net