### Chain Sort and Mapping Transforms

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

This example demonstrates chaining a sort transform to order data by a specific field, followed by a mapping transform to rename columns. Assumes a reader has already been initialized.

```csharp
//create use the sort transform to sort by the "StringField"
var sortField = new List<Sort> { new Sort("StringField", Descending} )};
var sortTransform = new TransformSort(reader, sortField);

//maps the "StringColumn" field to the name "OutputColumn"
var mappingFields = List<ColumnPair> mappingFields = new List<ColumnPair>() { new ColumnPair("StringColumn" "OutputColumn")};
var mappingTransform = new TransformMapping(sortTransform, mappingFields, null);

//read the data using mappingTransform.ReadAsync();
```

--------------------------------

### Full POCO Reader Example

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Demonstrates loading and displaying data from a list of BookClass objects using the POCO reader. The `Field` attribute can define column names and delta types.

```csharp
public class Program
{
    public static void Main(string[] args)
    {
        var poco = new CreatePocoReader();
        poco.Create();
    }
}

public class BookClass
{
    [Field("code", DeltaType = TableColumn.EDeltaType.NaturalKey)]
    public string Code { get; set; }

    [Field("name")]
    public string Name { get; set; }

    [Field("name")]
    public int Cost { get; set; }

    [Field("date_published")]
    public DateTime Published { get; set; }

}

public class CreatePocoReader
{
    public void Create()
    {
        var books = CreateBooksData();
        var reader = new ReaderPoco<BookClass>(books);

        DisplayReader(reader);
    }
    
    public List<BookClass> CreateBooksData()
    {
        var books = new List<BookClass>();
        
        books.Add(new BookClass() {Code = "001", Name = "Lord of the rings", Cost = 15, Published = new DateTime(1954, 07,29)});
        books.Add(new BookClass() {Code = "002", Name = "Harry Potter and the Philosopher's Stone", Cost = 12, Published = new DateTime(1997, 06,26)});
        books.Add(new BookClass() {Code = "003", Name = "A Game of Thrones", Cost = 16, Published = new DateTime(1996, 07,01)});

        return books;
    }

    public void DisplayReader(DbDataReader reader)
    {
        while (reader.Read())
        {
            for (var i = 0; i < reader.FieldCount; i++)
            {
                Console.Write(reader.GetName(i) + ":" + reader[i].ToString() + (i < reader.FieldCount-1 ? ", " : ""));
            }
            Console.WriteLine();
        }
    }
}
```

--------------------------------

### Perform Lookup with Caching and Filters

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Retrieves a specific record using a filter after opening a transform reader and setting it to preload the cache. This example demonstrates opening a reader, configuring cache, setting filters, and performing a lookup.

```csharp
//gets the transform reader.
var reader = connection.GetTransformReader(table, null);

//open the reader
openResult = await reader.Open();
if(!openResult.Success)
    throw new Exception("Open Reader failed:" + openResult.Message);

//set the caching
reader.SetCacheMethod(ECacheMethod.PreLoadCache);

//set a filter for the lookup
var filters = new List<Filter> { new Filter("IntColumn", Filter.ECompare.IsEqual, 5) };

//call the lookup
var returnLookup = await reader.LookupRow(filters);

if(returnLookup.Success)
    Console.WriteLine("The record was found.");
else
    Console.WriteLine("The record was not found.");

```

--------------------------------

### Complete Transform Pipeline Example

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Demonstrates a full data transformation pipeline: reading data from a SQL Server table, filtering records, performing aggregations (median and sum), and writing the results to another SQL Server table. Requires existing SqlConnection objects for source and target.

```csharp
public void FirstTransform(SqlConnection sourceConnection, SqlConnection targetConnection)
{
    // Retrieve the data from the database
    var cmd = new SqlCommand("select * from Sales.SalesOrderHeader ", sourceConnection);
    var sourceReader = cmd.ExecuteReader();

    // Load the reader into transform source, which will start the transform chain.
    var transformSource = new ReaderDbDataReader(sourceReader);

    // Create a custom filter that removes records where PurchaseOrderNumber is null
    var transformFilter = new TransformFilter(
        transformSource,
        new List<Function>()
        {
            new Function(
                new Func<string, bool>((value) => value != null), //function code
                new[] { "PurchaseOrderNumber" },  //input column
                null, null )
        }
    );

    // Add median, and sum calculation
    var transformGroup = new TransformGroup(
        transformFilter,
        new List<ColumnPair>() //The fields to groupby
        {
            new ColumnPair("OrderDate")
        },
        new List<Function>()
        {
            StandardFunctions.GetFunctionReference("Median", new[] { "TotalDue" }, "DailyMedian", null),
            StandardFunctions.GetFunctionReference("Sum", new[] { "TotalDue" }, "DailyTotal", null)
        },
        true //Pass through column = true will will pass through original fields/rows and merge in the aggregates
    );

    using (var bulkCopy = new SqlBulkCopy(targetConnection))
    {
        bulkCopy.DestinationTableName = "SalesOrderDaily";
        bulkCopy.WriteToServer(transformGroup);
    }
}
```

--------------------------------

### Write Transform Result using TransformWriter

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

This example demonstrates using the TransformWriter class to save data to a target table, including handling potential errors and checking the run status. It supports operations like update, delete, preserve, and reject.

```csharp
TransformWriter writer = new TransformWriter();
TransformWriterResult writerResult = new TransformWriterResult();
var returnResult = await writer.WriteAllRecords(writerResult, transform, targetTable, targetConnection, rejectTable, rejectConnection, CancellationToken.None);

if(!returnResult.Success)
    throw new Exception("The writer failed to run with message: " returnResult.Message);

if(!writerResult.RunStatus == Abended)
    throw new Exception("The writer failed with message: " writerResult.Message);

Console.WriteLine("Finished with status {0}, and processed {1} rows.", writerResult.RunStatus, writerResult.RowsTotal.ToString());
```

--------------------------------

### Wrap ADO.NET DbDataReader in Transform Chain

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Promote any existing `DbDataReader` (e.g., from a `SqlCommand`) into the transform chain using `ReaderDbDataReader` without changing query code. This example demonstrates wrapping a SQL Server reader and then sorting the results.

```csharp
using System.Data.SqlClient;
using dexih.transforms;

var conn = new SqlConnection("Server=.;Database=AdventureWorks;Trusted_Connection=True;");
conn.Open();
var cmd  = new SqlCommand("SELECT SalesOrderID, OrderDate, TotalDue FROM Sales.SalesOrderHeader", conn);
var sqlReader = cmd.ExecuteReader();

// Wrap the native reader
Transform source = new ReaderDbDataReader(sqlReader);

// Now pipe into any transform chain …
var sortedSource = new TransformSort(source, "TotalDue", ESortDirection.Descending);
await sortedSource.Open(0, null, CancellationToken.None);
while (await sortedSource.ReadAsync())
    Console.WriteLine($"{sortedSource["SalesOrderID"]} -> {sortedSource["TotalDue"]}");
```

--------------------------------

### Read from SQLite using ConnectionSqlite

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Establishes a connection to a local SQLite database, retrieves table information, and creates a database reader for a specified table. Ensure the SQLite database file exists at the specified path.

```csharp
//Create a connection to sqlite
var connection = new ConnectionSqlite()
{
    Name = "Test Connection",
    NtAuthentication = true,
    ServerName = "c:\\data\\database.sqlite"
};

//get the table structure.
var tableResult = connection.GetSourceTableInfo("SalesTable");
if(!table.Success)
    throw new Exception("Could not retrieve table information");
var table = tableResult.Value;

//create a new connection to the database.
var newConnection = connection.NewConnection();

var readerResult = connection.GetDatabaseReader(table, newConnection);

if(!readerResult.Success)
   throw new Exception("Reader issue: " + readerResult.Message);

//new instance of the reader
var reader = readerResult.Value;
```

--------------------------------

### Create and Populate Memory Reader

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Initializes a table with specified columns and data types, then populates it with rows. Finally, it creates a memory reader from the populated table.

```csharp
//Create the table class
var table = new Table("test", 0,
    new TableColumn("StringColumn", DataType.ETypeCode.String, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("IntColumn", DataType.ETypeCode.Int32, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("DecimalColumn", DataType.ETypeCode.Decimal, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("DateColumn", DataType.ETypeCode.DateTime, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("SortColumn", DataType.ETypeCode.Int32, TableColumn.EDeltaType.TrackingField)
            );

//Populate the table with some data
    table.AddRow("value01", 1, 1.1, Convert.ToDateTime("2015/01/01"), 10 );
    table.AddRow("value02", 2, 2.1, Convert.ToDateTime("2015/01/02"), 9 );
    table.AddRow("value03", 3, 3.1, Convert.ToDateTime("2015/01/03"), 8 );
    table.AddRow("value04", 4, 4.1, Convert.ToDateTime("2015/01/04"), 7 );
    table.AddRow("value05", 5, 5.1, Convert.ToDateTime("2015/01/05"), 6 );

//Initialize the ReaderMemory, with an indicator that the "StringColumn" is sorted
var reader = new ReaderMemory(table);
```

--------------------------------

### Connect to SQLite and Query Data

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Establishes a connection to a local SQLite database, retrieves table metadata, and queries data with server-side filtering and sorting.

```csharp
using dexih.connections.sqlite;
using dexih.transforms;
using dexih.functions.Query;
using dexih.functions;

var connection = new ConnectionSqlite
{
    Name       = "LocalDB",
    Filename   = "/data/sales.db",
    DefaultDatabase = "sales.db"
};

// Retrieve the remote table metadata
var table = await connection.GetSourceTableInfo("Orders", CancellationToken.None);

// Push a filter + sort down to SQLite
var query = new SelectQuery
{
    Filters = new Filters { new Filter("Status", ECompare.IsEqual, "Pending") },
    Sorts   = new Sorts   { new Sort("OrderDate", ESortDirection.Ascending) }
};

var reader = connection.GetTransformReader(table, true);
await reader.Open(0, query, CancellationToken.None);

while (await reader.ReadAsync())
    Console.WriteLine($"OrderId={reader["OrderId"]}, Date={reader["OrderDate"]}");

```

--------------------------------

### Implement Sum State Function in C#

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Demonstrates the implementation of a state function for calculating a sum. It includes the primary function, result retrieval, and reset logic.

```csharp
class CalculateSum
{
	int total = 0;
	
	public void Sum(int value)
	{
		total = total + value;
	}
	
	public int SumResult()
	{
		return total;
	}
	
	public void SumReset()
	{
		total =  0;
	}
}
```

--------------------------------

### Define Table Schema and Add Rows

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Build a table schema manually by defining columns with data types and delta semantics. Populate the table with in-memory rows.

```csharp
using dexih.functions;
using Dexih.Utils.DataType;

// Build a table schema manually
var salesTable = new Table("Sales");
salesTable.Columns.Add(new TableColumn("SaleId",   DataType.ETypeCode.Int32,    TableColumn.EDeltaType.NaturalKey));
salesTable.Columns.Add(new TableColumn("Product",  DataType.ETypeCode.String,   TableColumn.EDeltaType.TrackingField));
salesTable.Columns.Add(new TableColumn("Amount",   DataType.ETypeCode.Decimal,  TableColumn.EDeltaType.TrackingField));
salesTable.Columns.Add(new TableColumn("SaleDate", DataType.ETypeCode.DateTime, TableColumn.EDeltaType.TrackingField));

// Populate with in-memory rows
salesTable.AddRow(1, "Widget A", 99.50m,  new DateTime(2024, 1, 10));
salesTable.AddRow(2, "Widget B", 149.00m, new DateTime(2024, 1, 11));
salesTable.AddRow(3, "Widget A", 79.99m,  new DateTime(2024, 1, 12));

Console.WriteLine($"Schema: {string.Join(", ", salesTable.Columns.Select(c => c.Name))}");
// Output: Schema: SaleId, Product, Amount, SaleDate
Console.WriteLine($"Row count: {salesTable.Data.Count}");
// Output: Row count: 3
```

--------------------------------

### Configure Mapping Transform with ColumnPair and Function in C#

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Sets up a mapping transform to rename columns and apply a substring function to limit field length. Includes mapping by name and passthrough.

```csharp
List<ColumnPair> MappingColumns = new List<ColumnPair>();

//map the sourceFieldName to the targetFieldName
MappingColumns.Add(new ColumnPair("sourceFieldName", "targetFieldName"));

//map some other fields without any name change.
MappingColumns.Add(new ColumnPair("createDate"));
MappingColumns.Add(new ColumnPair("updateDate"));

List<Function> MappingFunctions = new List<Function>();

//use the substring function to limit the 'bigField' to 20 characters
Function = StandardFunctions.GetFunctionReference("Substring");
Function.TargetColumn = "trimmedField";
Function.Inputs = new dexih.functions.Parameter[] {
        new dexih.functions.Parameter("name", ETypeCode.String, true, null, "bigField" ),
        new dexih.functions.Parameter("start", ETypeCode.Int32, false, 0),
        new dexih.functions.Parameter("length", ETypeCode.Int32, false, 20) 
};
MappingFunctions.Add(Function);

//create the mapping transform
transformMapping = new TransformMapping(InTransform, MappingColumns, MappingFunctions);
```

--------------------------------

### Using Built-In Dexih Functions

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Leverage built-in Aggregate, Validation, and Security functions. Aggregate functions are stateful and require resetting. Security functions require a GlobalSettings object with an EncryptionKey.

```csharp
using dexih.functions.BuiltIn;
using Dexih.Utils.DataType;

// --- Aggregate (stateful) ---
var agg = new AggregateFunctions<decimal>();
agg.Sum(100m); agg.Sum(200m); agg.Sum(50m);
Console.WriteLine($"Sum={agg.GenericResult()}");  // Sum=350

agg.Reset();
agg.Average(100m); agg.Average(200m);
Console.WriteLine($"Average={agg.AverageResult()}"); // Average=150

// --- Validation ---
var val = new ValidationFunctions();
bool ok = val.CleanBlankString("", "N/A", out var cleaned);
Console.WriteLine($"valid={ok}, value={cleaned}"); // valid=False, value=N/A

bool trimOk = val.MaxLength("Hello World", 5, out var trimmed);
Console.WriteLine($"valid={trimOk}, value={trimmed}"); // valid=False, value=Hello

// --- Security ---
var sec = new SecurityFunctions
{
    GlobalSettings = new GlobalSettings { EncryptionKey = "my-secret-key" }
};
string hash = sec.SecureHash("P@ssw0rd");
bool verified = sec.ValidateSecureHash("P@ssw0rd", hash);
Console.WriteLine($"Hash verified: {verified}"); // Hash verified: True

string encrypted = sec.FastEncrypt("sensitive-data");
string decrypted = sec.FastDecrypt(encrypted);
Console.WriteLine($"Round-trip: {decrypted}"); // Round-trip: sensitive-data
```

--------------------------------

### Write Transform Result using SqlBulkCopy

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

This snippet shows how to write the results of a transform to a SQL Server table using the SqlBulkCopy class. Ensure the target connection is properly established.

```csharp
using (var bulkCopy = new SqlBulkCopy(targetConnection))
{
    bulkCopy.DestinationTableName = "SalesOrderDaily";
    bulkCopy.WriteToServer(mappingTransform);
}
```

--------------------------------

### In-Process Data Storage with ConnectionMemory

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Use ConnectionMemory for in-process data storage, ideal for unit testing. It stores tables in a dictionary and can be used to create, write to, and read from tables.

```csharp
using dexih.transforms;
using dexih.functions;
using Dexih.Utils.DataType;

var memConn = new ConnectionMemory();

// Create a target table in the memory store
var target = new Table("SalesAgg");
target.Columns.Add(new TableColumn("Product",    DataType.ETypeCode.String));
target.Columns.Add(new TableColumn("TotalSales", DataType.ETypeCode.Decimal));
await memConn.CreateTable(target, dropTable: true, CancellationToken.None);

// Build and run a pipeline that writes aggregated results to memory
var source  = new ReaderMemory(salesTable);
var grpMaps = new Mappings { GroupRows = true };
grpMaps.Add(new MapGroup(   new TableColumn("Product", DataType.ETypeCode.String)));
grpMaps.Add(new MapAggregate(new TableColumn("Amount", DataType.ETypeCode.Decimal),
                              new TableColumn("TotalSales", DataType.ETypeCode.Decimal), EAggregate.Sum));

var pipeline = new TransformGroup(source, grpMaps);

var writerResult = new TransformWriterResult();
var writer = new TransformWriterTarget(memConn, target, writerResult);
await writer.WriteAllRecordsAsync(pipeline, CancellationToken.None);

// Read the result back
var resultReader = memConn.GetTransformReader(target, false);
await resultReader.Open(0, null, CancellationToken.None);
while (await resultReader.ReadAsync())
    Console.WriteLine($"{resultReader["Product"]}: {resultReader["TotalSales"]}");
// Output:
// Widget A: 179.49
// Widget B: 149.00
```

--------------------------------

### Create and Test a Stateless Function

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Defines a new stateless function that adds two integer values. This function can be used in mapping transforms. It also shows how to test the function's output.

```csharp
using dexih.functions;

Function function1 =
	new Function(
		new Func<int, int, int>((i, j) => i + j),
		new string[] { "value1", "value2" },
		"AddResult",
		null //specify more output fields here.
	);

///test the function writes value - 8
Console.WriteLine ((Int32)function1.RunFunction(new object[] { 6, 2 }).Value
```

--------------------------------

### Create a Stateless Function by Referencing a Method

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Creates a stateless function by referencing an existing static method from a class. This is an alternative to defining the function logic directly with a lambda expression.

```csharp
using dexih.functions;

Function function2 =
	new Function(
		typeof(StandardFunctions),
		"Add",
		new string[] { "value1", "value2" },
		"AddResult",
		null
	);

```

--------------------------------

### Create Reader from Existing DbDataReader

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Wraps an existing DbDataReader object to be used within the Dexih transform framework. This allows integration of data from sources that already provide a DbDataReader.

```csharp
var reader = new ReaderDbDataReader(sourceReader);
```

--------------------------------

### Create POCO Reader

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Instantiates a reader from a list of Plain Old CLR Objects (POCOs). Use this to convert in-memory data structures into a streamable reader for further processing.

```csharp
var reader = new ReaderPoco<BookClass>(books);
```

--------------------------------

### Read Data from In-Memory Table

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Wrap a populated `Table` as the head of a transform chain using `ReaderMemory`. This is the simplest way to seed a pipeline from an in-memory collection.

```csharp
using dexih.transforms;
using dexih.functions;
using dexih.functions.Query;

// Reuse the salesTable from above
var reader = new ReaderMemory(salesTable);

await reader.Open(0, null, CancellationToken.None);

while (await reader.ReadAsync())
{
    Console.WriteLine($"Id={reader["SaleId"]}, Product={reader["Product"]}, Amount={reader["Amount"]}");
}
// Output:
// Id=1, Product=Widget A, Amount=99.50
// Id=2, Product=Widget B, Amount=149.00
// Id=3, Product=Widget A, Amount=79.99
```

--------------------------------

### Group Transform with Sum Aggregation in C#

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Illustrates using the Group transform to perform a sum aggregation on the 'Value' column, aliased as 'SumValue'. The 'Product' column is used for grouping.

```csharp
//create primary table
Table primaryTable = new Table("Sales", 0,
    new TableColumn("Product", DataType.ETypeCode.Int32, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("Value", DataType.ETypeCode.Decimal, TableColumn.EDeltaType.TrackingField);

//add data, note the productid column is sorted.
primaryTable.AddRow( 'Product1', 1 );
primaryTable.AddRow( 'Product1', 2 );
primaryTable.AddRow( 'Product2', 3 );
primaryTable.AddRow( 'Product2', 4 );

//create a reader, and indicate data is sorted by the productid
ReaderMemory primaryReader = new ReaderMemory(primaryTable, new List<Sort>() { new Sort("ProductId") } );

List<Function> Aggregates = new List<Function>() {
    StandardFunctions.GetFunctionReference("Sum", new[] { "Value" }, "SumValue", null)
};

TransformGroup transformGroup = new TransformGroup(primaryReader, null, Aggregates, true);

```

--------------------------------

### Map Columns and Calculate Values with TransformMapping

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Renames columns, applies scalar functions, and controls column flow. Unmapped columns can be passed through unchanged.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.BuiltIn;
using Dexih.Utils.DataType;

var source = new ReaderMemory(salesTable);

var mappings = new Mappings(passThroughColumns: true);

// Rename Amount -> SaleAmount
mappings.Add(new MapColumn(
    new TableColumn("Amount",     DataType.ETypeCode.Decimal),
    new TableColumn("SaleAmount", DataType.ETypeCode.Decimal)));

// Computed column: taxed = Amount * 1.1
mappings.Add(new MapFunction(
    new TransformFunction(
        typeof(ArithmeticFunctions),
        nameof(ArithmeticFunctions.Multiply),
        new[] { new TableColumn("Amount", DataType.ETypeCode.Decimal), new TableColumn("TaxRate", DataType.ETypeCode.Decimal) },
        new TableColumn("TaxedAmount", DataType.ETypeCode.Decimal)),
    new object[] { null, 1.1m }));

var map = new TransformMapping(source, mappings);
await map.Open(0, null, CancellationToken.None);

while (await map.ReadAsync())
    Console.WriteLine($"Net={map["SaleAmount"]}, Taxed={map["TaxedAmount"]}");

```

--------------------------------

### Group and Aggregate Data with TransformGroup

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Use TransformGroup for SQL-style GROUP BY operations with aggregate functions. Set GroupRows = true to collapse rows into a single group.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.BuiltIn;
using Dexih.Utils.DataType;

var source = new ReaderMemory(salesTable);

var mappings = new Mappings { GroupRows = true };   // true = collapse to one row per group

// Group by Product
mappings.Add(new MapGroup(new TableColumn("Product", DataType.ETypeCode.String)));

// Aggregate: SUM(Amount) -> TotalSales
mappings.Add(new MapAggregate(
    new TableColumn("Amount", DataType.ETypeCode.Decimal),
    new TableColumn("TotalSales", DataType.ETypeCode.Decimal),
    EAggregate.Sum));

// Aggregate: COUNT(*) -> SaleCount
mappings.Add(new MapAggregate(
    new TableColumn("Amount", DataType.ETypeCode.Decimal),
    new TableColumn("SaleCount", DataType.ETypeCode.Int32),
    EAggregate.Count));

var group = new TransformGroup(source, mappings);
await group.Open(0, null, CancellationToken.None);

while (await group.ReadAsync())
    Console.WriteLine($"Product={group["Product"]}, Total={group["TotalSales"]}, Count={group["SaleCount"]}");
// Output:
// Product=Widget A, Total=179.49, Count=2
// Product=Widget B, Total=149.00, Count=1
```

--------------------------------

### Configure Transform Caching

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Sets the caching method for the transform to OnDemandCache, retaining up to 1000 rows. This allows for selective caching of read records.

```csharp
transform.SetCacheMethod(ECacheMethod.OnDemandCache, 1000);
```

--------------------------------

### TransformWriterTarget: Pipeline Output to Target

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Utilize TransformWriterTarget as the final step in a pipeline to write data to a target. It supports bulk loading or row-by-row transactions and allows configuration of update strategies and target actions.

```csharp
using dexih.transforms;
using dexih.functions;

// Build a chain ending in the transform to persist
Transform finalTransform = buildPipeline(); // returns last chained transform

var writerResult = new TransformWriterResult();
var writerOptions = new TransformWriterOptions
{
    UpdateStrategy = EUpdateStrategy.AppendUpdate,
    TargetAction   = TransformWriterOptions.ETargetAction.DropCreate
};

var writer = new TransformWriterTarget(
    targetConnection,
    targetTable,
    writerResult,
    writerOptions,
    rejectConnection: rejectConnection,
    rejectTable: rejectTable);

// Subscribe to progress events
writer.OnProgressUpdate += result =>
    Console.WriteLine($"Progress: {result.RowsTotal} rows written");

writer.OnFinish += result =>
    Console.WriteLine($"Finished: status={{result.RunStatus}}, rows={{result.RowsTotal}}, rejected={{result.RowsRejected}}");

await writer.WriteAllRecordsAsync(finalTransform, CancellationToken.None);

if (writerResult.RunStatus != ERunStatus.Finished)
    throw new Exception($"Writer failed: {writerResult.Message}");

Console.WriteLine($"Total rows: {writerResult.RowsTotal}");
Console.WriteLine($"Rows inserted: {writerResult.RowsCreated}");
Console.WriteLine($"Rows updated:  {writerResult.RowsUpdated}");
Console.WriteLine($"Rows deleted:  {writerResult.RowsDeleted}");
Console.WriteLine($"Rows rejected: {writerResult.RowsRejected}");
```

--------------------------------

### TransformProfile: Gather Data Statistics

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Employ TransformProfile to collect statistics like null counts, distinct counts, and min/max values from a data stream without modification. Results are accessible via GetProfileResults().

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.BuiltIn;
using Dexih.Utils.DataType;

var source = new ReaderMemory(salesTable);

var profileMappings = new Mappings();
profileMappings.Add(new MapFunction(
    new TransformFunction(
        typeof(ProfileFunctions),
        nameof(ProfileFunctions.NullCount),
        new[] { new TableColumn("Product", DataType.ETypeCode.String) },
        new TableColumn("NullCount", DataType.ETypeCode.Int32)),
    Array.Empty<object>()));

var profile = new TransformProfile(source, profileMappings);
await profile.Open(0, null, CancellationToken.None);

// Drain the primary stream (profile accumulates internally)
while (await profile.ReadAsync()) { }

// Read back statistics
var stats = profile.GetProfileResults();
while (await stats.ReadAsync())
    Console.WriteLine($"Column={{stats["ColumnName"]}}, Statistic={{stats["Statistic"]}}, Value={{stats["Value"]}}");
```

--------------------------------

### TransformDelta: Detect Inserts, Updates, and Deletes

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Use TransformDelta to compare source and target streams and identify row operations (Insert, Update, Delete, Preserve). Configure the update strategy and whether to add default rows or update reasons.

```csharp
using dexih.transforms;
using dexih.functions;
using Dexih.Utils.DataType;

// newData: fresh extract from source system
var newData = new ReaderMemory(incomingTable);  // contains updated / new rows

// existing: current state in the data warehouse
Transform existingData = targetConnection.GetTransformReader(targetTable, true);

var delta = new TransformDelta(
    newData,
    existingData,
    EUpdateStrategy.AppendUpdateDelete,  // detect inserts, updates and deletes
    autoIncrementValue: 0,
    addDefaultRow: false,
    addUpdateReason: true);

await delta.Open(0, null, CancellationToken.None);

while (await delta.ReadAsync())
{
    var op = delta["Operation"]?.ToString();
    Console.WriteLine($"[op] Key={{delta["SaleId"]}}");
}
// Output:
// [Insert] Key=4
// [Update] Key=1
// [Delete] Key=2
```

--------------------------------

### Configure Filter Transform in C#

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Sets up a filter transform to exclude rows where a specific column matches a given value. Uses the built-in IsEqual function and negates the condition.

```csharp
//set a Conditions list
List<Function> Conditions = new List<Function>();

//use the built in IsEqual function.
Function Function = StandardFunctions.GetFunctionReference("IsEqual");
Function.Inputs = new dexih.functions.Parameter[] {
        new dexih.functions.Parameter("JunkColumn", ETypeCode.String, true, null, "StringColumn" ),
        new dexih.functions.Parameter("Compare", ETypeCode.String, false, "junk") };

//use the NotCondition property to change the function to `not equal`.
Function.NotCondition = true

//add the function to the conditions list
Conditions.Add(Function);

//create the new filter transform with the conditions applied.
TransformFilter TransformFilter = new TransformFilter(InTransform, Conditions);
```

--------------------------------

### Merge Join Operation in C#

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Demonstrates a merge join operation where incoming data is sorted by the join field. If sort operations were not specified, it would default to a hash join.

```csharp
//create primary table
Table primaryTable = new Table("Sales", 0,
    new TableColumn("SaleDate", DataType.ETypeCode.DateTime, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("ProductId", DataType.ETypeCode.Int32, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("SalesValue", DataType.ETypeCode.Decimal, TableColumn.EDeltaType.TrackingField);

//add data, note the productid column is sorted.
primaryTable.AddRow( Convert.ToDateTime("2015/01/01"), 10, 123 );
primaryTable.AddRow( Convert.ToDateTime("2015/01/01"), 10, 124 );
primaryTable.AddRow( Convert.ToDateTime("2015/01/02"), 20, 111 );
primaryTable.AddRow( Convert.ToDateTime("2015/01/02"), 20, 112 );

//create a reader, and indicate data is sorted by the productid
ReaderMemory primaryReader = new ReaderMemory(primaryTable, new List<Sort>() { new Sort("ProductId") } );

//create join table
joinTable table = new Table("Products", 0,
    new TableColumn("ProductId", DataType.ETypeCode.Int32, TableColumn.EDeltaType.NaturalKey),
    new TableColumn("ProductName", DataType.ETypeCode.String, TableColumn.EDeltaType.TrackingField);

//add data, note the productid column is sorted.
joinTable.AddRow( 10, "Product Number 10" );
joinTable.AddRow( 20, "Product Number 20" );

//create a reader, and indicate data is sorted by the productid
ReaderMemory joinReader = new ReaderMemory(joinTable, new List<Sort>() { new Sort("ProductId") } );

//create the join reader which can now be streamed by calling transformJoin.ReadAsync()
TransformJoin transformJoin = new TransformJoin(
    primaryReader, 
    joinReader, 
    new List<JoinPair>() { new JoinPair("ProductId", "ProductId") }
);

```

--------------------------------

### Generate Numeric Sequence with ReaderRowCreator

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Produce a numeric sequence using `ReaderRowCreator`. This is useful for generating date dimensions or synthetic test datasets without an external data source.

```csharp
using dexih.transforms;

// Generate rows with values 1, 3, 5, 7, 9  (start=1, end=10, step=2)
var rowCreator = new ReaderRowCreator(1, 10, 2);
await rowCreator.Open(0, null, CancellationToken.None);

while (await rowCreator.ReadAsync())
    Console.Write(rowCreator[0] + " ");
// Output: 1 3 5 7 9
```

--------------------------------

### Retrieve Table Information from Connection

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

This code retrieves table metadata from a connection using GetSourceTableInfo. It includes error handling for cases where the table information cannot be fetched.

```csharp
var getTableResult = connection.GetSourceTableInfo("MyTable", null);
if(!getTableResult.Success)
    throw new Exception("There was an issue getting the table: " + getTableResult.Message);

Table table = getTableResult.Value;
```

--------------------------------

### Create Row Creator Reader

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Generates a sequence of numbers within a specified range and increment. Useful for creating test data or sequential dimensions.

```csharp
//Create a sequence of rows from 1, 1000 incrementing by 2
var rowCreator = new ReaderRowCreator(1, 1000, 2);
```

--------------------------------

### Configure Sort Transform in C#

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Configures a sort transform to order data by multiple columns with specified directions (ascending/descending).

```csharp
var SortFields = new List<Sort> { 
    new Sort("Column1", Sort.EDirection.Ascending) 
    new Sort("Column2", Sort.EDirection.Descending) 
};
TransformSort TransformSort = new TransformSort(Source, SortFields);
```

--------------------------------

### Left-Outer Join with TransformJoin

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

TransformJoin performs a left-outer join between a primary and a reference stream, automatically selecting merge-join or hash-join strategies. Use EJoinNotFoundStrategy.NullJoin to include rows from the primary stream even if no match is found in the reference stream.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.Query;
using Dexih.Utils.DataType;

// Primary: sales
var primaryTable = new Table("Sales");
primaryTable.Columns.Add(new TableColumn("ProductId", DataType.ETypeCode.Int32,  TableColumn.EDeltaType.NaturalKey));
primaryTable.Columns.Add(new TableColumn("Amount",    DataType.ETypeCode.Decimal, TableColumn.EDeltaType.TrackingField));
primaryTable.AddRow(10, 99.50m);
primaryTable.AddRow(20, 149.00m);
primaryTable.AddRow(10, 79.99m);

// Reference: product lookup
var productTable = new Table("Products");
productTable.Columns.Add(new TableColumn("ProductId",   DataType.ETypeCode.Int32,  TableColumn.EDeltaType.NaturalKey));
productTable.Columns.Add(new TableColumn("ProductName", DataType.ETypeCode.String,  TableColumn.EDeltaType.TrackingField));
productTable.AddRow(10, "Widget A");
productTable.AddRow(20, "Widget B");

var primary   = new ReaderMemory(primaryTable);
var reference = new ReaderMemory(productTable);

var joinMappings = new Mappings(passThroughColumns: true);
joinMappings.Add(new MapJoin(
    new TableColumn("ProductId", DataType.ETypeCode.Int32),   // primary key
    new TableColumn("ProductId", DataType.ETypeCode.Int32))); // reference key

var join = new TransformJoin(
    primary, reference, joinMappings,
    EJoinStrategy.Auto,
    EDuplicateStrategy.First,
    EJoinNotFoundStrategy.NullJoin,
    joinSortField: null,
    tableAlias: "Products");

await join.Open(0, null, CancellationToken.None);
Console.WriteLine($"Join algorithm: {join.JoinAlgorithm}");

while (await join.ReadAsync())
    Console.WriteLine($"Amount={join["Amount"]}, Product={join["ProductName"]}");
// Output:
// Join algorithm: Hash
// Amount=99.50,  Product=Widget A
// Amount=149.00, Product=Widget B
// Amount=79.99,  Product=Widget A
```

--------------------------------

### Sort Data with TransformSort

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Sorts the entire dataset in memory if no upstream sort is available, or passes through if the required sort is already satisfied.

```csharp
using dexih.transforms;
using dexih.functions;
using dexih.functions.Query;

var source = new ReaderMemory(salesTable);

// Sort descending by Amount
var sort = new TransformSort(
    source,
    new Sorts { new Sort(new TableColumn("Amount"), ESortDirection.Descending) });

await sort.Open(0, null, CancellationToken.None);

while (await sort.ReadAsync())
    Console.WriteLine($"{sort["Product"]}: {sort["Amount"]}");
// Output:
// Widget B: 149.00
// Widget A: 99.50
// Widget A: 79.99

```

--------------------------------

### Perform Per-Row Remote Lookup with TransformLookup

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Use TransformLookup to call a reference data source for each primary row. It supports on-demand caching to optimize repeated lookups. Ensure the reference connection supports direct lookup capabilities.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using Dexih.Utils.DataType;

// reference connection supports direct lookup (e.g. ConnectionSqlite)
Transform lookupSource = referenceConnection.GetTransformReader(productTable, true);

var lookupMappings = new Mappings(passThroughColumns: true);
lookupMappings.Add(new MapJoin(
    new TableColumn("ProductId", DataType.ETypeCode.Int32),
    new TableColumn("ProductId", DataType.ETypeCode.Int32)));

var lookup = new TransformLookup(
    primary, lookupSource, lookupMappings,
    EDuplicateStrategy.First,
    EJoinNotFoundStrategy.NullJoin,
    tableAlias: "Products");

await lookup.Open(0, null, CancellationToken.None);
while (await lookup.ReadAsync())
    Console.WriteLine($"Sale {lookup["SaleId"]} => {lookup["ProductName"]}");

```

--------------------------------

### Analytic Aggregation with TransformAggregate

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

TransformAggregate computes aggregate values while preserving all original rows, appending aggregates as new columns. Set GroupRows = false for pass-through behavior.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using Dexih.Utils.DataType;

var source = new ReaderMemory(salesTable);

var mappings = new Mappings(passThroughColumns: true) { GroupRows = false };

// Group partition by Product
mappings.Add(new MapGroup(new TableColumn("Product", DataType.ETypeCode.String)));

// Running total per product group
mappings.Add(new MapAggregate(
    new TableColumn("Amount",     DataType.ETypeCode.Decimal),
    new TableColumn("ProductSum", DataType.ETypeCode.Decimal),
    EAggregate.Sum));

var agg = new TransformAggregate(source, mappings);
await agg.Open(0, null, CancellationToken.None);

while (await agg.ReadAsync())
    Console.WriteLine($"Row: {agg["SaleId"]} | Product={agg["Product"]} | Amount={agg["Amount"]} | ProductSum={agg["ProductSum"]}");
// Output:
// Row: 1 | Product=Widget A | Amount=99.50  | ProductSum=179.49
// Row: 3 | Product=Widget A | Amount=79.99  | ProductSum=179.49
// Row: 2 | Product=Widget B | Amount=149.00 | ProductSum=149.00
```

--------------------------------

### Union Two Data Streams with TransformConcatenate

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

TransformConcatenate vertically stacks two streams with compatible schemas using a UNION ALL operation. If both streams are sorted on the same key, it performs a sorted merge.

```csharp
using dexih.transforms;

var jan = new ReaderMemory(januarySales);  // Table with same columns as salesTable
var feb = new ReaderMemory(februarySales);

var concat = new TransformConcatenate(jan, feb);
await concat.Open(0, null, CancellationToken.None);

var total = 0;
while (await concat.ReadAsync()) total++;
Console.WriteLine($"Combined rows: {total}");

```

--------------------------------

### Read Records from Transform Object

Source: https://github.com/dataexperts/dexih.transforms/blob/master/README.md

Iterates through records from a Transform object, printing field names and values. Ensure the transform object is properly initialized before use.

```csharp
while(await transform.ReadAsync())
{
    for(int i =0; i < transform.FieldCount; i++)
    {
        Console.WriteLine("Field: {0}, Value: {1}", transform.GetName(i), transform[i].ToString());
    }
}
```

--------------------------------

### Enforce Data Quality with TransformValidation

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

TransformValidation tests rows against column-level rules using ValidationFunctions. Invalid rows can be corrected or routed to a reject table. Configure rules using MapValidation and specify validation behavior like validateDataTypes.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.BuiltIn;
using Dexih.Utils.DataType;

var source = new ReaderMemory(salesTable);

var valMappings = new Mappings(passThroughColumns: true);

// Rule: Product must not be blank; default to "Unknown"
valMappings.Add(new MapValidation(
    new TransformFunction(
        typeof(ValidationFunctions),
        nameof(ValidationFunctions.CleanBlankString),
        new[] { new TableColumn("Product", DataType.ETypeCode.String) },
        new TableColumn("Product", DataType.ETypeCode.String)),
    new object[] { null, "Unknown" }));

// Rule: Amount max length check (treated as string for demo)
valMappings.Add(new MapValidation(
    new TransformFunction(
        typeof(ValidationFunctions),
        nameof(ValidationFunctions.MaxLength),
        new[] { new TableColumn("Product", DataType.ETypeCode.String) },
        new TableColumn("Product", DataType.ETypeCode.String)),
    new object[] { null, 50 }));

var validation = new TransformValidation(source, valMappings, validateDataTypes: true);
await validation.Open(0, null, CancellationToken.None);

while (await validation.ReadAsync())
{
    if ((string)validation["ValidationStatus"] == "Rejected")
        Console.WriteLine($"REJECTED: {validation["RejectReason"]}");
    else
        Console.WriteLine($"OK: {validation["Product"]}");
}
Console.WriteLine($"Rejected rows: {validation.TransformRowsRejected}");

```

--------------------------------

### Stream CSV and JSON Data

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Use StreamCsv and StreamJson to convert DbDataReader output to streams for HTTP responses or file exports. Ensure the reader is opened before use.

```csharp
using dexih.transforms;
using dexih.functions.Query;

// Build your transform chain
var reader = new TransformSort(new ReaderMemory(salesTable), "Amount", ESortDirection.Descending);
await reader.Open(0, null, CancellationToken.None);

// --- CSV export to file ---
var csvStream = new StreamCsv(reader);
using var file = File.Create("/tmp/sales_export.csv");
await csvStream.CopyToAsync(file);

// --- JSON export (re-open or use a fresh chain) ---
var readerJson = new ReaderMemory(salesTable);
await readerJson.Open(0, null, CancellationToken.None);

var jsonStream = new StreamJson(readerJson);
// pipe into an ASP.NET Core response:
// Response.ContentType = "application/json";
// await jsonStream.CopyToAsync(Response.Body);

Console.WriteLine("Export complete.");
```

--------------------------------

### Filter Rows with TransformFilter

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

Removes rows that do not meet specified filter conditions. Conditions that can be translated to SQL WHERE clauses are pushed down automatically.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.Query;

var source = new ReaderMemory(salesTable);

var mappings = new Mappings(passThroughColumns: true);
// Keep only rows where Amount > 90
mappings.Add(new MapFilter(
    new TableColumn("Amount", DataType.ETypeCode.Decimal),
    90m,
    ECompare.GreaterThan));

var filter = new TransformFilter(source, mappings);
await filter.Open(0, null, CancellationToken.None);

while (await filter.ReadAsync())
    Console.WriteLine($"Passed: {filter["Product"]} = {filter["Amount"]}");
// Output:
// Passed: Widget A = 99.50
// Passed: Widget B = 149.00

Console.WriteLine($"Rows filtered out: {filter.TransformRowsFiltered}");
// Output: Rows filtered out: 1

```

--------------------------------

### Pivot/Expand Rows with TransformRows

Source: https://context7.com/dataexperts/dexih.transforms/llms.txt

TransformRows uses row-generating functions to transform a single input row into multiple output rows. This is useful for expanding array or JSON data into separate rows. Ensure the necessary function types are imported.

```csharp
using dexih.transforms;
using dexih.transforms.Mapping;
using dexih.functions;
using dexih.functions.BuiltIn;
using Dexih.Utils.DataType;

// Source has a column "Tags" containing comma-separated values: "a,b,c"
var rowMappings = new Mappings(passThroughColumns: true);
rowMappings.Add(new MapFunction(
    new TransformFunction(
        typeof(RowFunctions),
        nameof(RowFunctions.SplitColumnToRows),
        new[] { new TableColumn("Tags", DataType.ETypeCode.String) },
        new TableColumn("Tag", DataType.ETypeCode.String)),
    Array.Empty<object>()));

var rows = new TransformRows(source, rowMappings);
await rows.Open(0, null, CancellationToken.None);

while (await rows.ReadAsync())
    Console.WriteLine(rows["Tag"]);
// Output: a  b  c   (one per row)

```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.