Cost of Raising an Exception

Here’s an interesting bit of code that highlights the true costs when raising exceptions:

Sub Main()

     Dim sw As New Stopwatch
     sw.Start()
     Dim obj As String
     For i As Integer = 0 To 10000
         Try
             Dim myObj As Object = Guid.NewGuid

             obj = myObj

         Catch ex As Exception

         End Try
     Next

     sw.Stop()
     Console.WriteLine(sw.ElapsedMilliseconds)
     sw.Reset()
     sw.Start()
     For i As Integer = 0 To 10000
         Try
             Dim myObj As Object = Guid.NewGuid

             obj = CType(myObj, Guid).ToString

         Catch ex As Exception

         End Try

     Next

     sw.Stop()
     Console.WriteLine(sw.ElapsedMilliseconds)
     Console.ReadLine()
End Sub

The timings on my laptop were 30000ms for loop 1 i.e. where we’re trying to cause exceptions and just 6ms for loop 2 where we casting types correctly!

Filestreams and freetext search

File streams in SQL 2008 provide a nice way of storing large blobs of data in the database without impacting database size. However creating them manually is not something that interests most programmers who want to use SMO.

So how can we create these through code?

The first thing you need to do is enable FILESTREAMS if they’re not already. Do this from SQL Server Configuration Manager:

Select Properties from the action menu

Enable Filestreams & restart the database if needed.

You now need to enable Filestream access (Server Properties->Advanced) on the server from SQL Management studio.

We’re now ready to start creating a database with a filestream and some tables:

Create The Database:

You can look at the article by S.Vinothkumar or use the code based on his work below:

Sub CreateDatabaseWithStream(ByVal dbName As String, ByVal serverName As String, ByVal dbPath As String)

    Dim mdfFile As String = IO.Path.Combine(dbPath, dbName & ".mdf")
    Dim ldfFile As String = IO.Path.Combine(dbPath, dbName & "_log.ldf")
    Dim FilesDir As String = IO.Path.Combine(dbPath, dbName & "_filestream")

    Dim server As New Microsoft.SqlServer.Management.Smo.Server(serverName)
    Dim database As Database = New Database(server, dbName)
    database.FileGroups.Add(New FileGroup(database, "PRIMARY"))
    database.FileGroups.Add(New FileGroup(database, "FileStream" & dbName, True))
    Dim dtPrimary As DataFile = New DataFile(database.FileGroups("PRIMARY"), "PriValue", mdfFile)
    database.FileGroups("PRIMARY").Files.Add(dtPrimary)
    database.FileGroups("FileStream" & dbName).Files.Add(New DataFile(database.FileGroups("FileStream" & dbName), "FileStream" & dbName & " Values", FilesDir))
    Dim logFile As LogFile = New LogFile(database, "PriValue_log", ldfFile)
    database.LogFiles.Add(logFile)
    database.Create()
    database.Refresh()
End Sub

Add A Table:

we now need to create a table with some columns, a data column and a type column.

The function below does this, the important things to remember are to set the id as a unqiue key.

Sub CreateTable(ByVal db As Database, ByVal tableName As String)

      Dim tb As New Table(db, tableName)
      Dim col As Column
      col = New Column(tb, "rowid", DataType.UniqueIdentifier)
      col.Identity = False
      col.Nullable = False
      col.RowGuidCol = True
      Dim Dc As DefaultConstraint = col.AddDefaultConstraint("DF_RowGuid")
      Dc.Text = "NEWID()"
      tb.Columns.Add(col)
      tb.Create()

      Dim idx As New Index(tb, "AK_" & Now.Ticks)
      Dim icol1 As IndexedColumn
      icol1 = New IndexedColumn(idx, "rowid", False)
      idx.IndexedColumns.Add(icol1)
      idx.IndexKeyType = IndexKeyType.DriUniqueKey
      idx.IsClustered = True
      idx.Create()

      col = New Column(tb, "filedata", DataType.VarBinaryMax, True)
      col.Identity = False
      col.Nullable = True
      tb.Columns.Add(col)
      col = New Column(tb, "ext", DataType.VarChar(255))
      col.Identity = False
      col.Nullable = True
      tb.Columns.Add(col) 
      tb.Alter()
      db.Alter()
  End Sub

Create The Text Index:

Once we have our database then it’s time to create a our freetext index. You can do this simply by create a new freetext catalog for our new database, this is well documented here but in summary is creates a new catalog against the specified database.

dim ftc as New FullTextCatalog(db, "Test_Catalog")
ftc.IsDefault = True
ftc.Create()
Dim fti As FullTextIndex
fti = New FullTextIndex(tb)
Dim ftic As FullTextIndexColumn
ftic = New FullTextIndexColumn(fti, "filedata")
ftic.TypeColumnName = "ext"
ftic.Language = "English"
fti.IndexedColumns.Add(ftic)
fti.ChangeTracking = ChangeTracking.Automatic
fti.UniqueIndexName = idx.Name
fti.CatalogName = "Test_Catalog"
fti.Create()

So with everything setup the next thing to do is load your data into the table – make sure that you set the extension and the text indexing engine will do the rest. Then just use contains TSQL contains to mine the text catalog.

DECLARE @SearchWord nvarchar(30)
SET @SearchWord = 'test'
SELECT id,name
FROM files
WHERE CONTAINS(filedata, @SearchWord);

Rail Time!

If you used MyRail for the iPhone then check this link out

http://railtimes.it-dev.co.uk/railtimes/

It’s a web app with a simple interface that I knocked together to make searching train times simple and flexible.

DavCopy for Humyo

Humyo is so cool - the server side support for image and video is so neat.

After using it for a weeks or two I realised that the client saps bandwidth on an already slow ADSL connection and doesn’t do what I want it to do. So for those interested, I’ve built my own sync app that runs on .NET 2.0 and uses the WebDAV interface over https.

What it does do:

• Local folder/file to remote folder
• Remote folder to local folder
• Sets time stamps correctly (why do WebDAV interfaces not support this!!!)
• Tells you what it’s doing
• It’s free

What it doesn’t do

• Have a UI – it’s command line
• Cost anything for non commercial users
• Make tea,coffee or any other hot or cold beverage

DavCopy Link

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