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.MemberwiseClone()
object.ReferenceEquals(object, object)
object.ToString()
Namespace: ETLBox.Helper
Assembly: ETLBox.dll
Syntax
public sealed class BulkSqlGenerator<T> where T : DbParameter, new()
Type Parameters
| Name | Description |
|---|
| T | ADO.NET database parameter type |
Constructors
BulkSqlGenerator(ITableData)
Declaration
public BulkSqlGenerator(ITableData data)
Parameters
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
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
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
ConnectionType
The type of the database that the bulk insert statement is designed for
Declaration
public ConnectionType ConnectionType { get; set; }
Property Value
HasNextRecord
Indicates if all data was read
Declaration
public bool HasNextRecord { get; set; }
Property Value
JoinColumnNames
Declaration
public ICollection<string> JoinColumnNames { get; set; }
Property Value
| Type | Description |
|---|
| System.Collections.Generic.ICollection<T><string> | |
Limit
Declaration
public int Limit { get; set; }
Property Value
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
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
| Type | Description |
|---|
| System.Collections.Generic.List<T><T> | |
PP
The escape character use for the parameter name (default is @ for most databases)
Declaration
public string PP { get; set; }
Property Value
QB
The quotatation begin character that the database uses. (E.g. '[' for SqlServer or '"' for Postgres)
Declaration
public string QB { get; set; }
Property Value
QE
The quotatation end character that the database uses. (E.g. ']' for SqlServer or '"' for Postgres)
Declaration
public string QE { get; set; }
Property Value
SelectColumnNames
Declaration
public ICollection<string> SelectColumnNames { get; set; }
Property Value
| Type | Description |
|---|
| System.Collections.Generic.ICollection<T><string> | |
SetColumnNames
Declaration
public ICollection<string> SetColumnNames { get; set; }
Property Value
| Type | Description |
|---|
| System.Collections.Generic.ICollection<T><string> | |
TableData
The data used for the bulk operation
Declaration
public ITableData TableData { get; set; }
Property Value
TableName
The destination table name
Declaration
public string TableName { get; set; }
Property Value
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
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
UseParameterQuery
Indicates that the values are stored in parameter objects.
Default is true.
Declaration
public bool UseParameterQuery { get; set; }
Property Value
ValueToSqlConversionFunc
Declaration
public Func<ConversionContext, string> ValueToSqlConversionFunc { get; set; }
Property Value
Methods
CreateBulkDeleteStatement()
Declaration
public string CreateBulkDeleteStatement()
Returns
CreateBulkInsertStatement()
Create the sql that can be used as a bulk insert.
Declaration
public string CreateBulkInsertStatement()
Returns
CreateBulkSelectStatement()
Declaration
public string CreateBulkSelectStatement()
Returns
CreateBulkUpdateStatement()
Declaration
public string CreateBulkUpdateStatement()
Returns