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

object

BulkSqlGenerator<T>

Inherited Members

object.Equals(object)

object.Equals(object, object)

object.GetHashCode()

object.GetType()

object.ReferenceEquals(object, object)

object.ToString()

Namespace: ETLBox.Helper

Assembly: ETLBox.dll

Syntax

{{< /rawhtml >}} ```C# public sealed class BulkSqlGenerator where T : DbParameter, new() ``` {{< rawhtml >}}

Type Parameters

Name	Description
T	ADO.NET database parameter type

Constructors

BulkSqlGenerator(ITableData)

Declaration

{{< /rawhtml >}} ```C# public BulkSqlGenerator(ITableData data) ``` {{< rawhtml >}}

Parameters

Type	Name	Description
ITableData	data

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

{{< /rawhtml >}} ```C# public string AccessDummyTableName { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
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

{{< /rawhtml >}} ```C# public bool AddDbTypesFromDefinition { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
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

{{< /rawhtml >}} ```C# public bool AddParameterCastInSql { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
bool

ConnectionType

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

Declaration

{{< /rawhtml >}} ```C# public ConnectionType ConnectionType { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
ConnectionType

HasNextRecord

Indicates if all data was read

Declaration

{{< /rawhtml >}} ```C# public bool HasNextRecord { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
bool

JoinColumnNames

Declaration

{{< /rawhtml >}} ```C# public ICollection JoinColumnNames { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
ICollection<string>

Limit

Declaration

{{< /rawhtml >}} ```C# public int Limit { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
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

{{< /rawhtml >}} ```C# public bool NoParameterForNulls { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
bool

PP

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

Declaration

{{< /rawhtml >}} ```C# public string PP { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
string

Parameters

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

Declaration

{{< /rawhtml >}} ```C# public List Parameters { get; } ``` {{< rawhtml >}}

Property Value

Type	Description
List<T>

QB

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

Declaration

{{< /rawhtml >}} ```C# public string QB { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
string

QE

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

Declaration

{{< /rawhtml >}} ```C# public string QE { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
string

SelectColumnNames

Declaration

{{< /rawhtml >}} ```C# public ICollection SelectColumnNames { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
ICollection<string>

SetColumnNames

Declaration

{{< /rawhtml >}} ```C# public ICollection SetColumnNames { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
ICollection<string>

TableData

The data used for the bulk operation

Declaration

{{< /rawhtml >}} ```C# public ITableData TableData { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
ITableData

TableName

The destination table name

Declaration

{{< /rawhtml >}} ```C# public string TableName { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
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

{{< /rawhtml >}} ```C# public bool TryConvertParameterData { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
bool

UseNamedParameters

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

Declaration

{{< /rawhtml >}} ```C# public bool UseNamedParameters { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
bool

UseParameterQuery

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

Declaration

{{< /rawhtml >}} ```C# public bool UseParameterQuery { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
bool

ValueToSqlConversionFunc

Declaration

{{< /rawhtml >}} ```C# public Func ValueToSqlConversionFunc { get; set; } ``` {{< rawhtml >}}

Property Value

Type	Description
Func<ConversionContext, string>

Methods

CreateBulkDeleteStatement()

Declaration

{{< /rawhtml >}} ```C# public string CreateBulkDeleteStatement() ``` {{< rawhtml >}}

Returns

Type	Description
string

CreateBulkInsertStatement()

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

Declaration

{{< /rawhtml >}} ```C# public string CreateBulkInsertStatement() ``` {{< rawhtml >}}

Returns

Type	Description
string

CreateBulkSelectStatement()

Declaration

{{< /rawhtml >}} ```C# public string CreateBulkSelectStatement() ``` {{< rawhtml >}}

Returns

Type	Description
string

CreateBulkUpdateStatement()

Declaration

{{< /rawhtml >}} ```C# public string CreateBulkUpdateStatement() ``` {{< rawhtml >}}

Returns

Type	Description
string