Read entire worksheet in an Excel workbook using VB.net

In the following section you can see How to find the last row data in Excel worksheet or How to find the Used area in a worksheet. For finding last row we are using WorkSheet.UsedRange . It will return the last cell . The follwoing code open an Excel file and read the entire content.

Continue reading “Read entire worksheet in an Excel workbook using VB.net”

Advertisements

Read Data from Excel using OLEDB in VB.NET

Without using Excel Object we can insert , edit , delete , select etc. in cell content of an Excel file using OLEDB in VB.NET . Here we are using OleDbConnection , OleDbDataAdapter , DataSet for doing these operations in an Excel file. You have to import System.Data in the project for doing these operations . For read the content from Excel file using ado.net , We can use the SELECT command like in SQL Operations.

If you would like to export to excel, check out this article:

How to export from DataGridView to excel

Continue reading “Read Data from Excel using OLEDB in VB.NET”

How to run a Windows Service in Console Mode

Run Windows Service as a console program

Visual Studio and the .NET framework make it really easy to create Windows Services. All you have to do is create a new project, select “Windows Service” as your project type and you’re all set. However, debugging Windows Services in Visual Studio can be a big pain. The recommended way is to use InstallUtil to install them, and then restart the service and attach the debugger everytime you want to debug it. I wanted to be able to debug it without the hassle, so here’s what I came up with:

using System;
using System.ServiceProcess;
 
public partial class DemoService : ServiceBase
{
    static void Main(string[] args)
    {
        DemoService service = new DemoService();
 
        if (Environment.UserInteractive)
        {
            service.OnStart(args);
            Console.WriteLine("Press any key to stop program");
            Console.Read();
            service.OnStop();
        }
        else
        {
            ServiceBase.Run(service);
        }
 
    }
    public DemoService()
    {
        InitializeComponent();
    }
 
    protected override void OnStart(string[] args)
    {
        // TODO: Add code here to start your service.
    }
 
    protected override void OnStop()
    {
        // TODO: Add code here to perform any tear-down
        //necessary to stop your service.
    }
}

This will allow you to use your program as either a normal console program or a windows service, with no special builds, #DEBUG directives, command line parameters or anything like that. What it does is in the Main method it checks the “Environment.UserInteractive” property. This will be true when it is run from Visual Studio, or when you just click on the .exe file, but false if it’s being run as a service. When it’s run from Visual Studio or as a standalone program it will keep running until you press a key, then it will call your OnStop method and then terminate.

Two things to watch out for:

  1. You’ll have to right click on your project in Visual Studio, choose Properties and select the Output type as “Console application” for this to work.
  2. If your Main method is not in your service class, you’ll have to add public methods to your class that can start and stop it, for instance add a public void StartConsole(string[] args) that just calls your OnStart, since OnStart and OnStop are protected methods and as such not accessible from other classes.

Reduced Automapper – VB.NET Code

I am a big fan of AutoMapper. I am now using it in many projects for mapping entities between different domains like from wcf service model to business model.

After some load tests (with VS Profiler) in a sample website, I found that AutoMapper is responsible for high CPU consumption.
Which is faster: Automapper, Valuinjector, or manual mapping? To what degree is each one faster? [closed]

After a bit of work, the following VB.NET Function has been developed to help perform a deep-copy of an object.

Imports System.Reflection

'''

''' Class that maps an object to another performing a deep copy in the process
'''

 

Public Class ReducedAutoMapper
    Private Shared automapperInstance As ReducedAutoMapper
    Private mappingTypesDict As Dictionary(Of Object, Object)

    Public Shared ReadOnly Property Instance() As ReducedAutoMapper
        Get
            If automapperInstance Is Nothing Then
                automapperInstance = New ReducedAutoMapper() With {.MappingTypes = New Dictionary(Of Object, Object)()}
            End If
            Return automapperInstance
        End Get
    End Property

    Public Property MappingTypes() As Dictionary(Of Object, Object)
        Get
            Return Me.mappingTypesDict
        End Get
        Set(value As Dictionary(Of Object, Object))
            Me.mappingTypesDict = value
        End Set
    End Property

    Public Sub CreateMap(Of TSource As New, TDestination As New)()
        If Not Me.MappingTypes.ContainsKey(GetType(TSource)) Then
            Me.MappingTypes.Add(GetType(TSource), GetType(TDestination))
        End If
    End Sub

    Public Function Map(Of TSource As {Class, New}, TDestination As {Class, New})(realObject As TSource, Optional dtoObject As TDestination = Nothing, Optional alreadyInitializedObjects As Dictionary(Of Object, Object) = Nothing, Optional shouldMapInnerEntities As Boolean = True) As TDestination
        If realObject Is Nothing Then
            Return Nothing
        End If
        If alreadyInitializedObjects Is Nothing Then
            alreadyInitializedObjects = New Dictionary(Of Object, Object)()
        End If
        If dtoObject Is Nothing Then
            dtoObject = New TDestination()
        End If

        Dim realObjectType = realObject.[GetType]()
        Dim properties As PropertyInfo() = realObjectType.GetProperties()
        For Each currentRealProperty As PropertyInfo In properties
            Dim currentDtoProperty As PropertyInfo = dtoObject.[GetType]().GetProperty(currentRealProperty.Name)
            ''/Debug.WriteLine("The property {0} was not found in the DTO object in order to be mapped. Because of that we skip to map it.", currentRealProperty.Name);
            If currentDtoProperty Is Nothing Then
            Else
                If Me.mappingTypesDict.ContainsKey(currentRealProperty.PropertyType) AndAlso shouldMapInnerEntities Then
                    Dim mapToObject As Object = Me.MappingTypes(currentRealProperty.PropertyType)
                    Dim types = New Type() {currentRealProperty.PropertyType, DirectCast(mapToObject, Type)}
                    Dim method As MethodInfo = [GetType]().GetMethod("Map").MakeGenericMethod(types)
                    Dim realObjectPropertyValue = currentRealProperty.GetValue(realObject, Nothing)
                    Dim objects = New Object() {realObjectPropertyValue, Nothing, alreadyInitializedObjects, shouldMapInnerEntities}
                    If objects IsNot Nothing AndAlso realObjectPropertyValue IsNot Nothing Then
                        If alreadyInitializedObjects.ContainsKey(realObjectPropertyValue) AndAlso currentDtoProperty.CanWrite Then
                            ' Set the cached version of the same object (optimization)
                            currentDtoProperty.SetValue(dtoObject, alreadyInitializedObjects(realObjectPropertyValue), Nothing)
                        Else
                            ' Add the object to cached objects collection.
                            alreadyInitializedObjects.Add(realObjectPropertyValue, Nothing)
                            ' Recursively call Map method again to get the new proxy object.
                            Dim newProxyProperty = method.Invoke(Me, objects)
                            If currentDtoProperty.CanWrite Then
                                currentDtoProperty.SetValue(dtoObject, newProxyProperty, Nothing)
                            End If

                            If alreadyInitializedObjects.ContainsKey(realObjectPropertyValue) AndAlso alreadyInitializedObjects(realObjectPropertyValue) Is Nothing Then
                                alreadyInitializedObjects(realObjectPropertyValue) = newProxyProperty
                            End If
                        End If
                    ElseIf realObjectPropertyValue Is Nothing AndAlso currentDtoProperty.CanWrite Then
                        ' If the original value of the object was null set null to the destination property.
                        currentDtoProperty.SetValue(dtoObject, Nothing, Nothing)
                    End If
                ElseIf Not Me.mappingTypesDict.ContainsKey(currentRealProperty.PropertyType) Then
                    ' If the property is not custom type just set normally the value.
                    If currentDtoProperty.CanWrite Then
                        currentDtoProperty.SetValue(dtoObject, currentRealProperty.GetValue(realObject, Nothing), Nothing)
                    End If
                End If
            End If
        Next

        Return dtoObject
    End Function

    Public Function MapList(Of TSource As {Class, New}, TDestination As {Class, New})(realObjects As List(Of TSource), Optional alreadyInitializedObjects As Dictionary(Of Object, Object) = Nothing) As List(Of TDestination)
        Dim mappedEntities As New List(Of TDestination)()
        For Each currentRealObject As TSource In realObjects
            Dim currentMappedItem As TDestination = Me.Map(Of TSource, TDestination)(currentRealObject, alreadyInitializedObjects:=alreadyInitializedObjects)
            mappedEntities.Add(currentMappedItem)
        Next

        Return mappedEntities
    End Function

End Class