Insert, Update, Delete, and Select Operations on Tables and Views with the SQL adapter
The Microsoft BizTalk Adapter for SQL Serversurfaces a set of standard operations on each table and view in the SQL Server database. By using these operations, you can execute simple INSERT, UPDATE, SELECT, and DELETE statements qualified by a WHERE clause on the target table or view. These operations are also called data manipulation language (DML) operations.
The following table shows the DML operations that the SQL adapter supports:
| Operation | Description |
|---|---|
| Insert | Performs an Insert operation on the target table or view. - The Insert operation takes an array of records as input. Each record is strongly typed to the target table, and maps to the row being inserted in the table. - You can insert values in identity columns provided the value of the AllowIdentityInsert binding property is set to TRUE. For more information about the AllowIdentityInsert binding property, see Read about the BizTalk Adapter for SQL Server adapter binding properties. - The return value of the Insert operation is an array of Long data type. This array stores the identity values of the inserted rows, if any. If there is no identity column in a table, the return value is NULL. Some values in the Insert operation message are treated in the following manner by the SQL adapter: - Values specified for computed columns and timestamp columns are ignored. - If the node for an identity column is null, it is ignored. For all other values in the Insert operation message: - If a value is specified for a column, that value is used in the INSERT statement. - If the node for a particular column is null, NULL is used in the INSERT statement. Note: If for a particular record, there are no values that can be used in the INSERT statement (that is, either no values were specified for any column or all the column values were ignored), the adapter executes the following SQL statement: insert into <table_name> default values |
| Select | Performs a SELECT statement on the target table or view based on an array of records (columns) and a query string that specifies a WHERE clause. - A value must be specified for the list of the columns in the SELECT statement. If all columns have to be retrieved in a table or view, * must be specified in the SELECT statement. If specific columns have to be retrieved, the column names must be separated by comma, and specified in the same order as they are defined in the table or view. - The WHERE clause must be included in the SELECT statement. However, if you do not want to specify a value in the WHERE clause, you can either delete the Query element or leave it empty.- The Select operation also allows you to perform an Update operation. In this case, the UPDATE statement is placed inside the Query element of the SELECT statement.The return value of the Select operation is a strongly-typed result set that contains the specified columns and rows from the target table or view. |
| Update | Performs an Update operation on the target table or view. - The Update operation takes an array of record pairs as input. Each record pair is a collection of two records, and each record is strongly-typed to the target table. - The first record corresponds to new values that need to be updated, that is, it corresponds to the SET clause of the UPDATE statement. - The second record corresponds to the old values of the rows, that is, it corresponds to the WHERE clause of the UPDATE statement. Note: If, for a particular record pair, there are no values that can be used in the SET clause, no UPDATE statement is executed for that record pair. - You can update values in identity columns provided the value of the AllowIdentityInsert binding property is set to TRUE. For more information about the AllowIdentityInsert binding property, see Read about the BizTalk Adapter for SQL Server adapter binding properties. - The return value of the Update operation is of Int32 data type, and denotes the number of rows updated. Some values in the Update operation message are treated in the following manner by the SQL adapter: - Values specified for computed columns and timestamp columns in the SET clause of the message are ignored. - If a User Defined Type (UDT) is not byte ordered, the value specified for the UDT column in the WHERE clause is ignored. - If the node for an identity column is null in the SET clause of the message, it is ignored. - If the node for an identity or timestamp column is null in the WHERE clause of the message, it is ignored. - If the node for an image, XML, Text or Ntext column is not null in the WHERE clause of the message, the values specified for them are ignored because these values cannot be compared. For all other values in the Update operation message: - If a value is specified for a column in the SET clause of the UPDATE statement, the value is used in the SET clause of the statement ( set <column_name> = <value>).- If the node for a particular column is null in the SET clause, NULL is used in the UPDATE statement ( set <column_name> = null).- If a value is specified for a column in the WHERE clause of the UPDATE statement, the value is used in the WHERE clause of the statement ( where <column_name> = <value>).- If the node for a particular column is null in the WHERE clause of the UPDATE statement, NULL is used in the UPDATE statement ( where <column_name> is null). |
| Delete | Performs a Delete operation on the target table or view based on a strongly-typed array of records (list of column names) of the target table and a filter string that specifies a WHERE clause. The return value of the Delete operation is of Int32 data type, and denotes the number of rows deleted. Some values in the Delete operation message are treated in the following manner by the SQL adapter: - If the node for an image, XML, Text or Ntext column is not null in the WHERE clause of the message, the values specified for them are ignored because these values cannot be compared. - If the node for an identity or timestamp column is null, it is ignored. - If a UDT is not byte ordered, the value specified for the UDT column in the WHERE clause is ignored. For all other values in the Delete operation message: - If a value is specified for a column, the value is used in the WHERE clause of the DELETE statement ( where <column_name> = <value>).- If the node for a particular column is null, NULL is used in the DELETE statement ( where <column_name> is null). Note: If, for a particular record, there are no values that can be used in the DELETE statement (that is, either no values were specified for any column or if all the column values present were ignored), the adapter does not execute any DELETE statement. |
For more information about:
Performing these operations using BizTalk Server, see Insert, update, delete, or select operations using BizTalk Server with the SQL adapter.
Message structures and SOAP action for performing DML operations, see Message Schemas for Insert, Update, Delete, and Select Operations on Tables and Views.