If [SELECT which returns a single value] is selected when adding a query to a TableAdapter, it will not be possible to execute the SQL statement displayed on the window - therefore, correct the SQL statement.
If there is more than one Function with the same name, it will not be possible to create a TableAdapter using that Function.
When defining Function, ensure that the OUT parameter is the last one defined.
If the OUT parameter is not the last one defined, it will not be possible to enter any parameter values after the OUT parameter for the TableAdapter DataPreview.
Example incorrect definition: func(out p1 integer, inout p2 integer)
Example correct definition: func(inout p2 integer, out p1 integer)
Prefix named parameters with "@".
Uppercase object names cannot be used, even when enclosed in double quotation marks.
To use uppercase object names enclosed in double quotation marks, include them in SQL statements and enter these in the [Generate the SQL statements] window rather than in the Query Builder.
SQL statements that specify date or time data literals cannot be used. To use these, enter the SQL statements directly in the [Generate the SQL statements] window rather than in the Query Builder.
SQL statements cannot be correctly generated if the SQL statement specified in Filter matches any of the conditions below:
It uses PostgreSQL intrinsic operators such as << or ::.
It uses functions with keywords such as AS, FROM, IN, OVER.
Example: extract(field from timestamp), RANK( ) OVER
It uses functions with the same names as those prescribed in SQL conventions, but that require different arguments.
The temporary table is not displayed.
The database object function (FUNCTION) is displayed as a procedure.
The CommandBehavior.KeyInfo argument must be specified if executing ExecuteReader before obtaining metadata using GetSchemaTable.
Example
NpgsqlDataReader ndr=cmd.ExecuteReader(CommandBehavior.KeyInfo); DataTable dt = dr.GetSchemaTable();
Notes on automatically generating update-type SQL statements
If the SQL statement includes a query (which cannot be updated) that matches any of the conditions below, an update-type SQL statement will be generated (note that it may not be possible to execute this SQL statement in some cases):
It includes derived tables
It includes the same column name as the select list
Update-type SQL statements will be automatically generated in the following cases:
If update statements are obtained using NpgsqlCommandBuilder
If data is updated using NpgsqlDataAdapter
If data is updated using TableAdapter
Notes on distributed transactions
Applications using transaction scope can use distributed transactions by linking with Microsoft Distributed Transaction Coordinator (MSDTC). In this case, note the following:
Ensure that the value of max_prepared_transactions is greater than max_connection, so that "PREPARE TRANSACTION" can be issued for each transaction that simultaneously connects to the database server.
If each transaction in the transaction scope accesses the same resource using different connections, the database server will perceive it as requests from different applications, and a deadlock may occur. By configuring a timeout value for the transaction scope beforehand, the deadlock can be broken.
