Class AdbOdbcProvider
A static class where the GetUniversal() and GetUniversalBasic() overloads return AdbProvider immutable instances that wrap the ODBC ADO.NET database provider for accessing ODBC data sources, which assists with writing provider-independent code, mapping between .NET CLR types and database types, etc. Use it to instantiate AdbConnectionString, AdbConnectionBuilder, AdbKeepOpenConnectionBuilder and and AdbCommandBuilder types, or pass the provider instance to other methods for them to use it.
Use GetSqlServer(Boolean) etc. to get a targeted provider if available, since they support the most features. Otherwise use the GetUniversal() and GetUniversalBasic() overloads to get a universal provider that works with most ODBC data sources, but lacks some optional services.
Note that ODBC providers do not support provider-specific data types (i.e. SqlDateTime etc.)
Note that it is best practice to select which provider to use in only one place in the application, to make it easy to switch to a different provider instance, especially since settings such as how to quote identifiers is specified by creating the appropriate provider instance.
Each AdbProvider instance contains a set of services that implements the provider
functionality. You can create a new modified provider instance from an existing one by using
its With*() methods to replace one of its services.
Custom services can be derived from existing ones, or created from scratch.
Alternatively, create a custom provider by using the AdbProvider
constructor, specifying all the services.
This provider uses the following service classes:
- AdbSql92InsertStatementService
- AdbOdbcDataSourceInformationService
- AdbOdbcTypeMapper
- AdbOdbcTypeService
See SQL Database Access for further details.
Namespace: actionETL.Adb.OdbcExternal
Assembly: actionETL.dll
Syntax
public static class AdbOdbcProviderMethods
GetSqlServer()
Gets an immutable ODBC AdbProvider instance with default settings, targeted for accessing a SQL Server® data source. It supports all optional services.
It uses square brackets quote characters,
i.e. with IdentifierQuotePrefix set to [,
and IdentifierQuoteSuffix set to ].
Any AdbInsertTarget<TInputError> will generate multi-row
insert statements (only supported on SQL Server® 2008 and later) with
DefaultValuesPerBatch
equal to 256,
MaxValuesPerBatch
equal to 2098, and
DefaultValuesPerTransaction
equal to 16384.
Note that with multiple rows per batch or per transaction, any insert error will reject all the rows in the batch or the transaction.
Declaration
public static AdbProvider GetSqlServer()Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider immutable instance. |
See Also
GetSqlServer(Boolean)
Gets an immutable ODBC AdbProvider instance, targeted for accessing a SQL Server® data source. It supports all optional services.
Any AdbInsertTarget<TInputError> will generate multi-row
insert statements (only supported on SQL Server® 2008 and later) with
DefaultValuesPerBatch
equal to 256,
MaxValuesPerBatch
equal to 2098, and
DefaultValuesPerTransaction
equal to 16384.
Note that with multiple rows per batch or per transaction, any insert error will reject all the rows in the batch or the transaction.
Declaration
public static AdbProvider GetSqlServer(bool ansiQuotes)Parameters
| Type | Name | Description |
|---|---|---|
| Boolean | ansiQuotes | Set to
Set to |
Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider immutable instance. |
See Also
GetSqlServer(Boolean, Int32, Int32, Int64)
Gets an immutable ODBC AdbProvider instance, targeted for accessing a SQL Server® data source. It supports all optional services.
The batch and transaction parameters control how insert statements for any AdbInsertTarget<TInputError> will be created.
Inserting multiple rows per batch or per transaction typically increases performance, although using too many parameters can decrease performance, and increases database resource usage.
Note that with multiple rows per batch or per transaction, any insert error will reject all the rows in the batch or the transaction.
Note that multi-row insert statements are only supported on SQL Server® 2008 and later.
To use single row inserts that are wrapped in a large transaction for performance
(e.g. for SQL Server 2005 or earlier), call GetSqlServer(false, 1, 1, 0).
To use single row inserts without combining statements in any transaction (e.g. to allow
rejecting individual rows), call GetSqlServer(false, 1, 1, -1).
Declaration
public static AdbProvider GetSqlServer(bool ansiQuotes, int defaultValuesPerBatch, int maxValuesPerBatch, long defaultValuesPerTransaction)Parameters
| Type | Name | Description |
|---|---|---|
| Boolean | ansiQuotes | Set to
Set to |
| Int32 | defaultValuesPerBatch | The default number of values (or parameters) per insert batch (or multi-row statement), which determines the default number of rows per batch.
When set to |
| Int32 | maxValuesPerBatch | The maximum number of values (or parameters) allowed per batch or multi-row
insert statement, e.g. The maximum number of rows per batch is calculated by dividing by the number of values (or parameters) that is processed per row, and using no less than one row per batch. |
| Int64 | defaultValuesPerTransaction | The default number of values (or parameters) to use per explicit transaction. This is used to calculate the default number of rows per explicit transaction.
When set to
When set to
When set to less than Note that if the connection already has an active transaction, the insert worker should participate in it, irrespective of what this setting is set to. Wrapping just one or a few insert statements in a transaction is possible, but typically decreases performance. |
Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider instance. |
See Also
GetUniversal()
Gets a universal immutable System.Data.Odbc AdbProvider instance with default settings for accessing ODBC data sources.
It includes support for IAdbTableInformationService and TableInformation.
This universal provider works with many different ODBC data sources, but it lacks support for IAdbTableCommandService, and will not work at all with some data sources. Where available, do use (ODBC or targeted) providers that are configured for a specific data source.
Any AdbInsertTarget<TInputError> will have
DefaultValuesPerTransaction
equal to 256 and
MaxValuesPerBatch
equal to 1000.
Declaration
public static AdbProvider GetUniversal()Returns
| Type | Description |
|---|---|
| AdbProvider |
See Also
GetUniversal(Int32, Int32, Int32, Int64)
Gets a universal immutable System.Data.Odbc AdbProvider instance for accessing ODBC data sources.
It includes support for IAdbTableInformationService and TableInformation.
This universal provider works with many different ODBC data sources, but it lacks support for IAdbTableCommandService, and will not work at all with some data sources. Where available, do use (ODBC or targeted) providers that are configured for a specific data source.
The batch and transaction parameters control how insert statements for any AdbInsertTarget<TInputError> will be created.
Inserting multiple rows per batch or per transaction typically increases performance, although using too many parameters can decrease performance, and increases database resource usage.
Note that with multiple rows per batch or per transaction, any insert error will reject all the rows in the batch or the transaction.
To use single row inserts that are wrapped in a large transaction for performance,
call Get(1, 1, 0).
To use single row inserts without combining statements in any transaction (e.g. to allow
rejecting individual rows), call Get(1, 1, -1).
Declaration
public static AdbProvider GetUniversal(int defaultValuesPerBatch, int maxValuesPerBatch, int maxRowsPerBatch, long defaultValuesPerTransaction)Parameters
| Type | Name | Description |
|---|---|---|
| Int32 | defaultValuesPerBatch | The default number of values (or parameters) per insert batch (or multi-row statement), which determines the default number of rows per batch.
When set to |
| Int32 | maxValuesPerBatch | The maximum number of values (or parameters) allowed per batch or multi-row
insert statement, e.g. The maximum number of rows per batch is calculated by dividing by the number of values (or parameters) that is processed per row, and using no less than one row per batch. |
| Int32 | maxRowsPerBatch | The maximum number of rows allowed per batch or multi-row
insert statement, e.g. |
| Int64 | defaultValuesPerTransaction | The default number of values (or parameters) to use per explicit transaction. This is used to calculate the default number of rows per explicit transaction.
When set to
When set to
When set to less than Note that if the connection already has an active transaction, the insert worker should participate in it, irrespective of what this setting is set to. Wrapping just one or a few insert statements in a transaction is possible, but typically decreases performance. |
Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider instance. |
See Also
GetUniversalBasic()
Gets a universal immutable System.Data.Odbc AdbProvider instance with default settings for accessing ODBC data sources.
This basic provider lack support for IAdbTableInformationService and TableInformation, as well as for IAdbTableCommandService, and will not work at all with some data sources. Where available, do use (ODBC or targeted) providers that are configured for a specific data source.
Any AdbInsertTarget<TInputError> will generate multi-row
insert statements with
DefaultValuesPerBatch
equal to 256, and
DefaultValuesPerTransaction
equal to 16384.
Declaration
public static AdbProvider GetUniversalBasic()Returns
| Type | Description |
|---|---|
| AdbProvider |
See Also
GetUniversalBasic(Int32, Int32, Int32, Int64)
Gets a universal immutable System.Data.Odbc AdbProvider instance for accessing ODBC data sources.
This basic provider lack support for IAdbTableInformationService and TableInformation, as well as for IAdbTableCommandService, and will not work at all with some data sources. Where available, do use (ODBC or targeted) providers that are configured for a specific data source.
The batch and transaction parameters control how insert statements for any AdbInsertTarget<TInputError> will be created.
Inserting multiple rows per batch or per transaction typically increases performance, although using too many parameters can decrease performance, and increases database resource usage.
Note that with multiple rows per batch or per transaction, any insert error will reject all the rows in the batch or the transaction.
To use single row inserts that are wrapped in a large transaction for performance,
call Get(1, 1, 0).
To use single row inserts without combining statements in any transaction (e.g. to allow
rejecting individual rows), call Get(1, 1, -1).
Declaration
public static AdbProvider GetUniversalBasic(int defaultValuesPerBatch, int maxValuesPerBatch, int maxRowsPerBatch, long defaultValuesPerTransaction)Parameters
| Type | Name | Description |
|---|---|---|
| Int32 | defaultValuesPerBatch | The default number of values (or parameters) per insert batch (or multi-row statement), which determines the default number of rows per batch.
When set to |
| Int32 | maxValuesPerBatch | The maximum number of values (or parameters) allowed per batch or multi-row
insert statement, e.g. The maximum number of rows per batch is calculated by dividing by the number of values (or parameters) that is processed per row, and using no less than one row per batch. |
| Int32 | maxRowsPerBatch | The maximum number of rows allowed per batch or multi-row
insert statement, e.g. |
| Int64 | defaultValuesPerTransaction | The default number of values (or parameters) to use per explicit transaction. This is used to calculate the default number of rows per explicit transaction.
When set to
When set to
When set to less than Note that if the connection already has an active transaction, the insert worker should participate in it, irrespective of what this setting is set to. Wrapping just one or a few insert statements in a transaction is possible, but typically decreases performance. |
Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider instance. |