Adb SqlClient Provider

You can use this database (among others) in your ETL:

Important

The Microsoft.Data.SqlClient provider now (4.0.0 onward) enables encryption by default, and requires a valid certificate for the connection. If the certificate is from a non-trusted source you may get the following exception:

  • "A connection was successfully established with the server, but then an error occurred during the login process. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)"

Recommended Solutions - do one of:

  • Install the target SQL Server's TLS/SSL certificate in the client environment.
  • Set "TrustServerCertificate=true" in the connection string.

Insecure solutions - do one of:

  • Set "Encrypt=false" in the connection string.
  • Disable the "Force Encryption" setting on SQL Server.

For details, see Untrusted certificate authority error, SQL Server enforced encryption, and Connection string keywords.

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.

Unless otherwise noted, all the below .NET types can be round-tripped to the database, i.e. written out with one of the database target workers, and read back with AdbDataReaderSource<TOutput>.

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 types below with Shallow or SingleShallowThenDeep copy policy support multi-copy, the others (with SingleShallow) do not.

.NET Type Display Name DbType (or SqlDbType) enum SQL Type Example Copy Policy
Any enum or nullable enum Byte, Int16, Int32, Int64 tinyint, smallint, int, bigint Shallow
System.Boolean, System.Boolean? Boolean bit
System.Byte, System.Byte? Byte tinyint Shallow
System.Byte[] Binary binary(7), image, varbinary(10), varbinary(max) SingleShallowThenDeep
System.DateTime, System.DateTime? SqlDbType.DateTime2 datetime2. Without AdbColumnSchema also: date, datetime, smalldatetime. Shallow
System.DateTime, System.DateTime? SqlDbType.Date, SqlDbType.DateTime, SqlDbType.SmallDateTime With AdbColumnSchema: date, datetime, smalldatetime Shallow
System.DateTimeOffset, System.DateTimeOffset? DateTimeOffset datetimeoffset Shallow
System.Decimal, System.Decimal? Decimal decimal(38,0), money, numeric(10,2), smallmoney Shallow
System.Double, System.Double? Double float Shallow
System.Guid, System.Guid? Guid uniqueidentifier Shallow
System.Int16, System.Int16? Int16 smallint Shallow
System.Int32, System.Int32? Int32 int, integer Shallow
System.Int64, System.Int64? Int64 bigint Shallow
System.Single, System.Single? Single real Shallow
System.String String char(32), nchar(32), ntext, nvarchar(max), text, varchar(max) Shallow
System.TimeSpan, System.TimeSpan? SqlDbType.Time time Shallow

Provider-Specific .NET Types

.NET types below with Shallow or SingleShallowThenDeep copy policy support multi-copy, the others (with SingleShallow) do not.

Note that Microsoft.Data.SqlTypes.SqlFileStream is Always Column.

Note

The provider-specific data types are very useful for:

  • Avoiding conversion issues due to differences in collation, precision, and range
  • Providing SQL style null handling
    • While nullable types (e.g int?) can be very useful, note that the .NET null handling is different from SQL null handling; see Handling Null Values for details

Also note that using provider-specific types can have slightly higher or lower performance vs. using DbType data types, e.g. by avoiding data conversion, or due to providing more functionality.

.NET type display name
System.Data.SqlTypes		 SqlDbType enum SQL Type Example Copy Policy
SqlBinary Binary binary(7), image, varbinary(10), varbinary(max) Shallow
SqlBoolean Bit bit Shallow
SqlByte TinyInt tinyint Shallow
SqlBytes (not mapped) (Use SqlBinary instead) SingleShallowThenDeep
SqlChars (not mapped) (Use SqlString instead) SingleShallowThenDeep
SqlDateTime DateTime datetime, smalldatetime Shallow
SqlDecimal Decimal decimal(38,0), numeric(10,2) Shallow
SqlDouble Float float Shallow
Microsoft.Data.SqlTypes.SqlFileStream VarBinary(max) varbinary(max) with FILESTREAM column attribute SingleShallow
SqlGuid UniqueIdentifier uniqueidentifier Shallow
SqlInt16 SmallInt smallint Shallow
SqlInt32 Int int, integer Shallow
SqlInt64 BigInt bigint Shallow
SqlMoney Money money, smallmoney Shallow
SqlSingle Real real Shallow
SqlString NVarChar char(32), nchar(32), ntext, nvarchar(max), text, varchar(max) Shallow
Note

This provider also uses SqlDbType when mapping System.DateTime and System.TimeSpan .NET types above.

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

See Also