Paradox.Data.ParadoxClient 1.0.0.26270

Paradox.Data.ParadoxClient

A fully managed ADO.NET data provider for Paradox tables, shaped after Microsoft.Data.SqlClient. No BDE, no ODBC, no native code: the .DB, .MB, .PX and .PXI files are read and written directly.

Targets classical .net frameworks, netstandard2.0 and netstandard2.1.

using Paradox.Data.ParadoxClient;

using var cn = new ParadoxConnection(@"Data Source=C:\Data\Tables");
cn.Open();

using var cmd = new ParadoxCommand(
    @"SELECT c.Name, SUM(o.Total) AS Spend
      FROM Customers c INNER JOIN Orders o ON o.CustNo = c.CustNo
      WHERE c.State = @st
      GROUP BY c.Name
      ORDER BY Spend DESC", cn);

cmd.Parameters.AddWithValue("@st", "CA");

using var r = cmd.ExecuteReader();
while (r.Read())
{
    Console.WriteLine($"{r.GetString(0)}: {r.GetDecimal(1):C}");
}

The data source is a directory

Paradox has no server and no database file. Data Source names a folder; every .DB in it is a table, the way a Paradox working directory is a database.

What the SQL supports

Queries — SELECT with columns, *, aliases, DISTINCT, TOP n / LIMIT n; INNER, LEFT, RIGHT and CROSS JOIN, and the comma form; WHERE with AND OR NOT, comparisons, BETWEEN, IN, LIKE … ESCAPE, IS [NOT] NULL; GROUP BY … HAVING; COUNT/SUM/AVG/MIN/MAX, including COUNT(DISTINCT x); ORDER BY by expression, alias or position; UNION [ALL]; arithmetic, ||, CASE, CAST, and a library of scalar functions; @name, :name and ? parameters.

Changes — INSERT … VALUES (several rows at once) and INSERT … SELECT; UPDATE … SET … WHERE; DELETE FROM … WHERE.

Structure — CREATE TABLE (with IF NOT EXISTS), DROP TABLE, ALTER TABLE ADD | DROP | ALTER COLUMN, CREATE INDEX, DROP INDEX.

Deliberately out of scope: subqueries, EXISTS, CTEs, window functions, views, stored procedures, and cost-based join reordering. Join order follows the order you wrote.

NULL follows SQL's three-valued logic: WHERE Credit = NULL matches nothing, and NOT (x > 1) does not bring back the rows where x is NULL.

Types

A DECIMAL(p,s) becomes a BCD with s decimal places. ALPHA and BYTES need an explicit length.

Empty is NULL. Paradox marks a NULL field by zeroing its bytes, which means an empty Alpha and a NULL Alpha are the same thing on disk, and a numeric column cannot hold the one value whose encoding is all zeros. That is the format, not a choice this provider made.

Transactions

Paradox has no transactions. BeginTransaction gives you one anyway, built on an undo journal: the files a statement is about to change are copied aside as .pxj first, and Rollback puts them back. A journal still on disk when a connection opens is the trace of a session that died mid-write, and is rolled back before anything else happens.

Only IsolationLevel.Serializable is offered, because that is what taking the files for yourself amounts to. Journalling copies whole files, so a transaction over a very large table costs a copy of it; Journal=false turns that off and gives up rollback with it.

Indexes

CREATE INDEX records the index in a .PXI sidecar of this provider's own, and the planner then uses it: an equality on the indexed columns becomes an index seek instead of a scan. The primary key declared in the table's own header is used the same way, with no sidecar needed. ParadoxCommand.ExplainPlan() shows which was chosen.

This provider does not write Paradox's own .Xnn/.Ynn secondary index files. Their record layout is the least documented part of the format, and a file that Paradox misreads is worse than no file at all. Existing ones are reported by GetSchema("Indexes") and otherwise left alone; the .DB is never touched by any of this, so a .PXI can be deleted at any time and costs only speed.

After any write to a keyed table, the header's index out of date flag is set, which is how Paradox is told to rebuild its own .PX. This provider ignores a stale .PX rather than trusting it.

Encryption

An encrypted table is decrypted from a key held in its own header, so the password is not needed to read the data — it is only the check Paradox performs before allowing access. This provider performs the same check, and Ignore Password=true skips it.

The password hash is the one piece of the scheme that has not been confirmed against a real encrypted table, which is why the escape hatch exists. It cannot corrupt anything: a wrong hash can only refuse a correct password.

Concurrency

Locking maps onto file sharing, and that is all it does. This provider does not implement Borland's PDOXUSRS.NET protocol, so it cannot coordinate with a running BDE application writing the same tables. Reading alongside one is fine; writing alongside one is not supported.

Verifying against your own files

Every constant in the file format here is reverse-engineered, and the unit tests exercise it by round-tripping through this provider's own reader and writer. That catches a self-inconsistent implementation but not a shared misreading of the format: if the reader and the writer are wrong in the same way, they agree with each other perfectly and both are wrong.

Only a file this provider did not write can settle that, so there is an integration suite that runs against real Paradox tables. It finds nothing on a machine with no Paradox data, and says so in the test output rather than passing quietly.

To run it against your data, point it at the directory and run the tests:

set PARADOX_FIXTURES=C:\Legacy\Accounts;D:\Archive\Paradox
dotnet test

Semicolon-separated, searched recursively. Files that only share the .DB extension — SQLite databases, thumbnail caches — are recognised and skipped. Originals are never written to: every discovered table is opened read only, and the two tests that need to write copy the table and its companions to a temporary directory first, so pointing this at live data is safe.

Alternatively drop the files into Paradox.Data.ParadoxClient.Tests/Fixtures, which has a README covering the details.

What it checks, and which open question each one closes:

The last one is the only check that can catch a value being read consistently but wrongly, because it is the only one whose answers did not come from this provider. Put the row counts, column types and a few known cell values — taken from Paradox itself — into an expectations.json beside the fixtures.

On a CI job that is supposed to have the data, set PARADOX_REQUIRE_FIXTURES=1 so that finding none fails the build instead of passing.

Licence

MIT.