WIX..enforce overwrite files & more

If user had installed a product before and want to uninstall to reinstall same version but new build

sometimes the user need to delete the files manually from the installaton directory

so it is better for the product to force reinstallation for all the files , registry keys and others to avoid user undeletion

WIX support this by adding the follwing property to the Main fragment of the WIX project    

 <Property Id="REINSTALLMODE" Value="amus"/>

which mean:

a - Force all files to be reinstalled, regardless of version.
m - Rewrite all registry keys that go to HKEY_LOCAL_MACHINE or HKEY_CLASSES_ROOT.
u - Rewrite all registry keys that go to HKEY_CURRENT_USER or HKEY_USERS.
s - Reinstall shortcuts and icons.

Wix Sql:SqlString element - the SQL string format

For the SQL string to get correctly resolved some format rules must be followed:
1- Any Wix properties must be inside square brackets [].
2- Any square brackets that belong to SQl indeces that must be escaped: "[" -> "[\[]" AND "]" ->"[\]]".
3- Each Sql command mus tbe terminated by a semicolon ";".
4- If you use "Execute (N' bla bla ' );" then any string INSIDE this command must have an additional starting and trailing single quote.
5- If you use "Execute (N' bla bla ');" then any double quotation must be escaped by using 'ampersandquot;' instead of ' " '.
The following snippet illustrates each of the previous rules

<Sql:SqlString Id="FillAdjustmentDefinitionsTable" ExecuteOnInstall="yes" ExecuteOnReinstall="yes" Sequence="3" SqlDb="MasterDB" ContinueOnError="no"
SQL="USE DentalDB ;
SET ANSI_PADDING ON ;
Execute(N'CREATE TABLE #tempAdjustmentDefinitionTable(ThisLine xml);
bulk insert #tempAdjustmentDefinitionTable from ' '[APPLICATION]InitialDB\AdjustmentDefinitions.xml' '
with ( codepage=' 'ACP ' ')
INSERT INTO &qout;AdjustmentDefinition"
(
Type,Description,Amount
)
Select
ThisLine.value(' '(AdjustmentDefinition/@Type) [\[]1[\]] ' ', ' 'int' ') ,
ThisLine.value(' '(AdjustmentDefinition/@Description) [\[]1[\]]' ', ' 'nvarchar(100)' '),
ThisLine.value(' '(AdjustmentDefinition/@Amount) [\[]1[\]] ' ', ' 'money' ')
from #tempAdjustmentDefinitionTable
DROP TABLE #tempAdjustmentDefinitionTable ; ' ); "></Sql:SqlString>