Desktop MIS
Digital Evidence Centre
Dynamic multi-join query builder across 12+ dataset types with WHO growth classification and metadata-driven export. 76 C# files, 10,600 LOC.
This application is part of the MFB MIS Ecosystem — a suite of five cross-platform applications forming a complete data pipeline for health program monitoring.
Overview
Desktop data management system for a national child nutrition surveillance program, syncing field-collected data from mobile devices to a local SQLite database with multi-table query building, WHO child growth standard classification, and Excel/CSV reporting.
The query builder dynamically constructs multi-JOIN SQL strings from server-defined metadata, resolving N location tables, proxy data tables, review tables, and computed columns at runtime. The system manages 12+ distinct dataset types across an 8-level geographic hierarchy.
Export uses metadata-driven column definitions with type-specific transformations: null replacement, function computation, column type conversion, list-value expansion, date formatting, and column renaming — all via batch SQL UPDATE statements on temporary tables.
Hard Problems
Challenge
Each dataset has a different hierarchical location path. Queries must dynamically join N location tables, N proxy data tables, and computed columns from server-defined metadata.
Solution
MyQuerries class reads form metadata (NodeForm, NodeLocPath, NodeLocTable) at runtime to build multi-JOIN SQL strings. Proxy location tables resolved via view creation. Composite IDs generated inline.
Challenge
Export columns are defined by server-side metadata — each may be a raw value, date, list, computed function, or image reference.
Solution
ExportHandler iterates NodeDbs export definitions, resolves to NodeCia, applies type-specific transformations in sequence via batch SQL UPDATE on temporary export tables.
Key Decisions
| Decision | Chose | Over | Because |
|---|---|---|---|
| Query construction | Dynamic SQL string building | ORM/LINQ queries | Schema is server-defined at runtime via metadata tables — queries must adapt to arbitrary form configurations. |
| Database layer | SQLite (local) + PHP/MySQL (remote) | Direct MySQL connection | Offline-first architecture; field workers in rural Bangladesh have intermittent connectivity. |