Adding data using subqueries
Glossary Item Box
Examples of using the InsertSelect class to retrieve data using subqueries
You can download the package with the configuration web service implementing the cases described below using the following link.
The example code demonstrates available methods for passing parameters to the query. When developing a project, be aware that parameters originating from the user should never be passed to the Column.Const method, since this can lead to a successful SQL injection attack.
Example 1
Receive the SQL-query text
public string GetSqlTextExample(string contactName, string accountName) { var result = ""; 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); result = insertSelectQuery.GetSqlText(); return result; }
Example 2
Add the contact with the specified name and account
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 3
Add the contact with the specified name and connect it to all accounts
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; }
See also:
