Accessing Legacy Data (Fox pro) with XPO Using a Custom ODBC Provider

Accessing Legacy Data (Fox pro) with XPO Using a Custom ODBC Provider

by | Dec 23, 2025 | ADO, ADO.NET, XPO

One of the recurring challenges in real-world systems is not building new software — it’s
integrating with software that already exists.

Legacy systems don’t disappear just because newer technologies are available. They survive because they work,
because they hold critical business data, and because replacing them is often risky, expensive, or simply not allowed.

This article explores a practical approach to accessing legacy data using XPO by leveraging ODBC,
not as a universal abstraction, but as a bridge when no modern provider exists.

The Reality of Legacy Systems

Many organizations still rely on systems built on technologies such as:

  • FoxPro tables
  • AS400 platforms
  • DB2-based systems
  • Proprietary or vendor-abandoned databases

In these scenarios, it’s common to find that:

  • There is no modern .NET provider
  • There is no ORM support
  • There is an ODBC driver

That last point is crucial. ODBC often remains available long after official SDKs and providers have disappeared.
It becomes the last viable access path to critical data.

Why ORMs Struggle with Legacy Data

Modern ORMs assume a relatively friendly environment: a supported database engine, a known SQL dialect,
a compatible type system, and an actively maintained provider.

Legacy databases rarely meet those assumptions. As a result, teams are often forced to:

  • Drop down to raw SQL
  • Build ad-hoc data access layers
  • Treat legacy data as a second-class citizen

This becomes especially painful in systems that already rely heavily on DevExpress XPO for persistence,
transactions, and domain modeling.

ODBC Is Not Magic — and That’s the Point

ODBC is often misunderstood.

Using ODBC does not mean:

  • One provider works for every database
  • SQL becomes standardized
  • Type systems become compatible

Each ODBC-accessible database still has:

  • Its own SQL dialect
  • Its own limitations
  • Its own data types
  • Its own behavioral quirks

ODBC simply gives you a way in. It is a transport mechanism, not a universal language.

What an XPO ODBC Provider Really Is

When you implement an XPO provider on top of ODBC, you are not building a generic solution for all databases.

You are building a targeted adapter for a specific legacy system that happens to be reachable via ODBC.

This matters because ODBC is used here as a pragmatic trick:

  • To connect to something you otherwise couldn’t
  • To reuse an existing, stable access path
  • To avoid rewriting or destabilizing legacy systems

The database still dictates the SQL dialect, supported features, and type system. Your provider must respect those constraints.

Why XPO Makes This Possible

XPO is not just an ORM — it is a provider-based persistence framework.

All SQL-capable XPO providers are built on top of a shared foundation, most notably:

ConnectionProviderSql
https://docs.devexpress.com/CoreLibraries/DevExpress.Xpo.DB.ConnectionProviderSql

This architecture allows you to reuse XPO’s core benefits:

  • Object model
  • Sessions and units of work
  • Transaction handling
  • Integration with domain logic

While customizing what legacy systems require:

  • SQL generation
  • Command execution
  • Schema discovery
  • Type mapping

Dialects and Type Systems Still Matter

Even when accessed through ODBC:

  • FoxPro is not SQL Server
  • DB2 is not PostgreSQL
  • AS400 is not Oracle

Each system has its own:

  • Date and time semantics
  • Numeric precision rules
  • String handling behavior
  • Constraints and limits

An XPO ODBC provider must explicitly map database types, handle dialect-specific SQL,
and avoid assumptions about “standard SQL.” ODBC opens the door — it does not normalize what’s inside.

Real-World Experience: AS400 and DB2 in Production

This approach is not theoretical. Last year, we implemented a custom XPO provider using ODBC for
AS400 and DB2 systems in Mexico, where:

  • No viable modern .NET provider existed
  • The systems were deeply embedded in business operations
  • ODBC was the only stable integration path

By introducing an XPO provider on top of ODBC, we were able to integrate legacy data into a modern .NET architecture,
preserve domain models and transactional behavior, and avoid rewriting or destabilizing existing systems.

The Hidden Advantage: Modern UI and AI Access

Once legacy data is exposed through XPO, something powerful happens: that data becomes immediately available to modern platforms.

  • Blazor applications
  • .NET MAUI mobile and desktop apps
  • Background services
  • Integration APIs
  • AI agents and assistants

And you get this without rewriting the database, migrating the data, or changing the legacy system.
XPO becomes the adapter that allows decades-old data to participate in modern UI stacks, automated workflows,
and AI-driven experiences.

Why Not Just Use Raw ODBC?

Raw ODBC gives you rows, columns, and primitive values. XPO gives you domain objects, identity tracking,
relationships, transactions, and a consistent persistence model.

The goal is not to modernize the database. The goal is to modernize access to legacy data
so it can safely participate in modern architectures.

Closing Thought

An XPO ODBC provider is not a silver bullet. It will not magically unify SQL dialects, type systems, or database behavior.

But when used intentionally, it becomes a powerful bridge between systems that cannot be changed
and architectures that still need to evolve.

ODBC is the trick that lets you connect.
XPO is what makes that connection usable — everywhere, from Blazor UIs to AI agents.

ODBC: A Standard That Was Never Truly Neutral

ODBC: A Standard That Was Never Truly Neutral

by | Dec 23, 2025 | ADO, ADO.NET, C#

When I started working with computers, one of the tools that shaped my way of thinking as a developer was FoxPro.
At the time, FoxPro felt like a complete universe: database engine, forms, reports, and business logic all integrated into a single environment.

Looking back, FoxPro was effectively an application framework from the past—long before that term became common.

Accessing FoxPro data usually meant choosing between two paths:

  1. Direct FoxPro access – fast, tightly integrated, and fully aware of FoxPro’s features
  2. ODBC – a standardized way to access the data from outside the FoxPro ecosystem

This article focuses on that second option.

What Is ODBC?

ODBC (Open Database Connectivity) is a standardized API for accessing databases.
Instead of applications talking directly to a specific database engine, they talk to an ODBC driver,
which translates generic database calls into database-specific commands.

The promise was simple:

One API, many databases.

And for its time, this was revolutionary.

Supported Operating Systems and Use Cases

ODBC is still relevant today and supported across major platforms:

  • Windows – native support, mature tooling
  • Linux – via unixODBC and vendor drivers
  • macOS – supported through driver managers

Typical use cases include:

  • Legacy systems that must remain stable
  • Reporting and BI tools
  • Data migration and ETL pipelines
  • Cross-vendor integrations
  • Long-lived enterprise systems

ODBC excels where interoperability matters more than elegance.

The Lowest Common Denominator Problem

Although ODBC is a standard, it does not magically unify databases.

Each database has its own:

  • SQL dialect
  • Data types
  • Functions
  • Performance characteristics

ODBC standardizes access, not behavior.

You can absolutely open an ODBC connection and still:

  • Call native database functions
  • Use vendor-specific SQL
  • Rely on engine-specific behavior

This makes ODBC flexible—but not truly database-agnostic.

ODBC vs True Abstraction Layers

This is where ODBC differs from ORMs or persistence frameworks that aim for full abstraction.

  • ODBC: Gives you a common door and does not prevent database-specific usage
  • ORM-style frameworks: Try to hide database differences and enforce a common conceptual model

ODBC does not protect you from database specificity—it permits it.

ODBC in .NET: Avoiding Native Database Dependencies

This is an often-overlooked advantage of ODBC, especially in .NET applications.

ADO.NET is interface-driven:

  • IDbConnection
  • IDbCommand
  • IDataReader

However, each database requires its own concrete provider:

  • SQL Server
  • Oracle
  • DB2
  • Pervasive
  • PostgreSQL
  • MySQL

Each provider introduces:

  • Native binaries
  • Vendor SDKs
  • Version compatibility issues
  • Deployment complexity

Your code may be abstract — your deployment is not.

ODBC as a Binary Abstraction Layer

When using ODBC in .NET, your application depends on one provider only:

System.Data.Odbc

Database-specific dependencies are moved:

  • Out of your application
  • Into the operating system
  • Into driver configuration

This turns ODBC into a dependency firewall.

Minimal .NET Example: ODBC vs Native Provider

Native ADO.NET Provider (Example: SQL Server)

using System.Data.SqlClient;

using var connection =
    new SqlConnection("Server=.;Database=AppDb;Trusted_Connection=True;");

connection.Open();

Implications:

  • Requires SQL Server client libraries
  • Ties the binary to SQL Server
  • Changing database = new provider + rebuild

ODBC Provider (Database-Agnostic Binary)

using System.Data.Odbc;

using var connection =
    new OdbcConnection("DSN=AppDatabase");

connection.Open();

Implications:

  • Same binary works for SQL Server, Oracle, DB2, etc.
  • No vendor-specific DLLs in the app
  • Database choice is externalized

The SQL inside the connection may still be database-specific — but your application binary is not.

Trade-Offs (And Why They’re Acceptable)

Using ODBC means:

  • Fewer vendor-specific optimizations
  • Possible performance differences
  • Reliance on driver quality

But in exchange, you gain:

  • Simpler deployments
  • Easier migrations
  • Longer application lifespan
  • Reduced vendor lock-in

For many enterprise systems, this is a strategic win.

What’s Next – Phase 2: Customer Polish

Phase 1 is about making it work.
Phase 2 is about making it survivable for customers.

In Phase 2, ODBC shines by enabling:

  • Zero-code database switching
  • Cleaner installers
  • Fewer runtime surprises
  • Support for customer-controlled environments
  • Reduced friction in on-prem deployments

This is where architecture meets reality.

Customers don’t care how elegant your abstractions are — they care that your software runs on their infrastructure without drama.

Project References

Minimal and explicit:

System.Data
System.Data.Odbc

Optional (native providers, when required):

System.Data.SqlClient
Oracle.ManagedDataAccess
IBM.Data.DB2

ODBC allows these to become optional, not mandatory.

Closing Thought

ODBC never promised purity.
It promised compatibility.

Just like FoxPro once gave us everything in one place, ODBC gave us a way out — without burning everything down.

Decades later, that trade-off still matters.