.NET Share

Decrypt SQL 2005 long stored functions (scalar, inline table-valued, multi statement table-valued)

2010-04-19T09:08:00.008+07:00

I needed to decrypt an encrypted stored function in SQL Server 2005 and I found this promising T-SQL script from Omri Bahat. Omri has done a great job on creating this script. This script has bugs on stored function DML creation and printing decrypted text over 4,000 characters. I have fixed these two bugs so this script can be used for decrypting long stored function. My modification has small flaw. My script can't differ inline and multi statement table-valued, so user must tell the script whether the function is inline or multi statement (@IsInlineTableValued).

/*==================================================================================

NAME:          Decrypt SQL 2005 stored procedures, functions, views, and triggers

DESCRIPTION:            HEADS UP: In order to run this script you must log in
                      to the server in DAC mode: To do so, type
                      ADMIN: as your server name and use the "sa"
                      or any other server admin user with the appropriate password.
                      Example: sqlcmd -S (local) -A -E -i C:\Decrypt.sql -o C:\Output.txt
                    
                      CAUTION! DAC (dedicated admin access) will kick out all other
                      server users.
                    
                      The script below accepts an object (schema name + object name)
                      that were created using the WITH ENCRYPTION option and returns
                      the decrypted script that creates the object. This script
                      is useful to decrypt stored procedures, views, functions,
                      and triggers that were created WITH ENCRYPTION.
                    
                      The algorithm used below is the following:
                      1. Check that the object exists and that it is encrypted.
                      2. In order to decrypt the object, the script ALTER (!!!) it
                      and later restores the object to its original one. This is
                      required as part of the decryption process: The object
                      is altered to contain dummy text (the ALTER uses WITH ENCRYPTION)
                      and then compared to the CREATE statement of the same dummy
                      content.
                    
                      Note: The object is altered in a transaction, which is rolled
                      back immediately after the object is changed to restore
                      all previous settings.
                    
                      3. A XOR operation between the original binary stream of the
                      enrypted object with the binary representation of the dummy
                      object and the binary version of the object in clear-text
                      is used to decrypt the original object.
                 

USER PARAMETERS:        @ObjectOwnerOrSchema
                      @ObjectName
                      @IsInlineTableValued

RESULTSET:              NA

RESULTSET SORT:         NA

USING TABLES/VIEWS:     sys.sysobjvalues
                      syscomments

REVISIONS

DATE         DEVELOPER          DESCRIPTION OF REVISION             VERSION
=========    ===============    =================================   ===========
01/01/2007   Omri Bahat         Initial release                     1.00
19/04/2010   Alex Wijoyo        Support function and size>4000      1.10

==================================================================================
Copyright  SQL Farms Solutions, www.sqlfarms.com. All rights reserved.
This code can be used only for non-redistributable purposes.
The code can be used for free as long as this copyright notice is not removed.
==================================================================================*/


DECLARE @ObjectOwnerOrSchema NVARCHAR(128)
DECLARE @ObjectName NVARCHAR(128)
DECLARE @IsInlineTableValued BIT
USE NORTHWIND;
SET @ObjectOwnerOrSchema = 'dbo'
SET @ObjectName = 'Foo'
SET @IsInlineTableValued=0

DECLARE @IsTableValued BIT
DECLARE @i INT
DECLARE @ObjectDataLength INT
DECLARE @ContentOfEncryptedObject NVARCHAR(MAX)
DECLARE @ContentOfDecryptedObject NVARCHAR(MAX)
DECLARE @ContentOfFakeObject NVARCHAR(MAX)
DECLARE @ContentOfFakeEncryptedObject NVARCHAR(MAX)
DECLARE @ObjectType NVARCHAR(128)
DECLARE @ObjectID INT
DECLARE @DecryptedChar NCHAR
DECLARE @DecryptedLine NVARCHAR(MAX)

SET NOCOUNT ON

SET @IsTableValued=0

SET @ObjectID = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')

-- Check that the provided object exists in the database.
IF @ObjectID IS NULL
BEGIN
  RAISERROR('The object name or schema provided does not exist in the database', 16, 1)
  RETURN
END

-- Check that the provided object is encrypted.
IF NOT EXISTS(SELECT TOP 1 * FROM syscomments WHERE id = @ObjectID AND encrypted = 1)
BEGIN
  RAISERROR('The object provided exists however it is not encrypted. Aborting.', 16, 1)
  RETURN
END



-- Determine the type of the object
IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'PROCEDURE') IS NOT NULL
  SET @ObjectType = 'PROCEDURE'
ELSE
  IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'TRIGGER') IS NOT NULL
      SET @ObjectType = 'TRIGGER'
  ELSE
      IF OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']', 'VIEW') IS NOT NULL
          SET @ObjectType = 'VIEW'
      ELSE
          SET @ObjectType = 'FUNCTION'

IF @ObjectType='FUNCTION'          
  SELECT @IsTableValued=CASE WHEN COUNT(*)>0 THEN 1 ELSE 0 END
  FROM INFORMATION_SCHEMA.ROUTINES
  WHERE ROUTINE_SCHEMA=@ObjectOwnerOrSchema and ROUTINE_NAME=@ObjectName and DATA_TYPE='TABLE'

-- Get the binary representation of the object- syscomments no longer holds
-- the content of encrypted object.
SELECT TOP 1 @ContentOfEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
      AND valclass = 1 --and subobjid = 1


SET @ObjectDataLength = DATALENGTH(@ContentOfEncryptedObject)/2


-- We need to alter the existing object and make it into a dummy object
-- in order to decrypt its content. This is done in a transaction
-- (which is later rolled back) to ensure that all changes have a minimal
-- impact on the database.
SET @ContentOfFakeObject = N'ALTER ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N']'+CASE @ObjectType WHEN 'FUNCTION' THEN '() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1;END;' ELSE ' WITH ENCRYPTION AS' END

-- Modify table-valued function DML
IF @IsTableValued=1
BEGIN
  IF @IsInlineTableValued=1
  BEGIN
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'INT',N'TABLE')
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'BEGIN RETURN 1;END;',N'RETURN(SELECT 1 c);')
  END
  ELSE
  BEGIN
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'INT',N'@t TABLE(c INT)')
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'RETURN 1;',N'RETURN;')
  END
END

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength BEGIN         IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength                 SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)         ELSE                 SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) END  -- Since we need to alter the object in order to decrypt it, this is done -- in a transaction SET XACT_ABORT OFF BEGIN TRAN  EXEC(@ContentOfFakeObject)  IF @@ERROR <> 0
      ROLLBACK TRAN

-- Get the encrypted content of the new "fake" object.
SELECT TOP 1 @ContentOfFakeEncryptedObject = imageval
FROM sys.sysobjvalues
WHERE objid = OBJECT_ID('[' + @ObjectOwnerOrSchema + '].[' + @ObjectName + ']')
      AND valclass = 1 --and subobjid = 1


IF @@TRANCOUNT > 0
      ROLLBACK TRAN

-- Generate a CREATE script for the dummy object text.
SET @ContentOfFakeObject = N'CREATE ' + @ObjectType + N' [' + @ObjectOwnerOrSchema + N'].[' + @ObjectName + N']'+CASE @ObjectType WHEN 'FUNCTION' THEN '() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1;END;' ELSE ' WITH ENCRYPTION AS' END

-- Modify table-valued function DML
IF @IsTableValued=1
BEGIN
  IF @IsInlineTableValued=1
  BEGIN
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'INT',N'TABLE')
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'BEGIN RETURN 1;END;',N'RETURN(SELECT 1 c);')
  END
  ELSE
  BEGIN
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'INT',N'@t TABLE(c INT)')
      SET @ContentOfFakeObject = REPLACE(@ContentOfFakeObject,N'RETURN 1;',N'RETURN;')
  END
END

WHILE DATALENGTH(@ContentOfFakeObject)/2 < @ObjectDataLength BEGIN         IF DATALENGTH(@ContentOfFakeObject)/2 + 4000 < @ObjectDataLength                 SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', 4000)         ELSE                 SET @ContentOfFakeObject = @ContentOfFakeObject + REPLICATE(N'-', @ObjectDataLength - (DATALENGTH(@ContentOfFakeObject)/2)) END  SET @i = 1  --Fill the variable that holds the decrypted data with a filler character SET @ContentOfDecryptedObject = N''  WHILE DATALENGTH(@ContentOfDecryptedObject)/2 < @ObjectDataLength BEGIN         IF DATALENGTH(@ContentOfDecryptedObject)/2 + 4000 < @ObjectDataLength                 SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', 4000)         ELSE                 SET @ContentOfDecryptedObject = @ContentOfDecryptedObject + REPLICATE(N'A', @ObjectDataLength - (DATALENGTH(@ContentOfDecryptedObject)/2)) END  SET @DecryptedLine=N''  WHILE @i <= @ObjectDataLength BEGIN         --xor real & fake & fake encrypted         SET @DecryptedChar=NCHAR(                         UNICODE(SUBSTRING(@ContentOfEncryptedObject, @i, 1)) ^                         (                                 UNICODE(SUBSTRING(@ContentOfFakeObject, @i, 1)) ^                                 UNICODE(SUBSTRING(@ContentOfFakeEncryptedObject, @i, 1))                         ))                 IF @DecryptedChar=NCHAR(10)         BEGIN             PRINT @DecryptedLine             SET @DecryptedLine=N''                     END         ELSE             IF @DecryptedChar<>NCHAR(13)
              SET @DecryptedLine=@DecryptedLine+@DecryptedChar

      SET @ContentOfDecryptedObject = STUFF(@ContentOfDecryptedObject, @i, 1,@DecryptedChar)

      SET @i = @i + 1
END
PRINT @DecryptedLine

-- PRINT the content of the decrypted object
--PRINT(@ContentOfDecryptedObject)

Optimize SQL Like Comparison for Retrieving Specific or All Result

2009-10-10T10:45:00.007+07:00

My colleague found a strange case with his SQL. The SQL took more than two minutes to execute.

declare @Num int;
declare @Date datetime;
declare @CategoryId varchar(255);
declare @ProductionId varchar(50);
declare @UserId varchar(50);
select @Num=-1,@Date=getdate(),@CategoryId='All',@ProductionId='All',@UserId='ALEX';
select distinct (ROW_NUMBER() OVER (ORDER BY v.ProductVariantId ASC)+1)*-1 as FGLongtermPlanDetailNum,@Num as LongTermPlanNum,v.Warehouse_Dep,v.ProductVariantId,v.ProductVariantName,u.UnitId,isnull(SOQty,0) as TotalQtySO,isnull(fg.FGStock,0) as TotalFGStock,0 as FGRequirementQty,p.Weight,v.WeightPerBatch,@UserId as UpdateUserId,GETDATE() as UpdateTime,@UserId as CreateUserId,GETDATE() as CreateTime
from productvariant as v inner join productunit as pu on v.productid=pu.productid and pu.Warehouse_Dep=v.Warehouse_Dep inner join product p on p.productid=pu.productid and pu.Warehouse_Dep=p.Warehouse_Dep and (CategoryId like case when @CategoryId='All' then '%' else @CategoryId end) and (ProductionId like case when @ProductionId='All' then '%' else @ProductionId end)
inner join unit as u on pu.unitid=u.unitid
left join (select UnitId,VariantId,ProductId,Warehouse_Dep,sum(LeftQuantity) SOQty from soproduct where LeftQuantity<>0 and SODate<>'GR')
group by ProductVariantId,UnitId,ProductId) fg on fg.ProductId=v.ProductId and fg.ProductVariantId=v.ProductVariantId and v.Warehouse_Dep='FG' and u.UnitId=fg.UnitId
where v.Warehouse_Dep='FG' and u.IsExtended=0 and v.IsDisable=0;

But when he hard coded @ProductionId and @CategoryId into 'All' (in bold):

declare @Num int;
declare @Date datetime;
declare @CategoryId varchar(255);
declare @ProductionId varchar(50);
declare @UserId varchar(50);
select @Num=-1,@Date=getdate(),@CategoryId='All',@ProductionId='All',@UserId='ALEX';
select distinct (ROW_NUMBER() OVER (ORDER BY v.ProductVariantId ASC)+1)*-1 as FGLongtermPlanDetailNum,@Num as LongTermPlanNum,v.Warehouse_Dep,v.ProductVariantId,v.ProductVariantName,u.UnitId,isnull(SOQty,0) as TotalQtySO,isnull(fg.FGStock,0) as TotalFGStock,0 as FGRequirementQty,p.Weight,v.WeightPerBatch,@UserId as UpdateUserId,GETDATE() as UpdateTime,@UserId as CreateUserId,GETDATE() as CreateTime
from productvariant as v inner join productunit as pu on v.productid=pu.productid and pu.Warehouse_Dep=v.Warehouse_Dep inner join product p on p.productid=pu.productid and pu.Warehouse_Dep=p.Warehouse_Dep and (CategoryId like case when 'All'='All' then '%' else 'All' end) and (ProductionId like case when 'All'='All' then '%' else 'All' end)
inner join unit as u on pu.unitid=u.unitid
left join (select UnitId,VariantId,ProductId,Warehouse_Dep,sum(LeftQuantity) SOQty from soproduct where LeftQuantity<>0 and SODate<>'GR')
group by ProductVariantId,UnitId,ProductId) fg on fg.ProductId=v.ProductId and fg.ProductVariantId=v.ProductVariantId and v.Warehouse_Dep='FG' and u.UnitId=fg.UnitId
where v.Warehouse_Dep='FG' and u.IsExtended=0 and v.IsDisable=0;

The query only took two seconds to execute. After several trial and error efforts, we finally found the root cause. SQL Server seems to hate our like expression. Like expression used for giving freedom to user whether to retrieve specific or all result. So we change the like operator into equal operator (=) like this (changes are in bold):

declare @Num int;
declare @Date datetime;
declare @CategoryId varchar(255);
declare @ProductionId varchar(50);
declare @UserId varchar(50);
select @Num=-1,@Date=getdate(),@CategoryId='All',@ProductionId='All',@UserId='ALEX';
declare @MyCategoryId varchar(255);
declare @MyProductionId varchar(50);
  set @MyCategoryId=case when @CategoryId='All' then '%' else @CategoryId end;
  set @MyProductionId=case when @ProductionId='All' then '%' else @ProductionId end;

select distinct (ROW_NUMBER() OVER (ORDER BY v.ProductVariantId ASC)+1)*-1 as FGLongtermPlanDetailNum,@Num as LongTermPlanNum,v.Warehouse_Dep,v.ProductVariantId,v.ProductVariantName,u.UnitId,isnull(SOQty,0) as TotalQtySO,isnull(fg.FGStock,0) as TotalFGStock,0 as FGRequirementQty,p.Weight,v.WeightPerBatch,@UserId as UpdateUserId,GETDATE() as UpdateTime,@UserId as CreateUserId,GETDATE() as CreateTime
from productvariant as v inner join productunit as pu on v.productid=pu.productid and pu.Warehouse_Dep=v.Warehouse_Dep inner join product p on p.productid=pu.productid and pu.Warehouse_Dep=p.Warehouse_Dep and (CategoryId=@MyCategoryId or @MyCategoryId='%') and (ProductionId=@MyProductionId or @MyProductionId='%')
inner join unit as u on pu.unitid=u.unitid
left join (select UnitId,VariantId,ProductId,Warehouse_Dep,sum(LeftQuantity) SOQty from soproduct where LeftQuantity<>0 and SODate<>'GR')
group by ProductVariantId,UnitId,ProductId) fg on fg.ProductId=v.ProductId and fg.ProductVariantId=v.ProductVariantId and v.Warehouse_Dep='FG' and u.UnitId=fg.UnitId
where v.Warehouse_Dep='FG' and u.IsExtended=0 and v.IsDisable=0;

And it took only one second to execute, EUREKA. The only changes are:

CategoryId like case when @CategoryId='All' then '%' else @CategoryId end

Changed into:

CategoryId=@MyCategoryId or @MyCategoryId='%'

May be you're wondering why should I redeclare @ProductionId and @CategoryId into @MyProductionId and @MyCategoryId. I need to redeclare the variable for avoiding SQL Server's parameter sniffing disease in stored procedure. Parameter sniffing will cause your stored procedure execute way slower than the bare query. Just follow this rule of thumb for avoiding parameter sniffing: Do not assign values to parameters. Redeclare your parameters into local variables and assign values to local variables instead of parameters.

Generate DataMatrix Barcode from Your .NET Application

2009-09-12T08:59:00.007+07:00

Data Matrix barcode has several advantages over 1D barcodes such as:

Contains more character even alphanumeric.
Has square form for easier layout on printout.
Digital camera friendly even using your old cellphone camera.
Redundant information, you can scan 75% area of the barcode only for scanning.
A little bit omni scan direction.

Too bad my trusted DevExpress XtraReport XRBarcode doesn't have this symbology. Since I can't wait for DevExpress to implement Data Matrix so I decided to find open source solutions. Thanks God, I found external link on Data Matrix Wiki page for iec16022sharp an open source .NET Data Matrix generator library. Since I use VB.NET, I built the iec16022sharp assembly and use it by adding a reference to it. I am glad that iec16022sharp usage is very easy.
Here is my iec16022sharp usage snippet:

...
Imports IEC16022Sharp
...
   Dim aReport As New RptBarcode
   Dim aDataMatrix As New DataMatrix(BindSrcMaster.GetDataValue("BarcodeNum").ToString.Trim)

   HierarchyRefreshDataRow(BindSrcMaster.GetDataRow)
   With aReport
       .CallingForm = Me
       .PbDataMatrix.Image = DMImgUtility.SimpleResizeBmp(aDataMatrix.Image, 6, 0)
       .QueryLoad(Application.StartupPath + "\CustomReport\Barcode.xml", BindSrcMaster)
       .ShowPreview()
   End With
...

The only functions I called from this library is a DataMatrix constructor and a static function DMImgUtility.SimpleResizeBmp. From the DataMatrix constructor I already have a small DataMatrix barcode. For enlarging the generated DataMatrix I just called the SimpleResizeBmp for enlarging it six times with zero pixel border. Hope my post give you an idea on using DataMatrix. Thank you.

Application for Enabling Your WM Camera into a Barcode Scanner

2009-09-02T11:48:00.019+07:00

I was really excited when I found QuickMark (I am not affiliated with QuickMark). This Taiwan company makes 2D and 1D barcode scanner software for Windows Mobile and Symbian. You can find a lot of similar software like QuickMark on the web. But QuickMark has one thing that stands in the crowd. You can call QuickMark using its API from your .NET Compact Framework 2.0 application and get the scan result. I already tested their API and it worked even for my EAN13 1D barcode. For only U$ 10 your WM Camera becomes a barcode scanner yippeee. I have polished their API demo below (sorry the source codes are not colored).
FrmQuickMarkAPIDemo (the form that uses QuickMarkAPI):

Public Class FrmQuickMarkAPIDemo
   Private WithEvents aQuickMarkAPI As New QuickMarkAPI(AddressOf GetMsgString)

   Public Sub New()
       InitializeComponent()
   End Sub

   Private Sub MnuOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuOK.Click
       aQuickMarkAPI.LaunchQuickMark()
   End Sub

   Private Sub GetMsgString(ByVal MsgString As String)
       MsgBox(MsgString)
   End Sub
   'Just an alternative receive the message string using an event
   'Private Sub aQuickMarkAPI_OnGetMsgString(ByVal MsgString As String) Handles aQuickMarkAPI.OnGetMsgString
   '    MsgBox(MsgString)
   'End Sub
End Class

QuickMarkAPIDemo (The API itself):

Imports System.Diagnostics
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
Imports System.IO
Imports Microsoft.WindowsCE.Forms
'QuickMark

'Flow of QuickMark Messenger
'Open msgapi --> Register a window message for inter-application communication (RegisterWindowMessage)
'-->launch QuickMark (Press OK button) --> send a unique message for inter-application communication(HWND_BROADCAST)
' --> QuickMark recevie the handle of msgapi's window. --> Decoding using QuickMark
' --> QuickMark send result to handle. --> msgapi show the result.

Class QuickMarkAPI
    Inherits MessageWindow

    <DllImport("coredll.dll")> _
    Private Shared Function RegisterWindowMessage(ByVal lpString As String) As UInteger
    End Function

    <DllImport("coredll.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
    Private Shared Function PostMessageW( _
     ByVal hWnd As IntPtr, _
     ByVal Msg As UInteger, _
     ByVal wParam As IntPtr, _
     ByVal lParam As IntPtr) As Boolean
    End Function

    Friend Shared ReadOnly HWND_BROADCAST As New IntPtr(65535)

    'a unique message for inter-application communication
    Private RM_QuickMarkMessengerAPIApp As UInt32 = 0

    'Handle to the window whose window procedure will receive the decoded result.
    Public Delegate Sub OnGetMsgStringHandler(ByVal MsgString As String)
    Public DefaultBarcodeType As Integer = 2 'Default is 2D scanning.
    Event OnGetMsgString As OnGetMsgStringHandler
    Private Const WM_COPYDATA As Integer = 74
    Private aGetMsgString As OnGetMsgStringHandler = Nothing
    Public Sub LaunchQuickMark()
        ' Step 1: Get application path of QuickMark from registry.
        Dim szQuickMarkApp As String = ""
        Dim QuickMarkKey As RegistryKey = Registry.CurrentUser
        QuickMarkKey = QuickMarkKey.OpenSubKey("Software\QuickMark", False)
        szQuickMarkApp = QuickMarkKey.GetValue("QuickMarkAppPath").ToString()
        QuickMarkKey.Close()

        ' Step 2: Set default path of QuickMark.
        If szQuickMarkApp.Length = 0 Then
            szQuickMarkApp = "\Program Files\QuickMark\QuickMark.exe"
        End If

        ' Step 3: Launch QuickMark.
        If File.Exists(szQuickMarkApp) Then
            'Launch application
            Dim P As New Process()
            P.StartInfo.FileName = szQuickMarkApp
            P.StartInfo.Verb = "Open"
            P.Start()
            P.WaitForExit(1000)
            'wait 1 second
            'step 4: send RM_QuickMarkMessengerAPIApp to all top windows.
            'parameters: 
            '        HWND hWnd        : HWND_BROADCAST , all top windows.
            '        UINT Msg        : A unique message for inter-application communication.
            '        WPARAM wParam   : Handle to the window whose window procedure will receive the decoded result.
            '        LPARAM lParam    : Set QuickMark application to 1D or 2D scanning.  (1:1D   2:2D)
            PostMessageW(HWND_BROADCAST, RM_QuickMarkMessengerAPIApp, Me.Hwnd, DefaultBarcodeType)
        Else
            MessageBox.Show("QuickMark not found!")
        End If
    End Sub
    'Receive the decoded result.
    'STRUCT COPYDATASTRUCT member
    'dwData:Data types of data.
    '         TYPE_CHAR: The data is a Binary data.
    '         TYPE_WIDECHAR: The data is a Unicode string.
    'lpData:Long pointer to data.
    'cbData:Specifies the size, in bytes, of the data pointed to by the lpData member.
    Public Structure COPYDATASTRUCT
        Public dwData As Integer
        Public cbData As Integer
        Public lpData As IntPtr
    End Structure
    Public Sub New(ByVal aGetMsgString As OnGetMsgStringHandler)
        Me.aGetMsgString = aGetMsgString
        'Register a window message for inter-application communication
        Me.RM_QuickMarkMessengerAPIApp = RegisterWindowMessage("QuickMarkMessengerAPIApplication")
    End Sub

    Protected Overloads Overrides Sub WndProc(ByRef msg As Message)
        Select Case msg.Msg
            Case WM_COPYDATA
                Dim str As String = GetMsgString(msg.LParam)
                'TODO:Add your code here to process with the str.
                'MessageBox.Show(str, "Result")
                If aGetMsgString IsNot Nothing Then
                    aGetMsgString.Invoke(str)
                End If
                RaiseEvent OnGetMsgString(str)
                Exit Select
        End Select
        MyBase.WndProc(msg)
    End Sub

    Public Shared Function GetMsgString(ByVal lParam As IntPtr) As String
        If lParam <> IntPtr.Zero Then
            Dim st As COPYDATASTRUCT = DirectCast(Marshal.PtrToStructure(lParam, GetType(COPYDATASTRUCT)), COPYDATASTRUCT)
            Dim str As String = Marshal.PtrToStringUni(st.lpData)
            Return str
        Else
            Return Nothing
        End If
    End Function
End Class

A SearchDialog Using SqlCeResultSet

2009-08-31T10:04:00.017+07:00

On my previous post My First Journey to Mobile Database Application, I have mentioned using SearchDialog instead of ComboBox. On this post I will explain about the SearchDialog used by us. OK enough talking, let's get to the code.
I have documented almost every lines of our codes (FrmSearchDialog.vb). I hope you get the idea behind the SearchDialog. Here they are:

Imports System.Data
Imports System.Data.SqlServerCe
Public Class FrmSearchDialog
    ''' <summary>
    ''' Connection with connection string retrieved from a dictionary. 
    ''' The dictionary values are loaded from a RSA encrypted text file.
    ''' The connection must be always opened for browsing SqlCeResultSet.
    ''' </summary>
    Protected CconCurrent As New SqlCeConnection(DictConnectionString("SqlCeConnectionString"))
    ''' <summary>
    ''' Command used for searching.
    ''' </summary>
    Protected CcmdSearch As New SqlCeCommand("", CconCurrent)
    ''' <summary>
    ''' ResultSet for storing select result.
    ''' </summary>
    Protected CrsSearch As SqlCeResultSet
    ''' <summary>
    ''' HashTable for storing search result row.
    ''' </summary>
    Protected HashSearchResult As Hashtable = Nothing
    ''' <summary>
    ''' String for storing SQL passed by user.
    ''' </summary>
    Protected strSelect As String = ""
    ''' <summary>
    ''' String for storing Criteria passed by user.
    ''' </summary>
    Protected strCriteria As String = ""
    ''' <summary>
    ''' String for storing strSelect + strCriteriaClause + strCritera + incremental search criteria.
    ''' </summary>
    Protected strSearchSelect As String = ""
    ''' <summary>
    ''' List for storing ColumnTypes string for each column.
    ''' </summary>
    Protected strColumnTypes As New List(Of String)
    ''' <summary>
    ''' Boolean for storing whether strSelect is a group by SQL.
    ''' </summary>
    Protected boolGroupBy As Boolean = False
    ''' <summary>
    ''' String for storing criteria clause where for ordinary select and having for boolGroupBy=true.
    ''' </summary>
    Protected strCriteriaClause As String = " where "
    ''' <summary>
    ''' A static function for opening a SearchDialog for the select statement and Criteria passed by user.
    ''' </summary>
    ''' <param name="SelectSql">The select statement.</param>
    ''' <param name="HashSearchResult">The search result row HashTable reference.</param>
    ''' <param name="DefaultCriteria">The select criteria.</param>
    ''' <param name="AllowSearch">if set to <c>true</c> allow search.</param>
    ''' <returns>True if OK is clicked and false if Cancel is clicked.</returns>
    Shared Function Execute(ByVal SelectSql As String, ByRef HashSearchResult As Hashtable, Optional ByVal DefaultCriteria As String = "", Optional ByVal AllowSearch As Boolean = True) As Boolean
        'Create an instance of FrmSearchDialog because this function is static.
        Dim aFrmSearchDialog As New FrmSearchDialog
        With aFrmSearchDialog
            'Initializing allow search state
            .MnuSearch.Enabled = AllowSearch
            .CmbColumn.Enabled = AllowSearch
            .TxtSearch.Enabled = AllowSearch
            'Initializing the select statement and populating the grid.
            .Initialize(SelectSql, DefaultCriteria)
            'Showing modally and checking for dialog result
            If .ShowDialog() = Windows.Forms.DialogResult.OK Then
                HashSearchResult = .HashSearchResult
                Return True
            Else
                Return False
            End If
        End With
    End Function
    ''' <summary>
    ''' A procedure for initializing the specified select statement and default criteria.
    ''' </summary>
    ''' <param name="SelectSql">The select statement.</param>
    ''' <param name="DefaultCriteria">The default criteria.</param>
    Public Sub Initialize(ByVal SelectSql As String, Optional ByVal DefaultCriteria As String = "")
        'Initializing the DialogResult to None
        DialogResult = Windows.Forms.DialogResult.None
        'Storing the SelectSql and DefaultCriteria to data members
        Me.strSelect = SelectSql
        Me.strCriteria = DefaultCriteria
        'Open the Connection if it is not opened yet.
        If CconCurrent.State <> ConnectionState.Open Then
            CconCurrent.Open()
        End If
        'There are DefaultCriteria.
        If strCriteria.Length > 0 Then
            'Initializing the boolGroupBy by searching for group by clause
            boolGroupBy = strSelect.ToLower.IndexOf("group by") >= 0
            'It is a group by SelectSql
            If boolGroupBy Then
                'Initializing the strCriteriaClause as having for group by select.
                strCriteriaClause = " having "
            End If
            'Constructing the initial Search SQL.
            strSearchSelect = strSelect + strCriteriaClause + strCriteria
        Else 'There are no DefaultCriteria
            'Constructing the initial Search SQL without critera.
            strSearchSelect = strSelect
        End If
        'Setting the CommandText to strSearchSelect and execute a scrollable ResultSet from it.
        CcmdSearch.CommandText = strSearchSelect
        CrsSearch = CcmdSearch.ExecuteResultSet(ResultSetOptions.Scrollable)
        'There are row(s) returned.
        If CrsSearch.Read Then
            'Iterating all columns of ResultSet.
            For i As Integer = 0 To CrsSearch.FieldCount - 1
                'Storing columns names to combo box items.
                CmbColumn.Items.Add(CrsSearch.GetName(i))
                'Storing columns type names
                strColumnTypes.Add(CrsSearch.GetProviderSpecificFieldType(i).ToString)
            Next
        End If
        'Setting the combo box search column to first column.
        If CmbColumn.Items.Count > 0 Then
            CmbColumn.SelectedIndex = 0
        End If
        'Binding DataGrid to ResultSet returned.
        DgSearch.DataSource = CrsSearch
        'There are no incremental search yet, so empty the strSearchSelect.
        strSearchSelect = ""
    End Sub
    ''' <summary>
    ''' A procedure for storing a row of ResultSet in certain position to the HashTable.
    ''' </summary>
    ''' <param name="RowPosition">The row position.</param>
    Sub ResultSetRowToHashTable(ByVal RowPosition As Integer)
        'The row position is valid.
        If RowPosition >= 0 Then
            'Scrolling the ResultSet to RowPosition.
            CrsSearch.ReadAbsolute(RowPosition)
            'Creating an instance of HashTable.
            HashSearchResult = New Hashtable
            'Iterating all columns of ResultSet and storing their values to HashTable.
            For i As Integer = 0 To CrsSearch.FieldCount - 1
                HashSearchResult.Add(CrsSearch.GetName(i), CrsSearch.GetValue(i))
            Next
            'Closing and disposing the ResultSet.
            If Not CrsSearch.IsClosed Then
                CrsSearch.Close()
            End If
            CrsSearch.Dispose()
        Else 'The row position is invalid.
            'Triggering a cancel click.
            MnuCancel_Click(Nothing, Nothing)
        End If
    End Sub

    ''' <summary>
    ''' Handles the DoubleClick event of the GridSearch control for storing current row to HashTable and closing the dialog.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub GridSearch_DoubleClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles DgSearch.DoubleClick
        'The grid current row index is valid.
        If DgSearch.CurrentRowIndex >= 0 Then
            Try
                'Storing ResultSet on current row index to HashTable
                ResultSetRowToHashTable(DgSearch.CurrentRowIndex)
                'Returning OK dialog result and close the form.
                DialogResult = Windows.Forms.DialogResult.OK
                Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    ''' <summary>
    ''' Handles the Click event of the MnuOK control for storing current row to HashTable and closing the dialog.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub MnuOK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuOK.Click
        'The grid current row index is valid.
        If DgSearch.CurrentRowIndex >= 0 Then
            DialogResult = Windows.Forms.DialogResult.OK
            Try
                'Storing ResultSet on current row index to HashTable
                ResultSetRowToHashTable(DgSearch.CurrentRowIndex)
                'Returning OK dialog result and close the form.
                DialogResult = Windows.Forms.DialogResult.OK
                Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

    ''' <summary>
    ''' Handles the Click event of the MnuCancel control for closing the dialog.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub MnuCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuCancel.Click
        'Closing and disposing the ResultSet.
        If Not CrsSearch.IsClosed Then
            CrsSearch.Close()
        End If
        CrsSearch.Dispose()
        'Returning Cancel dialog result and close the form.
        DialogResult = Windows.Forms.DialogResult.Cancel
        Close()
    End Sub

    ''' <summary>
    ''' Handles the Click event of the MnuSearch control for constructing incremental search.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub MnuSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuSearch.Click
        'There is no incremental search on going.
        If strSearchSelect.Length = 0 Then
            'Constructing initial select and concating with and for adding a new search criterion.
            strSearchSelect = strSelect
            If strCriteria.Length > 0 Then
                strSearchSelect += strCriteriaClause + strCriteria + " and "
            Else
                strSearchSelect += strCriteriaClause
            End If
        Else 'There is incremental search on going.
            'Concating with and for adding a new search criterion.
            strSearchSelect += " and "
        End If
        'Current search column in combo box's type is a string.
        If strColumnTypes(CmbColumn.SelectedIndex).EndsWith("String") Then
            strSearchSelect += String.Format("{0} like '%{1}%'", CmbColumn.Text, TxtSearch.Text)
            'Current search column in combo box's type is a DateTime.
        ElseIf strColumnTypes(CmbColumn.SelectedIndex).EndsWith("DateTime") Then
            strSearchSelect += String.Format("convert(nvarchar,{0},3) like '%{1}%'", CmbColumn.Text, TxtSearch.Text)
        Else 'The other type of current search column in combo box.
            strSearchSelect += String.Format("convert(nvarchar,{0}) like '%{1}%'", CmbColumn.Text, TxtSearch.Text)
        End If
        'Closing and disposing the ResultSet before retrieving new ResultSet.
        If Not CrsSearch.IsClosed Then
            CrsSearch.Close()
        End If
        CrsSearch.Dispose()
        'Setting the CommandText to strSearchSelect and execute a scrollable ResultSet from it.
        CcmdSearch.CommandText = strSearchSelect
        CrsSearch = CcmdSearch.ExecuteResultSet(ResultSetOptions.Scrollable)
        'Binding DataGrid to ResultSet returned.
        DgSearch.DataSource = CrsSearch
        'Focusing to search column combo box for next search.
        CmbColumn.Focus()
    End Sub

    ''' <summary>
    ''' Handles the Click event of the MnuReset control for resetting incremental search to initial state.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub MnuReset_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MnuReset.Click
        'Closing and disposing the ResultSet before retrieving new ResultSet.
        If Not CrsSearch.IsClosed Then
            CrsSearch.Close()
        End If
        CrsSearch.Dispose()
        'There are DefaultCriteria.
        If strCriteria.Length > 0 Then
            'Constructing the initial Search SQL.
            strSearchSelect = strSelect + strCriteriaClause + strCriteria
        Else
            'Constructing the initial Search SQL.
            strSearchSelect = strSelect
        End If
        'Setting the CommandText to strSearchSelect and execute a scrollable ResultSet from it.
        CcmdSearch.CommandText = strSearchSelect
        CrsSearch = CcmdSearch.ExecuteResultSet(ResultSetOptions.Scrollable)
        'Binding DataGrid to ResultSet returned.
        DgSearch.DataSource = CrsSearch
        'There are no incremental search yet, so empty the strSearchSelect.
        strSearchSelect = ""
        'Empty the search text.
        TxtSearch.Text = ""
    End Sub

    ''' <summary>
    ''' Handles the LostFocus event of the TxtSearch control for triggering Search click.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub TxtSearch_LostFocus(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TxtSearch.LostFocus
        'Triggering search click.
        MnuSearch_Click(Nothing, Nothing)
    End Sub

    ''' <summary>
    ''' Handles the Deactivate event of the FrmSearchDialog control for triggering dialog close.
    ''' </summary>
    ''' <param name="sender">The source of the event.</param>
    ''' <param name="e">The <see cref="System.EventArgs" /> instance containing the event data.</param>
    Private Sub FrmSearchDialog_Deactivate(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Deactivate
        'The DialogResult is OK and no search result HashTable
        If DialogResult = Windows.Forms.DialogResult.OK AndAlso HashSearchResult Is Nothing Then
            'The grid current row index is valid.
            If DgSearch.CurrentRowIndex >= 0 Then
                'Triggering OK click.
                MnuOK_Click(Nothing, Nothing)
            Else 'Setting Cancel DialogResult.
                DialogResult = Windows.Forms.DialogResult.Cancel
            End If
        End If
    End Sub
End Class

My First Journey to Mobile Database Application

2009-08-25T12:36:00.005+07:00

My company assigns me and my team mates to automate our warehouses processes. We need to automate the manual recording of inventory flows processes.
Our first development approach is ASP.NET 2.0. This approach is the fastest to get done because we don't need to think about mobile database and server database. As long as there is wireless connection and a capable browser, the mobile devices can be used for data input. Our intended device is Motorola Symbol MC5590 with QWERTY keypad and built in barcode scanner. For development purpose we use HTC TyTN II with Windows Mobile 6.1 Pro OS. We found a China company specializing in barcodes, Sumlung and tried their software for reading a barcode through PDA camera. But their software is incompatible with our mobile device. When budget is your main consideration in developing a barcode application, you can review their solutions. We use AJAX control toolkit for automating our web pages. But to be our disappointment this ASP.NET 2.0 is failed. Our ASP.NET 2.0 website was running fine on Windows Mobile 6 Pro Emulator Images IE Mobile. But some features failed on our HTC TyTN II IE Mobile. The most obvious one is asp:Login control, the button doesn't react to a mouse click. So we find alternative browsers for our WM6. Opera Mobile can be used for running our web pages but the layout size doesn't seem right to us. Luckily we found the Iris browser. The browser can run our web pages just like we want them. Since there are no problems with software development, the project rolls onto hardware infrastructures planning. We are pretty surprised with the wireless infrastructure site survey result. Our company needs to spend a large amount of money for a full wireless LAN coverages on its warehouses. We thought an alternative from in house wireless infrastructures to unlimited HSDPA data plan from mobile telco operators. We use Hamachi Mobile from LogMeIn for connecting our mobile devices to our server. Everything seems right unless one crucial problem, Indonesia mobile telco operators are unreliable. At least there is 1 day in a week we can not connect from our mobile telco operator. We came back to wireless LAN infrastructures with reduced coverages. It seems there is no easy path on Mobile Database Application, we have to develop a .NET CF 2.0 database application.

Second approach, .NET CF 2.0 database application. First things we do are download all necessary software:
Windows Mobile 6 Pro Emulator Images (downloaded previously)
Windows Mobile 6 SDK
ActiveSync 4.5 for Windows XP (allow DMA connection for docking emulator)
Windows Mobile Device Center for Windows Vista
.NET CF 2.0 SP 2 for our Visual Studio 2005
SQL Server CE 3.1 (we download the Developer SDK and Tools for Visual Studio SP 1)
We are pretty surprised there are no significant differences between developing a desktop ADO.NET 2.0 application and a mobile ADO.NET 2.0 application. To cut the learning curve, we do not use Microsoft Synchronization Services, we use compact framework SqlClient directly to upload to and download from SQL Server 2005. There are several things about SqlCE we bumped into:

Do not use ComboBox for looking up a table with a lot of records. We create a SearchDialog that utilizes a scrollable SqlCeResultSet bound to a DataGrid instead of ComboBox. The SqlCeConnection must be always connected for using SqlCeResultSet. SqlCeResultSet will only fetch shown records on DataGrid so you don't need to wait the whole records to be loaded. Our SearchDialog construct incremental search select SQL and return a HashTable of a row selected.
SqlCE doesn't support update and delete using join, sub query and batch command. So we must keep this in our minds.
Eliminate unneeded columns on SqlCE.
Denormalize wisely for gaining performance.
We use untyped dataset and it took time to load it so we serialize the dataset schema to database and deserialize it on form load. PS: We hate typed dataset :-).
Our select, insert, update and delete command constructed on the fly based on table schema and it took time to load also. So we serialize and deserialize them using XML into database also.

The problem about performance and incompatible SQL make us considering several alternatives such as: SQLite, VistaDB and SSCE Direct. But time constraint makes us stay with SqlCE. For SQL Server 2005 side, all tables to be synchronized to SqlCE equipped with timestamp columns CreateTime and UpdateTime and Deleted tables for storing the deleted primary keys and their DeleteTime for each tables. We also applied these for all SqlCE tables to be uploaded to SQL Server. Records inserted in SqlCE use negative integer primary key, they will have positive integer primary key after synchronize with server.

I hope my share about our experience in developing mobile database application can be useful for all of you. Thank you.