Add data to the database using subqueries

Level: advanced

note

The examples in this article are also implemented in the web service. The package that contains the web service implementation is attached in the Resources block.

Example 1

Example

Add the contact that has the specified name and account.

InsertContactWithAccount() method

public string InsertContactWithAccount(string contactName, string accountName) {
    contactName = contactName ?? "Unknown contact";
    accountName = accountName ?? "Unknown account";

    var id = Guid.NewGuid();
    var selectQuery = new Select(UserConnection)
        .Column(Column.Parameter(contactName))
        .Column("Id")
        .From("Account")
        .Where("Name").IsEqual(Column.Parameter(accountName)) as Select;
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(selectQuery);

    var affectedRows = insertSelectQuery.Execute();
    var result = $ "Inserted new contact with name '{contactName}'" +
        $ " and account '{accountName}'." +
        $ " Affected {affectedRows} rows.";
    return result;
}

Example 2

Example

Add the contact that has the specified name and bind it to all accounts

InsertAllAccountsContact() method

public string InsertAllAccountsContact(string contactName) {
    contactName = contactName ?? "Unknown contact";

    var id = Guid.NewGuid();
    var insertSelectQuery = new InsertSelect(UserConnection)
        .Into("Contact")
        .Set("Name", "AccountId")
        .FromSelect(
            new Select(UserConnection)
            .Column(Column.Parameter(contactName))
            .Column("Id")
            .From("Account") as Select);

    var affectedRows = insertSelectQuery.Execute();
    var result = $ "Inserted {affectedRows} new contacts with name '{contactName}'";
    return result;

}

---

Resources

Package with example implementation (web service)