BulkSqlGenerator<T>

Class BulkSqlGenerator<T>

This class creates the necessary sql statements that simulate the missing bulk insert function in various database or Odbc/OleDb connections. Normally this will be a insert into with multiple values, but depending on the database type this can be different.

Inheritance
BulkSqlGenerator<T>
Namespace: ETLBox.Helper
Assembly: ETLBox.dll
Syntax
    public sealed class BulkSqlGenerator<T> where T : DbParameter, new()
Type Parameters
NameDescription
T

ADO.NET database parameter type

Constructors

BulkSqlGenerator(ITableData)

Declaration
    public BulkSqlGenerator(ITableData data)
Parameters
TypeNameDescription
ITableDatadata

Properties

AccessDummyTableName

When creating a bulk insert sql statement for Access, a dummy table is needed. The name of the dummy table is specified here.

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

AddDbTypesFromDefinition

If set to true, the parameter list will contain not only the object value, but also the DbType and Size of the parameter. Some ADO.NET connectors can derive the database type by the value type, others prefer to have some eplicitly set. Default is false.

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

AddParameterCastInSql

If set to true, the parameter name is encapsulated with a CAST expression. The datatype is the same data types used in the table definition.

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

ConnectionType

The type of the database that the bulk insert statement is designed for

Declaration
    public ConnectionType ConnectionType { get; set; }
Property Value
TypeDescription
ConnectionType

HasNextRecord

Indicates if all data was read

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

JoinColumnNames

Declaration
    public ICollection<string> JoinColumnNames { get; set; }
Property Value
TypeDescription
ICollection<string>

Limit

Declaration
    public int Limit { get; set; }
Property Value
TypeDescription
int

NoParameterForNulls

If UseParameterQuery is set to true, all values are written into parameter objects -including nulls. To avoid having parameters for null values, set this flag to true. Nulls will then be converted into "null" in the statement, and no parameter is used.

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

PP

The escape character use for the parameter name (default is @ for most databases)

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

Parameters

A list of parameters that contain the parameter objects for the generated sql query. Only has values if UseParameterQuery is true.

Declaration
    public List<T> Parameters { get; }
Property Value
TypeDescription
List<T>

QB

The quotatation begin character that the database uses. (E.g. '[' for SqlServer or '"' for Postgres)

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

QE

The quotatation end character that the database uses. (E.g. ']' for SqlServer or '"' for Postgres)

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

SelectColumnNames

Declaration
    public ICollection<string> SelectColumnNames { get; set; }
Property Value
TypeDescription
ICollection<string>

SetColumnNames

Declaration
    public ICollection<string> SetColumnNames { get; set; }
Property Value
TypeDescription
ICollection<string>

TableData

The data used for the bulk operation

Declaration
    public ITableData TableData { get; set; }
Property Value
TypeDescription
ITableData

TableName

The destination table name

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

TryConvertParameterData

If set to true, the values for the parameters are tried to convert into the corresponding .NET data type that is suitable for the corresponding database column. If a database column is of type INTEGER, but the input data is a string like "7", then the parameter value is converted into an System.Int32. The most ADO.NET connectors do this automatically, but this can be useful for Postgres.

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

UseNamedParameters

Indicates that the parameter variables in the sql have a name, otherwise a questionmark (?) is used

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

UseParameterQuery

Indicates that the values are stored in parameter objects. Default is true.

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

ValueToSqlConversionFunc

Declaration
    public Func<ConversionContext, string> ValueToSqlConversionFunc { get; set; }
Property Value
TypeDescription
Func<ConversionContext, string>

Methods

CreateBulkDeleteStatement()

Declaration
    public string CreateBulkDeleteStatement()
Returns
TypeDescription
string

CreateBulkInsertStatement()

Create the sql that can be used as a bulk insert.

Declaration
    public string CreateBulkInsertStatement()
Returns
TypeDescription
string

CreateBulkSelectStatement()

Declaration
    public string CreateBulkSelectStatement()
Returns
TypeDescription
string

CreateBulkUpdateStatement()

Declaration
    public string CreateBulkUpdateStatement()
Returns
TypeDescription
string