PdfSharp Chinese Encoding

Recently I started to use PdfSharp open source project, this is a great tool and its document rendering speed is great with small-medium sized pdf docs.

Anyway, in our program's pdf export function, it has a requirement to be able to cater for different languages such as CJK (Chinese, Japanese, Korean), etc. I got some hints from pdfsharp's forum, here's what's going to solve the issue:

  Private Sub DefineStyles()
        ' Get the predefined style Normal.
        Dim style As Style = Me.document.Styles("Normal")
        ' Because all styles are derived from Normal, the next line changes the
        ' font of the whole document. Or, more exactly, it changes the font of
        ' all styles and paragraphs that do not redefine the font.
        style.Font.Name = "Arial Unicode MS" 'This font will make Chinese characters display
   
        ' Create a new style called Table based on style Normal
        style = Me.document.Styles.AddStyle("Table", "Normal")
        style.Font.Name = "Arial Unicode MS"  
        style.Font.Size = 10    'Default font
    End Sub

Don't save any sensitive data into your MS Office products

Just don't do it, it takes a person less 1 minute to search on google and crack whatever password that you put on to 'protect' your file.

E.g. http://uknowit.uwgb.edu/page.php?id=28850

there are quite a few programs out there to crack your MS access password, so use SQL server if you can, access is really for fun.

Random values in Select Query to hide information

There are situations when manager or BA want you to hide certain information in a report, there are a lot of programming ways to achieve this, for laziness it is also doable in SQL.

E.g. I have a list of sensitive customer name and their sales figure, I would like to show the other party how many sales I made, however hiding all the names.

the idea came to my mind is, using a string 'Customer' and a random number from 1-10 combine to get a random name such as 'Customer5', etc.

syntax:

 'Customer' + CAST((CAST(RAND(CHECKSUM(NEWID())) * 10 as INT) + 1) as varchar(2))

RAND(Checksum(NEWID()))
Above provides a random number between 0 - 1, use it as root times 10 to get a random number between  1- 10. hence end up having 'Customer1' - 'Customer10' in the selection list.

Unlock System administrator account in SQL 2008 R2

If for some reason the system admin account got locked out of SQL server, you may take the following steps to unlock it.


Step 1 Add a new user using current ‘Windows Login user name’, e.g. PBTECH\Michael , then  Stop SQL server

Step 2 Change the server into single user mode, by add “;-m” at the end of path under SQL server property window advanced tab

Step 3: Save & restart, login to SSMS with windows authentication

Step 4: Give the new user added in step 1 System admin permission by executing :
sp_addsrvrolemember [domain\username],'sysadmin'

Step 5: Under security- login find the locked user, reset password and untick the lock, save and close.

Step 5: reverse step of step2, change SQL server back to multi user mode

The SA should be unlocked.

To Make your window form show as topmost window in VB.Net

In VB6 we used to use API function like :
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

the equivalent function in .Net is something like:

<DllImport("user32.dll", SetLastError:=True, CharSet:=CharSet.Auto)> _
Private Shared Function ShowWindow(ByVal hwnd As IntPtr, ByVal nCmdShow As  b      ShowWindowCommands) As Boolean
End Function


To make your window to topmost of your screen, there're a few simple steps to take:

• Get your process ID when you call the program

Private Function GetProcessID() as Integer
    Dim ProcessProperties As New ProcessStartInfo
    ProcessProperties.FileName = "test.exe"
    ProcessProperties.Arguments = ""
    Dim myProcess As Process = Process.Start(ProcessProperties)
    Return myProcess.ID
End Function
   

• Use the ProcessID to get window's Handle and set it to topmost

'SET API And variables
   <DllImport("user32.dll", CharSet:=CharSet.Auto, SetLastError:=True)> _
Private Shared Function SetWindowPos(ByVal hWnd As IntPtr, ByVal hWndInsertAfter As IntPtr, ByVal X As Integer, ByVal Y As Integer, ByVal cx As Integer, ByVal cy As Integer, ByVal uFlags As Integer) As Boolean
    End Function

    Private Const SWP_NOSIZE As Integer = &H1
    Private Const SWP_NOMOVE As Integer = &H2

    Private Shared ReadOnly HWND_TOPMOST As New IntPtr(-1)
    Private Shared ReadOnly HWND_NOTOPMOST As New IntPtr(-2)


'Set  Window Pos to top most
  Public Shared Sub SwitchToProcess(ByVal PID As Integer)
        Dim hWnd As Integer
        hWnd = CType(System.Diagnostics.Process.GetProcessById(PID).MainWindowHandle, Integer)
        'ShowWindow(hWnd, SW_MAX)
        'ShowWindow(hWnd, ShowWindowCommands.ShowNoActivate)
        SetWindowPos(hWnd, HWND_TOPMOST, 0, 0, 0, 0, SWP_NOMOVE Or SWP_NOSIZE)
  End Sub

Excel 2010 Querying(parameterised) SQL

Simple reports for end user may require you to run some query on an excel spreasheet, so that end user can refresh and get live data as they want and when they want.

Couple ways to do:
Create a View in SQL server, save it, then Open up Excel 2010

Choose 'From Microsoft Query' (You can, if you wish, to choose SQL server, what i noticed was you cannot change your query or put dynamic parameters if you choose that)

Point to the database server of your choice, Click 'Ok' then Click Cancel, it'll pop up asking whether you want to enter query editing window, click 'Yes'.

Then choose the table you wish to display in SQL Query edit window, you can then add criteria and modify the query and look of the output. even add parameters.

After you done with your query editing, click 'File --> Return Data to Excel' to go back to Excel main window.(Note: if you added parameters in your query, you'll need to fresh the connection to be able to set the parameter look up value, i.e. a cell or range of cells.)

User will be able to simply change the parameter to get the data refreshed. Easy as~

Auto Refreshing Excel data connection without open up excel

Ever wanted to get a bunch of pivot table data on excel files refreshed without the need of opening the file at all?
Here's a solution,
download the zip and extract. To use, just create a batch file and execute the .exe file on command line.

http://search.cpan.org/~ctbrown/Win32-Excel-Refresh-0.02/Refresh.pm