Skip to content

API Library ADO

Jump to bottom
Robert Polak edited this page Jul 29, 2019 · 31 revisions

Home / API Library / ADO

API Library ADO handle SQL Server. The API Library ADO Methods list with Parameters, Return examples.

Configuration

Configuration - Details

Configuration of ADO variables at App.config.

<configuration>
	<AppSettings>
		<!-- ADO - Default Connection Name -->
		<add key="API_ADO_DEFAULT_CONNECTION" value="defaultConnection" />
		<!-- ADO - Execution timeout in seconds -->
		<add key="API_ADO_EXECUTION_TIMEOUT" value="600" />
		<!-- ADO - Bulk Copy timeout in seconds -->
		<add key="API_ADO_BULKCOPY_TIMEOUT" value="600" />
		<!-- ADO - Bulk Copy BatchSize in rows (below 5000 to avoid Table locking) -->
		<add key="API_ADO_BULKCOPY_BATCHSIZE" value="4999" />		
	</AppSettings>
	<connectionStrings>
		<add name="defaultConnection" connectionString="Server=;Initial Catalog=;User ID=;Password=;Persist Security Info=False;Column Encryption Setting=enabled;" />
	</connectionStrings>
</configuration>

ADO

ADO - Details

API Library ADO handle SQL Server Open Connection to Data Base.

Method: API.ADO.ADO

Parameters:

Name Type Default Description
connectionName String API_ADO_DEFAULT_CONNECTION default Connection

Return: Access to SQL Server.

Open Connection

Open Connection - Details

Open Connection to Data Base.

Method: API.ADO.OpenConnection

Parameters:

Name Type Default Description
connectionName String API_ADO_DEFAULT_CONNECTION default Connection

Return: void.

API.ADO.OpenConnection code example:

 public void OpenConnection(string connectionName)
        {
            try
            {
            ...
            // Get the Connection String form the associated Name
            string connectionString = ConfigurationManager.ConnectionStrings[connectionName].ConnectionString;
            connection = new SqlConnection(connectionString);
            connection.Open();
            ...
            }
        }

Close Connection

Open Connection - Details

Close Connection to Data Base.

Method: API.ADO.CloseConnection

Parameters:

Name Type Default Description
onDispose Bool false default Connection

Return: void.

Start Transaction

Start Transaction - Details

Start Transaction on active Data Base Connection.

Method: API.ADO.StartTransaction

Parameters:

Name Type Default Description
transactionIsolation IsolationLevel IsolationLevel.Snapshot Isolation Level
IsolationLevel enum - Details 
        * A different isolation level than the one specified is being used, but the level
        cannot be determined.  

          `Unspecified` = -1,
        
        * The pending changes from more highly isolated transactions cannot be overwritten.  

          `Chaos` = 16,
        
        A dirty read is possible, meaning that no shared locks are issued and no exclusive
        locks are honored.

          `ReadUncommitted` = 256,
        
        Shared locks are held while the data is being read to avoid dirty reads, but
        the data can be changed before the end of the transaction, resulting in non-repeatable
        reads or phantom data.

          `ReadCommitted `= 4096,
        
        * Locks are placed on all data that is used in a query, preventing other users
        from updating the data. Prevents non-repeatable reads but phantom rows are still
        possible.
          `RepeatableRead` = 65536,
        
        * A range lock is placed on the System.Data.DataSet, preventing other users from
        updating or inserting rows into the dataset until the transaction is complete.

          `Serializable `= 1048576,
        
        * Reduces blocking by storing a version of data that one application can read while
        another is modifying the same data. Indicates that from one transaction you cannot
        see changes made in other transactions, even if you requery.

          `Snapshot` = 16777216

Return: void.

Reset Transaction

Reset Transaction - Details

Reset Transaction on active Data Base Connection.

Method: API.ADO.ResetTransaction

Parameters: N/A

Return: void.

Commit Transaction

Commit Transaction- Details

Commit Transaction on active Data Base Connection.

Method: API.ADO.CommitTransaction

Parameters: N/A

Return: void.

Rollback Transaction

Rollback Transaction - Details

Rollback Transaction on active Data Base Connection.

Method: API.ADO.RollbackTransaction

Parameters: N/A

Return: void.

Execute Non Query Procedure

Rollback Transaction - Details

Execute Non Query Procedure on active Data Base Connection.

Method: API.ADO.ExecuteNonQueryProcedure

Parameters:

Name Type Default Description
procedureName String Procedure Name
inputParams Array[ADO_inputParams] inputParams Input Params
returnParam object Return Param
outputParam object ADO_outputParam Output Param

ADO_inputParams

Name Type Default Description
name string name
value dynamic value
typeName string typeName

returnParam

Name Type Default Description
name string name
value string value

outputParam

Name Type Default Description
name string name
value string value

Return: void.

Execute Bulk Copy

Execute Bulk Copy - Details

Execute Bulk Copy on active Data Base Connection.

Method: API.ADO.ExecuteBulkCopy

Parameters:

Name Type Default Description
destinationTableName String Destination Table Name
mappings Array[SqlBulkCopyColumnMapping] Mappings
dt object DataTable object
useCurrentTransaction Bool false Use Current Transaction
copyOptions enum SqlBulkCopyOptions.Default Copy Options

SqlBulkCopyColumnMapping Parameter

Name Type Default Description
DestinationColumn string Destination Column
DestinationOrdinal int Destination Ordinal
SourceColumn int Source Column
SourceOrdinal string Source Ordinal
`SqlBulkCopyOptions` enum Parameter - Details 
        * Use the default values for all options.
		
          `Default` = 0,
        
        Preserve source identity values. When not specified, identity values are assigned
        by the destination.
		
          `KeepIdentity` = 1,
        
        Check constraints while data is being inserted. By default, constraints are not
        checked.
		
          `CheckConstraints` = 2,
        
        Obtain a bulk update lock for the duration of the bulk copy operation. When not
        specified, row locks are used.
		
          `TableLock` = 4,
        
        Preserve null values in the destination table regardless of the settings for
        default values. When not specified, null values are replaced by default values
        where applicable.
		
          `KeepNulls` = 8,
        

        When specified, cause the server to fire the insert triggers for the rows being
        inserted into the database.
		
          `FireTriggers` = 16,

        When specified, each batch of the bulk-copy operation will occur within a transaction.
        If you indicate this option and also provide a System.Data.SqlClient.SqlTransaction
        object to the constructor, an System.ArgumentException occurs.
		
        `UseInternalTransaction` = 32,

        When specified,AllowEncryptedValueModifications enables bulk copying of encrypted
        data between tables or databases, without decrypting the data. Typically, an
        application would select data from encrypted columns from one table without decrypting
        the data (the app would connect to the database with the column encryption setting
        keyword set to disabled) and then would use this option to bulk insert the data,
        which is still encrypted. For more information, see Always Encrypted.Use caution
        when specifying AllowEncryptedValueModifications as this may lead to corrupting
        the database because the driver does not check if the data is indeed encrypted,
        or if it is correctly encrypted using the same encryption type, algorithm and
        key as the target column.
		
          `AllowEncryptedValueModifications` = 64

Return: void.

Execute Reader Procedure

Execute Reader Procedure - Details

Execute Reader Procedure on active Data Base Connection.

Method: API.ADO.ExecuteReaderProcedure

Parameters:

Name Type Default Description
destinationTableName String Destination Table Name
procedureName String Procedure Name
inputParams Array[ADO_inputParams] Input Params

ADO_inputParams Parameter

Name Type Default Description
name string name
value dynamic value
typeName string typeName

Return: ADO_readerOutput object.

Name Type Default Description
hasData bool hasData
sizeInByte int sizeInByte
timeInSec float timeInSec
meta Array[dynamic] meta object
data Array[dynamic] data object

Dispose

Dispose - Details

Dispose all resources and close Data Base Connection.

Method: API.ADO.Dispose

Parameters: N/A

Return: void.

Top

Methods
Test
Clone this wiki locally