Mythical Man Moth

In recently needed to print labels/tickets on a series of ZEBRA printers.

This required some very specific sized labels/tickets. At first I designed everything in Crystal Reports, and everything previewed perfectly,until… I actually had to print it to the printer. The printer driver plays a very specific role, and can cause some serious headaches.

Eventually I resorted to using the .Net PrintDocument component, that allows you to specify the paper size and print to the printer on a lower level than Crystal Reports. This worked very nicely for one label/ticket, but I wanted to print a range of labels/tickets.

The Printdocument has a very strange way of processing the print command. In it’s PrintPage event, you set the e.HasMorePages property if you want to print more than one page, and the PrintPage method loops until e.HasMorePages is false. Talk about a messing with your train of thought.

What I needed was a way to say to the PrintDocument that it should print the next page, and then pass my ticket object with the next ticket’s information. Easier said than done. Fortunately, I stumbled onto this forum discussion. This code creates a custom class that inherits from the PrintDocument and give you a nifty .NextPage sub. This let’s the PrintDocument know that you’re moving onto the next page. I’m reposting it here for your convenience.

Printers sure are a lot of fun to try and figure out…depends on your opinion of fun

Class code:

Imports System.Threading

Imports System.Drawing.Printing

Public Class PrintDoc

    Inherits PrintDocument

    Private W1, W2 As EventWaitHandle

    Private PagesPrinted As Int32

    Public Event PrintPages(ByVal Sender As Object, ByRef e As System.Drawing.Printing.PrintPageEventArgs)

    Private T As Thread

    Private eRef As System.Drawing.Printing.PrintPageEventArgs

    Protected Overrides Sub OnPrintPage(ByVal e As System.Drawing.Printing.PrintPageEventArgs)

        eRef = e

        If PagesPrinted = 0 Then T.Start() Else W2.Set()

        PagesPrinted += 1

        W1.WaitOne()

    End Sub

    Private Sub PrintDoc_BeginPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles Me.BeginPrint

        W1 = New EventWaitHandle(False, EventResetMode.AutoReset)

        W2 = New EventWaitHandle(False, EventResetMode.AutoReset)

        T = New Thread(AddressOf RaisePrintPages)

        PagesPrinted = 0

    End Sub

    Public Sub NextPage()

        eRef.HasMorePages = True

        W1.Set() : W2.WaitOne()

    End Sub

    Private Sub RaisePrintPages()

        RaiseEvent PrintPages(Me, eRef)

        W1.Set()

    End Sub

    Private Sub PrintDoc_EndPrint(ByVal sender As Object, ByVal e As System.Drawing.Printing.PrintEventArgs) Handles Me.EndPrint

        W1.Close() : W2.Close()

    End Sub

End Class

Private Sub PrintDoc1_PrintPages(ByVal Sender As Object, ByRef e As System.Drawing.Printing.PrintPageEventArgs) Handles PrintDoc1.PrintPages

    e.Graphics.DrawString("Hello", New Font("Arial", 12, FontStyle.Regular), Brushes.Black, 100, 100)

    PrintDoc1.NextPage()

    e.Graphics.DrawString("Hello Again", New Font("Arial", 12, FontStyle.Regular), Brushes.Black, 100, 100)

End Sub

Ever wanted to search sql server for a specific value, but had no idea in which table or field it would be?

Well, I found the solution, it works well on smaller databases, but it’s author Narayana Vyas Kondreddi, warns that it will be slow on bigger databases.

Here is the code:

CREATE PROC SearchAllTables
(
@SearchStr nvarchar(100)
)
AS
BEGIN
-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT
CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
SET NOCOUNT ON
DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
WHILE @TableName IS NOT NULL
BEGIN
SET @ColumnName = ''
SET @TableName =
(
SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM     INFORMATION_SCHEMA.TABLES
WHERE         TABLE_TYPE = 'BASE TABLE'
AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
AND    OBJECTPROPERTY(
OBJECT_ID(
QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
), 'IsMSShipped'
) = 0
)
WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
BEGIN
SET @ColumnName =
(
SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM     INFORMATION_SCHEMA.COLUMNS
WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
AND    TABLE_NAME    = PARSENAME(@TableName, 1)
AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND    QUOTENAME(COLUMN_NAME) > @ColumnName
)
IF @ColumnName IS NOT NULL
BEGIN
INSERT INTO #Results
EXEC
(
'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
FROM ' + @TableName + ' (NOLOCK) ' +
' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
)
END
END
END
SELECT ColumnName, ColumnValue FROM #Results
END

-- To search all columns of all tables in Pubs database for the keyword "Computer"
EXEC SearchAllTables 'Computer'
GO 

In my previous post I discussed the challenge of deploying your application.

Since then, I have been hard at work contemplating and designing a proof of concept for the ultimate deployment technique. I guess the easiest solution would be just to create a web-based application, application updates can then happen from anywhere and at anytime.

Unfortunately web-based applications aren’t always an option, so what I’ve come up with so far is a web service solution. I’m still working on the structure of the service, but basically what would happen is the following:

• User click the "Check for Update" button in their application
• The application connects to the web service using the following criteria:
  • Application Name
  • Application Version
    Note: Later web service version can include fields like company name, user name etc. for licensing purposes.
• The web service takes the supplied information, and checks against a database whether a newer version for the Application exists or if the user is using the latest version.
• If a newer version exists, it retrieves the following information about that version:
  • Version Description
  • List of changes
  • List of SQL scripts
• The application will then download all the SQL scripts required for the release, and send the information about the release back to the client
• Once the SQL scripts has been downloaded it will be automatically run on the client database, and the status of the script( Successful, Failed) will be reported back to the web service, in order for the support team to respond appropriately.
• If no scripts failed, the standard click once deployment method will then be called to update the application with the latest assemblies.

So there you have it. Still a work in progress, but I think overall not a bad concept. I’ll report back on my progress as I go, maybe even throw in a demonstration.