Class SqlConnectionManager

Connection manager of a classic ADO.NET connection to a (Microsoft) Sql Server.

Inheritance
object
DbConnectionManager<SqlConnection, SqlTransaction, SqlParameter>
SqlConnectionManager
Implements
IConnectionManager<SqlConnection, SqlTransaction>
System.IDisposable
Inherited Members
object.Equals(object)
object.Equals(object, object)
object.GetHashCode()
object.GetType()
object.MemberwiseClone()
object.ReferenceEquals(object, object)
object.ToString()
Namespace: ETLBox.Connection
Assembly: ETLBox.SqlServer.dll
Syntax
    public class SqlConnectionManager : DbConnectionManager<SqlConnection, SqlTransaction, SqlParameter>, IConnectionManager<SqlConnection, SqlTransaction>, IConnectionManager, IDisposable
Examples
ControlFlow.DefaultDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;"));

Constructors

SqlConnectionManager()

Declaration
    public SqlConnectionManager()
Examples
ControlFlow.DefaultDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;"));

SqlConnectionManager(SqlConnectionString)

Declaration
    public SqlConnectionManager(SqlConnectionString connectionString)
Parameters
TypeNameDescription
SqlConnectionStringconnectionString
Examples
ControlFlow.DefaultDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;"));

SqlConnectionManager(string)

Declaration
    public SqlConnectionManager(string connectionString)
Parameters
TypeNameDescription
stringconnectionString
Examples
ControlFlow.DefaultDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;"));

Properties

AccessToken

If you provide an access token here, this token will be used for authentication with Sql Server

Declaration
    public string AccessToken { get; set; }
Property Value
TypeDescription
string

ConnectionManagerType

The database type for the connection manager.

Declaration
    public override ConnectionManagerType ConnectionManagerType { get; protected set; }
Property Value
TypeDescription
ConnectionManagerType
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.ConnectionManagerType

DisableCheckConstraints

If set to true, foreign key constraints will ot be checked when doing the bulk insert. By default, foreign key constraints are checked. To improve bulk insert performance, you can disable foreign key constrain checks.

Declaration
    [Obsolete]
public bool DisableCheckConstraints { get; set; }
Property Value
TypeDescription
bool

DisableTriggers

If set to true, triggers will not be fired when doing the bulk insert. By default, all triggers are fired in bulk insert operations.

Declaration
    [Obsolete]
public bool DisableTriggers { get; set; }
Property Value
TypeDescription
bool

MaxParameterSizeSql

Returns the maximum amount of parameters that can be passed into a sql query.

Declaration
    public override int MaxParameterSizeSql { get; protected set; }
Property Value
TypeDescription
int
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.MaxParameterSizeSql

ModifyDBSettings

Will set the database settings SET PAGE_VERIFY NONE and SET RECOVERY BULK_LOGGED Settings will be reverted after the bulk insert operation.

Declaration
    public bool ModifyDBSettings { get; set; }
Property Value
TypeDescription
bool

QB

The quotation begin character that is used in the database. E.g. SqlServer uses: '[' and Postgres: '"'

Declaration
    public override string QB { get; protected set; }
Property Value
TypeDescription
string
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.QB

QE

The quotation end character that is used in the database. E.g. SqlServer uses: ']' and Postgres: '"'

Declaration
    public override string QE { get; protected set; }
Property Value
TypeDescription
string
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.QE

UseRowLocks

By default, a table lock (for all bulk operations) is aquired when data is inserted. (This table lock will also allow concurrent bulk inserts. Only non bulk write operations are not allowed) If you want to use normal inserts/updates while inserted data in bulk, set this value to true to use row locks instead.

Declaration
    [Obsolete]
public bool UseRowLocks { get; set; }
Property Value
TypeDescription
bool

UseSqlBulkOperations

By default bulk operations are done creating a temporary table first, and then inserting/updating/deleting data either using a MERGE or DELETE FROM statement. Though this provides better performance, for compatibility purposes you can set this property to true. All bulk operations are then executed as "SQL only" statements. Reading ValueGeneratedColumns is not supported then.

Declaration
    public bool UseSqlBulkOperations { get; set; }
Property Value
TypeDescription
bool

Methods

BulkDelete(ITableData)

Declaration
    public override void BulkDelete(ITableData data)
Parameters
TypeNameDescription
ITableDatadata
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.BulkDelete(ETLBox.ControlFlow.ITableData)
Examples
ControlFlow.DefaultDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;"));

BulkInsert(ITableData)

Performs a bulk insert

Declaration
    public override void BulkInsert(ITableData data)
Parameters
TypeNameDescription
ITableDatadata

Batch of data

Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.BulkInsert(ETLBox.ControlFlow.ITableData)

BulkSelect(ITableData, ICollection<string>, Action, Action, params Action<object>[])

Declaration
    public override void BulkSelect(ITableData data, ICollection<string> selectColumnNames, Action beforeRowReadAction, Action afterRowReadAction, params Action<object>[] rowActions)
Parameters
TypeNameDescription
ITableDatadata
System.Collections.Generic.ICollection<T><string>selectColumnNames
System.ActionbeforeRowReadAction
System.ActionafterRowReadAction
System.Action<T><object>[]rowActions
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.BulkSelect(ETLBox.ControlFlow.ITableData, System.Collections.Generic.ICollection<string>, System.Action, System.Action, params System.Action<object>[])

BulkUpdate(ITableData, ICollection<string>, ICollection<string>)

Performs a bulk update

Declaration
    public override void BulkUpdate(ITableData data, ICollection<string> setColumnNames, ICollection<string> joinColumnNames)
Parameters
TypeNameDescription
ITableDatadata

Batch of data

System.Collections.Generic.ICollection<T><string>setColumnNames

The column names used in the set part of the update statement

System.Collections.Generic.ICollection<T><string>joinColumnNames

The column names to join for the update

Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.BulkUpdate(ETLBox.ControlFlow.ITableData, System.Collections.Generic.ICollection<string>, System.Collections.Generic.ICollection<string>)

CleanUpBulkInsert(string)

Called after the whole bulk insert operation to change back settings made to improve bulk insert performance

Declaration
    public override void CleanUpBulkInsert(string tablename)
Parameters
TypeNameDescription
stringtablename
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.CleanUpBulkInsert(string)

Clone()

Cretes a clone of the current connection manager

Declaration
    public override IConnectionManager Clone()
Returns
TypeDescription
IConnectionManager

A instance copy of the current connection manager

Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.Clone()

CreateDbConnection()

By default, a db connection is created with the given connection string value. Override this method if you want to pass additional properties to the specific Ado.NET db connection.

Declaration
    public override void CreateDbConnection()
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.CreateDbConnection()

PrepareBulkInsert(string)

Performs preparations needed to improved performance of a bulk insert operation

Declaration
    public override void PrepareBulkInsert(string tablename)
Parameters
TypeNameDescription
stringtablename
Overrides
ETLBox.Connection.DbConnectionManager<Microsoft.Data.SqlClient.SqlConnection, Microsoft.Data.SqlClient.SqlTransaction, Microsoft.Data.SqlClient.SqlParameter>.PrepareBulkInsert(string)

Implements

System.IDisposable