Class AdbSqlClientProvider
A static class where the Get() overloads return AdbProvider immutable instances that wrap the SQL Server® Microsoft.Data.SqlClient ADO.NET database provider, which assists with writing provider-independent code, mapping between .NET CLR types and database types, etc.
Use the retrieved AdbProvider instance to create AdbConnectionString, AdbConnectionBuilder, AdbKeepOpenConnectionBuilder and AdbCommandBuilder instances, or pass the provider instance to other methods for them to use it.
Note that it is best practice to select which provider(s) 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.
All Get() overloads return instances with
MaxRowsPerBatch
equal to 1000.
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:
- AdbSqlClientInsertStatementService
- AdbSqlClientDataSourceInformationService
- AdbSqlClientTableCommandService
- AdbSqlClientTableInformationService
- AdbSqlClientTypeMapper
- AdbSqlClientTypeService
See SQL Database Access for further details.
Namespace: actionETL.Adb.SqlClientExternal
Assembly: actionETL.dll
Syntax
public static class AdbSqlClientProviderMethods
Get()
Gets an immutable Microsoft.Data.SqlClient AdbProvider instance with default settings for accessing a SQL Server® data source.
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 Get()Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider immutable instance. |
See Also
Get(Boolean)
Gets an immutable Microsoft.Data.SqlClient AdbProvider instance for accessing SQL Server® data sources.
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 Get(bool ansiQuotes)Parameters
| Type | Name | Description |
|---|---|---|
| Boolean | ansiQuotes | Set to
Set to |
Returns
| Type | Description |
|---|---|
| AdbProvider | The specified AdbProvider immutable instance. |
See Also
Get(Boolean, Int32, Int32, Int64)
Gets an immutable Microsoft.Data.SqlClient AdbProvider instance for accessing SQL Server® data sources.
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 Get(false, 1, 1, 0).
To use single row inserts without combining statements in any transaction (e.g. to allow
rejecting individual rows), call Get(false, 1, 1, -1).
Declaration
public static AdbProvider Get(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
Get(Int32, Int32, Int64)
Gets an immutable Microsoft.Data.SqlClient AdbProvider instance for accessing SQL Server® data sources.
It uses square brackets quote characters,
i.e. with IdentifierQuotePrefix set to [,
and IdentifierQuoteSuffix set to ].
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 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 Get(int defaultValuesPerBatch, int maxValuesPerBatch, 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. |
| 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. |