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
System.Object
BulkSqlGenerator<T>
Inherited Members
System.Object.Equals(System.Object)
System.Object.Equals(System.Object, System.Object)
System.Object.GetHashCode()
System.Object.GetType()
System.Object.MemberwiseClone()
System.Object.ReferenceEquals(System.Object, System.Object)
System.Object.ToString()
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
System.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. This should only be necessary for SqlServer. Default is false.

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

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. Only works if AddDbTypesFromDefinition is set to true.

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

ConnectionType

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

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

HasNextRecord

Indicates if all data was read

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

IsMariaDb

Indicates if the VALUES table descriptor needs the keyword ROW (MySql only)

Declaration
    public bool IsMariaDb { get; set; }
Property Value
TypeDescription
System.Boolean

JoinColumnNames

Declaration
    public ICollection<string> JoinColumnNames { get; set; }
Property Value
TypeDescription
System.Collections.Generic.ICollection<System.String>

Limit

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

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
System.Boolean

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
System.Collections.Generic.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
System.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
System.String

SelectColumnNames

Declaration
    public ICollection<string> SelectColumnNames { get; set; }
Property Value
TypeDescription
System.Collections.Generic.ICollection<System.String>

SetColumnNames

Declaration
    public ICollection<string> SetColumnNames { get; set; }
Property Value
TypeDescription
System.Collections.Generic.ICollection<System.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
System.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. Only works if AddDbTypesFromDefinition is set to true.

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

UseNamedParameters

Indicates that the parameter variables in the sql have a name

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

UseParameterQuery

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

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

Methods

CreateBulkDeleteStatement()

Declaration
    public string CreateBulkDeleteStatement()
Returns
TypeDescription
System.String

CreateBulkInsertStatement()

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

Declaration
    public string CreateBulkInsertStatement()
Returns
TypeDescription
System.String

CreateBulkSelectStatement()

Declaration
    public string CreateBulkSelectStatement()
Returns
TypeDescription
System.String

CreateBulkUpdateStatement()

Declaration
    public string CreateBulkUpdateStatement()
Returns
TypeDescription
System.String