Set NoCount NO | OFF (Default)
The CONNECTIONSTRING attribute of the SQLConnection object supports the connection string key / value pair, which can be used to adjust the logic of the connection pool logic.
The following table describes the CONNECTIONSTRING value that can be used to adjust the connection pool behavior.
Name Default Description Connection Lifetime0 When the connection returns to the pool, it is compared to its creation time and the current time. If the time interval exceeds the value (in seconds) specified by the Connection Lifetime, the connection is destroyed. It can be used to enforce load balancing between running servers and just online servers. If the value is zero (0), the pool connection will have the largest timeout period. Connection reset'true 'is determined whether it will reset it when you remove a database connection from the pool. For Microsoft SQL Server Version 7.0, if set to false, avoid experience an additional round-trip process when the connection is acquired, but must be noted that the connection status (such as the database context) will not be reset. Enlist'true 'When True, if there is a transaction context, the pool management program will automatically register in the current transaction context of the thread. The maximum number of connections allowed in the Max Pool Size100 pool. Min Pool Size0 The minimum connection number of maintained in the pool. POOLING 'RRUE' When True, the connection will be removed from the corresponding pool, or create a connection if necessary and add it to the corresponding pool.
Automatic delivering
If the table from the data source contains an automatic handset, the value generated by the value generated by the data source is filled in the DataSet, and the method is to return automatic deliaving in the form of a stored procedure output parameter and map a column in the table. Or use the DataAdapter's RowupDated event. For example, see Retrieving the "ID" or "Auto Number" value.
However, the value in the DataSet may not be synchronized with the values in the data source and cause unexpected behavior. For example, consider a table that contains automatically increasing primary key columns Customerid. If two new customers are added to the DataSet, they will receive the automatically incremented Customerid value 1 and 2. When passing the second customer line to the UPDATE method of the DataAdapter, the newly added row will receive the automatic increment Customerid value of the data source, which does not match the value 2 in the DataSet. When DataAdapter fills the row in the DataSet using the return value, the Customerid of the first customer is already 1, thereby constraint conflicts.
To avoid this behavior, it is recommended that the autoincrementStep is a column created in the DataSet in the DataSet in the DataSet in the DataSet, and ensures that the data source generates from 1 to the DataSet in the DataSet. Automatic incremental identification values in the increment of the front step length. Thus, the DataSet will generate a negative amount to the automatic increment value, which does not conflict with the positive automatic increment value generated by the data source. Another method is to use the column of the GUID type instead of automatic delivery. The GUID generated by generating the GUID value is the same as the GUID generated by the data source. For more information on defining columns in DataSle, see Defining the architecture of the data table.
Sort by insert, update, and delete
In many cases, it is quite important to send the data source through the DataSet to the data source. For example, if the primary key value of the existing row has been updated, a new line with a new key key value is added, it is necessary to process updates before processing insertion.
You can use the DATATABLE SELECT method to return only the DataRow array with a specific ROWSTATE. The returned DataRow array can then be passed to the UPDATE method of the DataAdapter to process the modified row. By specifying the subset of rows to be updated, you can control the order in which insert, update, and delete. For example, the following code ensures that the row already deleted in the table is first processed, and then process the updated row and then process the inserted row.
[Visual Basic]
DIM UPDTABLE AS DATATABLE = Custds.Tables ("Customers")
'First process deletes.
Custda.Update (Updtable.select (Nothing, Nothing, DataViewRowState.delete))
'Next process updates.
Custda.Update (Updtable.Select (Nothing, Nothing, DataViewrowState.ModifiedCurrent))
'Finally, Process INSERTS.
Custda.Update (Updtable.select (Nothing, Nothing, DataViewrowState.Added))
ForeignKeyConstraint
ForeignKeyConstRAINT enforces the rules for propagating and deleting the relevant tables. For example, if you update or delete a value in a single table, this same value is also used in one or more related tables, and ForeignKeyConstRAINT will determine the operations that occur in the relevant table.
ForeignKeyConstraint's deleteRule and updateUle properties are defined in the user tried to delete or update actions taken when you are in a row. The following table describes the different settings that can be used for the deleterule and updaterule properties of ForeignKeyConstraint.
Rule Description Cascade deletes or updates the relevant row. This is the default setting. SetNULL sets the value in the relevant row to DBNULL. SetDefault sets the value in the relevant line to the default. NONE Specifies that you do not take any operations for the relevant line.
ForeignKeyConstRAINT can limit and disseminate changes to the relevant columns. Based on the properties set for the column, and if the DataSet's EnforceConstraints property is TRUE, some specific operations perform certain specifications to the father will result in an exception. For example, if ForeignKeyConstraint's deleteRule property is None, then in the case of a child row, you cannot delete the father.
You can create a single column or a set of foreign key constraints between single coli or a set of colors by using the ForeignKeyConstraint constructor. Pass the result's ForeignKeyConstraint object to the add method of the constraints attribute of the table, which is a constraintcollection. You can also pass the constructor parameters to several overloads of the CONSTRAINTCOLLECTION ADD method to create ForeignKeyConstraint.
AcceptrejectRule
You can use the AcceptChanges method to accept changes to the line, or cancel the changes in the REJECTCHANGES method using DataSet, DataTable or DataRow. When DataSet contains ForeignKeyConstraints, call AcceptChanges or RejectChanges methods to enforce AcceptRejectRule. The ACCEPTREJECTRULE attribute of ForeignKeyConstRAINT determines the operation of the sub-line when calling AcceptChanges or RejectChanges for the parent line. The following table lists the values that AcceptRejectRule can set.
Operating Instructions CASCADE accepts or rejects changes to the sub-line. NONE Specifies that there is no action to take any operation. It's the default value.
UniqueConstraint
UNIQUECONSTRAINT objects (a separate column or set column that can be assigned to DataTable) ensures that all data in a column or multiple columns is unique for each line. By using the UNIQUECONSTRAINT constructor, you can create unique constraints for a column or set column. Pass the uniqueConstRAINT object of the result to the add method of the constraints attribute of the table, which is a constraintcollection. You can also pass the constructor parameters to several overloads of the ADD method of the ConstraintCollection to create uniqueConstraint. When creating uniqueConstraints for a row or multiple columns, you can choose whether to specify this column or these columns as the primary key.
You can also create unique constraints for a column by setting the column's UNIQUE attribute to TRUE. Alternatively, any unique constraint that may exist can be removed by setting a single column's UNIQUE property to false. In addition, define a column or multiple columns as a primary key that will automatically create unique constraints for one or more specific columns. If a column is removed from the DataTable PrimaryKey property, UNIQUECONSTRAINT is also removed.
Paging query:
ORDERSQL = "SELECT TOP" & Pagesize & "* from Orders Where OrderID>" & LastRecord & "Order by OrderID"
