Conditionally creating SqlDatabase only by initial Installation, not by Major Upgrade

Jan 8 at 11:01 AM
How to add Condition for Component SqlDatabase in WixSharp?
My C# Code is following:
_project = new ManagedProject();
// -- Only single database is required.
        _project.SqlDatabases = new SqlDatabase[1];
        _project.SqlDatabases[0] = new SqlDatabase
        {
            Id = setting.Id,
            Server = setting.Server.Key,
            Database = setting.Database.Key,
            Instance = setting.Instance.Key,

            User = "SQLUser",
            CreateOnInstall = true,
            SqlScripts = new[] { new SqlScript("GenerateDatabase", ExecuteSql.OnInstall) },
            SqlStrings = new[]
            {
                new SqlString(DatabaseConstant.CreateSqlLoginAndUser, ExecuteSql.OnInstall)
            }
        };
And Wix *.wxs is following:
      <Component Id="SqlDatabase1" Guid="3338faaa-8c9c-43c3-b5ac-3f73970b42ec" KeyPath="yes">
        <SqlDatabase Id="DATABASE_UNIQUED_IDENTIFIER" Database="[DATABASENAME]" Server="[SERVERADDRESS]" CreateOnInstall="yes" Instance="[SERVERINSTANCE]" User="SQLUser" xmlns="http://schemas.microsoft.com/wix/SqlExtension">
          <SqlString Id="SqlString" SQL=" SQL SCRIPT " ExecuteOnInstall="yes" />

          <SqlScript Id="GenerateDatabase" BinaryKey="GenerateDatabase" ExecuteOnInstall="yes" />
        </SqlDatabase>
      </Component>
I would appreciate an Example.
Coordinator
Jan 9 at 12:21 PM
The condition element for SqlDatabase is not available directly so you need to insert it in the final XML:
project.WixSourceGenerated += document =>
{
    document.Root.FindSingle("SqlDatabase")
                 .Parent
                 .AddElement("Condition")
                 .SetValue("INSTALLING=\"yes\" AND UPGRADING=\"no\"");
};
In the code above I used dummy condition names INSTALLING and UPGRADING but you will need to find the real condition expression that constitutes "initial Installation, not by Major Upgrade"
Jan 11 at 10:45 AM
Thank you for your reply.
I have implemented your suggestion and it works fine by Major Upgrade.
But now by new Installation, the SqlDatabase not installed too.
I don’t know how to get which Condition should be on first position:
INSTALLING = “yes”.
See please my Upgrade and SqlDatabase Component fragments.
My question is, how could I get key value for first part of Condition?
 <Upgrade Id="3338faaa-8c9c-43c3-b5ac-3f737d6590fd">
  <UpgradeVersion Minimum="0.0.0.0" Maximum="1.0.2.10010" IncludeMinimum="yes" IncludeMaximum="no" Property="UPGRADEFOUND" />
  <UpgradeVersion Minimum="1.0.2.10010" IncludeMinimum="no" Property="NEWPRODUCTFOUND" />
</Upgrade>

<Component Id="SqlDatabase1" Guid="3338faaa-8c9c-43c3-b5ac-3f73970b42ec" KeyPath="yes">
        <SqlDatabase Id="DATABASE_UNIQUED_IDENTIFIER" Database="[DATABASENAME]" Server="[SERVERADDRESS]" CreateOnInstall="yes" Instance="[SERVERINSTANCE]" User="SQLUser" xmlns="http://schemas.microsoft.com/wix/SqlExtension">
          <SqlString Id="SqlString" SQL=" SQL SCRIPT " ExecuteOnInstall="yes" />

          <SqlScript Id="GenerateDatabase" BinaryKey="GenerateDatabase" ExecuteOnInstall="yes" />
        </SqlDatabase>

        <Condition>NEWPRODUCTFOUND="yes" AND UPGRADEFOUND="no"</Condition>
      </Component>
Coordinator
Jan 11 at 10:52 PM
Edited Jan 12 at 3:16 AM
I don’t know how to get which Condition should be on first position:
:) This is what I meant "you will need to find the real condition". It's not trivial. MSI as a technology is extremely convoluted and sometimes determining the state of the product and the status of the session is very difficult. I have already done this task in the MsiRuntime.cs on GitHub. BTW Wix# project has migrated to GitHub and probably it makes sense to continue this discussion there.

Anyway in the top area of MsiRuntime.cs you will find the table that maps the properties and their values to the install scenarios. You can use this table to compose the required condition. Alternatively you can use Managed Setup events and benefit from the runtime analysis that Wix# already does if this project type is used. Then you can just set your single custom property and it will be later evaluated for your DB installation.
var project = new ManagedProject(...
project.BeforeInstall += project_BeforeInstall;
...
static void project_BeforeInstall(SetupEventArgs e)
{
    bool installed = e.Session["Installed"] != ""; 
    bool reinstall = e.Session["REINSTALL"] != ""; 
    bool upgradingProductCode = e.Session["UPGRADINGPRODUCTCODE"] != "";

    if (reinstall && !reinstall && upgradingProductCode)
        e.Session["INSTALL_DB"] = "no";
    else
        e.Session["INSTALL_DB"] = "yes";
Though I cannot truly recommend this approach as I found that it is based on the property values that are not set/behaving at runtime as it is described in MSI documentation. Basically runtime state is not 100% deterministic.

I suggest you just play with ManageSetup/SetupEvents sample (it shows at runtime all relevant properties) and see what are the unique property values combination that correspond to "upgrading" scenario. Then you can use this combination in your code.
Coordinator
Jan 12 at 1:40 AM
I just published the wiki that can be helpful for you:
https://github.com/oleg-shilo/wixsharp/wiki/Detecting-upgrade-at-runtime