SqlConnectionManager
Class SqlConnectionManager
Connection manager of a classic ADO.NET connection to a (Microsoft) Sql Server.
Inheritance
Inherited Members
Namespace: ETLBox.SqlServer
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()
SqlConnectionManager(SqlConnectionString)
Declaration
public SqlConnectionManager(SqlConnectionString connectionString)
Parameters
Type | Name | Description |
---|---|---|
SqlConnectionString | connectionString |
SqlConnectionManager(string)
Declaration
public SqlConnectionManager(string connectionString)
Parameters
Type | Name | Description |
---|---|---|
string | connectionString |
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
Type | Description |
---|---|
string |
ConnectionType
The database type for the connection manager.
Declaration
public override ConnectionType ConnectionType { get; protected set; }
Property Value
Type | Description |
---|---|
ConnectionType |
Overrides
DisableCheckConstraints
If set to true, foreign key constraints will not 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.
This only applies for bulk inserts, and only if ValueGeneratedColumns are empty.
Declaration
public bool DisableCheckConstraints { get; set; }
Property Value
Type | Description |
---|---|
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. This only applies for bulk inserts, and only if ValueGeneratedColumns are empty.
Declaration
public bool DisableTriggers { get; set; }
Property Value
Type | Description |
---|---|
bool |
KeepNulls
If inserted data into columns with a DEFAULT constraint, the default value is always used if the mapped property in your data object holds a null value. If you want to insert a NULL value instead (and not trigger the default value generation), set this property to true.
Declaration
public bool KeepNulls { get; set; }
Property Value
Type | Description |
---|---|
bool |
MaxParameterSizeSql
Returns the maximum amount of parameters that can be passed into a sql query.
Declaration
public override int MaxParameterSizeSql { get; set; }
Property Value
Type | Description |
---|---|
int |
Overrides
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
Type | Description |
---|---|
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
Type | Description |
---|---|
string |
Overrides
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
Type | Description |
---|---|
string |
Overrides
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. This only applies for bulk inserts, and only if ValueGeneratedColumns are empty.
Declaration
public bool UseRowLocks { get; set; }
Property Value
Type | Description |
---|---|
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
Type | Description |
---|---|
bool |
Methods
BulkDelete(ITableData)
Performs a bulk delete
Declaration
public override void BulkDelete(ITableData data)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | Batch of data |
Overrides
BulkInsert(ITableData)
Performs a bulk insert
Declaration
public override void BulkInsert(ITableData data)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | Batch of data |
Overrides
BulkSelect(ITableData, ICollection<string>, Action, Action, params Action<object>[])
Performs a bulk select
Declaration
public override void BulkSelect(ITableData data, ICollection<string> selectColumnNames, Action beforeRowReadAction, Action afterRowReadAction, params Action<object>[] rowActions)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | Batch of data needed for the where condition |
ICollection<string> | selectColumnNames | Column names included in the select |
Action | beforeRowReadAction | Action invoked before any data is read |
Action | afterRowReadAction | Action invoked after all data is read |
Action<object>[] | rowActions |
Overrides
BulkUpdate(ITableData, ICollection<string>, ICollection<string>)
Performs a bulk update
Declaration
public override void BulkUpdate(ITableData data, ICollection<string> setColumnNames, ICollection<string> joinColumnNames)
Parameters
Type | Name | Description |
---|---|---|
ITableData | data | Batch of data |
ICollection<string> | setColumnNames | The column names used in the set part of the update statement |
ICollection<string> | joinColumnNames | The column names to join for the update |
Overrides
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
Type | Name | Description |
---|---|---|
string | tablename |
Overrides
Clone()
Cretes a clone of the current connection manager
Declaration
public override IConnectionManager Clone()
Returns
Type | Description |
---|---|
IConnectionManager | A instance copy of the current connection manager |
Overrides
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
PrepareBulkInsert(string)
Performs preparations needed to improved performance of a bulk insert operation
Declaration
public override void PrepareBulkInsert(string tablename)
Parameters
Type | Name | Description |
---|---|---|
string | tablename |