Repository navigation
ADO.NET: Database-independent way to generate parameter placeholders #25022
Description
In theory, parameter placeholders are an entirely database-specific details - there is no way to write a single SQL containing parameters, and then execute it on different databases.
In practice, it seems that most ADO.NET have aligned on the @name convention. This is quite a problem, since for databases which don't support it natively, the ADO.NET provider must parse and replace the @name placeholder with the one accepted by the database. For example, PostgreSQL uses positional placeholders ($1, $2...), which by the way are also specific to individual statements in a batch (parameters are per-statement, not per-batch).
We could design an API where the provider would generate the placeholder. This could be as simple as adding a Placeholder property on DbParameter, which each provider would implement as necessary. The user would then interpolate the placeholder into the CommandText. For positional placeholders, the logic would typically calculate the placeholder as the DbParameter is inserted into a DbParameterCollection, whereas for named placeholders the ParameterName would be returned.
PS Parameter placeholder translation is one of two things forcing Npgsql to parse the CommandText - the other is the lack of a proper batching API (#3688), which requires to parse and split on semicolons.
Activity
roji commentedon Feb 12, 2018
/cc @divega @ajcvickers @saurabh500
divega commentedon Feb 12, 2018
System.Data Triage: this seems to be a good idea. Once we prototype the API we can try using it in EF Core and other components that generate SQL.
Moving to future.
roji commentedon Feb 7, 2019
Note: this should take into account that some providers don't use placeholders, but rather render values literally into the command text (e.g. MySqlConnector in text mode, @bgrainger).
roji commentedon Feb 13, 2019
Continuing the above, there are three kinds of placeholders (not sure the naming is good):
DbParameter.ParameterNamein the command's parameter list. Example:@cityin SQL Server.$2in PostgreSQL.?in ODBC.Our API would need to somehow let consumers know whether placeholders are implicit or not, otherwise they can't know whether they can reuse the same placeholder or not.
Note the DataSourceInformation schema collection which has
ParameterMarkerFormat, which provides some of the information here (i.e. we can know whether the placeholders are explicit or implicit). This could be useful for a backwards-compatibility shim (assuming it is well-implemented).Finally, even if placeholder generation is taken over by the provider, the user should still have the option of providing an arbitrary string name. If the database supports named placeholders, that name could be used (better for logging etc). If it doesn't, the name would be ignored.
/cc @divega @ajcvickers @bricelam
roji commentedon Aug 15, 2023
Keep this open for possible future work.