FoxPro Grid RecordSource With Tables In A Chained Relationsip

Let's assume a case like this, where the tables are linked in a chained relationship.:

SELECT product
SET ORDER TO prodidx

SELECT customer
SET ORDER TO custidx
SET RELATION TO prod_id INTO product ADDITIVE

SELECT invoice
SET ORDER TO invoiceidx
SET RELATION TO cust_id INTO customer ADDITIVE

Now I would like to show "customer" records in a Grid (e.g. grdCustomer), based on the current record in "invoice" (e.g. record no. 1):

SELECT invoice
GOTO 1

WITH THISFORM.grdCustomer
    .RECORDSOURCE              = 'customer'

    .colCustId.CONTROLSOURCE   = 'customer.cust_id'
    .colProdId.CONTROLSOURCE   = 'customer.prod_id'
    .colProdDesc.CONTROLSOURCE = 'product.prod_desc'        && Get value from "product" relationship.
ENDWITH

The expected behaviour would be "colProdDesc" will show the "prod_desc" linked to each "customer" record's "prod_id" in the Grid.
However, If you run this code, it will not work. Even worse, there would be no record shown at all in the Grid.

This happens when the Grid's RecordSource ("customer") has another relationship linked to it ("invoice").

As such, as a workaround to show the relationship values (product.prod_desc), we just need to add "ALLTRIM()" to the ControlSource expression.

So we change

.colProdDesc.CONTROLSOURCE = 'product.prod_desc'

to

.colProdDesc.CONTROLSOURCE = 'ALLTRIM(product.prod_desc)'

This should fix the issue and show the records in the Grid correctly.

If you find this post helpful, would you buy me a coffee, maybe?

Assemblies Embedding And Obfuscation With Eazfuscator.NET

We could use ILMerge to merge our .Net assemblies. However, instead of merging, we could also "embed" our assemblies (reference). In fact, there are many obfuscation tools in the market which not only obfuscate, but also help to merge/embed assemblies in a single build process.

Eazfuscator.NET is one of them which is easy to use and is free. If what you need is some level of protection to prevent reverse engineering of your code, as well as assembly merging and embedding (it supports both), this is a handy tool to use.

Update: Eazfuscator.NET went commercial and is no longer free starting from version 3.4.71 (released on 2012-06-22).

The following steps show you how to embed and obfuscate your assemblies using Eazfuscator.NET:

• Disclaimer: This is only a guideline based on my commonly used scenario, you may want to refer the Eazfuscator.NET Documentation for more options and functionality.

1. Download and install Eazfuscator.NET (Close all opening Visual Studio before install).

2. Open your project in Visual Studio. You will see "Eazfuscator.NET Assistant" under the Visual Studio's main menu > "Tools". You can also access this from the Windows "Start" menu.

3. Drag and drop your main (StartUp) project from the Solution Explorer to the "Green" box. Now this project is set for obfuscation.

• Note: Drag and drop the project to the "Red" box to disable the obfuscation.

4. Add a new CS file named as "ObfuscationSettings.cs". Move this file into the "Properties" folder. Put the following contents into the file:

using System;
using System.Reflection;

[assembly: Obfuscation(Feature = "encrypt symbol names with password MyP@ssw0rd", Exclude = false)]
[assembly: Obfuscation(Feature = "code control flow obfuscation", Exclude = false)]

If Reflection is used within your project, you may add this line:

[assembly: Obfuscation(Feature = "Apply to * when public and class: renaming", Exclude = true)]

Note:

• Replace MyP@ssw0rd with your password.
• If all your projects use the same settings, you may put these into a commonly shared link file (e.g. CommonObfuscationSettings.cs) and add as link from your project.

5. In "ObfuscationSettings.cs", add attributes for each referenced .Net assembly you want to embed, for example:

[assembly: Obfuscation(Feature = "embed My.SupportAssembly1.dll", Exclude = false)]
[assembly: Obfuscation(Feature = "embed My.SupportAssembly2.dll", Exclude = false)]
[assembly: Obfuscation(Feature = "embed MySql.Data.dll", Exclude = false)]

Note:

• The assembly file name is case-sensitive.
• Mixed assembly (assembly which contains unmanaged code) is well supported.

6. Build the project. The main project's bin\Release will have the assembly which is obfuscated and can be deployed without the referenced assemblies.


Additional Resources:
Assemblies Merging With Post-build Event
Assemblies Merging With ILMerge and MSBuild

If you find this post helpful, would you buy me a coffee, maybe?

Assemblies Merging With ILMerge and MSBuild

ILMerge is a useful utility to merge multiple .NET assemblies into a single assembly. We can use it together with MSBuild to automate the merging process when building our projects.

There is another method which is more basic and has less features. Please refer Assemblies Merging With Post-build Event.

Thanks to some great online references I found here, here and here, this is a sample MSBuild script which has the following features:

• Runs only when building your project in "Release" mode.
• Automatically merge all "Copy Local" referenced assemblies, instead of having to define every assemblies to be merged.
• Option to define assemblies to be excluded from merging in the "ilmerge.assembly.exclude" file, for all projects (global) and/or each individual project.
• Option to define the ILMerge's internalized: excludeFile list in the "ilmerge.internalize.exclude" file, for all projects (global) and/or each individual project.
• Output to the bin\Release folder and the merged referenced assemblies will be deleted.

Setup ILMerge and Ilmerge.CSharp.targets (only set once):

1. Download and install the ILMerge utility.

• Note: You may copy the "ILMerge.exe" and put it into any custom folder, as long as you change to the correct path in the <ILMergeExeDir> property in the "Ilmerge.CSharp.targets" file below.

2. Create a file named as "Ilmerge.CSharp.targets" with the following contents:

<Project DefaultTargets="Build" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">

 <Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" />

 <PropertyGroup>
    <ILMergeExeDir>$(ProgramFiles)\Microsoft\ILMerge\</ILMergeExeDir>
 </PropertyGroup>

 <PropertyGroup>
    <ILMergeTargetPlatformArg>/targetplatform:v2,&quot;$(MSBuildBinPath)&quot;</ILMergeTargetPlatformArg>
 </PropertyGroup>

 <PropertyGroup Condition=" '$(TargetFrameworkVersion)' == 'v4.0' ">
    <ILMergeTargetPlatformArg>/targetplatform:v4,&quot;$(MSBuildToolsPath)&quot;</ILMergeTargetPlatformArg>
 </PropertyGroup>

 <PropertyGroup Condition=" '$(AssemblyOriginatorKeyFile)' != '' ">
    <ILMergeKeyFileArg>/keyfile:$(AssemblyOriginatorKeyFile)</ILMergeKeyFileArg>
 </PropertyGroup>

 <PropertyGroup Condition=" Exists('$(ILMergeExeDir)ilmerge.internalize.exclude') ">
    <ILMergeInternalizeExcludeArg>:&quot;$(ILMergeExeDir)ilmerge.internalize.exclude&quot;</ILMergeInternalizeExcludeArg>
 </PropertyGroup>
 <PropertyGroup Condition=" Exists('$(ProjectDir)ilmerge.internalize.exclude') ">
    <ILMergeInternalizeExcludeArg>:&quot;$(ProjectDir)ilmerge.internalize.exclude&quot;</ILMergeInternalizeExcludeArg>
 </PropertyGroup>

 <ItemGroup>
    <ILMergeAsmGlobalExcludeFile Include="$(ILMergeExeDir)ilmerge.assembly.exclude"/>
    <ILMergeAsmExcludeFile Include="$(ProjectDir)ilmerge.assembly.exclude"/>
 </ItemGroup>

 <Target Name="AfterBuild" Condition=" '$(Configuration)' == 'Release' ">
    <ReadLinesFromFile File="@(ILMergeAsmGlobalExcludeFile)" >
        <Output TaskParameter="Lines" ItemName="ILMergeAsmExclude"/>
    </ReadLinesFromFile>
    <ReadLinesFromFile File="@(ILMergeAsmExcludeFile)" >
        <Output TaskParameter="Lines" ItemName="ILMergeAsmExclude"/>
    </ReadLinesFromFile>

    <CreateItem Include="@(ReferenceCopyLocalPaths)" Condition="'%(Extension)'=='.dll' And '%(Filename)%(Extension)'!='@(ILMergeAsmExclude->'%(Filename)%(Extension)')'">
        <Output TaskParameter="Include" ItemName="AssembliesToMerge"/>
    </CreateItem>

    <Message Text="MERGING: @(AssembliesToMerge->'%(Filename)')" Importance="High" />

    <Exec Command="&quot;$(ILMergeExeDir)ILMerge.exe&quot; /internalize$(ILMergeInternalizeExcludeArg) /ndebug /allowDup $(ILMergeKeyFileArg) /out:@(MainAssembly->'&quot;%(FullPath)&quot;') $(ILMergeTargetPlatformArg) @(IntermediateAssembly->'&quot;%(FullPath)&quot;') @(AssembliesToMerge->'&quot;%(FullPath)&quot;', ' ')" />

    <CreateItem Include="@(ReferenceCopyLocalPaths->'$(OutDir)%(DestinationSubDirectory)%(Filename)%(Extension)')" Condition=" '%(Filename)%(Extension)'!='@(ILMergeAsmExclude->'%(Filename)%(Extension)')' ">
          <Output TaskParameter="Include" ItemName="AssembliesToDelete" />
    </CreateItem>
    <Delete Files="@(AssembliesToDelete)" />
 </Target>

 <Target Name="_CopyFilesMarkedCopyLocal"/>

</Project>

3. Save this file into the "C:\Program Files\MSBuild\ILMerge" folder.

• Note: You can put this file into any other folder, as long as you change to the correct path in the <Import Project> property in .csproj below.

Setup your project:

1. Close any opening project and solution in Visual Studio.
2. Open the main project's *.csproj with a text editor (e.g. Notepad).
3. Search for "Import", replace the <Import ...> line with the following:

<Import Project="$(MSBuildExtensionsPath)\ILMerge\Ilmerge.CSharp.targets" />

4. Save the *.csproj file.
5. Open the project/solution in Visual Studio. When prompted, select "Load Project Normally".
6. Build the project.


Exlcude file sample:

1. If you would like to exclude certain "Copy Local" assemblies from being merged, just simply create an ASCII file named as "ilmerge.assembly.exclude", then list down the assembly names (including extension and is case sensitive) on each line, as below:

System.Data.SQLite.DLL
MyAssembly.Data.dll
MyAssembly.Special.dll

2. Put this file in the same folder as ILMerge.exe to apply for all projects (global). Put this file in the project folder to only apply for that particular project. Either or both can exist.

3. The same goes for the "ilmerge.internalize.exclude" file.


Additional Resources:
ILMerge and ConfigurationSection

If you find this post helpful, would you buy me a coffee, maybe?

Ordinal String Comparison

When comparing strings, it would be best to use "Ordinal" or "OrdinalIgnoreCase" for culture-agnostic comparisons. And, as what stated in the MSDN, your code often gains speed, increases correctness, and becomes more reliable.

References:

• CA1309: Use ordinal StringComparison
• C#: String comparison guidelines and common usage

If you find this post helpful, would you buy me a coffee, maybe?

Get The Correct Assembly Path

For a typical application, the referenced supporting assemblies would have the same location as the main executable. If you need to get the application's location from one of the referenced assemblies, you may use the following function:

Note: To make sure the path is get from the correct assembly, the GetAssemblyPath function must exist in the assembly's source code itself (instead of putting into another helper assembly). As such, please paste the following code at whenever you need it.

/// <summary>
/// Gets current assembly path.
/// </summary>
/// <returns>Assembly path</returns>
internal static string GetAssemblyPath()
{
    Assembly assembly = Assembly.GetEntryAssembly();

    if (assembly == null)
        assembly = Assembly.GetCallingAssembly();
    if (assembly == null)
        assembly = Assembly.GetExecutingAssembly();

    if (assembly == null)
        return "";
    else
        return Path.GetFullPath((new Uri(assembly.CodeBase)).AbsolutePath);
}

If you find this post helpful, would you buy me a coffee, maybe?

MySQL Backup Batch Script With 7Zip And Email Alert

This is yet another MySQL Backup batch script with the following features:

• Dumps each database into separate folder and script.
• Allows to set database(s) excluded from backup.
• Each script is compressed (7zip) and encrypted with a password.
• Keeps the last n copies of backups.
• Email alert on backup errors.

It requires the following tools:

• MySQL bin utilities - mysql.exe and mysqldump.exe.
• 7zip compression tool.
• blat emailer tool.

Note: It can backup any networked MySQL databases (either Windows or Linux platforms), as long as the MySQL bin utilities can access them.

This script is easy to configure and highly customizable. You only need to change the values in the "Begin/End Settings" sections. Please refer the script header remarks for more information and other options.

How to use:

1. Create a batch file named as "weizh-mysql-backup.bat" with the following contents:

@ECHO OFF

REM =====================================================================================
REM Copyright 2011 Weizh Chang
REM 
REM This program is free software: you can redistribute it and/or modify
REM it under the terms of the GNU General Public License as published by
REM the Free Software Foundation, either version 3 of the License, or
REM (at your option) any later version.
REM
REM This program is distributed in the hope that it will be useful,
REM but WITHOUT ANY WARRANTY; without even the implied warranty of
REM MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
REM GNU General Public License for more details.
REM
REM You should have received a copy of the GNU General Public License
REM along with this program.  If not, see <http://www.gnu.org/licenses/>.
REM =====================================================================================

REM =====================================================================================
REM Program    : Weizh MySQL Backup Batch Script
REM File       : weizh-mysql-backup.bat
REM Version    : 1.0.0.0
REM URL        : simpcode.blogspot.com
REM Description:
REM
REM Please change the settings in the Begin/End Settings sections below.
REM
REM This script requires:
REM - MySQL bin utilities - mysql.exe and mysqldump.exe.
REM - 7zip compression tool (www.7-zip.org).
REM - blat emailer tool (www.blat.net).
REM
REM Features:
REM - Dumps each database into separate folder and script.
REM - Allows to set database(s) excluded from backup.
REM - Each script is compressed (7zip) and encrypted with a password.
REM - Keeps the last n copies of backups.
REM - Email alert on backup errors.
REM =====================================================================================


@ECHO ON


REM ================ Begin MySQL Settings ====================

SET mySQLBinDir=C:\Program Files\MySQL\MySQL Server 5.0\bin\

SET host=192.168.100.2
SET uid=admin
SET pwd=adminPassw0rd
SET port=3306

SET backupDir=D:\MySQL_Backup\
SET backupPrefix=MySQL_3306_
SET backupCopy=3

REM ==== Set mysqldump switch (optional)
SET sqldumpSwitch=--single-transaction

REM ==== Set database excluded from backup (comma separated, without quotes and spaces)
SET excludeDb=information_schema,performance_schema,test

REM ==== Set the 7zip exe directory and password
SET zipDir=C:\Program Files\7-Zip\
SET zipPwd=yourZipPassw0rd

REM ================ End MySQL Settings ======================

REM ================ Begin Email Settings ====================

REM ==== Set all values to empty if not using email alert
SET emailServer=mail.example.com
SET emailPort=25
SET emailUid=admin@example.com
SET emailPwd=adminPassw0rd
SET sendFrom=admin@example.com
SET sendTry=5
SET subject=MySQL Backup Alert

REM ==== Set receiver email (comma separated, without quotes and spaces)
SET sendTo=alex@example.com

REM ==== Set the blat exe directory
SET emailDir=C:\blat\

REM ================ End Email Settings ======================


REM ================ Begin Backup ============================

ECHO Performing backup...

REM ==== Set date and time
For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET mydate=%%c-%%a-%%b)
SET mytime=%TIME:~0,8%
SET mytime=%mytime: =0%

REM ==== Set command variable
SET doShowDb="%mySQLBinDir%mysql" -B -N -e "SHOW DATABASES" -h %host% -u %uid% -p%pwd% -P %port%
SET doDump="%mySQLBinDir%mysqldump" %sqldumpSwitch% -h %host% -u %uid% -p%pwd% -P %port%
SET doZip="%zipDir%7z.exe" a -t7z -mx9 -mhe -p%zipPwd%
SET doZipTest="%zipDir%7z.exe" -r -p%zipPwd% t

REM ==== Loop and backup each database
SETLOCAL ENABLEDELAYEDEXPANSION

SET errorDbTemp=

REM ==== Test the connection
!doShowDb!
IF !ERRORLEVEL! NEQ 0 (
    SET errorDbTemp=MySQL Connection Error
    GOTO EndLoop
)

FOR /F "tokens=*" %%G IN ('"!doShowDb!"') DO (
    
    SET exclude=0
    SET errorFound=0

    IF NOT "%excludeDb%"=="" (
        FOR %%S IN (%excludeDb%) DO (
            IF /I "%%S"=="%%G" SET exclude=1
        )
    )

    IF !exclude!==0 (
        REM ==== Set backup directory and file for this db
        IF NOT EXIST "%backupDir%%%G" MD "%backupDir%%%G"
        For /F "tokens=2-4 delims=/ " %%a IN ('DATE /t') DO (SET backupDate=%%c-%%a-%%b)
        SET backupTime=!TIME:~0,8!
        SET backupTime=!backupTime: =0!
        SET backupFile=%backupDir%%%G\%backupPrefix%%%G_!backupDate:-=!_!backupTime::=!

        REM ==== Perform MySQLDump
        !doDump! --databases %%G > "!backupFile!.sql"

        IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

        IF NOT EXIST "!backupFile!.sql" (
            SET errorFound=1
        ) ELSE (
            REM ==== Zip then delete the sql file
            !doZip! "!backupFile!.7z" "!backupFile!.sql"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)

            REM ==== Test the zip integrity
            !doZipTest! "!backupFile!.7z"

            IF !ERRORLEVEL! NEQ 0 (SET errorFound=1)
 
            DEL /Q "!backupFile!.sql"
        )

        IF !errorFound!==1 (
            REM ==== If errors, clean up and append the db name to a temp variable
            IF EXIST "!backupFile!.*" DEL /Q "!backupFile!.*"
            SET errorDbTemp=!errorDbTemp!%%G;
        ) ELSE (
            REM ==== Delete old zip copies, sort by file name descending
            IF EXIST "%backupDir%%%G\%backupPrefix%%%G_*.7z" (
                SET zipCount=0

                FOR /F "tokens=*" %%U in ('DIR /A:-D-H /O:-N /B "%backupDir%%%G\%backupPrefix%%%G_*.7z"') DO (
                    SET /A zipCount+=1
                    IF !zipCount! GTR %backupCopy% DEL /Q "%backupDir%%%G\%%U"
                )
            )
        )
    )
)

:EndLoop

ENDLOCAL & SET errorDb=%errorDbTemp%


IF NOT "%errorDb%"=="" GOTO SendError

GOTO Finish

REM ================ End Backup ==============================


REM ================ Begin Send Error Email Alert ============
:SendError

IF "%emailServer%"=="" (
    ECHO Backup error on %errorDb%
    GOTO Finish
)

ECHO Sending error email alert

REM ==== Set sendTo with surrounding quotes
SET sendTo="%sendTo%"

REM ==== Set email temp file
SET tmpErrorFile="%~dp0mysql_backup_error-%RANDOM%.tmp"

(
ECHO Dear Database Admin,
ECHO.
ECHO There were errors while performing the MySQL Backup on the following databases:
ECHO.
ECHO Date: %mydate%      Time: %mytime%
ECHO Host: %host%      Port: %port%
ECHO.
ECHO -----------------------------------------------------------------
ECHO %errorDb%
ECHO -----------------------------------------------------------------
ECHO.
ECHO.
ECHO Regards,
ECHO MySQL Server Admin
) >%tmpErrorFile%


REM ==== Send error email alert
"%emailDir%blat.exe" %tmpErrorFile% -server %emailServer%:%emailPort% -f %emailUid% -u %emailUid% -pw %emailPwd% -from %sendFrom% -to %sendTo% -subject "%subject%" -try %sendTry%

REM ==== Cleanup
IF EXIST %tmpErrorFile% DEL /Q %tmpErrorFile%

REM ================ End Send Error Email Alert ==============


:Finish
ECHO Backup process completed

2. Set values in the Begin/End MySQL Settings and Begin/End Email Settings sections. Put this file into any folder you like.

3. You may use the Windows Task Scheduler to run this script routinely or at anytime you prefer.

If you find this post helpful, would you buy me a coffee, maybe?

SQL Server 2008 R2 Express Installation And Setup

This post provides a basic guideline about installing SQL Server 2008 R2 Express together with the SQL Server Management Studio.

1. If you do not have Windows Power Shell installed, please install it from here.

• On that page, find the Download information > Windows Management Framework Core (WinRM 2.0 and Windows PowerShell 2.0), then select Download the Windows Management Framework Core for Windows XXX package now, whereby XXX is your Windows edition (thanks to this site for the link).
• Note: Power Shell is only needed by SQL Server Management Studio. You may skip this step if you do not want to install the studio.

2. Download and install "SQL Server 2008 R2 Express With Tools" from here.

• Note: To do a clean install, you may first want to remove any existing SQL Server 2008 (or R2) database and utilities from the Control Panel > Add/Remove Programs.

3. When prompted, select "New installation or add features to an existing installation". Follow the instructions.

• Instance Configuration > Named instance: You may want to rename it to "SQLEXPRESS2008R2" or just use the default.
• Server Configuration > Service Accounts: Enter any existing Windows account name and password which has sufficient rights (e.g. the admin account).
  
• Database Engine Configuration > Authentication mode: Select "Windows authentication mode" (this can be changed later).
• Restart the computer if needed.

4. Go to Start > Microsoft SQL Server 2008 R2 > Configuration Tools > SQL Server Configuration Manager:

• SQL Server Services > SQL Server: Make sure it is running.
• SQL Server Network Configuration > Protocols for xxxx: You can enable and set the remote network connection settings here.

5. Go to Start > Microsoft SQL Server 2008 R2 > SQL Server Management Studio > Connect to server:

• Server name: serverName\instanceName (e.g. myserver\SQLEXPRESS2008R2)
• Authentication: Windows Authentication

If everything is correct, you should be able to connect to the database now.


Setup Security and Authentication using SQL Server Management Studio

1. Enable "SQL Server Authentication Mode":

• In Object Explorer > Right-click the server to Properties > Security > Server authentication: Select "SQL Server and Windows Authentication mode".
• Once set, you may need to restart the service from "SQL Server Configuration Manager".

2. Enable "sa" login:

• By default the "sa" account is disabled. To enable:
  In Object Explorer > Security > Logins > sa > Right-click to Properties:
  • General: Set the password.
  • Status > Login: Select "Enabled".

3. Add new SQL Server authentication:

• In Object Explorer > Security > Logins > Right-click to New Login > Select "SQL Server authentication": From here, you can set the login name, password and default database.

4.Set database owner:

• In Object Explorer > Databases > Right-click on the database > Properties > Files: Select the Owner.

Additional Resources:
Introduction to SQL Server Express 2008 R2

If you find this post helpful, would you buy me a coffee, maybe?