Creating and using excel files

Some time ago I have stumbled upon this code fragment:

Dim sConnectionString As String
  sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Book1.xls;Extended Properties=Excel 8.0"
  Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
  objConn.Open()
  Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from [Sheet1]", objConn)
  Dim ds As New DataSet("Sheet1")
  da.Fill(ds, "Sheet1") 

In this sample we connect to excel file using jet.oledb driver. I have used this in production and it worked like a charm with extreme preformance. For creating excel files I use excel library. This is probably the best free solution.

Nullable datetime picker

I have for the first time used datetimepicker control in my project. Although it is a great control I found it useless because of two things that are related:

• Default value cannot be set to nothing (null in C#).
• Datetimepicker canot display null (nothing in VB) values.

When I checked the msdn there was a caution notice saying just that.

I checked the codeproject website to look for an alternative. I found this control that suited my needs I only changed it to recognize the DateTime.MinValue to be nothing and set default to nothing. What I liked about it that it was the extended control, so it kept all the nice things I liked about default control. Here it is:

Imports System
Imports System.ComponentModel
Imports System.Globalization
Imports System.Runtime.InteropServices
Imports System.Threading
Imports System.Windows.Forms

' Copyright (c) 2005 Claudio Grazioli, http://www.grazioli.ch
'
' This code is free software; you can redistribute it and/or modify it.
' However, this header must remain intact and unchanged.  Additional
' information may be appended after this header.  Publications based on
' this code must also include an appropriate reference.
' 
' This code is distributed in the hope that it will be useful, but 
' WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY 
' or FITNESS FOR A PARTICULAR PURPOSE.
'

''' <summary>
''' Represents a Windows date time picker control. It enhances the .NET standard <b>DateTimePicker</b>
''' control with the possibility to show empty values (null values).
''' </summary>
<ComVisible(False)> _
Public Class DateTimePickerCtrl
  Inherits System.Windows.Forms.DateTimePicker
#Region "Member variables"
  ' true, when no date shall be displayed (empty DateTimePicker)
  Private _isNull As Boolean

  ' If _isNull = true, this value is shown in the DTP
  Private _nullValue As String

  ' The format of the DateTimePicker control
  Private _format As DateTimePickerFormat = DateTimePickerFormat.[Long]

  ' The custom format of the DateTimePicker control
  Private _customFormat As String

  ' The format of the DateTimePicker control as string
  Private _formatAsString As String
#End Region

#Region "Constructor"
  ''' <summary>
  ''' Default Constructor
  ''' </summary>
  Public Sub New()
    MyBase.New()
    MyBase.Format = DateTimePickerFormat.[Custom]
    NullValue = " "
    Format = DateTimePickerFormat.[Long]
  End Sub
#End Region

#Region "Public properties"

  ''' <summary>
  ''' Gets or sets the date/time value assigned to the control.
  ''' </summary>
  ''' <value>The DateTime value assigned to the control
  ''' </value>
  ''' <remarks>
  ''' <p>If the <b>Value</b> property has not been changed in code or by the user, it is set
  ''' to the current date and time (<see cref="DateTime.Now"/>).</p>
  ''' <p>If <b>Value</b> is <b>null</b>, the DateTimePicker shows 
  ''' <see cref="NullValue"/>.</p>
  ''' </remarks>
  <Bindable(True)> _
  <Browsable(False)> _
  Public Shadows Property Value() As [Object]
    Get
      If _isNull Then
        Return Nothing
      Else
        Return DateTime.MinValue
      End If
    End Get
    Set(ByVal value As [Object])
      If value Is Nothing OrElse value Is DBNull.Value OrElse (value.GetType() Is GetType(DateTime) And CDate(value) = DateTime.MinValue) OrElse (CDate(value) = New Date(CType(0, Long))) Then
        SetToNullValue()
      Else
        SetToDateTimeValue()
        MyBase.Value = DirectCast(value, DateTime)
      End If
    End Set
  End Property

  ''' <summary>
  ''' Gets or sets the format of the date and time displayed in the control.
  ''' </summary>
  ''' <value>One of the <see cref="DateTimePickerFormat"/> values. The default is 
  ''' <see cref="DateTimePickerFormat.Long"/>.</value>
  <Browsable(True)> _
  <DefaultValue(DateTimePickerFormat.[Long]), TypeConverter(GetType([Enum]))> _
  Public Shadows Property Format() As DateTimePickerFormat
    Get
      Return _format
    End Get
    Set(ByVal value As DateTimePickerFormat)
      _format = value
      If Not _isNull Then
        SetFormat()
      End If
      OnFormatChanged(EventArgs.Empty)
    End Set
  End Property

  ''' <summary>
  ''' Gets or sets the custom date/time format string.
  ''' <value>A string that represents the custom date/time format. The default is a null
  ''' reference (<b>Nothing</b> in Visual Basic).</value>
  ''' </summary>
  Public Shadows Property CustomFormat() As [String]
    Get
      Return _customFormat
    End Get
    Set(ByVal value As [String])
      _customFormat = value
    End Set
  End Property

  ''' <summary>
  ''' Gets or sets the string value that is assigned to the control as null value. 
  ''' </summary>
  ''' <value>The string value assigned to the control as null value.</value>
  ''' <remarks>
  ''' If the <see cref="Value"/> is <b>null</b>, <b>NullValue</b> is
  ''' shown in the <b>DateTimePicker</b> control.
  ''' </remarks>
  <Browsable(True)> _
  <Category("Behavior")> _
  <Description("The string used to display null values in the control")> _
  <DefaultValue(" ")> _
  Public Property NullValue() As [String]
    Get
      Return _nullValue
    End Get
    Set(ByVal value As [String])
      _nullValue = value
    End Set
  End Property
#End Region

#Region "Private methods/properties"
  ''' <summary>
  ''' Stores the current format of the DateTimePicker as string. 
  ''' </summary>
  Private Property FormatAsString() As String
    Get
      Return _formatAsString
    End Get
    Set(ByVal value As String)
      _formatAsString = value
      MyBase.CustomFormat = value
    End Set
  End Property

  ''' <summary>
  ''' Sets the format according to the current DateTimePickerFormat.
  ''' </summary>
  Private Sub SetFormat()
    Dim ci As CultureInfo = Thread.CurrentThread.CurrentCulture
    Dim dtf As DateTimeFormatInfo = ci.DateTimeFormat
    Select Case _format
      Case DateTimePickerFormat.[Long]
        FormatAsString = dtf.LongDatePattern
        Exit Select
      Case DateTimePickerFormat.[Short]
        FormatAsString = dtf.ShortDatePattern
        Exit Select
      Case DateTimePickerFormat.Time
        FormatAsString = dtf.ShortTimePattern
        Exit Select
      Case DateTimePickerFormat.[Custom]
        FormatAsString = Me.CustomFormat
        Exit Select
    End Select
  End Sub

  ''' <summary>
  ''' Sets the <b>DateTimePicker</b> to the value of the <see cref="NullValue"/> property.
  ''' </summary>
  Private Sub SetToNullValue()
    _isNull = True
    MyBase.CustomFormat = If((_nullValue Is Nothing OrElse _nullValue = [String].Empty), " ", "'" & _nullValue & "'")
  End Sub

  ''' <summary>
  ''' Sets the <b>DateTimePicker</b> back to a non null value.
  ''' </summary>
  Private Sub SetToDateTimeValue()
    If _isNull Then
      SetFormat()
      _isNull = False
      MyBase.OnValueChanged(New EventArgs())
    End If
  End Sub
#End Region

#Region "Events"
  ''' <summary>
  ''' This member overrides <see cref="Control.WndProc"/>.
  ''' </summary>
  ''' <param name="m"></param>
  Protected Overloads Overrides Sub WndProc(ByRef m As Message)
    If _isNull Then
      If m.Msg = &H4E Then
        ' WM_NOTIFY
        Dim nm As NMHDR = DirectCast(m.GetLParam(GetType(NMHDR)), NMHDR)
        If nm.Code = -746 OrElse nm.Code = -722 Then
          ' DTN_CLOSEUP || DTN_?
          SetToDateTimeValue()
        End If
      End If
    End If
    MyBase.WndProc(m)
  End Sub

  <StructLayout(LayoutKind.Sequential)> _
  Private Structure NMHDR
    Public HwndFrom As IntPtr
    Public IdFrom As Integer
    Public Code As Integer
  End Structure

  ''' <summary>
  ''' This member overrides <see cref="Control.OnKeyDown"/>.
  ''' </summary>
  ''' <param name="e"></param>
  Protected Overloads Overrides Sub OnKeyUp(ByVal e As KeyEventArgs)
    If e.KeyCode = Keys.Delete Then
      Me.Value = Nothing
      OnValueChanged(EventArgs.Empty)
    End If
    MyBase.OnKeyUp(e)
  End Sub

  Protected Overloads Overrides Sub OnValueChanged(ByVal eventargs As EventArgs)
    MyBase.OnValueChanged(eventargs)
  End Sub

#End Region
End Class

How to use reflection to look for property that implements IList (or any other) iterface

In this sample I have created a function that gets a class of type T and inspects properties to find IList implementation.

Public Function GetResponse(Of T)(ByVal instance As T) As IList
    Dim props As PropertyInfo() = instance.GetType.GetProperties(BindingFlags.Public Or BindingFlags.Instance)
    For Each p In props
      If GetType(IList).IsAssignableFrom(p.PropertyType) AndAlso p.PropertyType.IsGenericType Then
        Dim listItems As IList = DirectCast(p.GetValue(instance, Nothing), IList)
        If listItems IsNot Nothing Then
          Return listItems
        End If
      End If
    Next

    Return Nothing
  End Function

How to convert dataset or dbreader to List(of T) or BindingList(Of T) using mapping

When we want to make our applications more open to other platforms than we cannot use datasets as Therefor we need a way to convert datasets to simple objects and back. In this implementation I used a custom attribute to provide mapping between database language and business language, because that was a requirement.

Here is the custom attribute class:

Imports System.Reflection

<AttributeUsage(AttributeTargets.Field Or AttributeTargets.[Property], AllowMultiple:=False, Inherited:=True)> _
Public NotInheritable Class DataColumnAttribute
  Inherits Attribute

  Private Const Flags As BindingFlags = BindingFlags.[Public] Or BindingFlags.NonPublic Or BindingFlags.Instance Or BindingFlags.FlattenHierarchy

  Private _name As String = Nothing

  Public ReadOnly Property Name() As String

    Get
      Return _name
    End Get
  End Property

  Public Sub New()

  End Sub

  Public Sub New(ByVal name As String)
    _name = name
  End Sub

  Private Function GetName(ByVal member As MemberInfo) As String
    Return If(_name, member.Name)
  End Function



  Public Shared Sub Bind(ByVal row As DataRow, ByVal target As Object)

    Dim type As Type = target.[GetType]()

    For Each column As DataColumn In row.Table.Columns

      For Each field As FieldInfo In type.GetFields(Flags)
        For Each att As DataColumnAttribute In field.GetCustomAttributes(GetType(DataColumnAttribute), True)
          att.Bind(column.ColumnName, target, field, row(column))
        Next
      Next

      For Each [property] As PropertyInfo In type.GetProperties(Flags)
        For Each att As DataColumnAttribute In [property].GetCustomAttributes(GetType(DataColumnAttribute), True)
          att.Bind(column.ColumnName, target, [property], row(column))
        Next
      Next
    Next
  End Sub

  Public Shared Sub Bind(ByRef rd As DbDataReader, ByVal target As Object)

    Dim type As Type = target.[GetType]()

    For i = 0 To rd.FieldCount - 1

      For Each field As FieldInfo In type.GetFields(Flags)
        For Each att As DataColumnAttribute In field.GetCustomAttributes(GetType(DataColumnAttribute), True)
          att.Bind(rd.GetName(i), target, field, rd.Item(i))
        Next
      Next

      For Each [property] As PropertyInfo In type.GetProperties(Flags)
        For Each att As DataColumnAttribute In [property].GetCustomAttributes(GetType(DataColumnAttribute), True)
          att.Bind(rd.GetName(i), target, [property], rd.Item(i))
        Next
      Next

    Next

  End Sub

  Private Sub Bind(ByVal columnName As String, ByVal target As Object, ByVal field As FieldInfo, ByVal value As Object)
    If GetName(field).ToUpper() = columnName.ToUpper() Then
      field.SetValue(target, value)
    End If
  End Sub

  Private Sub Bind(ByVal columnName As String, ByVal target As Object, ByVal [property] As PropertyInfo, ByVal value As Object)

    If value.GetType() IsNot GetType(System.DBNull) Then
      If GetName([property]).ToUpper = columnName.ToUpper() Then
        [property].SetValue(target, value, Nothing)
      End If
    End If
  End Sub

End Class

Here is the converter class

Imports System.ComponentModel

Public Class DataTableConverter(Of T)

  Public Function GetObjectsAsList(ByRef rd As DbDataReader) As List(Of T)
    Dim list As New List(Of T)()

    While rd.Read
      Dim target As T = Activator.CreateInstance(Of T)()
      DataColumnAttribute.Bind(rd, target)
      list.Add(target)
    End While


    Return list
  End Function

  Public Function GetObjectsAsBindingList(ByRef rd As DbDataReader) As BindingList(Of T)
    Dim list As New BindingList(Of T)()

    While rd.Read
      Dim target As T = Activator.CreateInstance(Of T)()
      DataColumnAttribute.Bind(rd, target)
      list.Add(target)
    End While


    Return list
  End Function
  Public Function GetObjectsAsBindingList(ByRef dt As DataTable) As BindingList(Of T)
    Dim list As New BindingList(Of T)()

    For Each row As DataRow In dt.Rows

      Dim target As T = Activator.CreateInstance(Of T)()
      DataColumnAttribute.Bind(row, target)
      list.Add(target)
    Next

    Return list
  End Function

  Public Function GetObjectsAsList(ByRef dt As DataTable) As List(Of T)
    Dim list As New List(Of T)()

    For Each row As DataRow In dt.Rows

      Dim target As T = Activator.CreateInstance(Of T)()
      DataColumnAttribute.Bind(row, target)
      list.Add(target)
    Next

    Return list
  End Function

End Class

Here is the reverse converter class:

Imports System.Reflection
Imports System.ComponentModel

Public Class ListConverter

  Public Function GetDataSetFromList(Of T)(ByVal list As List(Of T)) As DataSet

    Dim _resultDataSet As New DataSet()
    Dim _resultDataTable As New DataTable("results")
    Dim _resultDataRow As DataRow = Nothing
    Dim _itemProperties() As PropertyInfo = Nothing
    '    
    ' Meta Data. 
    '
    _itemProperties = list.Item(0).GetType().GetProperties()
    For Each p As PropertyInfo In _itemProperties
      _resultDataTable.Columns.Add(p.Name, _
                p.GetGetMethod.ReturnType())
    Next
    '
    ' Data
    '
    For Each item As T In list
      '
      ' Get the data from this item into a DataRow
      ' then add the DataRow to the DataTable.
      ' Eeach items property becomes a colunm.
      '
      _itemProperties = item.GetType().GetProperties()
      _resultDataRow = _resultDataTable.NewRow()
      For Each p As PropertyInfo In _itemProperties
        _resultDataRow(p.Name) = p.GetValue(item, Nothing)
      Next
      _resultDataTable.Rows.Add(_resultDataRow)
    Next
    '
    ' Add the DataTable to the DataSet, We are DONE!
    '
    _resultDataSet.Tables.Add(_resultDataTable)
    Return _resultDataSet
  End Function

  Public Function GetDataSetFromBindingList(Of T)(ByVal list As Bindinglist(Of T)) As DataSet

    Dim _resultDataSet As New DataSet()
    Dim _resultDataTable As New DataTable("results")
    Dim _resultDataRow As DataRow = Nothing
    Dim _itemProperties() As PropertyInfo = Nothing
    '    
    ' Meta Data. 
    '
    _itemProperties = list.Item(0).GetType().GetProperties()
    For Each p As PropertyInfo In _itemProperties
      For Each att As DataColumnAttribute In p.GetCustomAttributes(GetType(DataColumnAttribute), True)
        _resultDataTable.Columns.Add(p.Name, p.GetGetMethod.ReturnType())
      Next
    Next
    '
    ' Data
    '
    For Each item As T In list
      '
      ' Get the data from this item into a DataRow
      ' then add the DataRow to the DataTable.
      ' Eeach items property becomes a colunm.
      '
      _itemProperties = item.GetType().GetProperties()
      _resultDataRow = _resultDataTable.NewRow()
      For Each p As PropertyInfo In _itemProperties
        For Each att As DataColumnAttribute In p.GetCustomAttributes(GetType(DataColumnAttribute), True)
          _resultDataRow(p.Name) = p.GetValue(item, Nothing)
        Next
      Next
      _resultDataTable.Rows.Add(_resultDataRow)
    Next
    '
    ' Add the DataTable to the DataSet, We are DONE!
    '
    _resultDataSet.Tables.Add(_resultDataTable)
    Return _resultDataSet
  End Function


End Class

How to validate XML string using XSD file

I have spended quite some time looking for a .net 3.5 compatible implementation of XML validation with XSD. Since i could not find one I wrote my own, bases on 2.0 implementation that I did find. Here it is:

Imports System.Text
Imports System.Xml.Schema
Imports System.Xml

Public Class XMLValidator2
  ' Validation Error Count
  Shared ErrorsCount As Integer = 0
  ' Validation Error Message
  Shared ErrorMessage As String = ""

  ' Validation Error Count
  Shared XSDErrorsCount As Integer = 0
  ' Validation Error Message
  Shared XSDErrorMessage As String = ""

  Public Sub Validate(ByVal strXMLDoc As String, ByVal xsdFilePath As String)

  
    Dim strXMLReader As New IO.StringReader(strXMLDoc)
    Dim schemaTextReader As IO.TextReader = New IO.StreamReader(xsdFilePath)

    Dim readerSettings As XmlReaderSettings = New XmlReaderSettings()
    'NOTE: če bomo kdaj javno objavili shemo lahko za path damo url
    readerSettings.Schemas.Add(XmlSchema.Read(schemaTextReader, AddressOf XSDSchemaValidationHandler))
    readerSettings.ValidationType = ValidationType.Schema
    AddHandler readerSettings.ValidationEventHandler, New ValidationEventHandler(AddressOf ValidationHandler)

    Dim books As XmlReader = XmlReader.Create(strXMLReader, readerSettings)

    While books.Read()

    End While

    ' Raise exception, if XML validation fails
    If ErrorsCount > 0 Then
      Throw New ApplicationException(ErrorMessage)
    End If

    ' XML Validation succeeded
    Console.WriteLine("XML validation succeeded." & vbCr & vbLf)

  End Sub

  Private Sub ValidationHandler(ByVal sender As Object, ByVal args As ValidationEventArgs)
    ErrorMessage = ErrorMessage + args.Message & vbCr & vbLf
    ErrorsCount += 1
  End Sub

  Private Sub XSDSchemaValidationHandler(ByVal sender As Object, ByVal args As ValidationEventArgs)
    XSDErrorMessage = XSDErrorMessage + args.Message & vbCr & vbLf
    XSDErrorsCount += 1
  End Sub


End Class

Pain, pain, pain configuring smartparts on sharepoint

I have lost last two days trying to make Smartpart with Ajax work on Sharepoint server. It was a nightmare, but in the end I managed to get my ajax enabled control working. Here are some tips on how to do it: 1)Before even starting to work on Smartparts, enable the Ajax on sharepoint. Exact procedure of things to do is here. 2) Add scriptmanager to the master page of a site, and remove it form controls. 3) Get smartparts from Codeplex here. 4) turn the trace on and turn of custom errors, to have an idea what went wrong. 5) make sure that you have the right assemblies registered (use reflector to get version and public key) in Sharepoint's config file. That's it.

How to serialize xml into a class

Parsing xml by using System.XML functions ain't lovely thing to do. Generating XML from class also ain't that simple. Making it generic adds a level to the problem. Yet this can be achieved by two simple functions bellow:

Imports System.Xml.Serialization
Imports System.Runtime.Serialization
Imports System.IO

Public Class Serialization
    Public Shared Function Serialize(Of T As IDeserializationCallback)(ByVal instance As T) As String
        Dim sb As New System.Text.StringBuilder
        Dim xmw As System.Xml.XmlWriter = System.Xml.XmlWriter.Create(sb)
        Dim xmlserializer As New XmlSerializer(GetType(T))
        xmlserializer.Serialize(xmw, instance)
        Return sb.ToString
    End Function

    Public Shared Function Deserialize(Of T As IDeserializationCallback)(ByVal strXml As String) As T
        Dim rdr As System.Xml.XmlReader = System.Xml.XmlReader.Create(New System.IO.StringReader(strXml))
        Dim xmlserializer As New XmlSerializer(GetType(T))
        Dim obj As T = CType(xmlserializer.Deserialize(rdr), T)
        obj.OnDeserialization(Nothing)
        Return obj
    End Function
End Class

All left to do now is call the function like:

Dim cl As ClassName
cl = Deserialize(Of ClassName)(inXml)

XML is of course an XML string that you want to deserialize.

How to print contents of datagridview?

I was looking for an answer on a web since i didn't want to write PrintDocument form as scratch. I found this website to be useful, but when i implemented code there were minor bugs in the code. There was some problem with page numbers when using a print preview and code assumed that data source of a grid was dataset and that all columns were shown, witch was not true in my case. Fixed document class below:

using System;
using System.Data;
using System.Windows.Forms;
using System.Drawing.Printing;
using System.Drawing; 

namespace VA.Gui
{
  public class PrintGridDocument : PrintDocument
  {
    //Data Members
    private DataGridView m_oDataGrid;
    private int m_nCurrPage;
    private int m_nCurrRow;
    private int m_nColumns;
    private int m_nRows;
    private bool m_bInitialized;
    private int m_nLinesPerPage;
    private int m_nTotalPages;
    private int[] m_nColBounds;

    //Properties
    public Font PrintFont;
    public string Title;

    public PrintGridDocument(DataGridView aGrid)
      : base()
    {
      //Default Values
      m_oDataGrid = aGrid;
      m_nCurrPage = 0;
      m_nCurrRow = 0;
      m_bInitialized = false;

      m_nColumns = m_oDataGrid.ColumnCount;
      m_nRows = m_oDataGrid.RowCount ;
       
    }

    //Override OnBeginPrint to set up the font we are going to use
    protected override void OnBeginPrint(PrintEventArgs ev)
    {
      base.OnBeginPrint(ev);
      //If client has not created a font, create a default font
      // Note: an exception could be raised here, but it is deliberately not
      // being caught because there is nothing we could do at this point!
      if (PrintFont == null)
        PrintFont = new Font("Arial", 9);

    }

    //Override the OnPrintPage to provide the printing logic for the document
    protected override void OnPrintPage(PrintPageEventArgs e)
    {
      m_nCurrPage = 0;
      //Call base method
      base.OnPrintPage(e);

      //Get the margins 
      int nTextPosX = e.MarginBounds.Left;
      int nTextPosY = e.MarginBounds.Top;

      //Do first time initialization stuff
      if (!m_bInitialized)
      {
        // Calculate the number of lines per page.
        m_nLinesPerPage = (int)(e.MarginBounds.Height / PrintFont.GetHeight(e.Graphics));
        m_nTotalPages = (int)Math.Ceiling((float)m_nRows / (float)m_nLinesPerPage);

        //Create bounding box for columns
        m_nColBounds = new int[m_nColumns];

        //Calculate the correct spacing for the columns
        for (int nCol = 0; nCol < m_nColumns; nCol++)
        {
          //Measure the column headers first
          m_nColBounds[nCol] = (int)e.Graphics.MeasureString(
              m_oDataGrid.Columns[nCol].HeaderText , PrintFont).Width;

          for (int nRow = 0; nRow < m_nRows; nRow++)
          {
            //Compare data to current max
            if (e.Graphics.MeasureString(m_oDataGrid[nCol,nRow].Value.ToString(), PrintFont).Width > m_nColBounds[nCol])
              m_nColBounds[nCol] = (int)e.Graphics.MeasureString(m_oDataGrid[nCol, nRow].Value.ToString(), PrintFont).Width;
          }

          //Just use max possible size if too large
          if (m_nColBounds[nCol] > e.MarginBounds.Width / m_nColumns)
            m_nColBounds[nCol] = e.MarginBounds.Width / m_nColumns;

          //Can't be less than column width
          if (m_nColBounds[nCol] < (int)Math.Round(e.Graphics.MeasureString(m_oDataGrid.Columns[nCol].HeaderText , PrintFont).Width))
            m_nColBounds[nCol] = (int)Math.Round(e.Graphics.MeasureString(m_oDataGrid.Columns[nCol].HeaderText, PrintFont).Width);
        }

        //Move to correct starting page
        if (this.PrinterSettings.PrintRange == PrintRange.SomePages)
        {
          while (m_nCurrPage < this.PrinterSettings.FromPage - 1)
          {
            //Move to next page - advance data to next page as well
            m_nCurrRow += m_nLinesPerPage;
            m_nCurrPage++;
            if (m_nCurrRow > m_nRows)
              return;
          }

          if (m_nCurrPage > this.PrinterSettings.ToPage)
          {
            //Don't print anything more
            return;
          }
        }

        //Set flag
        m_bInitialized = true;
      }

      //Move to next page
      m_nCurrPage++;

      //Print Title if first page
      if (m_nCurrPage == 1)
      {
        Font TitleFont = new Font("Arial", 15);
        int nXPos = (int)(((e.PageBounds.Right - e.PageBounds.Left) / 2) -
            (e.Graphics.MeasureString(Title, TitleFont).Width / 2));
        e.Graphics.DrawString(Title, TitleFont, Brushes.Black, nXPos, e.MarginBounds.Top - TitleFont.GetHeight(e.Graphics) - 10);
      }

      //Draw page number
      string strOutput = "Page " + m_nCurrPage + " of " + m_nTotalPages;
      e.Graphics.DrawString(strOutput, PrintFont, Brushes.Black, e.MarginBounds.Right - e.Graphics.MeasureString(strOutput, PrintFont).Width,
          e.MarginBounds.Bottom);

      //Utility rectangle - use for many drawing operations
      Rectangle aRect = new Rectangle();

      //Loop through data
      for (int nRow = m_nCurrRow; nRow < m_nRows; nRow++)
      {
        //Draw the current row within a shaded/unshaded box
        aRect.X = e.MarginBounds.Left;
        aRect.Y = nTextPosY;
        aRect.Width = e.MarginBounds.Width;
        aRect.Height = (int)PrintFont.GetHeight(e.Graphics);

        //Draw the box
        if (nRow % 2 == 0)
          e.Graphics.FillRectangle(Brushes.LightGray, aRect);

        e.Graphics.DrawRectangle(Pens.Black, aRect);

        //Loop through each column
        for (int nCol = 0; nCol < m_nColumns; nCol++)
        {
          //Set the rectangle to the correct position
          aRect.X = nTextPosX;
          aRect.Y = nTextPosY;
          aRect.Width = m_nColBounds[nCol];
          aRect.Height = (int)PrintFont.GetHeight(e.Graphics);
          //Print the data
          e.Graphics.DrawString(m_oDataGrid[nCol,nRow].Value.ToString(), PrintFont, Brushes.Black, aRect);
          //Advance the x Position counter
          nTextPosX += m_nColBounds[nCol];
        }

        //Reassign the x position counter
        nTextPosX = e.MarginBounds.Left;
        //Move the y position counter down a line
        nTextPosY += (int)PrintFont.GetHeight(e.Graphics);

        //Check to see if we have reached the line limit - move to a new page if so
        if (nRow - ((m_nCurrPage - 1) * m_nLinesPerPage) == m_nLinesPerPage)
        {
          //Save the current row
          m_nCurrRow = ++nRow;
          e.HasMorePages = true;
          return;
        }
      }
    }
  }
}

Usage is simple:

private void buttonCtl1_Click(object sender, EventArgs e)
{
      //Setup the document to print
      PrintGridDocument aDoc = new PrintGridDocument(dataGridViewCtl1 );
      aDoc.Title = "Employee Report";
      printDialog1 .Document = aDoc;
      if (printDialog1.ShowDialog() == DialogResult.OK)
      {
        //Display the print preview dialog
        aDoc.DefaultPageSettings = new PageSettings(printDialog1.PrinterSettings);

        //comment next two lines if do not want to use print preview
        printPreviewDialog1.Document = aDoc;
        printPreviewDialog1.ShowDialog();

        //uncomment lines below if not using print preview
        //try
        //{
        //  //Print the document
        //  aDoc.Print();
        //}
        //catch (Exception ex)
        //{
        //  //Display any errors
        //  MessageBox.Show(ex.ToString());
        //}
}

How to execute (invoke) a function using reflection if we know it's name and class

Have you ever wondered how to do that. This can be useful if you build a custom control and want user to tell you what function to call, for some reason. Code shown bellow.

Public Shared Function CallFunctionByReflection(ByVal className As String, ByVal functionName As String, ByVal arguments As Object())
    Dim theType As Type = Nothing
    Dim theObj As [Object] = Nothing
    Dim theMethodInfo As MethodInfo = Nothing

    theType = System.Type.GetType(className)

    theObj = Activator.CreateInstance(theType)

    Try
      theMethodInfo = theType.GetMethod(functionName)

      If arguments IsNot Nothing Then
        Return theMethodInfo.Invoke(theObj, arguments)
      Else
        Return theMethodInfo.Invoke(theObj, Nothing)
      End If
    Catch Ex As Exception
      Throw Ex
      Return Nothing
    Finally
      theType = Nothing
      theObj = Nothing
      theMethodInfo = Nothing
    End Try
  End Function

Design patterns

I've received an order from my boss to present design patterns to other team members, since this topic is not well known within the firm. As I went looking for something to begin with i came across the book called: Head First - Design patterns . It's a great book and i am using it as a guide on how to present patterns subject in a human understandable way.

How to change the backgroud color of MDI windows

Code below:

For Each ctrl As Control In Controls
        If ctrl.GetType() IsNot GetType(MdiClient) Then
          Continue For
        End If

        Dim colorName As String = ConfigurationManager.AppSettings("Dialog.BackColor")
        If colorName Is Nothing Then
          Exit For
        End If

        Dim color As Color = color.FromName(colorName)
        'checks if color is transparent, mdiclient.backcolor doesn't support transparent colors
        If color.A <> 0 Then
          ctrl.BackColor = color.FromName(colorName)
        End If
next

How to programaticaly detect .NET framework installed

There are two ways either by reading the registry or by browsing the file system. Both approaches have their benefits.
Reading the registry:
C++ example:
http://astebner.sts.winisp.net/Tools/detectFX.cpp.txt
C# example:
http://www.codeproject.com/KB/dotnet/frameworkversiondetection.aspx

Browsing the file system folder %systemroot%\Microsoft.NET\Framework:
http://msdn.microsoft.com/en-us/kb/kb00318785.aspx

Browsing trough files programaticaly

To browse trough the filesystem .net provides in System.IO namespace two very useful functions:  Directory.GetFiles  and Directory.GetDirectories. What those functions do is quite obvious. Simple example below.

Public Function SearchForFile(ByVal targetDirectory As String, ByVal fileToFind As String, ByVal recursive As Boolean) As String
    Dim fileName As String = String.Empty

    'ProcessFolder(targetDirectory)
    If fileToFind IsNot Nothing Then
      Dim fileEntries As String() = Directory.GetFiles(targetDirectory)
      ' Process the list of files found in the directory.

      For Each fileName In fileEntries
        If fileName.ToLower.Contains(fileToFind) Then
          Return fileName
        End If
      Next fileName

      If recursive Then
        Dim subdirectoryEntries As String() = Directory.GetDirectories(targetDirectory)
        ' Recurse into subdirectories of this directory.
        Dim subdirectory As String
        For Each subdirectory In subdirectoryEntries
          SearchForFile(subdirectory, fileToFind, recursive)
        Next subdirectory
      End If
    End If

    Return fileName

  End Function

How to set backgroud color of MDI

Since the property is not available at design time, all you need is a simple loop through controls (probably in load event) to set the background color of mdi window.

foreach (Control ctrl in Controls)
{
  if (!(ctrl is MdiClient)) continue;
  string colorName = ConfigurationManager.AppSettings["Dialog.BackColor"];
  if (colorName == null) continue;
  Color color = Color.FromName(colorName);
  //checks if color is transparent, mdiclient.backcolor doesn't support transparent colors
  if (color.A != 0)
       ctrl.BackColor = Color.FromName(colorName);
}

How to make an simple SMTP client

There is nothing to it. MSDN example does it all. All you need is to call System.Net library. Here is a simple implementation:

Imports System
Imports System.Net
Imports System.Net.Mail
Imports System.Net.Mime
Imports System.Threading
Imports System.ComponentModel

Module SimpleAsynchronousExample

  Dim mailSent As Boolean = False

  Private Sub SendCompletedCallback(ByVal sender As Object, ByVal e As AsyncCompletedEventArgs)

    'Get the unique identifier for this asynchronous operation.
    Dim token As String = CType(e.UserState, String)

    If (e.Cancelled) Then
      Console.WriteLine("[{0}] Send canceled.", token)
      If (e.Error IsNot Nothing) Then
        Console.WriteLine("[{0}] {1}", token, e.Error.ToString())
      Else
        Console.WriteLine("Message sent.")
      End If
      mailSent = True
    End If
  End Sub
  

  Sub Main(ByVal args As String())
    'Command line argument must the the SMTP host.
    Dim client As SmtpClient = New SmtpClient("hostname")
    '' Specify the e-mail sender.
    '' Create a mailing address that includes a UTF8 character
    '' in the display name.
    Dim from As MailAddress = New MailAddress("someone@test.com", "Some" + " One", System.Text.Encoding.UTF8)
    ' Set destinations for the e-mail message.
    Dim toAddress As MailAddress = New MailAddress("someone@test.com")
    '' Specify the message content.
    Dim message As MailMessage = New MailMessage(from, toAddress)
    message.Body = "This is a test e-mail message sent by an application. "
    ''Include some non-ASCII characters in body and subject.
    message.Body += Environment.NewLine + "This is a mail"
    message.BodyEncoding = System.Text.Encoding.UTF8
    message.Subject = "test message 1" + "test message"
    message.SubjectEncoding = System.Text.Encoding.UTF8
    ' Set the method that is called back when the send operation ends.
    AddHandler client.SendCompleted, AddressOf SendCompletedCallback
    ' The userState can be any object that allows your callback 
    ' method to identify this send operation.
    'For this example, the userToken is a string constant.
    Dim userState As String = "test message1"
    client.SendAsync(message, "this is me")
    Console.WriteLine("Sending message... press c to cancel mail. Press any other key to exit.")
    Dim answer As String = Console.ReadLine()
    '' If the user canceled the send, and mail hasn't been sent yet,
    '' then cancel the pending operation.
    If (answer.StartsWith("c") AndAlso mailSent <> False) Then
      client.SendAsyncCancel()
    End If
    ''Clean up.
    message.Dispose()
    Console.WriteLine("Goodbye.")
  End Sub

End Module

In order for example to work hostname of a SMTP server must be entered. Also make sure to have relay rules set. If you want no rule to apply set:

Pagging as concept part2

In addition to my previous post I would like to add that select presented in my previous post was just to demonstrate the concept. In reality select would look something like (in Oracle):

SELECT *
  FROM (SELECT ROWNUM row_num, column1, column2
          FROM (SELECT   column1, column2
                    FROM table
               )
         WHERE ROWNUM <= :page_num * :rec_count)
 WHERE row_num > (:page_num - 1) * :rec_count

Rec_count tells us how many records we want on a page, an page_num is the number of page we want.
I like this version more that maybe more used version:

SELECT *
  FROM (SELECT ROWNUM, table_name.*
          FROM (SELECT *
                  FROM table) table_name
         WHERE ROWNUM <= 200)
 WHERE ROWNUM <= 101

Whole point of doing sub select is to limit the number of results with inner select and then extract the rows required with outer select.

Simple implementation of SQL for paging modification would look something like:

 Public Function GetSQL(ByVal sqlin As String, ByVal sort As String, ByVal table As String, ByVal page As Integer, ByVal rec_count As Integer) As String
    Dim sql2 As String = " SELECT * FROM  (SELECT ROWNUM as ROW_NUM, " & table & ".* FROM "
    sql2 = sql2 & " ( " & sqlin & " " & sort & " )  " & table
    sql2 = sql2 & "  WHERE ROWNUM <= " & page & " * " & rec_count & ")  WHERE  ROW_NUM > (" & page & " - 1) *" & rec_count
    Return sql2
  End Function

Paging as a concept

Paging is yet another mysterious word used in by software developers. In principle it means that instead of downloading all the data returned by a query at once from database, we download subset (page)(first 100 rows) and when user browse through records, we fetch another page, and continue to do so until we have downloaded all the data. When applying this concept we presume that user will narrow its search when he cannot find what he was looking for in first n pages. If done correctly paging can have a significant impact on application performance.

Here is how we do it on all the databases that support rownum:  

SELECT * FROM  
(   SELECT rownum as row_num,
    column1,
    column2,
    column3,
    FROM 
    datatable
)
WHERE ROWNUM <= 201 and  ROW_NUM >= 300

Downside of paging is that it does not guaranty the coherence of data. If data in database is modified during the paging, duplicates or missing data my appear.

Software testing presentation i did

Publish at Scribd or explore others: software testing

How to easily produce CSV files

It can be easily done either from dataset or datareader.

If dataset (datatable) is the source then:

Public Shared Sub ProduceCSV(ByVal dt As DataTable, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)
    Dim i As Int32
    Dim j As Int32
    Dim myCIintl As System.Globalization.CultureInfo
    myCIintl = System.Globalization.CultureInfo.CurrentCulture
    If (WriteHeader) Then
      Dim arr(dt.Columns.Count - 1) As String
      For i = 0 To dt.Columns.Count - 1
        arr(i) = dt.Columns(i).ColumnName
        arr(i) = GetWriteableValue(arr(i))
      Next
      file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, arr))
    End If

    For j = 0 To dt.Rows.Count - 1
      Dim dataArr(dt.Columns.Count - 1) As String
      For i = 0 To dt.Columns.Count - 1
        Dim o As Object = dt.Rows(j)(i)
        dataArr(i) = GetWriteableValue(o)
      Next
      file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, dataArr))
    Next
  End Sub

Making csv files from reader:

Public Shared Sub ProduceCSV(ByVal reader As DbDataReader, ByVal file As System.IO.StreamWriter, ByVal WriteHeader As Boolean)
    Dim i As Int32
    Dim myCIintl As System.Globalization.CultureInfo

    While reader.Read
      myCIintl = System.Globalization.CultureInfo.CurrentCulture
      If (WriteHeader) Then
        Dim arr(reader.FieldCount) As String
        For i = 0 To reader.FieldCount - 1
          arr(i) = reader.GetName(i)
          arr(i) = GetWriteableValue(arr(i))
        Next
        file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, arr))
        WriteHeader = False ''header zapisemo samo enkrat
      End If

      Dim dataArr(reader.FieldCount - 1) As String
      For i = 0 To reader.FieldCount - 1
        Dim val As String
        'If reader.GetName(i).ToLower.Contains("stid") Then
        '  val = String.Format("""STID:{0}""", reader.Item(i))
        'Else
        val = GetWriteableValue(reader.Item(i))
        'End If

        dataArr(i) = val
      Next
      file.WriteLine(String.Join(myCIintl.TextInfo.ListSeparator, dataArr))
    End While
  End Sub

Since csv in my case contain millions of records I use reader as a source to limit memory usage and boost performance.

How to make binding to objects (classes) work

Problem: From my service I receive a class collection and when showing data in datagridview, there is no sorting posible, bindingsource functions like addnew do not work etc. After I have spended quite some time online to find an apropriate solution, i finally managed to produce staisfactory solution: Solution: first make a standard class that contains data.

Public  Class ListOfUnits
  Private  _ID As Integer
Public Property ID() As Integer
    Get
       Return _ID
    End Get
  Set(ByVal value As  Integer)
      _ID = value
   End Set
End Property
Private _UNIT As String
Public  Property UNIT() As String
  Get
      Return _UNIT
  End Get
  Set(ByVal value As String)
      _UNIT= value
  End Set
End Property
end  class

then make another class, that inherits from bindinglist(of T), where T is your class name

Public Class SortableListOfUnits
  Inherits BindingList(Of ListOfUnits)

  Private mIsSorted As Boolean

  Private m_SortDirection As ListSortDirection
  Private m_SortProperty As PropertyDescriptor
  Protected Overrides ReadOnly Property SortPropertyCore() As System.ComponentModel.PropertyDescriptor
    Get
      Return m_SortProperty
    End Get
  End Property
  Protected Overrides ReadOnly Property SortDirectionCore() As System.ComponentModel.ListSortDirection
    Get
      Return m_SortDirection
    End Get
  End Property
  Protected Overrides ReadOnly Property SupportsSortingCore() As Boolean
    Get
      Return True
    End Get
  End Property

  Protected Overrides Sub ApplySortCore(ByVal prop As System.ComponentModel.PropertyDescriptor, ByVal direction As System.ComponentModel.ListSortDirection)
    Dim items As System.Collections.Generic.List(Of ListOfUnits) = DirectCast(Me.Items, List(Of ListOfUnits))
    If Not IsNothing(items) Then
      m_SortDirection = direction
      m_SortProperty = prop
      items.Sort(New PropertyComparer(Of ListOfUnits)(prop.Name, direction))
      mIsSorted = True
    Else
      mIsSorted = False
    End If
    Me.OnListChanged(New ListChangedEventArgs(ListChangedType.Reset, -1))
  End Sub
  Protected Overrides ReadOnly Property IsSortedCore() As Boolean
    Get
      Return mIsSorted
    End Get
  End Property
  Protected Overrides Sub RemoveSortCore()
    mIsSorted = False
  End Sub
End Class

3) all you need now is a property comparer or in simple words a class that will implement a IComparer(Of T) interface if you want to make your own comparing logic. I have found a excelent implementation of icomparer online, that is generic, since i do not want to write comparer logic for every class.

Imports System.Reflection

Public Class PropertyComparer(Of T)
  Implements IComparer(Of T)

  Private FPropertyName As String = ""
  Private FDirection As SortDirection

  Public Sub New(ByVal propertyName As String)
    FPropertyName = propertyName
    FDirection = SortDirection.Ascending
  End Sub
  Public Sub New(ByVal propertyName As String, ByVal Direction As SortDirection)
    FPropertyName = propertyName
    FDirection = Direction
  End Sub
  ' Try to sort based on type using CompareTo method
  ' Multiple by FDirection to alternate sort direction
  Public Function Compare(ByVal x As T, ByVal y As T) _
     As Integer Implements System.Collections.Generic. _
        IComparer(Of T).Compare
    Dim propertyX As PropertyInfo = x.GetType().GetProperty(FPropertyName)
    Dim propertyY As PropertyInfo = y.GetType().GetProperty(FPropertyName)
    Dim px As Object = propertyX.GetValue(x, Nothing)
    Dim py As Object = propertyY.GetValue(y, Nothing)
    If (TypeOf px Is Integer) Then
      Return Compare(Of Integer)(CType(px, Integer), CType(py, Integer)) * FDirection
    End If
    If (TypeOf px Is Decimal) Then
      Return Compare(Of Decimal)(CType(px, Decimal), CType(py, Decimal)) * FDirection
    End If
    If (TypeOf px Is DateTime) Then
      Return Compare(Of DateTime)(CType(px, DateTime), CType(py, DateTime)) * FDirection
    End If
    If (TypeOf px Is Double) Then
      Return Compare(Of Double)(CType(px, Double), CType(py, Double)) * FDirection
    End If
    If (TypeOf px Is String) Then
      Return Compare(Of String)(CType(px, String), CType(py, String)) * FDirection
    End If
    If (TypeOf px Is Decimal) Then
      Return Compare(Of Decimal)(CType(px, Decimal), CType(py, Decimal)) * FDirection
    End If
    Dim methodX As MethodInfo = propertyX.GetType().GetMethod("CompareTo")
    If (methodX Is Nothing = False) Then
      Return CType(methodX.Invoke(px, New Object() {py}), _
         Integer) * FDirection
    Else
      Return 0
    End If
  End Function
  Private Function Compare(Of K As IComparable)(ByVal x As K, _
     ByVal y As K) As Integer
    Return x.CompareTo(y)
  End Function


  Public Enum SortDirection
    Descending = -1
    Ascending = 1
  End Enum

End Class