Adb SqlClient Provider
You can use this database (among others) in your ETL:
- Databases: Microsoft SQL Server® 2008 onwards (external documentation)
- Both on-premises and cloud, including Azure SQL and SQL Server on other cloud platforms
- Features:
- Includes all optional provider services
- AdbSqlClientTableInformationService supports tables (including temporary), views, and table-valued functions
- Includes provider-specific types
- Includes Bulk Insert with AdbSqlClientBulkInsertTarget<TInput>
- Includes all optional provider services
- Adb provider: AdbSqlClientProvider
- .NET provider: "Microsoft.Data.SqlClient", a free
Microsoft.Data.SqlClient
NuGet package retrieved automatically (external documentation)
- Type enumeration: SqlDbType
- Type namespace: System.Data.SqlTypes
- Client namespace: Microsoft.Data.SqlClient
- Connection strings:
Important
The AdbInsertTarget<TInputError> batch insert performance is impacted by
an issue in the underlying
Microsoft.Data.SqlClient
provider. Hopefully this will soon be resolved by the
SqlClient
team, but until then you can alleviate the issue by doing either of:
- Use Bulk Insert by replacing
AdbInsertTarget
with AdbSqlClientBulkInsertTarget<TInput> - Instead of
SqlClient
, use the Adb ODBC Provider for SQL Server) for the AdbInsertTarget<TInputError> worker
Data Type Mappings
This section lists which database types map to or from which .NET types when querying a SqlClient server and working with its database parameters. The listed .NET types below are fully supported by the dataflow.
Note
Mappings tagged with AdbColumnSchema
below depend on whether or not an
AdbColumnSchema is supplied to
SetMappedType(AdbParameter, Type, AdbColumnSchema, ParameterDirection).
Of the out of box workers, this is only supplied by AdbInsertTarget<TInputError>.
Provider-Independent .NET Types
.NET Type Display Name | DbType (or SqlDbType) enum |
SQL Type Example |
---|---|---|
System.Boolean, System.Boolean? |
Boolean | bit |
System.Byte, System.Byte? |
Byte | tinyint |
System.Byte[] |
Binary | binary(7) , image , varbinary(10) , varbinary(max) |
System.DateTime, System.DateTime? |
SqlDbType.DateTime2 | datetime2 . Without AdbColumnSchema also: date , datetime , smalldatetime . |
System.DateTime, System.DateTime? |
SqlDbType.Date, SqlDbType.DateTime, SqlDbType.SmallDateTime | With AdbColumnSchema: date , datetime , smalldatetime |
System.DateTimeOffset, System.DateTimeOffset? |
DateTimeOffset | datetimeoffset |
System.Decimal, System.Decimal? |
Decimal | decimal(38,0) , money , numeric(10,2) , smallmoney |
System.Double, System.Double? |
Double | float |
System.Guid, System.Guid? |
Guid | uniqueidentifier |
System.Int16, System.Int16? |
Int16 | smallint |
System.Int32, System.Int32? |
Int32 | int , integer |
System.Int64, System.Int64? |
Int64 | bigint |
System.Single, System.Single? |
Single | real |
System.String | String | char(32) , nchar(32) , ntext , nvarchar(max) , text , varchar(max) |
System.TimeSpan, System.TimeSpan? |
SqlDbType.Time | time |
Provider-Specific .NET Types
.NET type display name System.Data.SqlTypes |
SqlDbType enum |
SQL Type Example |
---|---|---|
SqlBinary | Binary | binary(7) , image , varbinary(10) , varbinary(max) |
SqlBoolean | Bit | bit |
SqlByte | TinyInt | tinyint |
SqlDateTime | DateTime | datetime , smalldatetime |
SqlDecimal | Decimal | decimal(38,0) , numeric(10,2) |
SqlDouble | Float | float |
Microsoft.Data.SqlTypes.SqlFileStream | VarBinary(max) |
varbinary(max) with FILESTREAM column attribute |
SqlGuid | UniqueIdentifier | uniqueidentifier |
SqlInt16 | SmallInt | smallint |
SqlInt32 | Int | int , integer |
SqlInt64 | BigInt | bigint |
SqlMoney | Money | money , smallmoney |
SqlSingle | Real | real |
SqlString | NVarChar | char(32) , nchar(32) , ntext , nvarchar(max) , text , varchar(max) |
Note
This provider also uses SqlDbType when mapping System.DateTime and System.TimeSpan .NET types above.
Identifiers
- Quoting character:
- AdbSqlClientProvider.Get(): Default square brackets
[]
, e.g.[My Table]
- AdbSqlClientProvider.Get(true, false): ANSI double quote
"
, e.g."My Table"
- AdbSqlClientProvider.Get(): Default square brackets
- Max length: 128 (116 for temporary objects)
- Microsoft documentation: SQL Server Database Identifiers
- Useful summary: Completely Complete List of Rules for T-SQL Identifiers
Note that dataflow column name matching is ordinal case insensitive, but a case sensitive match takes precedence over a case insensitive match. See Dataflow Column Mapping for details.
Parameters
Use named parameters of the format @MyParameterName
. You can use
GetPlaceholder(String) to get the placeholder
in a portable manner.
Stored Procedures
Here we execute a stored procedure with parameters. Even building an executable program (or utility method) for executing this single query can be very worthwhile since actionETL adds configuration, error handling, and logging.
We create the command builder outside the worker system to make it easy to return a parameter value to the caller. More commonly, commands and connections are created inside the worker system, as and when they are needed.
using actionETL;
using actionETL.Adb;
using actionETL.Adb.SqlClientExternal;
using System.Data;
using System.Data.SqlTypes;
public static partial class ExecuteStoredProcedureSqlClient
{
public static SqlDateTime GetMaxDateTime()
{
// Get a command builder using "actionetl.aconfig.json" connection details.
// StoredProcedure(string) sets both CommandText and CommandType properties.
var cb = AdbSqlClientProvider.Get()
.CreateCommandBuilderFromDefaultAConfig("SqlServer")
.StoredProcedure("dbo.GetMaxDateTime");
// Many methods return an instance and allow method chaining.
// Whenever possible, set the database type explicitly.
cb.Parameters.Add("countryId").SetDbValue(42, DbType.Int32);
// Database type not set, provider has to guess the type (which is less good)
cb.Parameters.Add("category").SetValue("Batteries");
// Save the parameter reference, since we retrieve the returned value later
var maxDateTime = cb.Parameters.Add("maxDateTime", ParameterDirection.Output)
.SetProviderType(SqlDbType.DateTime);
new WorkerSystem()
// Curly braces and semi-colon not needed in 1-line lambda
.Root(ws => _ = new AdbExecuteNonQueryWorker(ws, "Call GetMaxDateTime", cb))
.Start()
.ThrowOnFailure();
// Value is of type object, and must be cast
return (SqlDateTime)maxDateTime.ProviderValue;
}
}
/* The example assumes the following stored procedure already exists:
CREATE PROCEDURE dbo.GetMaxDateTime
@countryId int,
@category nvarchar(50),
@maxDateTime DateTime output
AS
BEGIN
SET NOCOUNT ON;
-- Use input parameters...
SELECT @maxDateTime = '2017-03-13';
END
*/
Adb Information
The Adb Information example program dumps the below information with this provider querying a SQL Server. See that example for more information.
=== AdbConnectionBuilder ===
Provider=Microsoft.Data.SqlClient:SQL Server:
=== AdbConnection ===
DataSourceName=(localdb)\MSSQLLocalDB
DatabaseName=actionetl_tester
NumberOfColumnIdentifierParts=4
NumberOfColumnRestrictions=4
ServerVersion=13.00.4001
=== AdbConnection.Information ===
CompositeIdentifierSeparator=.
CompositeIdentifierSeparatorPattern=\.
DataSourceProductName=Microsoft SQL Server
DataSourceProductVersion=13.00.4001
GetPlaceholder("MyName")=@MyName
GroupByBehavior=Unrelated
IdentifierCase=Insensitive
IdentifierPattern=(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\"[^\"\0]|\"\"+\"$)
IdentifierQuotePrefix=[
IdentifierQuotePrefixes=["
IdentifierQuoteSuffix=]
IdentifierQuoteSuffixes=]"
OrderByColumnsInSelect=False
ParameterMarkerFormat={0}
ParameterMarkerPattern=@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
ParameterNameMaxLength=128
ParameterNamePattern=^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)
QuotedIdentifierCase=Insensitive
QuotedIdentifierPattern=(([^\[]|\]\])*)
QuoteIdentifierPart("My Table")=[My Table]
StatementSeparatorPattern=;
StringLiteralPattern='(([^']|'')*)'
SupportedJoinOperators=Inner, LeftOuter, RightOuter, FullOuter
UsesPositionalParameters=False
=== AdbConnection.TableInformation.CreateTableIdentifier([My Database].[My Schema].MyTable) ===
OriginalCompositeName=[My Database].[My Schema].MyTable
OriginalTableName=MyTable
OriginalSchemaName=[My Schema]
OriginalCatalogName=[My Database]
QuotedCompositeName=[My Database].[My Schema].[MyTable]
QuotedTableName=[MyTable]
QuotedSchemaName=[My Schema]
QuotedCatalogName=[My Database]
UnquotedTableName=MyTable
UnquotedSchemaName=My Schema
UnquotedCatalogName=My Database
=== AdbConnection.SchemaInformationCollection ===
Note: The metadata schemas can be retrieved with AdbConnection.GetSchema().
The returned information is provided from the .NET provider as-is. Some collections
can be inaccurate, and different values can also be needed for different purposes,
which is why database specific actionETL providers can be created that replace
individual values with the desired ones. See the vendor provider documentation for
details on the returned information.
CollectionName=MetaDataCollections
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=DataSourceInformation
IsCommon=True
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=DataTypes
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=Restrictions
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=ReservedWords
IsCommon=False
NumberOfRestrictions=0
NumberOfIdentifierParts=0
CollectionName=Users
IsCommon=False
NumberOfRestrictions=1
NumberOfIdentifierParts=1
CollectionName=Databases
IsCommon=False
NumberOfRestrictions=1
NumberOfIdentifierParts=1
CollectionName=Tables
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=Columns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=AllColumns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=ColumnSetColumns
IsCommon=False
NumberOfRestrictions=3
NumberOfIdentifierParts=3
CollectionName=StructuredTypeMembers
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=Views
IsCommon=False
NumberOfRestrictions=3
NumberOfIdentifierParts=3
CollectionName=ViewColumns
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=4
CollectionName=ProcedureParameters
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=1
CollectionName=Procedures
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=ForeignKeys
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=IndexColumns
IsCommon=False
NumberOfRestrictions=5
NumberOfIdentifierParts=4
CollectionName=Indexes
IsCommon=False
NumberOfRestrictions=4
NumberOfIdentifierParts=3
CollectionName=UserDefinedTypes
IsCommon=False
NumberOfRestrictions=2
NumberOfIdentifierParts=1