ETLBox ETLBox ETLBox Buy
API
Try Buy

DbSource<TOutput>

Class DbSource<TOutput>

A database source defines either a table or sql query that returns data from a database. Multiple database are supported. Use the corresponding connection manager that fits to your database.

Inheritance
object
LoggableTask
DataFlowComponent
DataFlowSource<TOutput>
DataFlowExecutableSource<TOutput>
DbSource<TOutput>
DbSource
Implements
Inherited Members
Namespace: ETLBox.DataFlow
Assembly: ETLBox.dll
Syntax
    public class DbSource<TOutput> : DataFlowExecutableSource<TOutput>, IDataFlowExecutableSource<TOutput>, IDataFlowSource<TOutput>, IDataFlowExecutableSource, IDataFlowSource, IDataFlowComponent, ILoggableTask
Type Parameters
NameDescription
TOutput

Type of outgoing data.

Examples
SqlConnectionManager connMan = new SqlConnectionManager("Data Source=localhost");
DbSource<MyRow> source = new DbSource<MyRow>(connMan, "dbo.table");

Constructors

DbSource()

Declaration
    public DbSource()

DbSource(IConnectionManager)

Declaration
    public DbSource(IConnectionManager connectionManager)
Parameters
TypeNameDescription
IConnectionManagerconnectionManager

Sets the ConnectionManager

DbSource(IConnectionManager, string)

Declaration
    public DbSource(IConnectionManager connectionManager, string tableName)
Parameters
TypeNameDescription
IConnectionManagerconnectionManager

Sets the ConnectionManager

stringtableName

Sets the TableName

DbSource(string)

Declaration
    public DbSource(string tableName)
Parameters
TypeNameDescription
stringtableName

Sets the TableName

Properties

BulkSelectBatchSize

The batch size used when retrieving data via BulkSelect as SelectMode.

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

ColumnConverters

Use a column converter to apply custom conversion function to the data of a column.

Declaration
    public ICollection<ColumnConverter> ColumnConverters { get; set; }
Property Value
TypeDescription
ICollection<ColumnConverter>

ColumnMapping

Column mapping to map column names to properties. E.g. if the value of column "Key" should be written into the property "Id", then you can simply add a column mapping: DbColumnName: "Key" -> PropertyName: "Id".

Declaration
    public ICollection<DbColumnMap> ColumnMapping { get; set; }
Property Value
TypeDescription
ICollection<DbColumnMap>

ColumnToPropertyNamesResolver

A custom resolver function that maps columns names in the source table to corresponding property names in the data object. You can only use either the ColumnToPropertyNamesResolver or specify a ColumnMapping, not both. Use this to define dynamic mappings, such as converting lowercase column names to camel_case column names in the data object.

Declaration
    public Func<string, string> ColumnToPropertyNamesResolver { get; set; }
Property Value
TypeDescription
Func<string, string>
Examples

Assume your database columns are all lowercase (e.g., "firstname", "lastname"), and your POCO properties are camelCase (e.g., "FirstName", "LastName").

dest.ColumnToPropertyResolver = colName => colName.ToCamelCase();

ConnectionManager

The connection manager used to connect to the database - use the right connection manager for your database type.

Declaration
    public virtual IConnectionManager ConnectionManager { get; set; }
Property Value
TypeDescription
IConnectionManager

FilterRows

Applies only when SelectMode is set to BulkSelect. Rows that are used to identify records that you want to retrieve when using the BulkSelect mode. This setting has no affect when SelectMode is set to Default.

Declaration
    public IEnumerable<TOutput> FilterRows { get; set; }
Property Value
TypeDescription
IEnumerable<TOutput>

IdColumns

Applies only when SelectMode is set to BulkSelect. List of id columns that are used to select particular records from the source. If the value of a id column matchs with the value in the corresponding property, the record will be read from the source.

Declaration
    public ICollection<IdColumn> IdColumns { get; set; }
Property Value
TypeDescription
ICollection<IdColumn>

SelectColumns

Applies only when SelectMode is set to BulkSelect. List of all properties that you would like to have populated with data from the source - by default, all properties that are matching with a database column are retrieved from the database (for dynamic objects and arrays all columns are loaded). If a column has a different name than your properties, you can use the DbColumnMap to add a mapping between property and column names.

Declaration
    public ICollection<SelectColumn> SelectColumns { get; set; }
Property Value
TypeDescription
ICollection<SelectColumn>

SelectMode

The default select mode retrieves all data from either a table (determined by the TableName or from a Sql statement. If you have a list of objects that you would like to use as a filter when retrieving data, set the SelectMode to BulkSelect. Now you can define IdColumns and FilterRows to select only particular rows.

Declaration
    public SelectOperation SelectMode { get; set; }
Property Value
TypeDescription
SelectOperation

Sql

A custom sql query to extract the data from the source.

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

SqlParameter

List of query parameters that are used to replace values in Sql For every parameter provided, your sql statement should contain a placeholder. E.g.: 'SELECT col1 FROM table WHERE col2 > @parameter1'

Declaration
    public IEnumerable<QueryParameter> SqlParameter { get; set; }
Property Value
TypeDescription
IEnumerable<QueryParameter>

TableDefinition

Pass a table definition that describe the source data. Only the column name needs to be provided - you can leave out other information. If you provide the TableName, ETLBox will try to read the table definition directly from the database. If you provide your own TableDefinition, this will always be used. If you provide a Sql statement, ETLBox will try to automatically read the column names from the sql query. Sometimes the automatic parsing from the query doesn't work. Then you can provide a TableDefinition which contains the column names in the same order as they appear in the sql statement.

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

TableName

The name of the database table to read data from.

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

Methods

CheckParameter()

Declaration
    protected override void CheckParameter()
Overrides

CleanUpOnFaulted(Exception)

Declaration
    protected override void CleanUpOnFaulted(Exception e)
Parameters
TypeNameDescription
Exceptione
Overrides

CleanUpOnSuccess()

Declaration
    protected override void CleanUpOnSuccess()
Overrides

InitCheckedParameter()

Declaration
    protected override void InitCheckedParameter()
Overrides

OnExecutionDoAsyncWork()

Declaration
    protected override void OnExecutionDoAsyncWork()
Overrides

PrepareParameterForCheck()

Declaration
    protected override void PrepareParameterForCheck()
Overrides

Implements