Render HTML To Image

This is a piece of .NET 2.0 code I’ve been toying with for ages and finally had some time to get it working – thanks to the many other people who’ve posted articles relating to this task. Lots of people seem to be selling components that do this but it’s such a fundamentally basic task I reckon it’s better to share this with everyone as it has so many uses.

 

Imports mshtml

Imports System.Runtime.InteropServices

Public Class getWebPageThumb

    Dim MyWebBrowser As System.Windows.Forms.WebBrowser = _

            New System.Windows.Forms.WebBrowser

 

    <Guid("3050f669-98b5-11cf-bb82-00aa00bdce0b"), _

    InterfaceType(ComInterfaceType.InterfaceIsIUnknown), _

    ComVisible(True), _

    ComImport()> _

    Interface IHTMLElementRender

        Sub DrawToDC(<[In]()> _

        ByVal hDC As IntPtr)

        Sub SetDocumentPrinter(<[In](), MarshalAs(UnmanagedType.BStr)> _

        ByVal bstrPrinterName As String, <[In]()> _

        ByVal hDC As IntPtr)

    End Interface

 

    Public Function getImage(ByVal url As String, ByVal thumbSize As Size) As Image

        Dim imageThumb As Image = Nothing

        MyWebBrowser.Url = New Uri(url)

        Dim startTime As Date = Now

 

        While MyWebBrowser.ReadyState <> WebBrowserReadyState.Complete

            Application.DoEvents()

            Threading.Thread.Sleep(100)

            If Now.Subtract(startTime).TotalMinutes > 1 Then Exit While

        End While

        MyWebBrowser.Size = New Size(800, 1200)

        Dim document As IHTMLDocument2 = _

                CType(MyWebBrowser.Document.DomDocument, IHTMLDocument2)

 

        If Not (document Is Nothing) Then

            Dim element As IHTMLElement = CType(document.body, IHTMLElement)

            If Not (element Is Nothing) Then

 

                Dim render As hostingForm.IHTMLElementRender _

                        = CType(element, hostingForm.IHTMLElementRender)

 

                If Not (render Is Nothing) Then

                    Dim bm As Bitmap = New Bitmap(1024, 1024)

                    Dim g As System.Drawing.Graphics

                    g = System.Drawing.Graphics.FromImage(bm)

                    g.SmoothingMode = Drawing2D.SmoothingMode.HighQuality

                    Try

                        Dim hdcDestination As IntPtr = g.GetHdc

                        render.DrawToDC(hdcDestination)

                        Dim hdcMemory As IntPtr = GDI32.CreateCompatibleDC(hdcDestination)

Dim bitmap As IntPtr = GDI32.CreateCompatibleBitmap(hdcDestination, _

MyWebBrowser.ClientRectangle.Width, MyWebBrowser.ClientRectangle.Height)

                        If Not (bitmap = IntPtr.Zero) Then

                            Dim hOld As IntPtr = CType(GDI32.SelectObject(hdcMemory, bitmap), IntPtr)

                            GDI32.BitBlt(hdcMemory, 0, 0, MyWebBrowser.ClientRectangle.Width, _

MyWebBrowser.ClientRectangle.Height, hdcDestination, 0, 0, _

CType(GDI32.TernaryRasterOperations.SRCCOPY, Integer))

                            GDI32.SelectObject(hdcMemory, hOld)

                            GDI32.DeleteDC(hdcMemory)

                            Dim myBmp As Bitmap = New Bitmap(1024, 1024)

                            g.ReleaseHdc(hdcDestination)

                            g.DrawImage(myBmp, New Point(0, 0))

                            imageThumb = bm.GetThumbnailImage(thumbSize.Width, thumbSize.Height, Nothing, Nothing)

                        End If

                    Finally

                        CType(g, IDisposable).Dispose()

                    End Try

                End If

            End If

        End If

        Return imageThumb

    End Function

 

End Class

 

Class GDI32

 

    Enum TernaryRasterOperations As Integer

        SRCCOPY = 13369376 'dest = source

        SRCPAINT = 15597702 'dest = source OR dest

        SRCAND = 8913094 'dest = source AND dest

        SRCINVERT = 6684742 'dest = source XOR dest

        SRCERASE = 4457256 'dest = source AND (NOT dest )

        NOTSRCCOPY = 3342344 'dest = (NOT source)

        NOTSRCERASE = 1114278 'dest = (NOT src) AND (NOT dest)

        MERGECOPY = 12583114 'dest = (source AND pattern)

        MERGEPAINT = 12255782 'dest = (NOT source) OR dest

        PATCOPY = 15728673 'dest = pattern

        PATPAINT = 16452105 'dest = DPSnoo

        PATINVERT = 5898313 'dest = pattern XOR dest

        DSTINVERT = 5570569 'dest = (NOT dest)

        BLACKNESS = 66 'dest = BLACK

        WHITENESS = 16711778 'dest = WHITE

 

    End Enum

 

    Public Shared SRCCOPY As Integer = &HCC0020

    ' BitBlt dwRop parameter

 

    <DllImport("gdi32.dll")> _

    Public Shared Function BitBlt(ByVal hObject As IntPtr, ByVal nXDest As Integer, ByVal nYDest As Integer, ByVal nWidth As Integer, ByVal nHeight As Integer, ByVal hObjectSource As IntPtr, ByVal nXSrc As Integer, ByVal nYSrc As Integer, ByVal dwRop As Integer) As Boolean

    End Function

    <DllImport("gdi32.dll")> _

            Public Shared Function CreateCompatibleBitmap(ByVal hDC As IntPtr, ByVal nWidth As Integer, ByVal nHeight As Integer) As IntPtr

    End Function

    <DllImport("gdi32.dll")> _

    Public Shared Function CreateCompatibleDC(ByVal hDC As IntPtr) As IntPtr

    End Function

    <DllImport("gdi32.dll")> _

    Public Shared Function DeleteDC(ByVal hDC As IntPtr) As Boolean

    End Function

    <DllImport("gdi32.dll")> _

    Public Shared Function DeleteObject(ByVal hObject As IntPtr) As Boolean

    End Function

    <DllImport("gdi32.dll")> _

    Public Shared Function SelectObject(ByVal hDC As IntPtr, ByVal hObject As IntPtr) As IntPtr

    End Function

 

End Class 'GDI32

 

SQL 2005 CLR Pivot Update

Here's an update to my SQL Pivot function but running it on the database under the CLR. Unfortunately you have to push in the source sql which is a bit naff but quite effective. You'll need to enable the EXTERNAL permissions and enable the CLR.

EXEC sp_configure 'clr enabled', 1

RECONFIGURE WITH OVERRIDE

ALTER DATABASE SET TRUSTWORTHY ON

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server

Partial Public Class StoredProcedures
<microsoft.sqlserver.server.sqlprocedure()> _
Public Shared Sub CLRPivot(ByVal srcQuery As String, ByVal pivotColumn As String, ByVal pivotKeyColumn As String, ByVal pivotValueColumn As String, ByVal separatorStr As String)
Dim p As SqlPipe = SqlContext.Pipe
Dim connection As SqlConnection = New SqlConnection("context connection=true")
Dim connection2 As SqlConnection = New SqlConnection("Server=localhost;Database=;Trusted_Connection=True;")
connection2.Open()
Try
connection.Open()
Dim sqlCommand As SqlCommand = New SqlCommand(srcQuery)
sqlCommand.Connection = connection
Dim sqlReader As SqlDataReader = sqlCommand.ExecuteReader
Dim source As DataTable = New DataTable
source.Load(sqlReader)
Dim dest As DataTable = New DataTable
dest.Columns.Add(pivotColumn)
For Each r As DataRow In source.Rows
If r.IsNull(pivotKeyColumn) = False Then
If dest.Columns.Contains(r.Item(pivotKeyColumn).ToString) = False Then dest.Columns.Add(r.Item(pivotKeyColumn).ToString)
End If
Next
Dim dv As DataView = New DataView(source, "", pivotColumn, DataViewRowState.CurrentRows)
Dim curPivotRowItem As String = Nothing
Dim pivotedRow As DataRow = Nothing
For Each r As DataRowView In dv
If curPivotRowItem <> r.Item(pivotColumn).ToString Then
curPivotRowItem = r.Item(pivotColumn).ToString
pivotedRow = dest.NewRow
dest.Rows.Add(pivotedRow)
pivotedRow.Item(pivotColumn) = curPivotRowItem
End If
If IsDBNull(r.Item(pivotValueColumn)) = False Then
Try
pivotedRow.Item(r.Item(pivotKeyColumn).ToString) += r.Item(pivotValueColumn).ToString & separatorStr
Catch ex As Exception
End Try
End If
Next
Dim tmpName As String = "#" & Guid.NewGuid.ToString.Replace("-", "")
Dim sql As String = "CREATE TABLE " & tmpName
sql += "("
For Each col As DataColumn In dest.Columns
sql += String.Format("[{0}] varchar(2000),", col.ColumnName)
Next
sql = sql.Substring(0, sql.Length - 1)
sql += ")"
Dim createTemp As SqlCommand = New SqlCommand(sql)
createTemp.Connection = connection2
createTemp.ExecuteNonQuery()

Dim Copy As SqlBulkCopy = New SqlBulkCopy(connection2)
Copy.DestinationTableName = tmpName
Copy.WriteToServer(dest)
Dim retReaderCommand As SqlCommand = New SqlCommand(String.Format("SELECT * FROM [{0}]", tmpName))
retReaderCommand.Connection = connection2
p.Send(retReaderCommand.ExecuteReader)
Catch ex As Exception
p.Send(ex.Message)
End Try
End Sub
End Class

SQL 2005 DataTable Pivots

When I saw the SQL 2005 PIVOT function I thought great at last an easy way to PIVOT on the DB, but to be honest it sucks for anything but basic operations. I then thought I'd have ago at integration services - nice piece of software but still falls short in terms of the PIVOT function.

One of the fundamental issues with pivotting large lumps of data is that you don't know the number of columns you're going to end up.

The only sane way to do this is to code it into a custom assembly.

This is my take on it wrapped into a function.

Public Function PivotDataTable(ByVal source As DataTable, ByVal pivotCol As String, ByVal keyCol As String, ByVal pivotValueCol As String, ByVal separatorStr As String) As DataTable
Dim dest As DataTable = New DataTable
dest.Columns.Add(pivotCol)
For Each r As DataRow In source.Rows
If r.IsNull(keyCol) = False Then
If dest.Columns.Contains(r.Item(keyCol)) = False Then
dest.Columns.Add(r.Item(keyCol))
End If
End If
Next
Dim dv As DataView = New DataView(source, "", pivotCol, DataViewRowState.CurrentRows)
Dim curPivotRowItem As String = Nothing
Dim pivotedRow As DataRow = Nothing
For Each r As DataRowView In dv
If curPivotRowItem <> r.Item(pivotCol).ToString Then
curPivotRowItem = r.Item(pivotCol).ToString
pivotedRow = dest.NewRow
dest.Rows.Add(pivotedRow)
pivotedRow.Item(pivotCol) = curPivotRowItem
End If
If IsDBNull(r.Item(pivotValueCol)) = False Then
pivotedRow.Item(r.Item(keyCol)) += r.Item(pivotValueCol) & separatorStr
End If
Next
Return dest
End Function

The function is based around the pretense that all fields are nvarchar of some sort. As with all Pivot examples I've seen, the weekness in the functions is the use case scenario, so i'll try and help you all with a decent example.

Take a source table that looks like this:

TableIDColumn	PivotColumn	PivotkeyCol	pivotValueColumn
1	2	Bar	a
2	3	BarFoo	b
3	3	Foo	c
4	5	Bar	d
5	5	FooBar	e

we're now going to pivot the table about the PivotColumn column such that a new column will be created for every distinct entry in PivotkeyCol column and the value in the pivotValueColumn will be placed in the new row and column entry.
so that we will end up with the following table:

PivotColumn	Bar	BarFoo	Foo	FooBar
2	a	null	null	null
3	null	b	c	null
5	d	null	null	e