Database drivers, ADO.NET and Redis - talking to databases and caches safely

How .NET talks to a database through ADO.NET - pooled connections, commands, parameters, readers and transactions - why parameters stop SQL injection, how one driver contract covers PostgreSQL, SQL Server and SQLite, how Redis stores, expires and evicts data, why SCAN beats KEYS, what a multiplexer is, and how to run a query you did not write without letting it change anything.

In short

Every program that keeps data talks to its database through a driver - a library that speaks that database's network protocol. In .NET, the drivers for PostgreSQL, SQL Server and SQLite share one shape called ADO.NET: a connection borrowed from a pool, a command with its parameters, a data reader and a transaction. Redis is a different kind of store: an in-memory server of typed keys that can expire, reached from .NET through StackExchange.Redis. {{brand}} uses all of them, and its SQL Tester and Cache Tester point them at a team's own database or cache - so they show how to do it with care.

After this lesson you can Write correct ADO.NET code with pooled connections, parameters, readers and transactions. Explain SQL injection, and why a rollback is a safety net and not a wall. Design one driver contract for several databases. Use Redis well: the right data type, a TTL on every cached key, SCAN instead of KEYS, one shared multiplexer and a hit rate you can read.

Words you need

How ADO.NET talks to a database

Whatever the database, an ADO.NET conversation has the same seven steps. Learn them once and you can read code for any driver, and you will understand what EF Core and Dapper do for you underneath.

Output values arrive last SQL Server sends a procedure's output parameters and its return value after all the rows. Read them once the reader is closed - inside the reading loop they are still empty. Code that reads every result set first and the outputs afterwards works on every driver.

Connection pooling, inside

Opening a real connection is slow: a TCP handshake, a TLS handshake and a login, often tens of milliseconds - far longer than a simple query. So every ADO.NET driver keeps a connection pool. Your code still writes open, use, dispose around every piece of work; the driver quietly turns that into borrow and return.

Read a load test with the handshake in mind Any load test against a database pays for the connection handshake on its first request, because the pool is empty. Read the slowest time with that in mind. {{brand}}'s own data reaches PostgreSQL through EF Core on Npgsql's pool.

Transactions: all or nothing

A transaction groups statements so that they succeed or fail as one. Databases keep that promise with a write-ahead log and locks; you meet it as four letters, ACID.

If a transaction is disposed without CommitAsync, the driver rolls it back. Do not rely on that for clarity, but know it is the safety net.

{{brand}}'s SQL Tester rolls back by default A test is a question, not a change, so {{brand}}'s SQL Tester runs statements inside a transaction that it rolls back by default. A rollback is a safety net, not a wall: teams should still give a tester a read-only login, the one wall no statement can climb.

SQL injection and parameters

SQL injection is still one of the most common security bugs - injection has a place in the OWASP Top 10. It happens when input becomes part of the SQL text. Parameters fix it at the root: the text and the values travel separately, so a value can never turn into SQL.

Why it is safe: PostgreSQL's extended protocol sends Parse (the text with numbered placeholders), then Bind (the values), then Execute - the parser has finished before any value arrives. SQL Server sends a parameterized command as a remote procedure call with typed values, the same thing sp_executesql does. A bonus: the server can reuse the plan of the same text with new values.

Parameters protect values, not statements A tool that runs SQL a person wrote cannot parameterize the statement - the statement is the input. That is why such tools need other layers: who may run what, where it may connect, what the text may contain, and a transaction around it. If your own design writes parameter names into the SQL text, validate them as plain identifiers first.

How Redis works

Redis keeps every key in memory and executes commands on one main thread, one after another. That makes each command atomic and very fast - usually well under a millisecond - and it also means one slow command makes every other client wait. Clients talk to it over TCP with a simple protocol called RESP.

When many requests miss at once If a popular key expires, hundreds of requests can miss together and all ask the database: a cache stampede. Spread expiry times with a little random jitter, let one request rebuild the value while the others wait, or refresh a hot key shortly before it expires. Also cache "not found" answers for a short time, so requests for ids that do not exist do not hit the database every time.

An application that runs several copies needs a shared cache such as Redis; with one copy, its own memory is enough. {{brand}} follows the same pattern for its own cache: when the cache cannot be reached, requests become slower, not failed.

The options in the market

What {{brand}} chose, and why

The libraries behind those choices:

The trade-off accepted More hand-written code and more care: every connection disposed, every vendor's errors mapped into one list, and a database is supported only once someone writes its driver. Choose Dapper for an application whose result shapes you know, EF Core for your own domain model, and Valkey if an open licence matters more than the newest Redis features.

One contract, many databases: the driver pattern

The adapter pattern puts one interface between your code and the vendors. The calling code asks to run a request; each adapter translates that into its own library. ADO.NET already makes half of the work vendor-neutral, so a good design writes the shared part once in a base class and lets each vendor fill in only what really differs.

Error codes are a vendor's real API Map SQLSTATE codes (PostgreSQL: 23505 unique violation, 40P01 deadlock) and SQL Server error numbers (2627 unique key, 1205 deadlock) into one list of your own. Then expecting a constraint violation means the same thing on every database.

Reading a Redis server without hurting it

Because Redis runs one command at a time, the most dangerous command is a long one. KEYS pattern walks every key in the database before it answers - on a server with ten million keys that can freeze it for seconds. SCAN does the same walk in small pages, so other clients keep being served in between.

INFO returns the server's health as text sections - server, clients, memory, persistence, stats, replication, keyspace. A few numbers tell most of the story, and the hit rate is the one every cache owner should know.

Where {{brand}} uses it Cache Tester browses keys with SCAN and fetches the type and TTL of a whole page of keys in one batch, so looking at a busy cache does not slow it down.

Running a query you did not write

A SQL testing tool, an admin console and an AI assistant that writes queries all face the same problem: the statement is untrusted input. No single check is enough, so safe systems stack layers - each one cheap, each one catching what the others miss.

Text filters are never complete, which is why the last layer is a read-only login.

When an AI assistant writes the query, use an allow-list rather than a deny-list: accept one statement that starts with SELECT or WITH and contains nothing that writes, locks, waits or reaches outside the database, and still run it in a rolled-back transaction.

Try it yourself: a drivers lab in an hour

All you need is Docker and the .NET SDK. You will see a rollback undo an insert, an injection return every row while a parameter returns none, and Redis report its hit rate.

Go one step further Run docker exec -it redis redis-cli --latency in a second terminal, then docker exec -it redis redis-cli keys '*' in a third, and watch the latency jump while KEYS walks every key. With 180,000 keys expect a spike of tens of milliseconds; with millions of keys it is seconds. Then run the program's SCAN loop and watch the latency stay flat. You have just seen why KEYS is banned on production servers.

Common mistakes

Interview questions

Related